聚合函数
聚合函数以数据库专家预期的方式正常工作。正常
ClickHouse 也支持
NULL 处理
在聚合过程中,所有 NULL
参数都会被跳过。如果聚合有多个参数,它将忽略其中一个或多个为 NULL 的任何行。
此规则有一个例外,即函数 first_value
、last_value
及其别名(分别为 any
和 anyLast
),后跟修饰符 RESPECT NULLS
。例如,FIRST_VALUE(b) RESPECT NULLS
。
示例
考虑此表
┌─x─┬────y─┐
│ 1 │ 2 │
│ 2 │ ᴺᵁᴸᴸ │
│ 3 │ 2 │
│ 3 │ 3 │
│ 3 │ ᴺᵁᴸᴸ │
└───┴──────┘
假设您需要对 y
列中的值求和
SELECT sum(y) FROM t_null_big
┌─sum(y)─┐
│ 7 │
└────────┘
现在您可以使用 groupArray
函数从 y
列创建数组
SELECT groupArray(y) FROM t_null_big
┌─groupArray(y)─┐
│ [2,2,3] │
└───────────────┘
groupArray
不包含结果数组中的 NULL
。
您可以使用 COALESCE 将 NULL 更改为在您的用例中具有意义的值。例如:avg(COALESCE(column, 0))
将在聚合中使用列值,如果为 NULL 则使用零
SELECT
avg(y),
avg(coalesce(y, 0))
FROM t_null_big
┌─────────────avg(y)─┬─avg(coalesce(y, 0))─┐
│ 2.3333333333333335 │ 1.4 │
└────────────────────┴─────────────────────┘
您还可以使用 元组 来解决 NULL 跳过行为。仅包含 NULL
值的元组不是 NULL
,因此聚合函数不会因为该 NULL
值而跳过该行。
SELECT
groupArray(y),
groupArray(tuple(y)).1
FROM t_null_big;
┌─groupArray(y)─┬─tupleElement(groupArray(tuple(y)), 1)─┐
│ [2,2,3] │ [2,NULL,2,3,NULL] │
└───────────────┴───────────────────────────────────────┘
请注意,当列用作聚合函数的参数时,聚合会被跳过。例如,不带参数的 count
(count()
)或带有常量参数的(count(1)
)将计算块中的所有行(独立于 GROUP BY 列的值,因为它不是参数),而 count(column)
仅返回列不为 NULL 的行数。
SELECT
v,
count(1),
count(v)
FROM
(
SELECT if(number < 10, NULL, number % 3) AS v
FROM numbers(15)
)
GROUP BY v
┌────v─┬─count()─┬─count(v)─┐
│ ᴺᵁᴸᴸ │ 10 │ 0 │
│ 0 │ 1 │ 1 │
│ 1 │ 2 │ 2 │
│ 2 │ 2 │ 2 │
└──────┴─────────┴──────────┘
这是一个带有 RESPECT NULLS
的 first_value
示例,在这里我们可以看到 NULL 输入会被尊重,并且它将返回读取的第一个值,无论它是 NULL 还是其他值。
SELECT
col || '_' || ((col + 1) * 5 - 1) as range,
first_value(odd_or_null) as first,
first_value(odd_or_null) IGNORE NULLS as first_ignore_null,
first_value(odd_or_null) RESPECT NULLS as first_respect_nulls
FROM
(
SELECT
intDiv(number, 5) AS col,
if(number % 2 == 0, NULL, number) as odd_or_null
FROM numbers(15)
)
GROUP BY col
ORDER BY col
┌─range─┬─first─┬─first_ignore_null─┬─first_respect_nulls─┐
│ 0_4 │ 1 │ 1 │ ᴺᵁᴸᴸ │
│ 1_9 │ 5 │ 5 │ 5 │
│ 2_14 │ 11 │ 11 │ ᴺᵁᴸᴸ │
└───────┴───────┴───────────────────┴─────────────────────┘