我需要实现每天的累计总和。
例如,我的数据集如下:
buyer | bread | date |
---------------------------
b1 | 2 | 2018-01-01|
b1 | 3 | 2018-01-02|
b1 | 1 | 2018-01-04|
b2 | 2 | 2018-01-02|
我需要得到如下选择:
buyer | cum_sum_on_01_01 | cum_sum_on_01_02 | cum_sum_on_01_03 | cum_sum_on_01_04 | cum_sum_on_01_05 |...
----------------------------------------------------------------------------------------------------------
b1 | 2 | 5 | 5 | 6 | 6 |...
b2 | 0 | 2 | 2 | 2 | 2 |...
怎么做?
2条答案
按热度按时间dxxyhpgq1#
这有什么意义
without built-in function
? 目前在clickhouse中实现累计金额的唯一方法是arrayCumSum
. 所以答案是构建候选数组并将其传递给arrayCumSum
. 步骤如下:步骤1:为每个买家构建面包阵列
步骤2:为每个买家应用arraycumsum
替换
groupArray(bread) AS breads
至arrayCumSum(groupArray(bread)) AS breads
```┌─buyer─┬─breads──────────────────────────────────────────────────────────┐
│ b1 │ [2,5,5,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6] │
│ b2 │ [0,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2] │
└───────┴─────────────────────────────────────────────────────────────────┘
yks3o0rb2#
公认的答案非常好,您确实应该使用内置的arraycumsum函数来计算累积和。但是,如果原始问题的动机之一是找出如何在clickhouse(例如cummax、cummin等)不支持累积/折叠样式的函数时创建它们,那么这里有一种方法可以用于clickhouse中的任何聚合函数。
实现这一点的核心逻辑是使用arrayreduceinranges并生成表单的所有元组范围
(1, 1), (1, 2), ... (1, n)
使用arraymap和arrayenumerate。然后,无论您选择哪个函数作为arrayreduceinranges的高阶聚合函数,例如“sum”或“max”,都将转换为该函数基于数组的累积形式。这个逻辑是这样的:从这里,您可以将这些值从数组中重新连接起来,或将它们保持为数组形式以供进一步计算。
对于使用bread的特定应用程序,以下是使用上述核心逻辑可以工作的内容(假设您的表名为bread\u data):
注意第一个
WITH
子句,该子句按日期和买方对表进行排序,以便保证后续的grouparray调用以相同、一致的顺序构造它们的数组(clickhouse文档注意到,否则,对grouparray的任何调用都可以以随机顺序构造元素)。它可能看起来很复杂,但是当您使用第一个核心逻辑片段来分解它,并且这里的很多语法都是围绕数组分组和解分组的,这样我们就可以在数组空间中完成我们的主要工作了,希望它会有一些直观的意义。
输出如下所示: