SQL多维分析

原创

Yiwenwu

修改于 2024-07-08 10:08:28

4550

修改于 2024-07-08 10:08:28

文章被收录于专栏：SQL查询优化

背景

早在 1993年，关系数据库之父 E.F.Codd[1] 提出了 OLAP 概念，不遗余力指出面向记录的OLTP关系型数据库从根本上不适合查询分析的需求。

OLAP(Online analytical processing) 联机分析处理：是计算机技术中快速解决多维分析问题（MDA, multi-dimensional analytical）的一种方法。OLAP是商业智能BI范畴的一部分，支持用户从多角度交互分析多维数据，可通过拖拽维度(Dimension)来汇总度量(Measure)，即每个度量都有一组维度构成。

OLAP可细分为不同类型，常见类型包括：

ROLAP：Relational OLAP，基于关系型数据库扩展的多维数据集分析操作，基于标准的SQL查询来执行复杂的分析和聚合，例如Spark、Presto系统。该模式基于RDBMS扩展，可应用性广泛，但存在功能受限，大数据量下性能较低。
MOLAP：Multidimensional OLAP，基于多维数据集Cube实现存储和计算分析，例如Apache Kylin、Apache Druid。该模式与OLAP匹配度高，性能较优，但可处理场景单一。
HOLAP：Hybrid OLAP，结合ROLAP和MOLAP的混合体，通常将数据的详细信息存储在关系型数据库中，而将聚合数据存储在多维数据库中。

基本概念

维度

维度(Dimension)：分析数据的特定角度，是用于事实数据分类的结构，常用维度：时间、产品类别、组织等。数据仓库中，维度通常具有包含以下信息：

层次结构(hierarchy)：维度可以包含一个或多个层次结构，层次结构中基于级别(level)描述维度特征的关系和顺序，每一层即为一个级别。例如，在时间维度中，有基于日历的层级结构，一年共四个季度Q1、Q2、Q3、Q4，而每个季度分别有3个月份。因此该层次结构从上往下可分为年份、季度、月份三个级别。
成员(member）：每个级别中有一个或多个成员，例如，季度级别中有四个成员Q1、Q2、Q3、Q4。基于成员可用于分组和汇总数据。
属性(Attributes)：描述维度的额外特征，例如产品维度的价格、销售量特征。属性可用于筛选和约束数据。

多维数据集

OLAP系统的核心是多维数据集，是一种克服关系数据库局限性，可支持快速数据分析的数据结构。基于多维数据集可显示和汇总大量数据，并向用户提供任意数据点的搜索访问，而每个数据集通常有特定的对应用途。

多维分析中数据通常以立方体(Cube)形式存储，Cube可理解为一组多维数据集，即多个维度构成的数据集，可由多个维度中的维度成员交叉形成单元格数据组成。三个维度如下图所示可构成空间结构立方体Cube，更多维度则构成超立方体Hypercube。

分析操作

OLAP由四个基本的分析操作组成[4]：上卷（roll-up）、下钻（drill-down）、切片（slicing）和切块（dicing）、旋转(Pivot)。

上卷

上卷(roll-up)：也被称为合并(consolidation)和聚合(aggregation)，一般会通过两种方式执行上卷：

减少维度
基于层级结构向上汇总，例如月份 → 季度 → 年份的级别聚合。

即上卷是通过删减维度或者级别汇总进行分析数据聚合。如下图展示按照Locations 维度聚合，在上卷过程中，删减了维度的cities级别。将cities城市级别聚合为contry国家级别，因此USA国家的汇总数据为：

2000(U.S.A) = 1560(Los Angeles) + 440(New Jersery)

下钻

下钻(drill-down)：与上卷相反的操作，基于下钻将数据分割为更小的部分。一般会通过两种方式执行下钻：

增加维度
基于层级结构向下拆解，例如年份 → 季度 → 月份的级别拆解

即下钻是通过增加维度或者级别拆解进行分析数据细分。如下图展示增加了月份维度级别，将季度级别拆分为月份级别，Q1可下钻拆分为1月、2月、3月。

切片&切块

切片(slicing)：在多维度中选择一个维度作为条件，产生出新的子Cube多维数据集。如下图展示选择基于时间Time维度进行切片，Time=Q1作为过滤条件并产生新的子Cube，该子Cube的有两个维度：城市Cities、品类Item。

切块(dicing)：与切片操作类似，不同点在于切块会选择2个或以上的维度作为过滤条件。如下图展示切块条件包括：Locations、Time、Item 过滤。

旋转

旋转(Pivot)：基于数据轴(data axes)变换并产生全新可代替的数据表示，可增加数据灵活性和可重组性。转换常用于数据从一种格式变换为另一种格式。如下图展示行转列操作，基于品类item进行旋转。

分析算子

以下将以Spark SQL举例，说明ROLAP中常用的多维分析算子

GROUP BY

GROUP BY 子句通过一组指定的分组表达式对行数据分组，并基于一个或多个聚合函数在对应行进行聚合计算，相同分组条件在结果集中只显示一行记录。

除此之外，Spark还支持通过 GROUPING SETS, CUBE, ROLLUP 子句进行高级聚合，对同一输入记录集进行多重聚合。分组表达式和高级聚合可以在 GROUP BY 子句中混合使用，并嵌套在 GROUPING SETS 子句中。当聚合函数携带 FILTER 子句时，只有匹配的行才会传递给该函数。更多详情可参考Spark SQL官方文档。

GROUP BY子句语法结构：

GROUP BY group_expression [ , group_expression [ , ... ] ] [ WITH { ROLLUP | CUBE } ]

GROUP BY { group_expression | { ROLLUP | CUBE | GROUPING SETS } (grouping_set [ , ...]) } [ , ... ]

聚合函数定义：

aggregate_name ( [ DISTINCT ] expression [ , ... ] ) [ FILTER ( WHERE boolean_expression ) ]

示例数据准备：

CREATE TABLE dealer (id INT, city STRING, car_model STRING, quantity INT);
INSERT INTO dealer VALUES
    (100, 'Fremont', 'Honda Civic', 10),
    (100, 'Fremont', 'Honda Accord', 15),
    (100, 'Fremont', 'Honda CRV', 7),
    (200, 'Dublin', 'Honda Civic', 20),
    (200, 'Dublin', 'Honda Accord', 10),
    (200, 'Dublin', 'Honda CRV', 3),
    (300, 'San Jose', 'Honda Civic', 5),
    (300, 'San Jose', 'Honda Accord', 8);

1. 基本聚合

基于经销商ID聚合并计算销售总量：

SELECT id, sum(quantity) FROM dealer GROUP BY id ORDER BY id;

在聚合GroupBy中，也支持基于字段Index位置聚合，index取值范围从1开始，代表第一个字段。

SELECT id, sum(quantity) FROM dealer GROUP BY 1 ORDER BY 1;

多个聚合函数：(1).销售总量，(2).销售最大值：

SELECT id, sum(quantity) AS sum, max(quantity) AS max FROM dealer GROUP BY id ORDER BY id;

计算每个车型的销售城市：聚合函数与Distinct共用

SELECT car_model, count(DISTINCT city) AS count FROM dealer GROUP BY car_model;

携带过滤条件的分组聚合，在聚合时仅对满足过滤条件的分组行数据进行计算：

SELECT id, sum(quantity) FILTER (
            WHERE car_model IN ('Honda Civic', 'Honda CRV')
        ) AS `sum(quantity)` FROM dealer
    GROUP BY id ORDER BY id;

2. GROUPING SETS

GROUPING SETS 是基于GROUP BY子句之上提供的高级分组聚合功能，允许在单个 SQL语句中对多组列进行聚合计算。

SELECT city, car_model, sum(quantity) AS sum FROM dealer
    GROUP BY GROUPING SETS ((city, car_model), (city), (car_model), ());

以上SQL的聚合组列包括：

city, car_model：根据城市city和车型car_model 分组聚合，计算每个城市和每个车型组合的总销量；
city：根据城市city分组聚合，计算每个城市的总销量；
car_model：根据车型car_model 分组聚合，计算每个车型的总销量；
空集合：不进行任何分组，聚合计算所有总销量。

执行结果：

+---------+------------+---+
|     city|   car_model|sum|
+---------+------------+---+
|   Dublin| HondaAccord| 10| --1.按照城市和车型聚合
|   Dublin|    HondaCRV|  3|
|   Dublin|  HondaCivic| 20|
|  Fremont| HondaAccord| 15|
|  Fremont|    HondaCRV|  7|
|  Fremont|  HondaCivic| 10|
| San Jose| HondaAccord|  8|
| San Jose|  HondaCivic|  5|
|   Dublin|        null| 33| --2.按照城市聚合
|  Fremont|        null| 32|
| San Jose|        null| 13|
|     null| HondaAccord| 33| --3.按照车型聚合
|     null|    HondaCRV| 10|
|     null|  HondaCivic| 35|
|     null|        null| 78| --4.聚合所有总销量
+---------+------------+---+

备注：如果是GROUPING SETS单个字段，字段括号可以去除，等价于

SELECT city, car_model, sum(quantity) AS sum FROM dealer
    GROUP BY GROUPING SETS ((city, car_model), city, car_model, ());

3. ROLLUP

在GROUP BY子句中，基于ROLLUP 会顺序组合并上卷各维度。如GroupBy N个字段，则产生的分组数量为 N+1 个。

SELECT city, car_model, sum(quantity) AS sum FROM dealer
    GROUP BY city, car_model WITH ROLLUP

以上SQL的聚合组列包括：

city, car_model：根据城市city和车型car_model 分组聚合，计算每个城市和每个车型组合的总销量；
city：根据城市city分组聚合，计算每个城市的总销量；
空集合：不进行任何分组，聚合计算所有总销量。

可等价与GROUPING SETS ：

SELECT city, car_model, sum(quantity) AS sum FROM dealer
    GROUP BY GROUPING SETS ((city, car_model), (city), ());

4. CUBE

CUBE与ROLLUP思想类似，都是对GROUP BY子句的多组合上卷展开，但CUBE会遍历每一种可能的维度组合，如GroupBy N个字段，则产生的分组数量为 2^N 个。

SELECT city, car_model, sum(quantity) AS sum FROM dealer
    GROUP BY city, car_model WITH CUBE

可等价与GROUPING SETS ：

SELECT city, car_model, sum(quantity) AS sum FROM dealer
    GROUP BY GROUPING SETS ((city, car_model), (city), (car_model), ());

基于3个分组字段CUBE：

SELECT id, city, car_model, sum(quantity) AS sum FROM dealer
    GROUP BY id, city, car_model WITH CUBE;

等价于GROUPING SETS 8 个分组：

SELECT city, car_model, sum(quantity) AS sum FROM dealer
    GROUP BY GROUPING SETS ((id, city, car_model), (id, city), (id, car_model), (id), (city, car_model), (city), (car_model), () );

5. 混合/嵌套Grouping

ROLLUP、CUBE可理解为GROUPING SETS 的语法糖，它们是可以混合/嵌套使用的

SELECT city, car_model, sum(quantity) AS sum FROM dealer
    GROUP BY GROUPING SETS((city), ROLLUP(city, car_model), CUBE(city, car_model));

以上SQL等价于：

SELECT city, car_model, sum(quantity) AS sum FROM dealer
    GROUP BY GROUPING SETS((city), (city, car_model), (city), (), (city, car_model), (city), (car_model), ());

PIVOT

PIVOT 子句可用于数据透视图转换，基于特定列值获取聚合值，旋转列值并转换为 SELECT 子句中的多个列。PIVOT 子句可以在表名或子查询之后指定。

PIVOT 子句语法结构：基于FOR column_list 指定旋转后替换的列，IN expression_list 指定聚合列的条件。

PIVOT ( { aggregate_expression [ AS aggregate_expression_alias ] } [ , ... ]
    FOR column_list IN ( expression_list ) )

示例数据准备：

CREATE TABLE person (id INT, name STRING, age INT, class INT, address STRING);
INSERT INTO person VALUES
    (100, 'John', 30, 1, 'Street 1'),
    (200, 'Mary', NULL, 1, 'Street 2'),
    (300, 'Mike', 80, 3, 'Street 3'),
    (400, 'Dan', 50, 4, 'Street 4');

PIVOT查询示例1：以name为查询条件，满足name in ('John', 'Mike')：

SELECT * FROM person
    PIVOT (
        SUM(age) AS s, AVG(class) AS a
        FOR name IN ('John' AS john, 'Mike' AS mike)
    );

旋转后得到4个新的列：john_s、john_a、mike_s、mike_a：

+------+-----------+---------+---------+---------+---------+
|  id  |  address  | john_s  | john_a  | mike_s  | mike_a |
+------+-----------+---------+---------+---------+---------+
| 200  | Street 2  | NULL    | NULL    | NULL    | NULL    |
| 100  | Street 1  | 30      | 1.0     | NULL    | NULL    |
| 300  | Street 3  | NULL    | NULL    | 80      | 3.0     |
| 400  | Street 4  | NULL    | NULL    | NULL    | NULL    |
+------+-----------+---------+---------+---------+---------+

PIVOT查询示例2：以name和age为查询条件，满足(name, age) in (（'John', 30）, ('Mike', 40))：

SELECT * FROM person
    PIVOT (
        SUM(age) AS s, AVG(class) AS a
        FOR (name, age) IN (('John', 30) AS c1, ('Mike', 40) AS c2)
    );

转换后得到4个新列：c1_s, c1_a, c2_s, c2_a，只有id=100的记录满足查询条件：

+------+-----------+-------+-------+-------+-------+
|  id  |  address  | c1_s  | c1_a  | c2_s  | c2_a  |
+------+-----------+-------+-------+-------+-------+
| 200  | Street 2  | NULL  | NULL  | NULL  | NULL  |
| 100  | Street 1  | 30    | 1.0   | NULL  | NULL  |
| 300  | Street 3  | NULL  | NULL  | NULL  | NULL  |
| 400  | Street 4  | NULL  | NULL  | NULL  | NULL  |
+------+-----------+-------+-------+-------+-------+

LATERAL VIEW

LATERAL VIEW 支持展开嵌套数组，可以将一个行中的数组映射拆分成多行并维护在新列中，属于行转列操作。与Presto中 CROSS JOIN UNNEST实现类似。LATERAL VIEW 子句可以与生成器函数（如 EXPLODE）一起使用，生成器函数将生成一行或多行的虚拟表，LATERAL VIEW 可以将把生成的行应用到每一个原始输出行上。

LATERAL VIEW 子句语法结构：

LATERAL VIEW [ OUTER ] generator_function ( expression [ , ... ] ) [ table_alias ] AS column_alias [ , ... ]

以下示例SQL会生成4个新行：(30, 40), (30,80), (60,40), (60, 80)，分别对应两个2个新列(c_age, d_age)。这4个新行会对原有表的每行数据扩展，如原始表5行数据，则LATERAL VIEW 变为20 (5✖️4)行数据。

SELECT * FROM person
    LATERAL VIEW EXPLODE(ARRAY(30, 60)) tableName AS c_age
    LATERAL VIEW EXPLODE(ARRAY(40, 80)) AS d_age;

总结

OLAP（在线分析处理）多维分析技术在智能商业BI（商业智能）领域中扮演着至关重要的角色。这种分析方法允许用户从多个维度（即不同的角度或分类）对数据进行高效、灵活的探索和分析。多维分析的核心概念是将数据按照不同的属性或特征进行组织，以便用户可以从不同层面深入了解数据的内在联系和潜在价值。