聚合函数
概述
聚合函数对一组输入值进行计算,返回单个结果值。聚合函数通常与GROUP BY子句配合使用,将数据按分组键聚合后计算每组的结果;也可作为窗口聚合函数在 GROUP WINDOW 或OVER WINDOW中使用。所有聚合函数在计算时默认跳过NULL值(COUNT(*) 除外),支持通过DISTINCT关键字去除重复值后再聚合。
更多内容请参考Aggregate Functions。
适用场景
- 按维度分组统计指标,如按地区统计订单数量、平均金额。
- 窗口聚合计算滚动/滑动指标,如最近5分钟的UV、GMV。
- 数据去重统计,如COUNT(DISTINCT user_id) 计算独立访客数。
- 多值字段聚合,如将分组内标签收集为集合、将多行文本拼接为一条。
- ROLLUP/CUBE 多维分析中标识聚合层级。
约束与限制
- NULL值处理:除 COUNT(*) 外,所有聚合函数在计算时自动跳过NULL值;若分组内所有值均为NULL,聚合结果为NULL。
- DISTINCT限制:DISTINCT仅支持COUNT、AVG、SUM、COLLECT四个函数,MAX、MIN、STDDEV_POP、STDDEV_SAMP、VAR_POP、VAR_SAMP、LISTAGG、GROUPING、BIT_AND、BIT_OR不支持DISTINCT。
- 嵌套聚合:聚合函数不可嵌套使用,如SUM(COUNT(*)) 不合法,需通过子查询实现。
- GROUP BY一致性:在SELECT中引用非聚合列时,该列必须出现在GROUP BY子句中。
- 窗口聚合:聚合函数在OVER WINDOW中使用时,须配合ORDER BY和窗口范围(ROWS BETWEEN / RANGE BETWEEN)定义。
- STDDEV_SAMP / VAR_SAMP最低行数:样本标准差和样本方差要求分组内至少有2个非NULL值,否则返回NULL。
- BIT_AND / BIT_OR类型约束:仅支持整数类型输入,传入非整数类型将导致类型校验失败。
- COLLECT返回类型:COLLECT返回的是MULTISET类型,如需转为数组可使用ARRAY_DISTINCT(COLLECT(...)) 等方式。
- LISTAGG分隔符:separator参数须为字符串常量,不支持列引用或表达式。
语法结构
| 语法 | 返回类型 | 说明 | 示例 |
|---|---|---|---|
| COUNT(*) | BIGINT | 统计所有行数,包含NULL值的行。无需指定列,对整行计数。 | SELECT COUNT(*) FROM orders; -- 10 |
| COUNT(value) | BIGINT | 统计value非NULL的行数。参数可为任意类型;value为NULL的行不计入。 | SELECT COUNT(amount) FROM orders; -- 8(跳过2条NULL) |
| COUNT(DISTINCT value) | BIGINT | 统计value去NULL并去重后的不同值个数。参数可为任意类型。 | SELECT COUNT(DISTINCT user_id) FROM orders; -- 5 |
| AVG(value) | DECIMAL / DOUBLE | 计算value的算术平均值。参数须为数值类型(TINYINT / SMALLINT / INT / BIGINT / FLOAT / DOUBLE / DECIMAL);整型输入返回 DECIMAL,浮点输入返回DOUBLE;跳过 NULL。 | SELECT AVG(price) FROM orders; -- 25.50 |
| AVG(DISTINCT value) | DECIMAL / DOUBLE | 计算value去NULL并去重后的算术平均值。参数须为数值类型;返回类型规则同AVG。 | SELECT AVG(DISTINCT price) FROM orders; -- 30.00 |
| SUM(value) | 与输入类型一致 | 计算value的总和。参数须为数值类型;整型输入返回整型(INT→INT, BIGINT→BIGINT),DECIMAL→DECIMAL,FLOAT/DOUBLE→DOUBLE;跳过NULL。 | SELECT SUM(amount) FROM orders; -- 1500 |
| SUM(DISTINCT value) | 与输入类型一致 | 计算value去NULL并去重后的总和。参数须为数值类型;返回类型规则同SUM。 | SELECT SUM(DISTINCT amount) FROM orders; -- 1200 |
| MAX(value) | 与输入类型一致 | 返回value的最大值。参数可为任意可比较类型(数值、字符串、日期时间等);跳过NULL;字符串按字典序比较。 | SELECT MAX(price) FROM orders; -- 99.90 |
| MIN(value) | 与输入类型一致 | 返回value的最小值。参数可为任意可比较类型(数值、字符串、日期时间等);跳过NULL;字符串按字典序比较。 | SELECT MIN(price) FROM orders; -- 5.00 |
| STDDEV_POP(value) | DOUBLE | 计算value的总体标准差(Population Standard Deviation)。参数须为数值类型;公式为 √(Σ(x−μ)²/N);跳过NULL;当输入行数 < 1 时返回NULL。 | SELECT STDDEV_POP(score) FROM exam; -- 12.45 |
| STDDEV_SAMP(value) | DOUBLE | 计算value的样本标准差(Sample Standard Deviation)。参数须为数值类型;公式为 √(Σ(x−μ)²/(N−1));跳过NULL;当输入行数 < 2时返回NULL。 | SELECT STDDEV_SAMP(score) FROM exam; -- 13.20 |
| VAR_POP(value) | DOUBLE | 计算value的总体方差(Population Variance)。参数须为数值类型;公式为 Σ(x−μ)²/N;跳过NULL;当输入行数 < 1 时返回NULL。 | SELECT VAR_POP(score) FROM exam; -- 155.00 |
| VAR_SAMP(value) | DOUBLE | 计算value的样本方差(Sample Variance)。参数须为数值类型;公式为 Σ(x−μ)²/(N−1);跳过 NULL;当输入行数 < 2时返回NULL。 | SELECT VAR_SAMP(score) FROM exam; -- 174.38 |
| COLLECT(value) | MULTISET | 将value收集为多值集合(MULTISET),保留重复元素。参数可为任意类型;跳过NULL;返回类型为MULTISET<参数类型>。 | SELECT COLLECT(tag) FROM articles; -- [a, b, b, c] |
| COLLECT(DISTINCT value) | MULTISET | 将value去NULL并去重后收集为多值集合。参数可为任意类型;返回类型为MULTISET<参数类型>。 | SELECT COLLECT(DISTINCT tag) FROM articles; -- [a, b, c] |
| LISTAGG(value, separator) | STRING | 将value以separator为分隔符拼接为单个字符串。value须为字符串类型,separator须为字符串常量;跳过NULL;无分隔符时separator传空字符串 ''。 | SELECT LISTAGG(name, ',') FROM users; -- Alice,Bob,Carol |
| GROUPING(expression) | INT | 判断expression是否因GROUP BY ROLLUP或GROUP BY CUBE而被聚合填充为NULL。返回 1 表示该列被聚合(值由 ROLLUP/CUBE 产生),返回0表示该列保留了原始分组值。须在包含 ROLLUP/CUBE的GROUP BY中使用。 | SELECT year, region, GROUPING(region) AS g FROM sales GROUP BY ROLLUP (year, region); -- g=0(原始行)或 g=1(小计行) |
| BIT_AND(value) | INT / BIGINT | 对value执行按位与聚合。参数须为整数类型(TINYINT / SMALLINT / INT / BIGINT);等价于对所有非NULL值依次做&运算;跳过 NULL;仅一个非NULL值时返回该值本身。 | SELECT BIT_AND(flags) FROM events; -- 8 |
| BIT_OR(value) | INT / BIGINT | 对value执行按位或聚合。参数须为整数类型(TINYINT / SMALLINT / INT / BIGINT);等价于对所有非NULL值依次做|运算;跳过NULL;仅一个非NULL值时返回该值本身。 | SELECT BIT_OR(flags) FROM events; -- 15 |