相关文章推荐
腼腆的柠檬  ·  SQL注入(二)手工注入流程，实战，文件读写 ...·  2 周前    · 
很拉风的灭火器  ·  WHERE 子句 (Microsoft ...·  1 周前    · 
沉稳的麻辣香锅  ·  ALL、DISTINCT、DISTINCTR ...·  1 周前    · 
愉快的铁链  ·  在select选项上绑定事件 - ·  3 天前    · 
英姿勃勃的地瓜  ·  如何使用grouping ...·  昨天    · 
想出国的碗  ·  echarts：如何去掉echart的绘制动 ...·  9 月前    · 
帅气的甜瓜  ·  Mysql find the book ...·  1 年前    · 
痴情的雪糕  ·  the type initializer ...·  1 年前    · 
霸气的骆驼  ·  前端生成pdf，jspdf+html2Can ...·  1 年前    · 
傻傻的佛珠  ·  junit+powermock+mockit ...·  1 年前    · 
Code  ›  如何使用grouping sets进行多维聚合_云原生大数据计算服务 MaxCompute(MaxCompute)-阿里云帮助中心
linux系统 select
https://help.aliyun.com/zh/maxcompute/user-guide/grouping-sets
英姿勃勃的地瓜
昨天
GROUPING SETS

GROUPING SETS

更新时间：
产品详情
我的收藏

对于经常需要对数据进行多维度的聚合分析的场景，您既需要对 A 列做聚合，也要对 B 列做聚合，同时要对 A、B 两列做聚合，因此需要多次使用 union all 。您可以使用 grouping sets 快速解决此类问题。本文为您介绍如何使用 grouping sets 进行多维聚合。

功能介绍

grouping sets 是对 select 语句中 group by 子句的扩展，允许您采用多种方式对结果分组，而不必使用多个 select 语句再 union all 来实现。这样能够使 MaxCompute 的引擎给出更有效的执行计划，从而提高执行性能。

与 grouping sets 相关联的语法如下。

类型

说明

cube

特殊的 grouping sets ，将指定列的所有可能组合作为 grouping sets ，也可以与 grouping sets 组合使用。 

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)
)

rollup

特殊的 grouping sets ，以按层级聚合的方式产生 grouping sets ，也可以与 grouping sets 组合使用。 

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

grouping

grouping sets 结果中使用 NULL 充当占位符，导致您会无法区分占位符 NULL 与数据中真正的 NULL。因此，MaxCompute 为您提供了 grouping 。 grouping 接受一个列名作为参数，如果结果对应行使用了参数列做聚合，返回 0，此时意味着 NULL 来自输入数据。否则返回 1，此时意味着 NULL 是 grouping sets 的占位符。

grouping_id

接受一个或多个列名作为参数。结果是将参数列的 grouping 结果按照 Bitmap 的方式组成整数。

grouping__id

grouping__id 不带参数，用于兼容 Hive 查询。此表达方式在 MaxCompute 中等价于 grouping_id(group by 参数列表) ，参数与 group by 的顺序一致。

说明

MaxCompute 和 Hive 2.3.0 及以上版本兼容该函数，在 Hive 2.3.0 以下版本中该函数输出不一致，因此并不推荐您使用此函数。

GROUPING SETS 使用示例

grouping sets 使用示例如下：

  1. 准备数据。 

    create table requests lifecycle 20 as 
select * from values 
    (1, 'windows', 'PC', 'Beijing'),
    (2, 'windows', 'PC', 'Shijiazhuang'),
    (3, 'linux', 'Phone', 'Beijing'),
    (4, 'windows', 'PC', 'Beijing'),
    (5, 'ios', 'Phone', 'Shijiazhuang'),
    (6, 'linux', 'PC', 'Beijing'),
    (7, 'windows', 'Phone', 'Shijiazhuang') 
as t(id, os, device, city);

  2. 对数据进行分组。您可以通过如下两种方式进行分组：

    • 使用多个 select 语句进行分组。 

      select NULL, NULL, NULL, count(*)
from requests
union all
select os, device, NULL, count(*)
from requests group by os, device
union all
select null, null, city, count(*)
from requests group by city;

    • 使用 grouping sets 进行分组。 

      select os,device, city ,count(*)
from requests
group by grouping sets((os, device), (city), ());

      返回结果如下： 

      +------------+------------+------------+------------+
| os         | device     | city       | _c3        |
+------------+------------+------------+------------+
| NULL       | NULL       | NULL       | 7          |
| NULL       | NULL       | Beijing    | 4          |
| NULL       | NULL       | Shijiazhuang | 3          |
| ios        | Phone      | NULL       | 1          |
| linux      | PC         | NULL       | 1          |
| linux      | Phone      | NULL       | 1          |
| windows    | PC         | NULL       | 3          |
| windows    | Phone      | NULL       | 1          |
+------------+------------+------------+------------+
    说明

    分组集中不使用的表达式，会使用 NULL 充当占位符，使得这些结果集可以做操作。例如结果第 4~8 行的 city 列。

CUBE | ROLLUP 使用示例

基于 grouping sets 示例表， cube 及 rollup 使用示例如下：

  • 示例 1：通过 cube 枚举 os、device、city 的所有可能列为 grouping sets 。命令示例如下： 

    select os,device, city, count(*)
from requests 
group by cube (os, device, city);
--等效于如下语句。
select os,device, city, count(*)
from requests 
group by grouping sets ((os, device, city),(os, device),(os, city),(device,city),(os),(device),(city),());

    返回结果如下： 

    +------------+------------+------------+------------+
| os         | device     | city       | _c3        |
+------------+------------+------------+------------+
| NULL       | NULL       | NULL       | 7          |
| NULL       | NULL       | Beijing    | 4          |
| NULL       | NULL       | Shijiazhuang | 3          |
| NULL       | PC         | NULL       | 4          |
| NULL       | PC         | Beijing    | 3          |
| NULL       | PC         | Shijiazhuang | 1          |
| NULL       | Phone      | NULL       | 3          |
| NULL       | Phone      | Beijing    | 1          |
| NULL       | Phone      | Shijiazhuang | 2          |
| ios        | NULL       | NULL       | 1          |
| ios        | NULL       | Shijiazhuang | 1          |
| ios        | Phone      | NULL       | 1          |
| ios        | Phone      | Shijiazhuang | 1          |
| linux      | NULL       | NULL       | 2          |
| linux      | NULL       | Beijing    | 2          |
| linux      | PC         | NULL       | 1          |
| linux      | PC         | Beijing    | 1          |
| linux      | Phone      | NULL       | 1          |
| linux      | Phone      | Beijing    | 1          |
| windows    | NULL       | NULL       | 4          |
| windows    | NULL       | Beijing    | 2          |
| windows    | NULL       | Shijiazhuang | 2          |
| windows    | PC         | NULL       | 3          |
| windows    | PC         | Beijing    | 2          |
| windows    | PC         | Shijiazhuang | 1          |
| windows    | Phone      | NULL       | 1          |
| windows    | Phone      | Shijiazhuang | 1          |
+------------+------------+------------+------------+

  • 示例 2：通过 cube 枚举 (os, device),(device, city) 所有可能列为 grouping sets 。命令示例如下： 

    select os,device, city, count(*) 
from requests 
group by cube ((os, device), (device, city));
--等效于如下语句。
select os,device, city, count(*) 
from requests 
group by grouping sets ((os, device, city),(os, device),(device,city),());

    返回结果如下： 

    +------------+------------+------------+------------+
| os         | device     | city       | _c3        |
+------------+------------+------------+------------+
| NULL       | NULL       | NULL       | 7          |
| NULL       | PC         | Beijing    | 3          |
| NULL       | PC         | Shijiazhuang | 1          |
| NULL       | Phone      | Beijing    | 1          |
| NULL       | Phone      | Shijiazhuang | 2          |
| ios        | Phone      | NULL       | 1          |
| ios        | Phone      | Shijiazhuang | 1          |
| linux      | PC         | NULL       | 1          |
| linux      | PC         | Beijing    | 1          |
| linux      | Phone      | NULL       | 1          |
| linux      | Phone      | Beijing    | 1          |
| windows    | PC         | NULL       | 3          |
| windows    | PC         | Beijing    | 2          |
| windows    | PC         | Shijiazhuang | 1          |
| windows    | Phone      | NULL       | 1          |
| windows    | Phone      | Shijiazhuang | 1          |
+------------+------------+------------+------------+

  • 示例 3：通过 rollup 对 os、device、city 以按层级聚合的方式产生 grouping sets 。命令示例如下： 

    select os,device, city, count(*)
from requests 
group by rollup (os, device, city);
--等效于如下语句。
select os,device, city, count(*)
from requests 
group by grouping sets ((os, device, city),(os, device),(os),());

    返回结果如下： 

    +------------+------------+------------+------------+
| os         | device     | city       | _c3        |
+------------+------------+------------+------------+
| NULL       | NULL       | NULL       | 7          |
| ios        | NULL       | NULL       | 1          |
| ios        | Phone      | NULL       | 1          |
| ios        | Phone      | Shijiazhuang | 1          |
| linux      | NULL       | NULL       | 2          |
| linux      | PC         | NULL       | 1          |
| linux      | PC         | Beijing    | 1          |
| linux      | Phone      | NULL       | 1          |
| linux      | Phone      | Beijing    | 1          |
| windows    | NULL       | NULL       | 4          |
| windows    | PC         | NULL       | 3          |
| windows    | PC         | Beijing    | 2          |
| windows    | PC         | Shijiazhuang | 1          |
| windows    | Phone      | NULL       | 1          |
| windows    | Phone      | Shijiazhuang | 1          |
+------------+------------+------------+------------+

  • 示例 4：通过 rollup 对 os, (os,device), city 以按层级聚合的方式产生 grouping sets 。命令示例如下： 

    select os,device, city, count(*)
from requests 
group by rollup (os, (os,device), city);
--等效于如下语句。
select os,device, city, count(*)
from requests 
group by grouping sets ((os, device, city),(os, device),(os),());

    返回结果如下： 

    +------------+------------+------------+------------+
| os         | device     | city       | _c3        |
+------------+------------+------------+------------+
| NULL       | NULL       | NULL       | 7          |
| ios        | NULL       | NULL       | 1          |
| ios        | Phone      | NULL       | 1          |
| ios        | Phone      | Shijiazhuang | 1          |
| linux      | NULL       | NULL       | 2          |
| linux      | PC         | NULL       | 1          |
| linux      | PC         | Beijing    | 1          |
| linux      | Phone      | NULL       | 1          |
| linux      | Phone      | Beijing    | 1          |
| windows    | NULL       | NULL       | 4          |
| windows    | PC         | NULL       | 3          |
| windows    | PC         | Beijing    | 2          |
| windows    | PC         | Shijiazhuang | 1          |
| windows    | Phone      | NULL       | 1          |
| windows    | Phone      | Shijiazhuang | 1          |
+------------+------------+------------+------------+

  • 示例 5：通过 group by 、 cube 、 grouping sets 组合产生 grouping sets 。命令示例如下： 

    select os,device, city, count(*)
from requests 
group by os, cube(os,device), grouping sets(city);
--等效于如下语句。
select os,device, city, count(*)
from requests 
group by grouping sets((os,device,city),(os,city),(os,device,city));

    返回结果如下： 

    +------------+------------+------------+------------+
| os         | device     | city       | _c3        |
+------------+------------+------------+------------+
| ios        | NULL       | Shijiazhuang | 1          |
| ios        | Phone      | Shijiazhuang | 1          |
| linux      | NULL       | Beijing    | 2          |
| linux      | PC         | Beijing    | 1          |
| linux      | Phone      | Beijing    | 1          |
| windows    | NULL       | Beijing    | 2          |
| windows    | NULL       | Shijiazhuang | 2          |
| windows    | PC         | Beijing    | 2          |
| windows    | PC         | Shijiazhuang | 1          |
| windows    | Phone      | Shijiazhuang | 1          |
+------------+------------+------------+------------+

GROUPING | GROUPING_ID 使用示例

grouping 及 grouping_id 使用示例如下： 

select a,b,c,count(*),
grouping(a) ga, grouping(b) gb, grouping(c) gc, grouping_id(a,b,c) groupingid 
from values (1,2,3) as t(a,b,c)
group by cube(a,b,c);

返回结果如下： 

+------------+------------+------------+------------+------------+------------+------------+------------+
| a          | b          | c          | _c3        | ga         | gb         | gc         | groupingid |
+------------+------------+------------+------------+------------+------------+------------+------------+
| NULL       | NULL       | NULL       | 1          | 1          | 1          | 1          | 7          |
| NULL       | NULL       | 3          | 1          | 1          | 1          | 0          | 6          |
| NULL       | 2          | NULL       | 1          | 1          | 0          | 1          | 5          |
| NULL       | 2          | 3          | 1          | 1          | 0          | 0          | 4          |
| 1          | NULL       | NULL       | 1          | 0          | 1          | 1          | 3          |
| 1          | NULL       | 3          | 1          | 0          | 1          | 0          | 2          |
| 1          | 2          | NULL       | 1          | 0          | 0          | 1          | 1          |
| 1          | 2          | 3          | 1          | 0          | 0          | 0          | 0          |
+------------+------------+------------+------------+------------+------------+------------+------------+

默认情况， group by 列表中不被使用的列，会被填充为 NULL。您可以通过 grouping 输出更有实际意义的值。基于 grouping sets 示例表，命令示例如下： 

select
  if(grouping(os) == 0, os, 'ALL') as os,
  if(grouping(device) == 0, device, 'ALL') as device,
  if(grouping(city) == 0, city, 'ALL') as city, 
  count(*) as count 
from requests 
group by os, device, city grouping sets((os, device), (city), ());

返回结果如下： 

+------------+------------+------------+------------+
| os         | device     | city       | count      |
+------------+------------+------------+------------+
| ALL        | ALL        | ALL        | 7          |
| ALL        | ALL        | Beijing    | 4          |
| ALL        | ALL        | Shijiazhuang | 3          |
| ios        | Phone      | ALL        | 1          |
| linux      | PC         | ALL        | 1          |
| linux      | Phone      | ALL        | 1          |
| windows    | PC         | ALL        | 3          |
| windows    | Phone      | ALL        | 1          |
+------------+------------+------------+------------+

GROUPING__ID 使用示例

grouping__id 不带参数的命令示例如下： 

set odps.sql.hive.compatible=true;
select      
a, b, c, count(*), grouping__id 
from values (1,2,3) as t(a,b,c) 
group by a, b, c grouping sets ((a,b,c), (a));
--等效于如下语句。
select      
a, b, c, count(*), grouping_id(a,b,c)  
from values (1,2,3) as t(a,b,c) 
group by a, b, c grouping sets ((a,b,c), (a));

返回结果如下： 

+------------+------------+------------+------------+------------+
| a          | b          | c          | _c3        | _c4        |
+------------+------------+------------+------------+------------+
| 1          | NULL       | NULL       | 1          | 3          |
| 1          | 2          | 3          | 1          | 0          |
+------------+------------+------------+------------+------------+
上一篇：Lateral View 下一篇：SELECT TRANSFORM
 
推荐文章
腼腆的柠檬  ·  SQL注入(二)手工注入流程，实战，文件读写-天翼云
2 周前
很拉风的灭火器  ·  WHERE 子句 (Microsoft Access SQL) | Microsoft Learn
1 周前
沉稳的麻辣香锅  ·  ALL、DISTINCT、DISTINCTROW、top 谓词 (Microsoft Access SQL) | Microsoft Learn
1 周前
愉快的铁链  ·  在select选项上绑定事件 -
3 天前
英姿勃勃的地瓜  ·  如何使用grouping sets进行多维聚合_云原生大数据计算服务 MaxCompute(MaxCompute)-阿里云帮助中心
昨天
想出国的碗  ·  echarts：如何去掉echart的绘制动画？_ecahrts 刷新数据,不需要动画-CSDN博客
9 月前
帅气的甜瓜  ·  Mysql find the book with the highest rating for each country in my db - CodeProject
1 年前
痴情的雪糕  ·  the type initializer for 'system.data.entity.internal.appconfig' threw an exception. c# - CodeProjec
1 年前
霸气的骆驼  ·  前端生成pdf，jspdf+html2Canvas的使用(vue)-腾讯云开发者社区-腾讯云
1 年前
傻傻的佛珠  ·  junit+powermock+mockito静态方法测试 - Cuddles - 博客园
1 年前
今天看啥   ·   Py中国   ·   codingpro   ·   小百科   ·   link之家   ·   卧龙AI搜索
删除内容请联系邮箱 2879853325@qq.com
Code - 代码工具平台
© 2024 ~ 沪ICP备11025650号