类别：

查询语法

GROUP BY GROUPING SETS

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

GROUP BY GROUPING SETS 等同于同一结果集中的两个或多个 GROUP BY 操作的 UNION ：

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

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

语法

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

其中：

groupSet ::= { <column_alias> | <position> | <expr> }
Copy
column_alias

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

position

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

expr

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

使用说明

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

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

示例

这些示例使用有关受过灾难援助培训的护士的信息表。所有这些护士都拥有护士执照（例如 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);
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 |
+----------+-----------------+---------------+

下一个示例显示当某些列包含 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);
Copy

输出：

前 5 行与上一个查询相同。

最后一行一开始可能会令人困惑：为什么两列中有一行具有 NULL ？如果所有值都是 NULL，为什么 COUNT(*) 等于 3？

答案是该行的 RADIO_LICENSE 列中出现 NULL，因为三名护士没有任何无线电执照。（“SELECT DISTINCT RADIO_LICENSE FROM 护士”现可返回四个非重复值：“Technician”、“General”、“Amateur Extra”和“NULL”。）

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

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

如果需要，可以将此输出与不带 GROUPING SETS 子句的 GROUP BY 输出进行比较：

SELECT COUNT(*), medical_license, radio_license
  FROM nurses
  GROUP BY medical_license, radio_license;
Copy

输出：

+----------+-----------------+---------------+
| COUNT(*) | MEDICAL_LICENSE | RADIO_LICENSE |
|----------+-----------------+---------------|
|        2 | LVN             | Technician    |
|        1 | LVN             | General       |
|        1 | RN              | Amateur Extra |
|        2 | LVN             | NULL          |
|        1 | RN              | NULL          |
+----------+-----------------+---------------+
语言: 中文