类别：

查询语法

GROUP BY GROUPING SETS

GROUPING SETS 是 GROUP BY 子句的强大扩展，可在单个语句中计算多个 GROUP BY 子句。分组集 是一组维度列。

GROUPING SETS 表达式可以与其他 GROUP BY 表达式组合，从而使此结构成为 GROUP BY 子句的集成部分，而不是单独的构造。例如，您可以编写 GROUP BY x, GROUPING SETS(y, z) 以按照列 x 进行分组，并对 y 和 z 进行单独的分组操作。

GROUPING SETS 表达式等效于同一结果集中两个或多个 GROUP BY 操作的并集。例如：

  • GROUP BY GROUPING SETS((a)) 等同于单个分组集操作 GROUP BY a。

  • GROUP BY GROUPING SETS((a), (b)) 等同于 GROUP BY a UNION ALL GROUP BY b。

请注意，不带额外括号的 GROUPING SETS(a, b) 在逻辑上等同于 GROUPING SETS((a), (b))，因为两者都会创建两个单独的分组集，一个用于列 a，一个用于列 b。此表达式与 GROUPING SETS((a, b)) 不同，这将创建按两列分组的单个分组集。

语法

SELECT ...
FROM ...
[ ... ]
GROUP BY [ groupItem [ , groupItem [ , ... ] ] , ] GROUPING SETS ( groupSet [ , groupSet [ , ... ] ] )
[ ... ]
Copy

其中：

groupItem ::= { <column_alias> | <position> | <expr> }

groupSet ::= groupItem | ( groupItem [ , groupItem [ , ... ] ] )
Copy

参数

column_alias

出现在查询块的 SELECT 列表中的列别名。

position

表达式在 SELECT 列表中的位置。

expr

当前作用域中表的任意表达式。

使用说明

  • Snowflake 允许同一查询块中最多有 128 个分组集。

  • 带括号的语法变体：

    • GROUPING SETS(a, b) 是 GROUPING SETS((a), (b)) 的简写。两者都会创建两个单独的分组集：一个按列 a 分组，另一个按列 b 分组。

    • GROUPING SETS((a, b)) 创建按列 a 和 b 分组的单个分组集（类似于 GROUP BY a, b）。

  • 您可以将常规的 GROUP BY 列与 GROUPING SETS 结合：GROUP BY x, GROUPING SETS(y, z) 会按列 x 进行分组，并对 y 和 z 进行单独的分组。

  • 输出通常包含一些 NULL 值。由于 GROUP BY GROUPING SETS 合并了两个或多个结果集的结果，每个结果集都按不同条件进行分组，因此在一个结果集中具有单个值的某些列可能在另一个结果集中具有多个对应值。例如，如果将按部门分组的雇员集合与按资历分组的集合进行并集,则资历最高的集合中的成员不一定都在同一部门，因此，department_name 的值设置为 NULL。基于此原因，以下示例包含 NULL 值。

另请参阅

示例

这些示例使用有关受过灾难援助培训的护士的信息表。所有这些护士都拥有护士执照（例如，RN 拥有“注册护士”执照），以及与灾难相关的专业领域（例如，搜索和救援、无线电通信等）的其他执照。此示例简化并仅使用两类执照：

  • 护理：RN（注册护士）和 LVN（执业职业护士）。

  • 非专业（“业余”）无线电：业余无线电执照包括“Technician”、“General”和“Amateur Extra”。

以下命令创建并加载表：

CREATE or replace TABLE nurses (
  ID INTEGER,
  full_name VARCHAR,
  medical_license VARCHAR,   -- LVN, RN, etc.
  radio_license VARCHAR      -- Technician, General, Amateur Extra
  )
  ;

INSERT INTO nurses
    (ID, full_name, medical_license, radio_license)
  VALUES
    (201, 'Thomas Leonard Vicente', 'LVN', 'Technician'),
    (202, 'Tamara Lolita VanZant', 'LVN', 'Technician'),
    (341, 'Georgeann Linda Vente', 'LVN', 'General'),
    (471, 'Andrea Renee Nouveau', 'RN', 'Amateur Extra')
    ;
Copy

此查询使用 GROUP BY GROUPING SETS：

SELECT COUNT(*), medical_license, radio_license
  FROM nurses
  GROUP BY GROUPING SETS (medical_license, radio_license)
  ORDER BY 3 DESC NULLS FIRST;
Copy

前两行显示 RNs 和 LVNs 的数量（两种类型的护理执照）。这两行的 radio_license 列中的 NULL 值是经过深思熟虑的；该查询将所有 LVNs 分组在一起（以及所有 RNs 分组在一起），无论其无线电执照如何，因此结果无法在每一行的 radio_license 列中显示必然适用于该行中分组的所有 LVNs 或 RNs 的值。

接下来的三行显示了拥有每种业余无线电执照（“Technician”、“General”和“Amateur Extra”）的护士人数。这三行中每一行的 medical_license 的 NULL 值都是经过深思熟虑的，因为没有一个医疗执照必须适用于每行的所有成员。

+----------+-----------------+---------------+
| COUNT(*) | MEDICAL_LICENSE | RADIO_LICENSE |
|----------+-----------------+---------------|
|        3 | LVN             | NULL          |
|        1 | RN              | NULL          |
|        2 | NULL            | Technician    |
|        1 | NULL            | General       |
|        1 | NULL            | Amateur Extra |
+----------+-----------------+---------------+

以下示例演示了按列分别分组与按列联合分组之间的区别。该查询按 medical_license 和 radio_license 的组合进行分组：

SELECT COUNT(*), medical_license, radio_license
  FROM nurses
  GROUP BY GROUPING SETS ((medical_license, radio_license))
  ORDER BY 3 DESC NULLS FIRST;
Copy

此查询生成的行中，medical_license 和 radio_license 的每个组合与其计数一起显示。与前面的示例不同，输出中没有 NULL 值，因为查询按两列一起分组，而不是为每列创建单独的分组。

+----------+-----------------+---------------+
| COUNT(*) | MEDICAL_LICENSE | RADIO_LICENSE |
|----------+-----------------+---------------|
|        2 | LVN             | Technician    |
|        1 | LVN             | General       |
|        1 | RN              | Amateur Extra |
+----------+-----------------+---------------+

下一个示例显示当某些列包含 NULL 值时会发生什么情况。首先增加三名尚未获得业余无线电执照的新护士。

INSERT INTO nurses
    (ID, full_name, medical_license, radio_license)
  VALUES
    (101, 'Lily Vine', 'LVN', NULL),
    (102, 'Larry Vancouver', 'LVN', NULL),
    (172, 'Rhonda Nova', 'RN', NULL)
    ;
Copy

然后运行与之前相同的查询：

SELECT COUNT(*), medical_license, radio_license
  FROM nurses
  GROUP BY GROUPING SETS (medical_license, radio_license)
  ORDER BY 3 DESC NULLS FIRST;
Copy

为什么现在两列中出现了包含 NULL 的行？如果所有值都是 NULL，为什么 COUNT(*) 结果等于 3？

答案是，该行的 radio_license 列中的 NULL 是因为有三名护士没有取得无线电执照。（查询 SELECT DISTINCT radio_license FROM nurses 现在返回四个不同的值：“Technician”、“General”、“Amateur Extra”和“NULL”。）

medical_licenses 列出现 NULL 值的原因与早期查询结果中出现 NULL 值的原因相同：此行中计数的护士具有不同的医疗执照，因此没有一个值（RN 或 LVN）必然适用于此行中计数的所有护士。

+----------+-----------------+---------------+
| COUNT(*) | MEDICAL_LICENSE | RADIO_LICENSE |
|----------+-----------------+---------------|
|        2 | RN              | NULL          |
|        5 | LVN             | NULL          |
|        3 | NULL            | NULL          |
|        2 | NULL            | Technician    |
|        1 | NULL            | General       |
|        1 | NULL            | Amateur Extra |
+----------+-----------------+---------------+

以下示例演示了常规 GROUP BY 列与 GROUPING SETS 集合的组合。此查询按 medical_license 分组，在每个医疗许可证组内，为每个 radio_license 值和所有组合的无线电许可证分别创建聚合：

SELECT COUNT(*), medical_license, radio_license
  FROM nurses
  GROUP BY medical_license, GROUPING SETS (radio_license, ())
  ORDER BY 3 DESC NULLS FIRST;
Copy

对于每个医疗执照（LVN 和 RN），输出显示：

  • 按以下每个特定 radio_license 值进行分组的行：Technician、General、Amateur Extra 或 NULL（对于那些没有无线电执照的人）

  • 包含 NULL 值的汇总行，其中 radio_license 列的值表示所有持有该医疗执照的护士，而不论其是否持有无线电执照

+----------+-----------------+---------------+
| COUNT(*) | MEDICAL_LICENSE | RADIO_LICENSE |
|----------+-----------------+---------------|
|        2 | LVN             | NULL          |
|        1 | RN              | NULL          |
|        2 | RN              | NULL          |
|        5 | LVN             | NULL          |
|        2 | LVN             | Technician    |
|        1 | LVN             | General       |
|        1 | RN              | Amateur Extra |
+----------+-----------------+---------------+

您可以将此输出与不带 GROUPING SETS 子句的 GROUP BY 查询的输出进行比较：

SELECT COUNT(*), medical_license, radio_license
  FROM nurses
  GROUP BY medical_license, radio_license
  ORDER BY 3 DESC NULLS FIRST;
Copy
+----------+-----------------+---------------+
| COUNT(*) | MEDICAL_LICENSE | RADIO_LICENSE |
|----------+-----------------+---------------|
|        2 | LVN             | NULL          |
|        1 | RN              | NULL          |
|        2 | LVN             | Technician    |
|        1 | LVN             | General       |
|        1 | RN              | Amateur Extra |
+----------+-----------------+---------------+

使用 GROUPING 函数

GROUPING 实用函数有助于确定生成每行的聚合级别。这对于区分由分组操作产生的 NULL 值与数据中的实际 NULL 值非常有用。

GROUPING 函数返回以下值：

  • 对于按照指定列进行分组的行，值为 0

  • 对于未按指定列分组的行（其中 NULL 由于聚合而出现），值为 1

此示例为查询添加了 GROUPING 函数，以澄清输出：

SELECT
    COUNT(*),
    medical_license,
    radio_license,
    GROUPING(medical_license) AS grp_medical,
    GROUPING(radio_license) AS grp_radio
  FROM nurses
  GROUP BY GROUPING SETS (medical_license, radio_license);
Copy

grp_medical 和 grp_radio 列显示了哪些列用于分组：

  • 第 1-2 行：按 medical_license (grp_medical=0) 分组，而不是按 radio_license (grp_radio=1) 分组

  • 第 3-6 行：按 radio_license (grp_radio=0) 分组，而不是按 medical_license (grp_medical=1) 分组

  • 第 6 行：radio_license 中的 NULL 值为实际数据 (grp_radio=0)，而 medical_license 中的 NULL 来自聚合 (grp_medical=1)

+----------+-----------------+---------------+-------------+-----------+
| COUNT(*) | MEDICAL_LICENSE | RADIO_LICENSE | GRP_MEDICAL | GRP_RADIO |
|----------+-----------------+---------------+-------------+-----------|
|        2 | RN              | NULL          |           0 |         1 |
|        5 | LVN             | NULL          |           0 |         1 |
|        2 | NULL            | Technician    |           1 |         0 |
|        1 | NULL            | General       |           1 |         0 |
|        3 | NULL            | NULL          |           1 |         0 |
|        1 | NULL            | Amateur Extra |           1 |         0 |
+----------+-----------------+---------------+-------------+-----------+