聚合函数
原创大约 6 分钟
虽然前面已经介绍了大量的聚合函数，但还是有一些高频出现的没覆盖到，所以这里就来集中汇总一下。
1. topK(N)(x)
经常会出现统计数量，然后按数量的倒序排序并取前N个值的场景，这就是典型的TopN需求。
Clickhouse直接提供了返回指定列中最频繁出现的前N个值的函数。
N一般不超过10，x则代表指定的列。
:) CREATE TABLE t_topn
(
id UInt32,
name String
) ENGINE = MergeTree()
ORDER BY id;
:) INSERT INTO t_topn VALUES
(1, '石昊'),
(2, '王林'),
(3, '萧炎'),
(4, '秦羽'),
(5, '石昊'),
(6, '石昊'),
(7, '王林'),
(8, '萧炎'),
(9, '王林'),
(10, '王林');
:) SELECT topK(3)(name) AS res
FROM t_topn;
┌─res───────────────────┐
│ ['王林','石昊','萧炎'] │
└───────────────────────┘2. groupXXX系列函数
2.1 groupArray
groupArray()函数的功能和arrayJoin()恰好相反。
arrayJoin()是将数组展开为列中的一行行数据。groupArray()是将列的一行行数据压缩成数组。
:) SELECT groupArray(name) FROM t_topn;
┌─groupArray(name)────────────────────────────────────────────────────────┐
│ ['石昊','王林','萧炎','秦羽','石昊','石昊','王林','萧炎','王林','王林'] │
└─────────────────────────────────────────────────────────────────────────┘
:) SELECT name, groupArray(name) AS arr, arrayJoin(arr) AS other
FROM t_topn
GROUP BY name;
┌─name─┬─arr───────────────────────────┬─other─┐
│ 萧炎 │ ['萧炎','萧炎'] │ 萧炎 │
│ 萧炎 │ ['萧炎','萧炎'] │ 萧炎 │
│ 秦羽 │ ['秦羽'] │ 秦羽 │
│ 王林 │ ['王林','王林','王林','王林'] │ 王林 │
│ 王林 │ ['王林','王林','王林','王林'] │ 王林 │
│ 王林 │ ['王林','王林','王林','王林'] │ 王林 │
│ 王林 │ ['王林','王林','王林','王林'] │ 王林 │
│ 石昊 │ ['石昊','石昊','石昊'] │ 石昊 │
│ 石昊 │ ['石昊','石昊','石昊'] │ 石昊 │
│ 石昊 │ ['石昊','石昊','石昊'] │ 石昊 │
└──────┴────────────────────────────────┴───────┘如果想限制数组元素的个数，可以这样写：groupArray(max_size)(name)，max_size表示数组元素的最大数量。
2.2 groupUniqArray
如果换成groupUniqArray()函数，那么结果就是这样。
:) SELECT name, groupUniqArray(2)(name) AS arr, arrayJoin(arr) AS other
FROM t_topn
GROUP BY name;
┌─name─┬─arr──────┬─other─┐
│ 萧炎 │ ['萧炎'] │ 萧炎 │
│ 秦羽 │ ['秦羽'] │ 秦羽 │
│ 王林 │ ['王林'] │ 王林 │
│ 石昊 │ ['石昊'] │ 石昊 │
└──────┴──────────┴───────┘2.3 groupArraySample
修改一下之前的表数据。
:) TRUNCATE TABLE t_topn;
:) INSERT INTO t_topn VALUES
(1, '石昊'),
(2, '王林'),
(3, '萧炎'),
(4, '秦羽'),
(5, '罗峰'),
(6, '唐三'),
(7, '柳神'),
(8, '李慕婉'),
(9, '姜立'),
(10, '云韵');
:) SELECT groupArraySample(5)(name) as selected
FROM t_topn;
┌─selected─────────────────────────────┐
│ ['柳神','王林','云韵','秦羽','罗峰'] │
└──────────────────────────────────────┘每次查询的结果都会不同，也可以给查询结果加上前缀。
:) SELECT groupArraySample(5)(if(id < 7, concat('国漫男神-', name), concat('国漫女神-', name))) as selected
FROM t_topn;
┌─selected────────────────────────────────────────────────────────────────────────────┐
│ ['国漫女神-李慕婉','国漫女神-姜立','国漫男神-萧炎','国漫女神-云韵','国漫男神-罗峰'] │
└─────────────────────────────────────────────────────────────────────────────────────┘3. 聚合函数组合器
Clickhouse允许在聚合函数的名称后面附加一个后缀，不仅改变了名称，也改变了功能。
先创建几张测试表。
:) CREATE TABLE t_employee
(
id UInt16,
name String,
job String,
leaderid UInt16,
hiredate Date,
salaries decimal(10, 2),
bonus decimal(8, 2),
departid UInt32
) ENGINE = MergeTree()
ORDER BY id;
:) INSERT INTO t_employee VALUES
(1, '萧炎', '销售员', 13, '2023-12-17', 2800, null, 2),
(2, '柳神', '市场人员', 6,'2023-02-20', 1600, 300, 3),
(3, '李慕婉', '营销部长', 6, '2023-02-22', 2250, 500, 3),
(4, '秦羽', '销售部长', 9, '2023-04-02', 2975, null, 2),
(5, '姜立', '市场人员', 6, '2023-09-28', 2250, 1400, 3),
(6, '罗峰', '市场经理', 9, '2023-05-01', 2850, null, 3),
(7, '唐三', '项目经理', 9, '2023-06-09', 2450, null, 1),
(8, '徐欣', '营销人员', 4, '2023-04-19', 3000, null, 2),
(9, '石昊', '架构师', null, '2023-11-17', 5000, null, 1),
(10, '云韵', '市场人员', 6, '2023-09-08', 1500, 0, 3),
(11, '龙昊晨', '销售员', 8, '2023-05-23', 1100, null, 2),
(12, '美杜莎', '市场人员', 6, '2023-12-03', 1950, null, 3),
(13, '王林', '营销人员', 4,'2023-12-02', 3000, null, 2),
(14, '小舞', '软件工程师', 7, '2023-01-23', 2300, null, 1);
:) CREATE TABLE t_department
(
id UInt32,
name String,
location String
) ENGINE = MergeTree()
ORDER BY id;
:) INSERT INTO t_department VALUES
(1, '研发部', '北京'),
(2, '销售部', '上海'),
(3, '营销部', '武汉'),
(4, '客服部', '西安');3.1 -If
-If可以加在任何聚合函数之后，增加了-If的聚合函数需要接受一个新的逻辑条件参数。
如果条件得到满足，那么就由聚合函数执行处理，否则返回0或空字符串""。
统计所有销售部的员工薪资。
:) SELECT sumIf(salaries, departid = 2) AS total_salaries
FROM t_employee;
┌─total_salaries─┐
│ 12875 │
└────────────────┘统计薪资大于等于2000的员工。
:) SELECT countIf(id, salaries >= 2000) AS total
FROM t_employee;
┌─total─┐
│ 10 │
└───────┘统计研发部的平均薪资。
:) SELECT avgIf(salaries, departid = 1) AS avg
FROM t_employee;
┌──avg─┐
│ 3250 │
└──────┘3.2. -Array
任何聚合函数都可以通过增加后缀-Array，来使原来的参数类型T变为新的参数类型Array(T)。
如果聚合函数接受多个参数，则它必须是长度相等的数组。
对所有工资进行求和。
:) WITH
(
SELECT groupArray(salaries) FROM t_employee
) AS arr
SELECT sumArray(arr) AS SUM
FROM t_employee;
┌────SUM─┐
│ 490350 │
└────────┘-If和-Array可以组合使用，但-Array必须在前，-If在后。
:) SELECT sumArrayIf(array(number), number % 2 == 0) AS mod
FROM
(
SELECT number FROM numbers(9)
);
┌─mod─┐
│ 20 │
└─────┘3.3. -ForEach
-ForEach将对表使用的聚合函数，转换为对数组使用的聚合函数，并对数组的每一项进行处理，同时返回一个结果数组。
:) WITH
(
array(1,2,3),
array(4,5,6,7)
) AS arr
SELECT sumForEach(arr1) AS res
FROM
(
SELECT arr.1 AS arr1
UNION ALL
SELECT arr.2 AS arr1
)
┌─res───────┐
│ [5,7,9,7] │
└───────────┘:) WITH
(
array(1,2,3),
array(4,5,6,7)
) AS arr
SELECT countForEach(arr1) AS res
FROM
(
SELECT arr.1 AS arr1
UNION ALL
SELECT arr.2 AS arr1
)
┌─res───────┐
│ [2,2,2,1] │
└───────────┘4. 其他
4.1. sumMap(key, value)
从语法来看，sumMap()既能处理Map，又能处理Tuple。
-- sumMap()语法
sumMap(key, value)
或
sumMap(Tuple(key, value))它根据key数组中相同的键对value进行求和，然后返回两个数组组成的一个元组: 排好序的key和对应value之和。
传递key和value组成的元组与分别传递key和value数组是同义的。
但key和value的数组元素数量必须相同。
:) CREATE TABLE t_sum_map
(
id UInt32,
statusMap Nested(
status UInt16,
requests UInt64
),
statusTuple Tuple(Array(Int32), Array(Int32))
) ENGINE = MergeTree()
ORDER BY id;
:) INSERT INTO t_sum_map VALUES
(1, [1, 2, 3], [11, 27, 34], ([1, 2, 3], [11, 27, 34])),
(1, [2, 3, 4], [59, 80, 76], ([3, 4, 5], [59, 80, 76])),
(2, [4, 5, 6], [21, 57, 92], ([4, 5, 6], [21, 57, 92])),
(2, [6, 7, 8], [36, 43, 68], ([6, 7, 8], [36, 43, 68]));
:) SELECT id, sumMap(statusMap.status, statusMap.requests) AS res1, sumMap(statusTuple) AS res2
FROM t_sum_map
GROUP BY id
ORDER BY id;
┌─id─┬─res1────────────────────────────┬─res2────────────────────────────┐
│ 1 │ ([1,2,3,4],[11,86,114,76]) │ ([1,2,3,4,5],[11,27,93,80,76]) │
│ 2 │ ([4,5,6,7,8],[21,57,128,43,68]) │ ([4,5,6,7,8],[21,57,128,43,68]) │
└────┴─────────────────────────────────┴─────────────────────────────────┘从结果可以验证sumMap()的语法定义。
在
res1列中，对id = 1的相同键2、3和id = 2的相同键6求和；在res2列中，对id = 1的相同键3和id = 2的相同键6求和。对应的
key都已经排好序，且返回一个由数组组成的元组。
感谢支持
更多内容，请移步《超级个体》。