SQL 指定多维度 Grouping Sets

更新时间：2022-03-23 08:28:40 标签：hive hql 多维数据 cube

提示

Hive SQL 教程编写中，使用过程中有任何建议，提供意见、建议、纠错、催更加微信 gairuo123。欢迎关注本教程作者新书《深入浅出Pandas：利用Python进行数据处理与分析》。作者开办 Python 数据分析培训，详情 Python 数据分析培训。

利用 SQL 编写分组聚合逻辑时，Group By 是来实现它们的语句，但是每次 Group By 只能有一个维度。如果一次性想编写所有维度的话使用我们之前讲过的 with cube，如果有指定的多个维度的话就要用到 GROUPING SETS。注意，不是一个字段，维度是一个或者多个字段组合组成，没有 Group By 也可以认为是一个维度。

功能说明

Hive SQL 的 GROUPING SETS 多维度分析可以在分组中上增强聚合功能，指定多个分组维度之后，可以在这些维度组合进行聚合，对应维度上不做分组时输出 NULL 代表当前维度的全体。基本的语法是：

-- GROUPING SETS 指定维度
SELECT a,b, SUM(c)
FROM tab
GROUP BY a,b
GROUPING SETS ((a,b),a)

以上 SQL 在正常的 GROUP BY 内指定了两个维度，分别是 a,b 和 a。在第二个维度数据展示进对应位会显示 NULL。如：

a、b
a、NULL

它个的数据会展示在一个结果表里。

详细说明

GROUP BY 中的 GROUP SETS子句允许我们在同一记录集中指定多个 GROUP BY 选项。所有分组集子句都可以逻辑地表示为通过 UNION 连接的多个 GROUP BY 查询。

表1显示了几种类似的表达方式，这有助于形成分组集子句的概念。GROUPING SETS子句中的空白集（）计算总聚合。

以下是一些详细的对比：

SELECT a, b, SUM(c) FROM tab1 GROUP BY a, b GROUPING SETS ((a,b))
-- 相当于：
SELECT a, b, SUM(c) FROM tab1 GROUP BY a, b

--

SELECT a, b, SUM(c) FROM tab1 GROUP BY a, b GROUPING SETS ((a,b), a)
-- 相当于：
SELECT a, b, SUM(c) FROM tab1 GROUP BY a, b
UNION
SELECT a, null, SUM(c) FROM tab1 GROUP BY a

--

SELECT a,b, SUM(c) FROM tab1 GROUP BY a, b GROUPING SETS (a,b)
-- 相当于：
SELECT a, null, SUM(c) FROM tab1 GROUP BY a
UNION
SELECT null, b, SUM(c) FROM tab1 GROUP BY b

--

SELECT a, b, SUM(c) FROM tab1
GROUP BY a, b GROUPING SETS ((a, b), a, b, ())
-- 相当于：
SELECT a, b, SUM(c) FROM tab1 GROUP BY a, b
UNION
SELECT a, null, SUM(c) FROM tab1 GROUP BY a, null
UNION
SELECT null, b, SUM( c ) FROM tab1 GROUP BY null, b
UNION
SELECT null, null, SUM( c ) FROM tab1

与 cube 同时使用

在阿里云的云原生大数据计算服务 MaxCompute 中，grouping sets 可与 cube 同时使用。见以下示例：

group by cube (a, b, c)  
--等效于以下语句。  
grouping sets ((a,b,c),(a,b),(a,c),(b,c),(a),(b),(c),())

group by cube ( (a, b), (c, d) ) 
--等效于以下语句。 
grouping sets (
    ( a, b, c, d ),
    ( a, b       ),
    (       c, d ),
    (            )
)
group by a, cube (b, c), grouping sets ((d), (e)) 
--等效于以下语句。 
group by grouping sets (
    (a, b, c, d), (a, b, c, e),
    (a, b, d),    (a, b, e),
    (a, c, d),    (a, c, e),
    (a, d),       (a, e)
)

参考

https://cwiki.apache.org/confluence/x/mxLMAQ

<< 多维度分析 CUBE Hive SQL 教程按层级聚合 With Rollup >>