GROUP BY 子句
GROUP BY 子句将 SELECT 查询切换到聚合模式，其工作原理如下：
GROUP BY子句包含表达式列表（或单个表达式，被视为长度为一的列表）。该列表充当“分组键”，而每个单独的表达式将被称为“键表达式”。SELECT，HAVING和ORDER BY子句中的所有表达式**必须**根据键表达式**或**对非键表达式的聚合函数（包括普通列）进行计算。换句话说，从表中选择的每一列都必须在键表达式中使用，或在聚合函数中使用，但不能同时使用两者。- 聚合
SELECT查询的结果将包含与源表中“分组键”的唯一值数量一样多的行。通常，这会显着减少行数，通常是数量级的减少，但并非一定如此：如果所有“分组键”值都是不同的，则行数保持不变。
当您想按列号而不是列名对表中的数据进行分组时，请启用设置enable_positional_arguments。
还有一种方法可以对表进行聚合。如果查询仅包含聚合函数内部的表列，则可以省略GROUP BY 子句，并假定按空键集进行聚合。此类查询始终只返回一行。
NULL 处理
对于分组，ClickHouse 将NULL 解释为一个值，且NULL==NULL。这与大多数其他情况下的NULL 处理不同。
以下是一个示例，展示了这意味着什么。
假设您有以下表格
┌─x─┬────y─┐
│ 1 │ 2 │
│ 2 │ ᴺᵁᴸᴸ │
│ 3 │ 2 │
│ 3 │ 3 │
│ 3 │ ᴺᵁᴸᴸ │
└───┴──────┘
查询SELECT sum(x), y FROM t_null_big GROUP BY y 的结果是
┌─sum(x)─┬────y─┐
│ 4 │ 2 │
│ 3 │ 3 │
│ 5 │ ᴺᵁᴸᴸ │
└────────┴──────┘
您可以看到，对于y = NULL，GROUP BY 对x 进行了求和，就好像NULL 是这个值一样。
如果您将多个键传递给GROUP BY，结果将为您提供所有选择组合，就好像NULL 是一个特定值一样。
ROLLUP 修饰符
ROLLUP 修饰符用于根据GROUP BY 列表中键表达式的顺序计算键表达式的子总数。子总数行将添加到结果表之后。
子总数按相反顺序计算：首先计算列表中最后一个键表达式的子总数，然后计算前一个键表达式的子总数，依此类推，直到第一个键表达式。
在子总数行中，已“分组”的键表达式的值将设置为0 或空行。
请注意，HAVING 子句可能会影响子总数结果。
示例
考虑表 t
┌─year─┬─month─┬─day─┐
│ 2019 │ 1 │ 5 │
│ 2019 │ 1 │ 15 │
│ 2020 │ 1 │ 5 │
│ 2020 │ 1 │ 15 │
│ 2020 │ 10 │ 5 │
│ 2020 │ 10 │ 15 │
└──────┴───────┴─────┘
查询
SELECT year, month, day, count(*) FROM t GROUP BY ROLLUP(year, month, day);
由于GROUP BY 部分有三个键表达式，因此结果包含四个表，其中子总数从右到左“滚动”
GROUP BY year, month, day;GROUP BY year, month（而day列填充为零）；GROUP BY year（现在month, day列都填充为零）；- 以及总计（所有三个键表达式列都为零）。
┌─year─┬─month─┬─day─┬─count()─┐
│ 2020 │ 10 │ 15 │ 1 │
│ 2020 │ 1 │ 5 │ 1 │
│ 2019 │ 1 │ 5 │ 1 │
│ 2020 │ 1 │ 15 │ 1 │
│ 2019 │ 1 │ 15 │ 1 │
│ 2020 │ 10 │ 5 │ 1 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 2019 │ 1 │ 0 │ 2 │
│ 2020 │ 1 │ 0 │ 2 │
│ 2020 │ 10 │ 0 │ 2 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 2019 │ 0 │ 0 │ 2 │
│ 2020 │ 0 │ 0 │ 4 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 0 │ 0 │ 0 │ 6 │
└──────┴───────┴─────┴─────────┘
相同的查询也可以使用WITH 关键字编写。
SELECT year, month, day, count(*) FROM t GROUP BY year, month, day WITH ROLLUP;
另请参阅
- group_by_use_nulls 设置以实现 SQL 标准兼容性。
CUBE 修饰符
CUBE 修饰符用于计算GROUP BY 列表中键表达式的每个组合的子总数。子总数行将添加到结果表之后。
在子总数行中，所有已“分组”的键表达式的值将设置为0 或空行。
请注意，HAVING 子句可能会影响子总数结果。
示例
考虑表 t
┌─year─┬─month─┬─day─┐
│ 2019 │ 1 │ 5 │
│ 2019 │ 1 │ 15 │
│ 2020 │ 1 │ 5 │
│ 2020 │ 1 │ 15 │
│ 2020 │ 10 │ 5 │
│ 2020 │ 10 │ 15 │
└──────┴───────┴─────┘
查询
SELECT year, month, day, count(*) FROM t GROUP BY CUBE(year, month, day);
由于GROUP BY 部分有三个键表达式，因此结果包含八个表，其中包含所有键表达式组合的子总数
GROUP BY year, month, dayGROUP BY year, monthGROUP BY year, dayGROUP BY yearGROUP BY month, dayGROUP BY monthGROUP BY day- 以及总计。
从GROUP BY 中排除的列将填充为零。
┌─year─┬─month─┬─day─┬─count()─┐
│ 2020 │ 10 │ 15 │ 1 │
│ 2020 │ 1 │ 5 │ 1 │
│ 2019 │ 1 │ 5 │ 1 │
│ 2020 │ 1 │ 15 │ 1 │
│ 2019 │ 1 │ 15 │ 1 │
│ 2020 │ 10 │ 5 │ 1 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 2019 │ 1 │ 0 │ 2 │
│ 2020 │ 1 │ 0 │ 2 │
│ 2020 │ 10 │ 0 │ 2 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 2020 │ 0 │ 5 │ 2 │
│ 2019 │ 0 │ 5 │ 1 │
│ 2020 │ 0 │ 15 │ 2 │
│ 2019 │ 0 │ 15 │ 1 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 2019 │ 0 │ 0 │ 2 │
│ 2020 │ 0 │ 0 │ 4 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 0 │ 1 │ 5 │ 2 │
│ 0 │ 10 │ 15 │ 1 │
│ 0 │ 10 │ 5 │ 1 │
│ 0 │ 1 │ 15 │ 2 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 0 │ 1 │ 0 │ 4 │
│ 0 │ 10 │ 0 │ 2 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 0 │ 0 │ 5 │ 3 │
│ 0 │ 0 │ 15 │ 3 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 0 │ 0 │ 0 │ 6 │
└──────┴───────┴─────┴─────────┘
相同的查询也可以使用WITH 关键字编写。
SELECT year, month, day, count(*) FROM t GROUP BY year, month, day WITH CUBE;
另请参阅
- group_by_use_nulls 设置以实现 SQL 标准兼容性。
WITH TOTALS 修饰符
如果指定了WITH TOTALS 修饰符，将计算另一行。该行将包含包含默认值（零或空行）的键列，以及包含跨所有行计算的值（“总计”值）的聚合函数列。
此额外的行仅在JSON*、TabSeparated* 和Pretty* 格式中生成，与其他行分开
- 在
XML和JSON*格式中，该行将作为单独的“总计”字段输出。 - 在
TabSeparated*、CSV*和Vertical格式中，该行位于主结果之后，前面有一个空行（在其他数据之后）。 - 在
Pretty*格式中，该行将作为主结果之后的单独表输出。 - 在
Template格式中，该行将根据指定的模板输出。 - 在其他格式中，它不可用。
总计在SELECT 查询的结果中输出，而在INSERT INTO ... SELECT 中不输出。
当存在HAVING 时，WITH TOTALS 可以以不同的方式运行。行为取决于totals_mode 设置。
配置总计处理
默认情况下，totals_mode = 'before_having'。在这种情况下，“总计”是在所有行上计算的，包括那些未通过 HAVING 的行，以及max_rows_to_group_by。
其他替代方案包括仅在“总计”中包括通过 HAVING 的行，并且在设置max_rows_to_group_by 和group_by_overflow_mode = 'any' 时表现不同。
after_having_exclusive – 不要包括未通过max_rows_to_group_by 的行。换句话说，“总计”的行数将少于或等于省略max_rows_to_group_by 时的行数。
after_having_inclusive – 将所有未通过max_rows_to_group_by 的行包含在“总计”中。换句话说，“总计”的行数将大于或等于省略max_rows_to_group_by 时的行数。
after_having_auto – 计算通过 HAVING 的行数。如果它超过某个数量（默认情况下为 50%），则将所有未通过max_rows_to_group_by 的行包含在“总计”中。否则，不要包含它们。
totals_auto_threshold – 默认情况下为 0.5。after_having_auto 的系数。
如果未使用max_rows_to_group_by 和group_by_overflow_mode = 'any'，则after_having 的所有变体都相同，您可以使用它们中的任何一个（例如，after_having_auto）。
您可以在子查询中使用WITH TOTALS，包括JOIN 子句中的子查询（在这种情况下，相应的总计值将合并）。
GROUP BY ALL
GROUP BY ALL 等效于列出所有未作为聚合函数的 SELECT-ed 表达式。
例如
SELECT
a * 2,
b,
count(c),
FROM t
GROUP BY ALL
与以下相同
SELECT
a * 2,
b,
count(c),
FROM t
GROUP BY a * 2, b
对于特殊情况，如果函数同时具有聚合函数和其他字段作为其参数，则GROUP BY 键将包含我们可以从中提取的最大非聚合字段。
例如
SELECT
substring(a, 4, 2),
substring(substring(a, 1, 2), 1, count(b))
FROM t
GROUP BY ALL
与以下相同
SELECT
substring(a, 4, 2),
substring(substring(a, 1, 2), 1, count(b))
FROM t
GROUP BY substring(a, 4, 2), substring(a, 1, 2)
示例
示例
SELECT
count(),
median(FetchTiming > 60 ? 60 : FetchTiming),
count() - sum(Refresh)
FROM hits
与 MySQL（以及符合标准 SQL）不同，您无法获取不在键或聚合函数中的某些列的某些值（除了常量表达式）。为了解决这个问题，您可以使用“any”聚合函数（获取遇到的第一个值）或“min/max”。
示例
SELECT
domainWithoutWWW(URL) AS domain,
count(),
any(Title) AS title -- getting the first occurred page header for each domain.
FROM hits
GROUP BY domain
对于遇到的每个不同的键值，GROUP BY 计算一组聚合函数值。
GROUPING SETS 修饰符
这是最通用的修饰符。此修饰符允许手动指定多个聚合键集（分组集）。对每个分组集分别执行聚合，然后将所有结果组合在一起。如果列未出现在分组集中，则会填充默认值。
换句话说，上面描述的修饰符可以通过 GROUPING SETS 来表示。尽管使用 ROLLUP、CUBE 和 GROUPING SETS 修饰符的查询在语法上是相等的，但它们的执行方式可能不同。当 GROUPING SETS 尝试并行执行所有内容时，ROLLUP 和 CUBE 在单个线程中执行聚合的最终合并。
在源列包含默认值的情况下，可能难以区分一行是使用这些列作为键的聚合的一部分还是不是。为了解决这个问题，必须使用 GROUPING 函数。
示例
以下两个查询是等效的。
-- Query 1
SELECT year, month, day, count(*) FROM t GROUP BY year, month, day WITH ROLLUP;
-- Query 2
SELECT year, month, day, count(*) FROM t GROUP BY
GROUPING SETS
(
(year, month, day),
(year, month),
(year),
()
);
另请参阅
- group_by_use_nulls 设置以实现 SQL 标准兼容性。
实现细节
聚合是列式 DBMS 最重要的功能之一，因此它的实现是 ClickHouse 中优化最重的部分之一。默认情况下，聚合是在内存中使用哈希表完成的。它有 40 多种专门化，这些专门化会根据“分组键”数据类型自动选择。
根据表排序键进行 GROUP BY 优化
如果表按某个键排序，并且 GROUP BY 表达式至少包含排序键的前缀或单射函数，则聚合可以更有效地执行。在这种情况下，当从表中读取新键时，可以完成聚合的中间结果并发送到客户端。此行为通过 optimize_aggregation_in_order 设置打开。这种优化减少了聚合期间的内存使用，但在某些情况下可能会减慢查询执行速度。
外部内存中的 GROUP BY
您可以启用将临时数据转储到磁盘以限制 GROUP BY 期间的内存使用。该 max_bytes_before_external_group_by 设置确定将 GROUP BY 临时数据转储到文件系统的阈值 RAM 使用量。如果设置为 0（默认值），则禁用它。
当使用 max_bytes_before_external_group_by 时，我们建议您将 max_memory_usage 设置为大约两倍高。这是必要的，因为聚合有两个阶段：读取数据并形成中间数据（1）和合并中间数据（2）。将数据转储到文件系统只能在阶段 1 期间进行。如果临时数据未转储，则阶段 2 可能需要与阶段 1 相同的内存量。
例如，如果 max_memory_usage 设置为 10000000000 并且您想要使用外部聚合，那么将 max_bytes_before_external_group_by 设置为 10000000000 以及 max_memory_usage 设置为 20000000000 是有意义的。当触发外部聚合（如果至少有一个临时数据转储）时，RAM 的最大消耗仅略高于 max_bytes_before_external_group_by。
使用分布式查询处理，外部聚合在远程服务器上执行。为了使请求服务器仅使用少量 RAM，请将 distributed_aggregation_memory_efficient 设置为 1。
当合并刷新到磁盘的数据以及当启用 distributed_aggregation_memory_efficient 设置时从远程服务器合并结果时，会消耗总 RAM 量中的 1/256 * the_number_of_threads。
当启用外部聚合时，如果数据少于 max_bytes_before_external_group_by（即数据未刷新），则查询运行速度与没有外部聚合一样快。如果任何临时数据被刷新，运行时间将延长几倍（大约三倍）。
如果您在 GROUP BY 之后有一个带有 LIMIT 的 ORDER BY，那么使用的 RAM 量取决于 LIMIT 中的数据量，而不是整个表中的数据量。但是，如果 ORDER BY 没有 LIMIT，请不要忘记启用外部排序（max_bytes_before_external_sort）。