GROUP BY 子句

GROUP BY 子句将 SELECT 查询切换到聚合模式，其工作方式如下

GROUP BY 子句包含表达式列表（或单个表达式，它被认为是长度为一的列表）。此列表充当“分组键”，而每个单独的表达式将被称为“键表达式”。
SELECT、HAVING 和 ORDER BY 子句中的所有表达式必须基于键表达式或基于非键表达式（包括普通列）上的聚合函数来计算。换句话说，从表中选择的每个列必须在键表达式或聚合函数内部使用，但不能同时使用。
聚合 SELECT 查询的结果将包含与源表中“分组键”的唯一值一样多的行。通常，这会显着减少行数，通常会减少几个数量级，但不一定：如果所有“分组键”值都不同，则行数保持不变。

当您想要按列号而不是列名对表中的数据进行分组时，请启用设置 enable_positional_arguments。

注意

还有另一种方法可以对表运行聚合。如果查询仅在聚合函数内部包含表列，则可以省略 GROUP BY clause，并且假定按空键集进行聚合。此类查询始终返回一行。

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 │ ᴺᵁᴸᴸ │
└────────┴──────┘

您可以看到 GROUP BY 对于 y = NULL 将 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 部分有三个键表达式，因此结果包含四个表，其中小计从右到左“汇总”

按年、月、日分组;
按年、月分组（并且 day 列填充为零）；
按年分组（现在 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─┬─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* 格式中，此行作为单独的 ‘totals’ 字段输出。
在 TabSeparated*、CSV* 和 Vertical 格式中，该行在主要结果之后出现，前面有一个空行（在其他数据之后）。
在 Pretty* 格式中，该行作为主结果之后的单独表输出。
在 Template 格式中，该行根据指定的模板输出。
在其他格式中，它不可用。

注意

totals 在 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 表达式。

例如

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_ratio_before_external_group_by，这允许仅在查询达到某个已用内存阈值后才在外部内存中使用 GROUP BY。

当使用 max_bytes_before_external_group_by 时，我们建议您将 max_memory_usage 设置为大约两倍高（或 max_bytes_ratio_before_external_group_by=0.5）。这是必要的，因为聚合有两个阶段：读取数据和形成中间数据 (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)。

NULL 处理​

ROLLUP 修饰符​

CUBE 修饰符​

WITH TOTALS 修饰符​

配置总计处理​

GROUP BY ALL​

示例​

GROUPING SETS 修饰符​

实现细节​

GROUP BY 优化（取决于表排序键）​

外部内存中的 GROUP BY​

NULL 处理

ROLLUP 修饰符

CUBE 修饰符

WITH TOTALS 修饰符

配置总计处理

GROUP BY ALL

示例

GROUPING SETS 修饰符

实现细节

GROUP BY 优化（取决于表排序键）

外部内存中的 GROUP BY