ClickHouse中的常⽤聚合函数
楔⼦
这次来说⼀下 ClickHouse 中的聚合函数，因为和关系型数据库的相似性，本来聚合函数不打算说的，但是 ClickHouse 提供了很多关系型数据库中没有的函数，所以我们还是从头了解⼀下。
count：计算数据的⾏数，有以下⼏种⽅式：
count(字段)：计算该字段中不为 Null 的元素数量
count()、count(*)：计算数据集的总⾏数
所有如果某个字段中不包含 Null，那么对该字段进⾏ count 得到的结果和 count()、count(*) 是相等的。
SELECT count(), count(*), count(product) FROM sales_data;
/*
┌─count()─┬─count()─┬─count(product)─┐
│ 1349 │ 1349 │ 1349 │
└─────────┴─────────┴────────────────┘
这⾥再提⼀下聚合函数，聚合函数针对的是多⾏结果集，⽽不是数组。
-- 这⾥得到的是 1，原因在于这⾥只有⼀⾏数据
SELECT count([1, 2, 3]);
/*
┌─count()─┐
│ 1 │
└─────────┘
*/
-- 如果将其展开的话，那么会得到 3，因为展开之后变成了 3 ⾏数据
SELECT count(arrayJoin([1, 2, 3]));
/
*
┌─count(arrayJoin([1, 2, 3]))─┐
│ 3 │
└─────────────────────────────┘
*/
当然使⽤ count 计算某个字段的元素数量时，还可以进⾏去重。
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：计算每组数据的最⼩值、最⼤值、总和、平均值
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 也⽐较实⽤，那么这两个函数是⼲什么的呢？看⼀张图就明⽩了。
我们可以测试⼀下：
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：
-- 由于 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。
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 中也是可以使⽤的，另外我们看到返回的结果集的字段名叫，虽然我们使⽤的是 CASE WHEN，但是 ClickHouse 在底层会给语句进⾏优化，在功能不变的前提下，寻⼀个在 ClickHouse 中效率更⾼的替代⽅案。因此你直接使⽤ 也是可以的，⽐如：
定时发邮件SELECT multiIf(less(A, B), A, B) FROM test_1
⽽⾄于上⾯的 multiIf，它的功能和 CASE WHEN 是完全类似的。只不过这⾥个⼈有⼀点建议，既然 ClickHouse 会进⾏语句的优化，那么能⽤关系型数据库语法解决的问题，就⽤关系型数据库语法去解决。这么做的原因主要是为了考虑 SQL 语句的可读性，因为相⽐ ClickHouse，⼤部分⼈对关系型数据库语法显然更熟悉⼀些。如果使⽤这⾥的 ，那么当别⼈阅读时，可能还要查阅⼀下 multiIf 函数、或者 mulitIf ⾥⾯⼜调⽤的 less 函数是做什么的；但如果使⽤ CASE WHEN，绝对的⼀⽬了然。
当然以上只是个⼈的建议，如果你对 ClickHouse 的函数⽤的⾮常 6，那么完全可以不优先使⽤关系型数据库的语法，不然这些函数不是⽩掌握了吗。
any：选择每组数据中第⼀个出现的值
-- 按照 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 看起来貌似没有实际的意义，因为聚合之后每组第⼀个出现的值并不⼀定能代表什么。那么问题来了，如果想选择分组中的任意⼀个值，该怎么办呢？
-- 使⽤ 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 │
└─────────────────────────────────────┘
*/
如果想分组之后选择，选择每个组的最⼩值该怎么做呢？
-- 在上⾯的基础上再调⽤⼀下 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 ⼤的值。
-- 从⼩到⼤排个序即可，然后选择索引为 -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：计算⽅差，{\frac{\sum(x - \hat{x})^2} n}；stddevPop：计算标准差，等于⽅差开根号
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 │
└──────────┴──────────┴────────────────────────────────────────────────┘
*/
问题来了，如果我们想⼿动实现⽅差的计算该怎么办？试⼀下：
-- 将结果集转成数组，并先计算好平均值
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：计算协⽅差，{\frac{\sum(x - \hat{x})(y - \hat{y})} n}
⽐较少⽤，这⾥不演⽰的了，可以⾃⼰测试⼀下。
anyHeavy：使⽤算法选择每组中出现频率最⾼的值
SELECT anyHeavy(amount) FROM sales_data;
/*
┌─anyHeavy(amount)─┐
│ 2369 │
└──────────────────┘
*/
anyLast：选择每组中的最后⼀个值
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论