案例 MovieNow
一家虚构的在线影视租赁公司 MovieNow，它拥有电影信息（类型和主演）以及（用户信息，用户评分）。它需要决定：投资于哪些新电影？
BI（商业智能目标）
- 运营决策的数据
- 根据演员的流行度，决定投资哪一部电影。
- 根据上月的收入，辅助决定，短期的投资预算
- 策略决策的数据
- 在哪国更受欢迎，用来决定市场扩张
- 长线的投资及收入
KPI（关键绩效指标）
- 租赁总收入
- 用户满意度：所有电影的平均评分
- 活跃用户总数：用户参与度
OLAP（联机分析处理）
联机分析处理（英语：Online analytical processing），简称OLAP (/ˈoʊlæp/)，是计算机技术中快速解决多维分析问题（英语：multi-dimensional analytical）（MDA）的一种方法。[1] OLAP是更广泛的商业智能范畴的一部分，它还包括关系数据库、报告编写和数据挖掘。[2] OLAP的典型应用包括销售业务报告（英语：business reporting）、市场营销、管理报告、业务流程管理（BPM）[3]、预算和预测、财务报表以及类似领域，新的应用正在出现，如农业。[4] 术语“OLAP”是对传统数据库术语“联机事务处理”（OLTP）稍微修改而成的。[5]
下面主要是上卷（roll-up）和切片（slicing）的例子。
上卷：汇集所有的电影租赁数据、用户评分，查询该租赁网站用户偏好的趋势。
切片：根据不同维度，国家、性别对演员的评分进行筛选查看。
ROLLUP运算符
SELECT
c.country,
m.genre,
AVG(r.rating) AS avg_rating,
COUNT(*) AS num_rating
FROM renting AS r
LEFT JOIN movies AS m
ON m.movie_id = r.movie_id
LEFT JOIN customers AS c
ON r.customer_id = c.customer_id
GROUP BY ROLLUP(c.country, m.genre)
ORDER BY c.country, m.genre;
按照租赁用户所属国家、电影的类型来查看平均评分及评分数目。
相比与 GOURP BY ， ROLLUP 包含了所有电影的平均评分，不同国家不限类型电影的平均评分，不同类型电影的平均评分。
CUBE运算符
SELECT
c.country,
m.genre,
AVG(r.rating) AS avg_rating
FROM renting AS r
LEFT JOIN movies AS m
ON m.movie_id = r.movie_id
LEFT JOIN customers AS c
ON r.customer_id = c.customer_id
GROUP BY CUBE(country, genre);
按照租赁用户所属国家、电影的类型来查看平均评分及评分数目。
ROLLUP 结果是 88条结果，CUBE 得到 96 条结果，多出来的结果，是因为 CUBE 生成所选列的所有合计组合。而 ROLLUP 是生成所选列的层级聚合结果。
ROLLUP (YEAR, MONTH, DAY)
With a ROLLUP, it will have the following outputs:
YEAR, MONTH, DAY
YEAR, MONTH
YEAR
()
With CUBE, it will have the following:
YEAR, MONTH, DAY
YEAR, MONTH
YEAR, DAY
YEAR
MONTH, DAY
MONTH
DAY
()
GROUPING SET运算符
SELECT
nationality,
gender,
COUNT(*)
FROM actors
GROUP BY GROUPING SETS ((nationality), (gender), ());
GROUP SET 最灵活
可以返回不同的 GROUP BY 检索的集合
完整BI例子
问题: 投资哪些新电影？需要考虑，新电影比旧电影更贵。
- 用户对新旧电影的评分差异？
- 不同国家用户之间，对新旧电影的评分，有差异吗？
表联结(Join data)
联结三张表，分别是 renting/customers/movies
SELECT *
FROM renting AS r
LEFT JOIN customers AS c
ON c.customer_id = r.customer_id
LEFT JOIN movies AS m
ON m.movie_id = r.movie_id;
选择相关记录(Select relevant records)
SELECT *
FROM renting AS r
LEFT JOIN customers AS c
ON c.customer_id = r.customer_id
LEFT JOIN movies AS m
ON m.movie_id = r.movie_id
WHERE r.movie_id IN (
SELECT movie_id
FROM renting
GROUP BY movie_id
HAVING COUNT(rating) >= 4)
AND r.date_renting >= '2018-04-01';
聚集数据（Aggregation）
SELECT c.country,
m.year_of_release,
COUNT(*) AS n_rentals,
COUNT(DISTINCT r.movie_id) AS n_movies,
AVG(rating) AS avg_rating
FROM renting AS r
LEFT JOIN customers AS c
ON c.customer_id = r.customer_id
LEFT JOIN movies AS m
ON m.movie_id = r.movie_id
WHERE r.movie_id IN (
SELECT movie_id
FROM renting
GROUP BY movie_id
HAVING COUNT(rating) >= 4)
AND r.date_renting >= '2018-04-01'
GROUP BY ROLLUP (m.year_of_release, c.country)
ORDER BY c.country, m.year_of_release;
按照不同的年份、国家来查看租赁次数、电影总数和平均评分
SELECT genre,
AVG(rating) AS avg_rating,
COUNT(rating) AS n_rating,
COUNT(*) AS n_rentals,
COUNT(DISTINCT m.movie_id) AS n_movies
FROM renting AS r
LEFT JOIN movies AS m
ON m.movie_id = r.movie_id
WHERE r.movie_id IN (
SELECT movie_id
FROM renting
GROUP BY movie_id
HAVING COUNT(rating) >= 3 )
AND r.date_renting >= '2018-01-01'
GROUP BY genre
ORDER BY avg_rating DESC;
筛选条件是 2018 年以后的租赁记录，评分超过 3 次的电影。
根据类型 genre 分组，按照平均评分降序排序
SELECT a.nationality,
a.gender,
AVG(r.rating) AS avg_rating,
COUNT(r.rating) AS n_rating,
COUNT(*) AS n_rentals,
COUNT(DISTINCT a.actor_id) AS n_actors
FROM renting AS r
LEFT JOIN actsin AS ai
ON ai.movie_id = r.movie_id
LEFT JOIN actors AS a
ON ai.actor_id = a.actor_id
WHERE r.movie_id IN (
SELECT movie_id
FROM renting
GROUP BY movie_id
HAVING COUNT(rating) >= 4)
AND r.date_renting >= '2018-04-01'
GROUP BY CUBE(nationality, gender);
哪些国家/性别的演员更受欢迎？
根据国籍、性别，查看演员参演的电影之中，所获得的平均评分、租赁字数、演员数目等等。
参考资料：
DataCamp<Data-Driven Decision Making in SQL>
SQL中ROLLUP、CUBE的用法和区别
understanding-the-differences-between-cube-and-rollup
