ClickHouse 中的常用聚合函数(十一)

​ 本文来源： ( https://www.cnblogs.com/traditional/tag/ClickHouse：一款速度快到让人发指的列式存储数据库/ )

楔子

这次来说一下 ClickHouse 中的聚合函数，因为和关系型数据库的相似性，本来聚合函数不打算说的，但是 ClickHouse 提供了很多关系型数据库中没有的函数，所以我们还是从头了解一下。

count：计算数据的行数，有以下几种方式：

  • count(字段)：计算该字段中不为 Null 的元素数量
  • count()、count(*)：计算数据集的总行数

所有如果某个字段中不包含 Null，那么对该字段进行 count 得到的结果和 count()、count(*) 是相等的。

1
2
3
4
5
SELECT count(), count(*), count(product) FROM sales_data;
/*
┌─count()─┬─count()─┬─count(product)─┐
│    1349 │    1349 │           1349 │
└─────────┴─────────┴────────────────┘

这里再提一下聚合函数，聚合函数针对的是多行结果集，而不是数组。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 这里得到的是 1，原因在于这里只有一行数据
SELECT count([1, 2, 3]);
/*
┌─count()─┐
│       1 │
└─────────┘
*/

-- 如果将其展开的话，那么会得到 3，因为展开之后变成了 3 行数据
SELECT count(arrayJoin([1, 2, 3]));
/*
┌─count(arrayJoin([1, 2, 3]))─┐
│                           3 │
└─────────────────────────────┘
*/

当然使用 count 计算某个字段的元素数量时，还可以进行去重。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT count(DISTINCT product) FROM sales_data;
/*
┌─uniqExact(product)─┐
│                  3 │
└────────────────────┘
*/

-- 根据返回的字段名，我们发现 ClickHouse 在底层实际上调用的是 uniqExact 函数
SELECT uniqExact(product) FROM sales_data;
/*
┌─uniqExact(product)─┐
│                  3 │
└────────────────────┘
*/
-- 也就是 count(DISTINCT) 等价于 uniqExact
-- 不过还是建议像关系型数据库那样使用 count(DISTINCT) 比较好，因为更加习惯

min、max、sum、avg：计算每组数据的最小值、最大值、总和、平均值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT min(amount), max(amount), sum(amount), avg(amount) 
FROM sales_data GROUP BY product, channel;
/*
┌─min(amount)─┬─max(amount)─┬─sum(amount)─┬────────avg(amount)─┐
│         547 │        2788 │      248175 │ 1643.5430463576158 │
│         658 │        2805 │      252148 │ 1669.8543046357615 │
│         613 │        2803 │      246198 │ 1652.3355704697988 │
│         709 │        2870 │      256602 │ 1699.3509933774835 │
│         599 │        2869 │      245029 │ 1601.4967320261437 │
│         511 │        2673 │      252908 │ 1686.0533333333333 │
│         564 │        2710 │      252057 │ 1714.6734693877552 │
│         621 │        2832 │      251795 │ 1701.3175675675675 │
│         642 │        2803 │      245904 │ 1650.3624161073826 │
└─────────────┴─────────────┴─────────────┴────────────────────┘
*/

除此之外还有两个非聚合函数 least、greatest 也比较实用，那么这两个函数是干什么的呢？看一张图就明白了。

img

我们可以测试一下：

1
2
3
4
5
6
7
8
9
10
SELECT least(A, B), greatest(A, B) FROM test_1;
/*
┌─least(A, B)─┬─greatest(A, B)─┐
│          11 │             13 │
│           7 │              8 │
│           5 │              8 │
│          11 │             15 │
│           9 │             13 │
└─────────────┴────────────────┘
*/

问题来了，如果 ClickHouse 没有提供 least 和 greatest 这两个函数，那么我们要如何实现此功能呢？首先我们可以使用 arrayMap：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 由于 arrayMap 针对的是数组，不是多行结果集，所以需要借助 groupArray 将多行结果集转成数组
-- 另外在比较大小的时候也要将两个元素组合成数组 [x, y]，然后使用 arrayMin 比较
-- 或者使用 least(x, y) 也可以对两个标量进行比较，不过这里我们是为了实现 least，所以就不用它了
SELECT arrayMap(x, y -> arrayMin([x, y]), groupArray(A), groupArray(B)) arr FROM test_1;
/*
┌─arr───────────┐
│ [11,7,5,11,9] │
└───────────────┘
*/

-- 结果确实实现了，但结果是数组，我们还要再将其展开成多行
-- 这里我们使用 WITH，注意 WITH 子句里的查询只可以返回一行结果集
WITH (
    SELECT arrayMap(x, y -> arrayMin([x, y]), groupArray(A), groupArray(B)) FROM test_1
) AS arr SELECT arrayJoin(arr);
/*
┌─arrayJoin(arr)─┐
│             11 │
│              7 │
│              5 │
│             11 │
│              9 │
└────────────────┘
*/

以上就实现了 least，至于 greatest 也是同理。那么除了使用数组的方式，还可以怎么做呢？如果将这个问题的背景再改成关系型数据库的话，你一定能想到，没错，就是 CASE WHEN。

1
2
3
4
5
6
7
8
9
10
SELECT CASE WHEN A < B THEN A ELSE B END FROM test_1;
/*
┌─multiIf(less(A, B), A, B)─┐
│                        11 │
│                         7 │
│                         5 │
│                        11 │
│                         9 │
└───────────────────────────┘
*/

整个过程显然变得简单了，所以也不要忘记关系型数据库的语法在 ClickHouse 中也是可以使用的，另外我们看到返回的结果集的字段名叫 multiIf…，虽然我们使用的是 CASE WHEN，但是 ClickHouse 在底层会给语句进行优化，在功能不变的前提下，寻找一个在 ClickHouse 中效率更高的替代方案。因此你直接使用 multiIf… 也是可以的，比如：

1
SELECT multiIf(less(A, B), A, B) FROM test_1

而至于上面的 multiIf，它的功能和 CASE WHEN 是完全类似的。只不过这里个人有一点建议，既然 ClickHouse 会进行语句的优化，那么能用关系型数据库语法解决的问题，就用关系型数据库语法去解决。这么做的原因主要是为了考虑 SQL 语句的可读性，因为相比 ClickHouse，大部分人对关系型数据库语法显然更熟悉一些。如果使用这里的 mulitIf…，那么当别人阅读时，可能还要查阅一下 multiIf 函数、或者 mulitIf 里面又调用的 less 函数是做什么的；但如果使用 CASE WHEN，绝对的一目了然。

当然以上只是个人的建议，如果你对 ClickHouse 的函数用的非常 6，那么完全可以不优先使用关系型数据库的语法，不然这些函数不是白掌握了吗。

any：选择每组数据中第一个出现的值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 按照 product, channel 进行分组之后，我们可以求每组的最小值、最大值、平均值等等
-- 而这里的 any 则表示获取每组第一个出现的值
SELECT any(amount) FROM sales_data GROUP BY product, channel;
/*
┌─any(amount)─┐
│        1864 │
│        1573 │
│         847 │
│        1178 │
│        1736 │
│         511 │
│         568 │
│        1329 │
│        1364 │
└─────────────┘
*/

当然 any 看起来貌似没有实际的意义，因为聚合之后每组第一个出现的值并不一定能代表什么。那么问题来了，如果想选择分组中的任意一个值，该怎么办呢？

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 使用 groupArray 变成一个数组，然后再通过索引选择即可
-- 因为我们选择的是第 1 个元素，所以此时等价于 any
SELECT groupArray(amount)[1] FROM sales_data 
GROUP BY product, channel;
/*
┌─arrayElement(groupArray(amount), 1)─┐
│                                1864 │
│                                1573 │
│                                 847 │
│                                1178 │
│                                1736 │
│                                 511 │
│                                 568 │
│                                1329 │
│                                1364 │
└─────────────────────────────────────┘
*/

如果想分组之后选择，选择每个组的最小值该怎么做呢？

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 在上面的基础上再调用一下 arrayMin 即可
SELECT arrayMin(groupArray(amount)) FROM sales_data
GROUP BY product, channel;
/*
┌─arrayMin(groupArray(amount))─┐
│                          547 │
│                          658 │
│                          613 │
│                          709 │
│                          599 │
│                          511 │
│                          564 │
│                          621 │
│                          642 │
└──────────────────────────────┘
*/

如果想分组之后选择，选择每个组的第 N 大的值该怎么做呢？比如我们选择第 3 大的值。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 从小到大排个序即可，然后选择索引为 -3 的元素
-- 或者从大到小排个序，然后选择索引为 3 的元素
SELECT arraySort(groupArray(amount))[-2] rank3_1, arrayReverseSort(groupArray(amount))[2] rank3_2
FROM sales_data GROUP BY product, channel;
/*
┌─rank3_1─┬─rank3_2─┐
│    2784 │    2784 │
│    2804 │    2804 │
│    2650 │    2650 │
│    2856 │    2856 │
│    2865 │    2865 │
│    2610 │    2610 │
│    2632 │    2632 │
│    2754 │    2754 │
│    2694 │    2694 │
└─────────┴─────────┘
*/

确实给人一种 pandas 的感觉，之前做数据分析主要用 pandas。但是 pandas 有一个致命的问题，就是它要求数据能全部加载到内存中，所以在处理中小型数据集的时候确实很方便，但是对于大型数据集就无能为力了，只能另辟蹊径。但是 ClickHouse 则是通过分片机制支持分布式运算，所以个人觉得它简直就是分布式的 pandas。

varPop：计算方差，∑(x−x^)2n∑(�−�^)2�；stddevPop：计算标准差，等于方差开根号

1
2
3
4
5
6
7
SELECT varPop(amount) v1, stddevPop(amount) v2, v2 * v2 
FROM sales_data;
/*
┌───────v1─┬───────v2─┬─multiply(stddevPop(amount), stddevPop(amount))─┐
│ 269907.7 │ 519.5264 │                              269907.7096217908 │
└──────────┴──────────┴────────────────────────────────────────────────┘
*/

问题来了，如果我们想手动实现方差的计算该怎么办？试一下：

1
2
3
4
5
6
7
8
-- 将结果集转成数组，并先计算好平均值
WITH (SELECT groupArray(amount) FROM sales_data) AS arr, 
      arraySum(arr) / length(arr) AS amount_avg
-- 通过 arrayMap 将数组中的每一个元素都和平均值做减法，然后再平方，得到新数组
-- 最后再用 arrayAvg 对新数组取平均值，即可计算出方差
SELECT arrayAvg(
    arrayMap(x -> pow(x - amount_avg, 2), arr)
)

covarPop：计算协方差，∑(x−x^)(y−y^)n∑(�−�^)(�−�^)�

比较少用，这里不演示的了，可以自己测试一下。

anyHeavy：使用 heavy hitters 算法选择每组中出现频率最高的值

1
2
3
4
5
6
SELECT anyHeavy(amount) FROM sales_data;
/*
┌─anyHeavy(amount)─┐
│             2369 │
└──────────────────┘
*/

anyLast：选择每组中的最后一个值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
SELECT anyLast(amount) FROM sales_data GROUP BY product, channel;
/*
┌─anyLast(amount)─┐
│            1679 │
│            1767 │
│            2369 │
│            2660 │
│            2865 │
│            2422 │
│            1481 │
│            1439 │
│            2443 │
└─────────────────┘
*/

-- 同样可以借助数组实现
SELECT groupArray(amount)[-1] FROM sales_data GROUP BY product, channel;
/*
┌─arrayElement(groupArray(amount), -1)─┐
│                                 1679 │
│                                 1767 │
│                                 2369 │
│                                 2660 │
│                                 2865 │
│                                 2422 │
│                                 1481 │
│                                 1439 │
│                                 2443 │
└──────────────────────────────────────┘
*/

argMin：接收两个列，根据另一个列选择当前列的最小值，我们画一张图，通过和 min 进行比对，就能看出它的用法了

1
2
3
4
5
6
7
8
9
10
11
12
13
14
WITH [1, 2, Null] AS arr SELECT has(arr, 2), has(arr, 0), has(arr, Null);
/*
┌─has(arr, 2)─┬─has(arr, 0)─┬─has(arr, NULL)─┐
│           1 │           0 │              1 │
└─────────────┴─────────────┴────────────────┘
*/

-- 嵌套数组也是可以的
SELECT has([[1, 2]], [1, 2]);
/*
┌─has([[1, 2]], [1, 2])─┐
│                     1 │
└───────────────────────┘
*/

img

首先 min(A) 和 min(B) 分别返回 5 和 7 无需解释，而 argMin(A, B) 表示根据 B 的最小值选择 A，B 的最小值是 7，对应 A 就是 8；同理 argMin(B, A) 表示根据 A 的最小值选择 B，A 的最小值是 5，对应 B 就是 8。

以上就是 argMin，同理还有 argMax。

topK：选择出现频率最高的 K 个元素

1
2
3
4
5
6
7
-- 这里选择出现频率最高的两个元素
SELECT topK(2)(arrayJoin([1, 1, 1, 1, 1, 2, 2, 2, 3, 3, 3, 3]));
/*
┌─topK(2)(arrayJoin([1, 1, 1, 1, 1, 2, 2, 2, 3, 3, 3, 3]))─┐
│ [1,3]                                                    │
└──────────────────────────────────────────────────────────┘
*/

我们看到以数组的形式返回，因为聚合函数最终每个组只会对应一行数据，所以得到的是数组。

topK 也是非常常见的，如果让我们自己实现，虽然可以做到，但会比较麻烦，ClickHouse 替我们考虑的还是很周到的。

groupArrayMovingSum：滑动窗口，每个窗口内的数据进行累和。

1
2
3
4
5
6
7
SELECT groupArray(number), groupArrayMovingSum(4)(number)
FROM (SELECT number FROM numbers(10));
/*
┌─groupArray(number)────┬─groupArrayMovingSum(4)(number)─┐
│ [0,1,2,3,4,5,6,7,8,9] │ [0,1,3,6,10,14,18,22,26,30]    │
└───────────────────────┴────────────────────────────────┘
*/

画一张图，来解释一下：

img

首先 groupArrayMovingSum(4) 表示窗口的长度为 4，然后不断的向下滑动，计算包含当前元素在内往上的四个元素之和。如果元素的个数不够窗口的长度，那么有几个算几个，比如前三个元素。

那么试想一下，如果窗口长度等于数组的长度，那么会发生什么呢？

1
2
3
4
5
6
7
8
9
-- 不指定窗口长度，那么窗口长度就等于数组长度
SELECT groupArray(number), groupArrayMovingSum(number)
FROM (SELECT number FROM numbers(10));
/*
┌─groupArray(number)────┬─groupArrayMovingSum(number)─┐
│ [0,1,2,3,4,5,6,7,8,9] │ [0,1,3,6,10,15,21,28,36,45] │
└───────────────────────┴─────────────────────────────┘
*/
-- 显然相当于进行了累和

这就是 ClickHouse 提供的窗口函数，但关系型数据库中的窗口函数语法在 ClickHouse 还没有得到完美的支持，但很明显通过这些强大的函数我们也可以实现相应的功能。

除了 groupArrayMovingSum 之外，还有一个 groupArrayMovingAvg，用法完全一样，只不过计算的是平均值，这里就不单独说了。

groupArraySample：随机选择 N 个元素

1
2
3
4
5
6
7
-- 随机选择 3 个元素
SELECT groupArraySample(3)(amount) FROM sales_data;
/*
┌─groupArraySample(3)(amount)─┐
│ [1268,2246,1606]            │
└─────────────────────────────┘
*/

我们还可以绑定一个随机种子，如果种子一样，那么每次随机选择的数据也是一样的。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT groupArraySample(3, 666)(amount) FROM sales_data;
/*
┌─groupArraySample(3, 666)(amount)─┐
│ [635,1290,1846]                  │
└──────────────────────────────────┘
*/

SELECT groupArraySample(3, 666)(amount) FROM sales_data;
/*
┌─groupArraySample(3, 666)(amount)─┐
│ [635,1290,1846]                  │
└──────────────────────────────────┘
*/

SELECT groupArraySample(3, 661)(amount) FROM sales_data;
/*
┌─groupArraySample(3, 661)(amount)─┐
│ [2011,2125,1542]                 │
└──────────────────────────────────┘
*/

deltaSum：对相邻的行进行做差，然后求和，注意：小于 0 不会计算在内

1
2
3
4
5
6
7
8
9
10
11
-- 3 - 1 = 2
-- 4 - 3 = 1
-- 1 - 4 = -3
-- 8 - 1 = 7
-- 所以结果为 2 + 1 + 7
SELECT deltaSum(arrayJoin([1, 3, 4, 1, 8]));
/*
┌─deltaSum(arrayJoin([1, 3, 4, 1, 8]))─┐
│                                   10 │
└──────────────────────────────────────┘
*/

小结

以上就是关于 ClickHouse 的一些聚合函数，还有相当一部分没有介绍到，主要是觉得应用的场景非常少见，后续再补充。