Clickhouse 使用分组运行差异

Question

一般任务

一个表由三列(时间、键、值)组成。任务是计算每个键的运行差异。
所以，从输入

---------------
| time | key | value |
---------------
| 1    | A   | 4     |
| 2    | B   | 1     |
| 3    | A   | 6     |
| 4    | A   | 7     |
| 5    | B   | 3     |
| 6    | B   | 7     |

希望得到

----------------------
| key | value | delta |
----------------------
| A   | 4     |  0    |
| B   | 1     |  0    |
| A   | 6     |  2    |
| A   | 7     |  1    |
| B   | 3     |  2    |
| B   | 7     |  4    |

方法

runningDifference功能。工作，如果键是固定的。所以我们可以

select *, runningDifference(value) from
    (SELECT key, value from table where key = 'A' order by time)

请注意，这里需要子查询。当您想为不同的键获取此解决方案时，此解决方案会受到影响

groupArray .

select key, groupArray(value) from
    (SELECT key, value from table order by time)
    group by key

所以，现在我们得到一个键和一个带有这个键的元素数组。好的。

但是如何计算滑动差 ?如果我们能做到，那么 ARRAY JOIN将导致我们的结果。

或者我们甚至可以 zip数组本身然后应用 lambda(我们有 arrayMap)但是......我们没有任何 zip选择。

有任何想法吗？
提前致谢。

Answer 1

数组解决方案:

WITH 
   groupArray(value) as time_sorted_vals,
   arrayEnumerate(time_sorted_vals) as indexes,
   arrayMap( i -> time_sorted_vals[i] - time_sorted_vals[i-1], indexes) as running_diffs
SELECT 
   key,
   running_diffs
FROM 
    (SELECT key, value from table order by time)
GROUP by key

其他选项(在每个组内分别进行排序，这在很多情况下更为优化)

WITH
   groupArray( tuple(value,time) ) as val_time_tuples,
   arraySort( x -> x.2, val_time_tuples ) as val_time_tuples_sorted,
   arrayMap( t -> t.1, indexes) as time_sorted_vals,
   arrayEnumerate(time_sorted_vals) as indexes,
   arrayMap( i -> time_sorted_vals[i] - time_sorted_vals[i-1], indexes) as running_diffs
SELECT 
   key,
   running_diffs
FROM 
   time
GROUP by key

之后您可以对结果应用 ARRAY JOIN。