AI 助理
备案控制台
开发者社区 数据库 文章 正文

【笔记】开发指南—DQL语句—Grouping Sets、Rollup和Cube扩展

2022-07-23 107
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云原生数据库 PolarDB 分布式版，标准版 2核8GB
推荐场景：
使用PolarDB-X与Flink搭建实时数据大屏
简介： 在关系型数据库中，通常需要使用多个SELECT + UNION语句来实现按照多组维度的结果分组，PolarDB-X新增支持通过Grouping Sets、Rollup和Cube扩展来实现这一目的。此外，PolarDB-X还支持在SELECT命令或HAVING子句中使用GROUPING函数和GROUPING_ID函数，来帮助解释使用上述扩展时的结果。本文将介绍相关语法和示例。

注意事项

  • 本文介绍的所有GROUP BY相关的扩展语法，均不支持查询下推至LogicalView算子中执行。关于查询下推，请参见查询改写与下推。
  • 本文示例中所用测试数据信息如下：使用如下语句创建一张requests表：
CREATE TABLE requests (

`id` int(10) UNSIGNED NOT NULL,
 `os` varchar(20) DEFAULT NULL,
 `device` varchar(20) DEFAULT NULL,
 `city` varchar(20) DEFAULT NULL,
 PRIMARY KEY (`id`)
 ) ENGINE = InnoDB DEFAULT CHARSET = utf8 dbpartition BY hash(`id`) tbpartition BY hash(`id`);
  • 在requests表中使用如下语句插入测试所需的数据：
INSERT INTO requests (id, os, device, city) 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');

GROUPING SETS扩展

  • 功能介绍GROUPING SETS是GROUP BY子句的扩展，可以生成一个结果集，该结果集实际上是基于不同分组的多个结果集的串联（与UNION ALL运算结果类似），但UNION ALL运算和GROUPING SETS扩展并不会消除合并结果集中的重复行。
  • 语法
GROUPING SETS (
  { expr_1 | ( expr_1a [, expr_1b ] ...) |
    ROLLUP ( expr_list ) | CUBE ( expr_list )
  } [, ...] )
  • 说明GROUPING SETS扩展可包含一个或多个由半角逗号（,）分隔表达式（如expr_1或(expr_1a [, expr_1b ] ...)）的任意组合，以及带半角圆括号（()）的表达式列表（如( expr_list )），其中：
    • 每个表达式都可用于确定结果集的分组方式。
    • GROUPING SETS内也支持嵌套使用ROLLUP或者CUBE。
  • 示例
    • 通过GROUPING SETS扩展对数据进行分组查询，语法如下：
select os,device, city ,count(*)
from requests
group by grouping sets((os, device), (city), ());
上述语句等效于如下语句：
select os, device, NULL, count(*)
from requests group by os, device
union all
select NULL, NULL, NULL, count(*)
from requests
union all
select null, null, city, count(*)
from requests group by city;
    • 返回结果如下：
+---------+--------+--------------+----------+
| os      | device | city         | count(*) |
+---------+--------+--------------+----------+
| windows | PC     | NULL         |        3 |
| linux   | PC     | NULL         |        1 |
| linux   | Phone  | NULL         |        1 |
| windows | Phone  | NULL         |        1 |
| ios     | Phone  | NULL         |        1 |
| NULL    | NULL   | Shijiazhuang |        3 |
| NULL    | NULL   | Beijing      |        4 |
| NULL    | NULL   | NULL         |        7 |
+---------+--------+--------------+----------+

    • 说明 未在分组集中使用的表达式，会用NULL充当占位符，便于对这些未在分组集使用的结果集进行操作，例如结果city列中显示为NULL的行。
    • 通过在GROUPING SETS中嵌套ROLLUP来对数据进行分组，语法如下：
select os,device, city ,count(*) from requests 
group by grouping sets((city), ROLLUP(os, device));
上述语句等效于如下语句：
select os,device, city ,count(*) from requests 
group by grouping sets((city), (os), (os, device), ());
    • 返回结果如下：
+---------+--------+--------------+----------+
| os      | device | city         | count(*) |
+---------+--------+--------------+----------+
| NULL    | NULL   | Shijiazhuang |        3 |
| NULL    | NULL   | Beijing      |        4 |
| windows | PC     | NULL         |        3 |
| linux   | PC     | NULL         |        1 |
| ios     | Phone  | NULL         |        1 |
| linux   | Phone  | NULL         |        1 |
| windows | Phone  | NULL         |        1 |
| windows | NULL   | NULL         |        4 |
| linux   | NULL   | NULL         |        2 |
| ios     | NULL   | NULL         |        1 |
| NULL    | NULL   | NULL         |        7 |
+---------+--------+--------------+----------+
    • 通过在GROUPING SETS中嵌套CUBE扩展来对数据进行分组，语法如下：
select os,device, city ,count(*) from requests 
group by grouping sets((city), CUBE(os, device));
上述语句等效于如下语句：
select os,device, city ,count(*) from requests 
group by grouping sets((city), (os), (os, device), (), (device));
    • 返回结果如下：
+---------+--------+--------------+----------+
| os      | device | city         | count(*) |
+---------+--------+--------------+----------+
| NULL    | NULL   | Beijing      |        4 |
| NULL    | NULL   | Shijiazhuang |        3 |
| windows | PC     | NULL         |        3 |
| ios     | Phone  | NULL         |        1 |
| linux   | Phone  | NULL         |        1 |
| windows | Phone  | NULL         |        1 |
| linux   | PC     | NULL         |        1 |
| windows | NULL   | NULL         |        4 |
| ios     | NULL   | NULL         |        1 |
| linux   | NULL   | NULL         |        2 |
| NULL    | PC     | NULL         |        4 |
| NULL    | Phone  | NULL         |        3 |
| NULL    | NULL   | NULL         |        7 |
+---------+--------+--------------+----------+
    • 通过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         | count(*) |
+---------+--------+--------------+----------+
| linux   | Phone  | Beijing      |        1 |
| windows | Phone  | Shijiazhuang |        1 |
| windows | PC     | Shijiazhuang |        1 |
| linux   | PC     | Beijing      |        1 |
| windows | PC     | Beijing      |        2 |
| ios     | Phone  | Shijiazhuang |        1 |
| linux   | NULL   | Beijing      |        2 |
| windows | NULL   | Shijiazhuang |        2 |
| windows | NULL   | Beijing      |        2 |
| ios     | NULL   | Shijiazhuang |        1 |
+---------+--------+--------------+----------+

ROLLUP扩展

  • 功能介绍ROLLUP扩展生成一系列有总计的分层组，每个分层组都有小计。该层次结构的顺序由ROLLUP表达式列表中给定的表达式的顺序确定。该层次结构的顶部是列表中最左侧的项。每个连续项都会沿右侧在该层次结构中向下移动，最右侧的项是最低级别。
  • 语法
ROLLUP ( { expr_1 | ( expr_1a [, expr_1b ] ...) }
  [, expr_2 | ( expr_2a [, expr_2b ] ...) ] ...)
  • 说明
    • 每个表达式都会用于确定结果集的分组方式。如果采用带圆括号形式的表达式，例如( expr_1a, expr_1b, ...)，则 expr_1a和expr_1b返回的值组合定义层次结构的单个分组级别。
    • 对于列表中的第一项，例如expr_1或( expr_1a, expr_1b, ...)的组合，PolarDB-X将为每个唯一值返回一个小计。对于列表中的第二项，例如expr_2 或( expr_2a, expr_2b, ...)的组合，PolarDB-X将为第二项的每个分组中的每个唯一值返回一个小计，依此类推。最后，PolarDB-X将为整个结果集返回一个总计。
    • 对于小计行，将为小计包含的各项返回NULL。
  • 示例
    • 通过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 os, device, city with rollup;
也等效于如下语句：
select os,device, city, count(*)
from requests 
group by grouping sets ((os, device, city),(os, device),(os),());
    • 返回结果如下：
+---------+--------+--------------+----------+
| os      | device | city         | count(*) |
+---------+--------+--------------+----------+
| windows | PC     | Beijing      |        2 |
| ios     | Phone  | Shijiazhuang |        1 |
| windows | PC     | Shijiazhuang |        1 |
| linux   | PC     | Beijing      |        1 |
| linux   | Phone  | Beijing      |        1 |
| windows | Phone  | Shijiazhuang |        1 |
| windows | PC     | NULL         |        3 |
| ios     | Phone  | NULL         |        1 |
| linux   | PC     | NULL         |        1 |
| linux   | Phone  | NULL         |        1 |
| windows | Phone  | NULL         |        1 |
| windows | NULL   | NULL         |        4 |
| ios     | NULL   | NULL         |        1 |
| linux   | NULL   | NULL         |        2 |
| NULL    | NULL   | NULL         |        7 |
+---------+--------+--------------+----------+
    • 通过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 os, (os,device), city with rollup;
也等效于如下语句：
select os,device, city, count(*)
from requests 
group by grouping sets ((os, device, city),(os, device),(os),());
    • 返回结果如下：
+---------+--------+--------------+----------+
| os      | device | city         | count(*) |
+---------+--------+--------------+----------+
| windows | PC     | Beijing      |        2 |
| windows | PC     | Shijiazhuang |        1 |
| linux   | PC     | Beijing      |        1 |
| linux   | Phone  | Beijing      |        1 |
| windows | Phone  | Shijiazhuang |        1 |
| ios     | Phone  | Shijiazhuang |        1 |
| windows | PC     | NULL         |        3 |
| linux   | PC     | NULL         |        1 |
| linux   | Phone  | NULL         |        1 |
| windows | Phone  | NULL         |        1 |
| ios     | Phone  | NULL         |        1 |
| windows | NULL   | NULL         |        4 |
| linux   | NULL   | NULL         |        2 |
| ios     | NULL   | NULL         |        1 |
| NULL    | NULL   | NULL         |        7 |
+---------+--------+--------------+----------+

CUBE扩展

  • 功能介绍CUBE扩展与ROLLUP扩展类似，但与生成分组并基于ROLLUP表达式列表中从左到右的项列表生成层次结构的ROLLUP扩展不同，CUBE是基于CUBE表达式列表中所有项的每个排列生成分组和小计。因此，与对同一表达式列表执行的ROLLUP相比，CUBE结果集会包含更多的行。
  • 语法
CUBE ( { expr_1 | ( expr_1a [, expr_1b ] ...) }
  [, expr_2 | ( expr_2a [, expr_2b ] ...) ] ...)
  • 说明
    • 每个表达式都会用于确定结果集的分组方式。如果采用带半角圆括号的形式，例如( expr_1a, expr_1b, ...)，则 expr_1a和expr_1b返回的值组合定义单个组。
    • 对于列表中的第一项，例如expr_1或( expr_1a, expr_1b, ...)的组合，PolarDB-X将为每个唯一值返回一个小计。对于列表中的第二项，例如expr_2 或( expr_2a, expr_2b, ...)的组合，PolarDB-X在为每个唯一值返回一个小计的同时，还将为第一项和第二项的每个唯一组合返回一个小计。如果存在第三项，PolarDB-X则会为第三项的每个唯一值、第三项和第一项组合的每个唯一值、第三项和第二项组合的每个唯一值以及第三项、第二项和第一项组合的每个唯一值返回一个小计。最后，再将为整个结果集返回一个总计。
    • 对于小计行，将为小计包含的各项返回NULL。
  • 示例
    • 通过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         | count(*) |
+---------+--------+--------------+----------+
| linux   | Phone  | Beijing      |        1 |
| windows | Phone  | Shijiazhuang |        1 |
| windows | PC     | Beijing      |        2 |
| ios     | Phone  | Shijiazhuang |        1 |
| windows | PC     | Shijiazhuang |        1 |
| linux   | PC     | Beijing      |        1 |
| linux   | Phone  | NULL         |        1 |
| windows | Phone  | NULL         |        1 |
| windows | PC     | NULL         |        3 |
| ios     | Phone  | NULL         |        1 |
| linux   | PC     | NULL         |        1 |
| linux   | NULL   | Beijing      |        2 |
| windows | NULL   | Shijiazhuang |        2 |
| windows | NULL   | Beijing      |        2 |
| ios     | NULL   | Shijiazhuang |        1 |
| linux   | NULL   | NULL         |        2 |
| windows | NULL   | NULL         |        4 |
| ios     | NULL   | NULL         |        1 |
| NULL    | Phone  | Beijing      |        1 |
| NULL    | Phone  | Shijiazhuang |        2 |
| NULL    | PC     | Beijing      |        3 |
| NULL    | PC     | Shijiazhuang |        1 |
| NULL    | Phone  | NULL         |        3 |
| NULL    | PC     | NULL         |        4 |
| NULL    | NULL   | Beijing      |        4 |
| NULL    | NULL   | Shijiazhuang |        3 |
| NULL    | NULL   | NULL         |        7 |
+---------+--------+--------------+----------+
    • 通过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         | count(*) |
+---------+--------+--------------+----------+
| linux   | Phone  | Beijing      |        1 |
| windows | Phone  | Shijiazhuang |        1 |
| windows | PC     | Beijing      |        2 |
| windows | PC     | Shijiazhuang |        1 |
| linux   | PC     | Beijing      |        1 |
| ios     | Phone  | Shijiazhuang |        1 |
| linux   | Phone  | NULL         |        1 |
| windows | Phone  | NULL         |        1 |
| windows | PC     | NULL         |        3 |
| linux   | PC     | NULL         |        1 |
| ios     | Phone  | NULL         |        1 |
| NULL    | Phone  | Beijing      |        1 |
| NULL    | Phone  | Shijiazhuang |        2 |
| NULL    | PC     | Beijing      |        3 |
| NULL    | PC     | Shijiazhuang |        1 |
| NULL    | NULL   | NULL         |        7 |
+---------+--------+--------------+----------+

GROUPING和GROUPING_ID函数

  • 功能介绍
    • GROUPING函数在GROUP BY子句使用GROUPING SETS、ROLLUP、或CUBE扩展时，GROUPING SETS结果中会使用NULL来充当占位符，导致无法区分占位符NULL与数据中真正的NULL。此时，您可以使用PolarDB-X提供的GROUPING函数来作区分。
      GROUPING函数接受一个列名作为参数，如果结果对应行使用了参数列做聚合，则结果返回0，此时意味着NULL来自输入数据。如果结果对应行未使用参数列做聚合，则返回1，此时意味着NULL来自GROUPING SETS结果中的占位符。
    • GROUPING_ID函数GROUPING_ID函数简化了GROUPING函数，用于确定ROLLBACK、CUBE或GROUPING SETS扩展的结果集中行的小计级别。GROUPING函数仅采用一个列表达式并返回一个值来指示行是否为给定列的所有值的小计。因此，当解释具有多个分组列的查询的小计级别时，可能需要多个 GROUPING函数。GROUPING_ID函数接受ROLLBACK、CUBE或GROUPINGSETS扩展中已使用的一个或多个列表达式，并返回单个整数，该整数可用于确定其中哪一列已聚合小计。
  • 语法
    • GROUPING函数
SELECT [ expr ...,] GROUPING( col_expr ) [, expr ] ...
FROM ...
GROUP BY { ROLLUP | CUBE | GROUPING SETS }( [...,] col_expr
  [, ...] ) [, ...]

    • 说明 GROUPING函数采用单个参数，该参数必须是GROUP BY子句中ROLLUP、CUBE或GROUPING SETS扩展的表达式列表中指定的维度列的表达式。
    • GROUPING_ID函数
SELECT [ expr ...,]
  GROUPING_ID( col_expr_1 [, col_expr_2 ] ... )
  [, expr ] ...
FROM ...
GROUP BY { ROLLUP | CUBE | GROUPING SETS }( [...,] col_expr_1
  [, col_expr_2 ] [, ...] ) [, ...]
  • 示例通过GROUPING_ID函数将多个列名作为参数，并将参数列的GROUPING结果按照Bitmap的方式组成整数，语法如下：
select a,b,c,count(*),
grouping(a) ga, grouping(b) gb, grouping(c) gc, grouping_id(a,b,c) groupingid 
from (select 1 as a ,2 as b,3 as c)
group by cube(a,b,c);
  • 返回结果如下：
+------+------+------+----------+------+------+------+------------+
| a    | b    | c    | count(*) | ga   | gb   | gc   | groupingid |
+------+------+------+----------+------+------+------+------------+
|    1 |    2 |    3 |        1 |    0 |    0 |    0 |          0 |
|    1 |    2 | NULL |        1 |    0 |    0 |    1 |          1 |
|    1 | NULL |    3 |        1 |    0 |    1 |    0 |          2 |
|    1 | NULL | NULL |        1 |    0 |    1 |    1 |          3 |
| NULL |    2 |    3 |        1 |    1 |    0 |    0 |          4 |
| NULL |    2 | NULL |        1 |    1 |    0 |    1 |          5 |
| NULL | NULL |    3 |        1 |    1 |    1 |    0 |          6 |
| NULL | NULL | NULL |        1 |    1 |    1 |    1 |          7 |
+------+------+------+----------+------+------+------+------------+
文章标签：
云原生分布式数据库 PolarDB-X
SQL
关系型数据库
xaubllxwtvaqiu
目录
相关文章
API小知识
|
JSON 安全 API
如何使用Python开发API接口？
在现代软件开发中，API（应用程序编程接口）用于不同软件组件之间的通信和数据交换，实现系统互操作性。Python因其简单易用和强大功能，成为开发API的热门选择。本文详细介绍了Python开发API的基础知识、优势、实现方式（如Flask和Django框架）、实战示例及注意事项，帮助读者掌握高效、安全的API开发技巧。
API小知识
1516 3
如何使用Python开发API接口？
SoraLuna
|
自然语言处理 开发者
「Mac畅玩鸿蒙与硬件11」鸿蒙 UI 组件篇1 - Text和Button组件详解
本篇将详细介绍鸿蒙应用开发中的 Text 和 Button 组件。通过本篇内容，你将学习如何使用 Text 组件显示文本、格式化文本样式，以及如何使用 Button 组件处理点击事件并自定义样式。掌握这些基本组件的用法将为后续的 UI 开发奠定基础。
SoraLuna
542 4
「Mac畅玩鸿蒙与硬件11」鸿蒙 UI 组件篇1 - Text和Button组件详解
喵帕斯先生
|
安全 Java API
深入探索Java网络编程中的HttpURLConnection：从基础到进阶
本文介绍了Java网络编程中HttpURLConnection的高级特性，包括灵活使用不同HTTP方法、处理重定向、管理Cookie、优化安全性以及处理大文件上传和下载。通过解答五个常见问题，帮助开发者提升网络编程的效率和安全性。
喵帕斯先生
507 9
1941623231718325
|
机器学习/深度学习 人工智能 机器人
智能废物管理系统：城市垃圾的自动化处理
【10月更文挑战第24天】智能废物管理系统利用物联网、大数据、人工智能和自动化机器人等技术，实现城市垃圾从分类、收集到处理的全过程自动化，提高处理效率，优化资源配置，提升居民参与度，降低运营成本，推动城市可持续发展。
1941623231718325
1249 2
郑小健
|
分布式计算 安全 Hadoop
Hadoop安全性和数据保护机制
【8月更文第28天】随着大数据技术的普及，Hadoop已成为处理大规模数据集的核心技术之一。然而，随着数据价值的增加，数据安全问题变得越来越重要。本文将深入探讨Hadoop的安全模型以及如何实施访问控制、数据加密等措施来保障数据安全。
郑小健
1096 0
软件求生
|
存储 缓存 监控
快速掌握Redis优化要点，告别性能瓶颈！
# Redis优化指南 了解如何提升Redis性能，从读写方式（整体与部分）、KV size、Key数量、读写峰值、命中率、过期策略、平均穿透加载时间、可运维性、安全性等方面着手。选择合适的读写策略，如只整体读写或部分读写变更，优化KV size避免过大或差异过大，合理管理Key数量，应对不同读写峰值，监控命中率并持续优化，设置智能过期策略，减少平均穿透加载时间，确保高可运维性并强化安全性。一起探索Redis的性能潜力！
软件求生
2798 5
东方睿赢
|
数据处理 Python
数据变形记：Python转换技巧大公开，轻松玩转数据魔方！
【7月更文挑战第21天】在Python数据处理中，变形单元格是洞察的关键。案例展示了如何处理电商用户购买行为数据：使用Pandas加载CSV，将日期字符串转为日期类型，按用户ID计算总消费，及应用10%折扣计算新价格。这些技巧揭示了数据变形的威力，将原始数据转化为可分析的洞察。
东方睿赢
122 0
Vez'nan的幸福生活
|
存储 SQL 关系型数据库
【MySQL进阶之路 | 基础篇】存储过程
【MySQL进阶之路 | 基础篇】存储过程
Vez'nan的幸福生活
237 1
.29.
|
SQL 存储 关系型数据库
③【操作表数据】MySQL添加数据、修改数据、删除数据
③【操作表数据】MySQL添加数据、修改数据、删除数据
.29.
352 0
云内容小助手
|
数据采集 运维 DataWorks
客户案例：数仓规范化-菜鸟数据模型管理实践（一）| 学习笔记
快速学习客户案例：数仓规范化-菜鸟数据模型管理实践。
云内容小助手
794 0
客户案例：数仓规范化-菜鸟数据模型管理实践（一）| 学习笔记