OLAP：CUBE 运算符

用 SQL 进行数据驱动决策

Irene Ortner

Data Scientist at Applied Statistics

OLAP 简介

  • OLAP：联机分析处理
  • 聚合数据，获得更清晰概览
    • 统计每位客户的租片次数。
    • 计算各国家、各类型的电影平均评分。
  • 生成数据透视表展示聚合结果
用 SQL 进行数据驱动决策

表 renting_extended

| renting_id | country | genre  | rating |
|------------|---------|--------|--------|
| 2          | Belgium | Drama  | 10     |
| 32         | Belgium | Drama  | 10     |
| 203        | Austria | Drama  | 6      |
| 292        | Austria | Comedy | 8      |
| 363        | Belgium | Drama  | 7      |
| .......... | ....... | .....  | ...... |
用 SQL 进行数据驱动决策

数据透视表：电影租赁次数

 

 

用 SQL 进行数据驱动决策

数据透视表与 SQL 输出

用 SQL 进行数据驱动决策

GROUP BY CUBE

SELECT country, 
       genre, 
       COUNT(*)
FROM renting_extended
GROUP BY CUBE (country, genre);

| country  | genre  | count |
|----------|--------|-------|
| Austria  | Comedy | 2     |
| Belgium  | Drama  | 15    |
| Austria  | Drama  | 4     |
| Belgium  | Comedy | 1     |
| Belgium  | null   | 16    |
| Austria  | null   | 6     |
| null     | Comedy | 3     |
| null     | Drama  | 19    |
| null     | null   | 22    |
用 SQL 进行数据驱动决策

评分数

SELECT country, 
       genre, 
       COUNT(rating)
FROM renting_extended
GROUP BY CUBE (country, genre);

| country  | genre  | count |
|----------|--------|-------|
| Austria  | Comedy | 1     |
| Belgium  | Drama  | 6     |
| Austria  | Drama  | 2     |
| Belgium  | Comedy | 0     |
| Belgium  | null   | 6     |
| Austria  | null   | 3     |
| null     | Comedy | 1     |
| null     | Drama  | 8     |
| null     | null   | 9     |
用 SQL 进行数据驱动决策

轮到您使用 GROUP BY CUBE 了！

用 SQL 进行数据驱动决策

Preparing Video For Download...