跳到主要内容
跳到主要内容

GROUP BY 子句

GROUP BY 子句将 SELECT 查询切换到聚合模式,工作原理如下:

  • GROUP BY 子句包含一个表达式列表(或者一个表达式,视为长度为一的列表)。该列表作为“分组键”,而每个单独的表达式将被称为“键表达式”。
  • 所有在 SELECTHAVINGORDER BY 子句中的表达式 必须 基于键表达式 在非键表达式(包括普通列)上的 聚合函数 来计算。换句话说,从表中选择的每一列必须要么用于键表达式,要么在聚合函数内使用,但不能同时用于两者。
  • 聚合 SELECT 查询的结果行数将与源表中“分组键”的唯一值数量相等。通常,这会显著降低行数,往往是几个数量级,但不一定:如果所有“分组键”值都是唯一的,行数保持不变。

当您想根据列号而不是列名对表中的数据进行分组时,可以启用设置 enable_positional_arguments

备注

还有另一种在表上运行聚合的方法。如果查询仅在聚合函数内包含表列,则 GROUP BY 子句可以省略,并假定使用空的键集进行聚合。这种查询始终返回正好一行。

NULL 处理

在分组时,ClickHouse 将 NULL 视为一个值,并且 NULL==NULL。这与其他上下文中的 NULL 处理有所不同。

以下是一个示例,说明这意味着什么。

假设您有以下表:

查询 SELECT sum(x), y FROM t_null_big GROUP BY y 的结果为:

您可以看到 GROUP BYy = NULL 的处理对 x 进行了求和,就好像 NULL 是这个值一样。

如果您向 GROUP BY 传递多个键,结果将给出所选内容的所有组合,就好像 NULL 是一个特定的值。

ROLLUP 修饰符

ROLLUP 修饰符用于根据 GROUP BY 列表中的顺序计算键表达式的子总计。子总计行在结果表之后添加。

子总计是按相反顺序计算的:首先为列表中的最后一个键表达式计算子总计,然后是前一个,以此类推,直到第一个键表达式。

在子总计行中,已“分组”的键表达式的值被设置为 0 或空行。

备注

请注意, HAVING 子句可能会影响子总计结果。

示例

考虑表 t:

查询:

由于 GROUP BY 部分有三个键表达式,因此结果包含四个带有子总计的表,这些总计是从右到左“滚动”得出的:

  • GROUP BY year, month, day
  • GROUP BY year, monthday 列填充为零);
  • GROUP BY year(现在 month, day 列都填充为零);
  • 和总计(所有三个键表达式列都是零)。

同样的查询也可以使用 WITH 关键字重写。

另请参阅

CUBE 修饰符

CUBE 修饰符用于计算 GROUP BY 列表中每个键表达式组合的子总计。子总计行在结果表之后添加。

在子总计行中,所有“分组”键表达式的值被设置为 0 或空行。

备注

请注意, HAVING 子句可能会影响子总计结果。

示例

考虑表 t:

查询:

由于 GROUP BY 部分有三个键表达式,因此结果包含八个带有所有键表达式组合的子总计的表:

  • GROUP BY year, month, day
  • GROUP BY year, month
  • GROUP BY year, day
  • GROUP BY year
  • GROUP BY month, day
  • GROUP BY month
  • GROUP BY day
  • 和总计。

未包含在 GROUP BY 中的列填充为零。

同样的查询也可以使用 WITH 关键字重写。

另请参阅

WITH TOTALS 修饰符

如果指定了 WITH TOTALS 修饰符,将计算另一行。该行将具有包含默认值(零或空行)的键列,以及在所有行上计算的聚合函数的列(“总计”值)。

此额外行仅在 JSON*TabSeparated*Pretty* 格式中生成,与其他行分开:

  • XMLJSON* 格式中,此行作为单独的 'totals' 字段输出。
  • TabSeparated*CSV*Vertical 格式中,行在主要结果之后,前面有一行空行(在其他数据之后)。
  • Pretty* 格式中,该行在主要结果后作为单独的表输出。
  • Template 格式中,行根据指定的模板输出。
  • 在其他格式中不可用。
备注

SELECT 查询的结果中输出 totals,但在 INSERT INTO ... SELECT 中不会输出。

当存在 HAVING 时,WITH TOTALS 可以以不同方式运行。该行为取决于 totals_mode 设置。

配置总计处理

默认情况下,totals_mode = 'before_having'。在这种情况下,'totals' 是基于所有行计算的,包括那些未通过 HAVING 和 max_rows_to_group_by 的行。

其他选项包括仅将通过 HAVING 的行包含在 'totals' 中,并在 max_rows_to_group_bygroup_by_overflow_mode = 'any' 设置上表现不同。

after_having_exclusive – 不包括未通过 max_rows_to_group_by 的行。换句话说,'totals' 的行数将少于或等于未通过 max_rows_to_group_by 时的行数。

after_having_inclusive – 包含所有未通过 'max_rows_to_group_by' 的行。换句话说,'totals' 的行数将多于或等于未通过 max_rows_to_group_by 时的行数。

after_having_auto – 计算通过 HAVING 的行数。如果超过某个数量(默认值 50%),则将所有未通过 'max_rows_to_group_by' 的行包含在 'totals' 中。否则,不包括它们。

totals_auto_threshold – 默认值为 0.5。用于 after_having_auto 的系数。

如果未使用 max_rows_to_group_bygroup_by_overflow_mode = 'any',则所有 after_having 的变体是相同的,您可以使用它们中的任何一个(例如, after_having_auto)。

您可以在子查询中使用 WITH TOTALS,包括在 JOIN 子句中的子查询(在这种情况下,相应的总值会组合在一起)。

GROUP BY ALL

GROUP BY ALL 等同于列出所有未使用聚合函数的 SELECT 表达式。

例如:

相同。

对于特殊情况,如果存在一个函数接受聚合函数和其他字段作为其参数,GROUP BY 键将包含我们能够提取的最大非聚合字段。

例如:

相同。

示例

示例:

与 MySQL 相比(并符合标准 SQL),您不能获取一些不在键或聚合函数中的列的值(常量表达式除外)。为了解决这个问题,您可以使用 'any' 聚合函数(获取首次遇到的值)或 'min/max'。

示例:

对于每个遇到的不同键值,GROUP BY 计算一组聚合函数值。

GROUPING SETS 修饰符

这是最通用的修饰符。 此修饰符允许手动指定多个聚合键集(分组集)。 聚合分别在每个分组集上执行,之后将所有结果合并。 如果某列未出现在分组集中,则用默认值填充。

换句话说,上述描述的修饰符可以通过 GROUPING SETS 表示。 尽管带有 ROLLUPCUBEGROUPING SETS 修饰符的查询在语法上是相等的,但它们的执行方式可能不同。 当 GROUPING SETS 尝试并行执行所有操作时,ROLLUPCUBE 在单个线程中执行聚合的最终合并。

在源列包含默认值的情况下,可能很难区分一行是否是使用这些列作为键的聚合的一部分。 为了解决此问题,必须使用 GROUPING 函数。

示例

以下两个查询是等效的。

另请参阅

实现细节

聚合是列式 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 设置启用时合并来自远程服务器的结果时,会消耗最多 1/256 * 线程数 的总 RAM。

当启用外部聚合时,如果数据少于 max_bytes_before_external_group_by(即数据未被刷新),查询的运行速度与没有外部聚合时相同。如果有任何临时数据被刷新,则运行时间将长几倍(大约三倍)。

如果您的 ORDER BY 后有一个 LIMIT,则使用的 RAM 量取决于 LIMIT 中的数据量,而不是整个表中的数据量。但如果 ORDER BY 没有 LIMIT,请记得启用外部排序(max_bytes_before_external_sort)。