ByteHouse云数仓版
ByteHouse云数仓版
- 文档首页
ByteHouse云数仓版开发参考SQL 参考函数参考GROUPING 函数
文档反馈
问问助手本文将介绍 GROUPING 函数与 GROUPING SETS 修饰符的用法，并介绍 GROUPING SETS 与 CUBE 修饰符、基础 GROUP BY 子句的核心区别与适用场景。
GROUPING
ROLLUP 和 CUBE 是 GROUP BY 的修饰符，均用于计算数据小计：
ROLLUP接收列的有序列表（如(day, month, year)），会在聚合的每个层级依次计算小计，最终返回总计结果；CUBE会针对指定列的所有可能组合计算小计。
GROUPING 函数用于区分 ROLLUP 或 CUBE 返回结果中的两类行：一类是更高层级的聚合行（superaggregate，即小计行），另一类是未使用 ROLLUP/CUBE 修饰时，GROUP BY 本身应返回的普通分组行。
GROUPING 函数接收多个列作为参数，返回一个位掩码（bitmask）值，其返回值含义如下：
1：表示该行是经ROLLUP或CUBE修饰的GROUP BY返回的小计行；0：表示该行不是经ROLLUP或CUBE修饰的GROUP BY返回的小计行（即普通分组行）。
GROUPING SETS
默认情况下，CUBE 修饰符会对传入的所有列计算其所有可能组合的小计；而 GROUPING SETS 支持直接指定需要计算的具体列组合，使用更灵活。ROLLUP、CUBE 及 GROUPING SETS 修饰符的典型应用场景是层次化数据分析。
本文示例基于一张数据表，该表记录了两个数据中心中安装的 Linux 发行版及其对应版本信息；按「发行版」「版本」「数据中心位置」维度拆分查看这些数据，能为业务分析提供有效参考价值。
示例数据
本文将使用以下数据表作为示例，示例命令如下：
创建表：
CREATE DATABASE bh_demo; CREATE TABLE bh_demo.servers ( datacenter VARCHAR(255), distro VARCHAR(255) NOT NULL, version VARCHAR(50) NOT NULL, quantity INT ) ENGINE = CnchMergeTree() ORDER BY (datacenter, distro, version);
插入数据：
INSERT INTO bh_demo.servers(datacenter, distro, version, quantity) VALUES ('Schenectady', 'Arch','2022.08.05',50), ('Westport', 'Arch','2022.08.05',40), ('Schenectady','Arch','2021.09.01',30), ('Westport', 'Arch','2021.09.01',20), ('Schenectady','Arch','2020.05.01',10), ('Westport', 'Arch','2020.05.01',5), ('Schenectady','RHEL','9',60), ('Westport','RHEL','9',70), ('Westport','RHEL','7',80), ('Schenectady','RHEL','7',80)
查询数据：
SELECT * FROM bh_demo.servers;
查询结果：
┌─datacenter──┬─distro─┬─version────┬─quantity─┐ │ Schenectady │ Arch │ 2020.05.01 │ 10 │ │ Schenectady │ Arch │ 2021.09.01 │ 30 │ │ Schenectady │ Arch │ 2022.08.05 │ 50 │ │ Schenectady │ RHEL │ 7 │ 80 │ │ Schenectady │ RHEL │ 9 │ 60 │ │ Westport │ Arch │ 2020.05.01 │ 5 │ │ Westport │ Arch │ 2021.09.01 │ 20 │ │ Westport │ Arch │ 2022.08.05 │ 40 │ │ Westport │ RHEL │ 7 │ 80 │ │ Westport │ RHEL │ 9 │ 70 │ └─────────────┴────────┴────────────┴──────────┘ 10 行数据。耗时: 0.359 秒。
简单查询
以下查询通过基础 GROUP BY 子句，按不同维度统计服务器数量，明确各维度下的数量分布：
按「数据中心 + 发行版」组合维度统计
统计每个数据中心（datacenter）中各 Linux 发行版（distro）对应的服务器总数量。
执行查询：SELECT datacenter, distro, SUM (quantity) qty FROM bh_demo.servers GROUP BY datacenter, distro;查询结果：
┌─datacenter──┬─distro─┬─qty─┐ │ Schenectady │ RHEL │ 140 │ │ Westport │ Arch │ 65 │ │ Schenectady │ Arch │ 90 │ │ Westport │ RHEL │ 150 │ └─────────────┴────────┴─────┘ 返回 4 行。耗时：0.060 秒。按「数据中心」单一维度统计
统计每个数据中心（datacenter）对应的所有服务器总数量（不区分发行版）。
执行查询：SELECT datacenter, SUM (quantity) qty FROM bh_demo.servers GROUP BY datacenter;查询结果：
┌─datacenter──┬─qty─┐ │ Westport │ 215 │ │ Schenectady │ 230 │ └─────────────┴─────┘ 返回 2 行。用时:0.051 秒。按「发行版」单一维度统计
统计各 Linux 发行版（distro）对应的服务器总数量（不区分数据中心）。
执行查询：SELECT distro, SUM (quantity) qty FROM bh_demo.servers GROUP BY distro;查询结果：
┌─distro─┬─qty─┐ │ Arch │ 155 │ │ RHEL │ 290 │ └────────┴─────┘ 查询返回 2 行。用时：0.037 秒。全量统计（无分组维度）
统计所有服务器的总数量（不区分数据中心和发行版）。
执行查询：SELECT SUM(quantity) qty FROM bh_demo.servers;查询结果：
┌─qty─┐ │ 445 │ └─────┘ 返回 1 行。耗时:0.030 秒。
对比 GROUP BY 与 GROUPING SETS 的查询差异
以下示例对比了两种实现 “多维度聚合统计” 的方式：
- 前者使用基础
GROUP BY结合UNION ALL拼接子查询； - 后者使用
GROUPING SETS单语句实现。
以下示例的核心目的均为一次性获取「数据中心 + 发行版」「数据中心」「发行版」及「全量」四个维度的服务器数量统计结果。
使用
GROUP BY查询
执行查询：SELECT datacenter, distro, SUM (quantity) qty FROM bh_demo.servers GROUP BY datacenter, distro UNION ALL SELECT datacenter, null, SUM (quantity) qty FROM bh_demo.servers GROUP BY datacenter UNION ALL SELECT null, distro, SUM (quantity) qty FROM bh_demo.servers GROUP BY distro UNION ALL SELECT null, null, SUM(quantity) qty FROM bh_demo.servers;查询结果：
┌─datacenter─┬─distro─┬─qty─┐ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ 445 │ └────────────┴────────┴─────┘ ┌─datacenter──┬─distro─┬─qty─┐ │ Westport │ ᴺᵁᴸᴸ │ 215 │ │ Schenectady │ ᴺᵁᴸᴸ │ 230 │ └─────────────┴────────┴─────┘ ┌─datacenter──┬─distro─┬─qty─┐ │ Schenectady │ RHEL │ 140 │ │ Westport │ Arch │ 65 │ │ Schenectady │ Arch │ 90 │ │ Westport │ RHEL │ 150 │ └─────────────┴────────┴─────┘ ┌─datacenter─┬─distro─┬─qty─┐ │ ᴺᵁᴸᴸ │ Arch │ 155 │ │ ᴺᵁᴸᴸ │ RHEL │ 290 │ └────────────┴────────┴─────┘ 返回 9 行。耗时：0.066 秒。使用
GROUPING SETS查询
执行查询：SELECT datacenter, distro, SUM (quantity) qty FROM bh_demo.servers GROUP BY GROUPING SETS( (datacenter,distro), (datacenter), (distro), () );查询结果：
┌─datacenter──┬─distro─┬─qty─┐ │ Schenectady │ RHEL │ 140 │ │ Westport │ Arch │ 65 │ │ Schenectady │ Arch │ 90 │ │ Westport │ RHEL │ 150 │ └─────────────┴────────┴─────┘ ┌─datacenter──┬─distro─┬─qty─┐ │ Westport │ │ 215 │ │ Schenectady │ │ 230 │ └─────────────┴────────┴─────┘ ┌─datacenter─┬─distro─┬─qty─┐ │ │ │ 445 │ └────────────┴────────┴─────┘ ┌─datacenter─┬─distro─┬─qty─┐ │ │ Arch │ 155 │ │ │ RHEL │ 290 │ └────────────┴────────┴─────┘ 返回9行。耗时：0.040 秒。
对比 CUBE 与 GROUPING SETS 的查询差异
CUBE 会对指定列生成所有可能的组合维度（包含无效 / 无意义的组合），例如，以下 CUBE 查询中使用的 CUBE(datacenter, distro, version) 修饰符，生成的列组合层次结构可能不符合实际分析需求。因为跨不同发行版（Arch 和 RHEL）比较版本并无实际意义，二者的发布周期与版本命名规范存在差异，直接对比缺乏参考价值。
而 GROUPING SETS 支持自定义仅需统计的有效维度组合，更贴合业务需求，例如，以下示例中 GROUPING SETS 将 distro（发行版）与 version（版本）纳入同一分组集合，确保版本对比始终基于同一发行版维度，分析逻辑更合理。
使用 CUBE 查询
执行查询：SELECT datacenter, distro, version, SUM(quantity) FROM bh_demo.servers GROUP BY CUBE(datacenter,distro,version) ORDER BY datacenter, distro;查询结果：
┌─datacenter──┬─distro─┬─version────┬─sum(quantity)─┐ │ │ │ 7 │ 160 │ │ │ │ 2020.05.01 │ 15 │ │ │ │ 2021.09.01 │ 50 │ │ │ │ 2022.08.05 │ 90 │ │ │ │ 9 │ 130 │ │ │ │ │ 445 │ │ │ Arch │ 2021.09.01 │ 50 │ │ │ Arch │ 2022.08.05 │ 90 │ │ │ Arch │ 2020.05.01 │ 15 │ │ │ Arch │ │ 155 │ │ │ RHEL │ 9 │ 130 │ │ │ RHEL │ 7 │ 160 │ │ │ RHEL │ │ 290 │ │ Schenectady │ │ 9 │ 60 │ │ Schenectady │ │ 2021.09.01 │ 30 │ │ Schenectady │ │ 7 │ 80 │ │ Schenectady │ │ 2022.08.05 │ 50 │ │ Schenectady │ │ 2020.05.01 │ 10 │ │ Schenectady │ │ │ 230 │ │ Schenectady │ Arch │ 2022.08.05 │ 50 │ │ Schenectady │ Arch │ 2021.09.01 │ 30 │ │ Schenectady │ Arch │ 2020.05.01 │ 10 │ │ Schenectady │ Arch │ │ 90 │ │ Schenectady │ RHEL │ 7 │ 80 │ │ Schenectady │ RHEL │ 9 │ 60 │ │ Schenectady │ RHEL │ │ 140 │ │ Westport │ │ 9 │ 70 │ │ Westport │ │ 2020.05.01 │ 5 │ │ Westport │ │ 2022.08.05 │ 40 │ │ Westport │ │ 7 │ 80 │ │ Westport │ │ 2021.09.01 │ 20 │ │ Westport │ │ │ 215 │ │ Westport │ Arch │ 2020.05.01 │ 5 │ │ Westport │ Arch │ 2021.09.01 │ 20 │ │ Westport │ Arch │ 2022.08.05 │ 40 │ │ Westport │ Arch │ │ 65 │ │ Westport │ RHEL │ 9 │ 70 │ │ Westport │ RHEL │ 7 │ 80 │ │ Westport │ RHEL │ │ 150 │ └─────────────┴────────┴────────────┴───────────────┘ 返回 39 行。耗时：0.051 秒。注意
若版本未与发行版建立关联维度，即上述示例中单独拆分的
version（发行版版本），则该分组逻辑缺乏实际分析意义；若需分析的是内核版本，则该组合逻辑更合理，原因是内核版本具备跨发行版的通用性，可与任一发行版关联。
这种场景下，选择GROUPING SETS进行分组查询（如下一个示例所示），会更贴合实际分析需求。使用 GROUPING SETS 查询
执行查询：SELECT datacenter, distro, version, SUM(quantity) FROM bh_demo.servers GROUP BY GROUPING SETS ( (datacenter, distro, version), (datacenter, distro));查询结果：
┌─datacenter──┬─distro─┬─version────┬─sum(quantity)─┐ │ Westport │ RHEL │ 9 │ 70 │ │ Schenectady │ Arch │ 2022.08.05 │ 50 │ │ Schenectady │ Arch │ 2021.09.01 │ 30 │ │ Schenectady │ RHEL │ 7 │ 80 │ │ Westport │ Arch │ 2020.05.01 │ 5 │ │ Westport │ RHEL │ 7 │ 80 │ │ Westport │ Arch │ 2021.09.01 │ 20 │ │ Westport │ Arch │ 2022.08.05 │ 40 │ │ Schenectady │ RHEL │ 9 │ 60 │ │ Schenectady │ Arch │ 2020.05.01 │ 10 │ └─────────────┴────────┴────────────┴───────────────┘ ┌─datacenter──┬─distro─┬─version─┬─sum(quantity)─┐ │ Schenectady │ RHEL │ │ 140 │ │ Westport │ Arch │ │ 65 │ │ Schenectady │ Arch │ │ 90 │ │ Westport │ RHEL │ │ 150 │ └─────────────┴────────┴─────────┴───────────────┘ 返回 14 行。耗时： 0.041 秒