ClickHouse中按id分组使用neighbor函数计算amount差值的问题

问题原因 #

你当前使用的neighbor(amount, -1)函数是基于全局排序后的行取前一行值，不会识别不同id的分组边界，所以遇到新id时，仍会跨id取上一行值计算，导致差值错误。

解决方案 #

改用ClickHouse支持的窗口函数LAG()，通过PARTITION BY id限定在同一id分组内计算，结合ORDER BY time保证分组内行的时间顺序，即可正确获取分组内前一行的amount值。

修正后的SQL语句：

SELECT 
    toInt64(visitParamExtractString(JSONExtractString(message,'id'),'id')) as id,
    visitParamExtractString(message,'name') as name,
    toFloat64(visitParamExtractString(message, 'amount')) as amount,
    round(amount - COALESCE(LAG(amount) OVER (PARTITION BY id ORDER BY time), 0), 2) as diff
FROM stage1.log dl 
WHERE   
    time >= '2023-04-22 13:01:00'
    and time <= '2023-04-22 13:01:30'
    and name in ('apple')
    and amount > 0 
ORDER BY id, time

代码说明 #

1. LAG(amount) OVER (PARTITION BY id ORDER BY time)：在每个id分组内按时间排序，获取当前行的前一行amount值；分组内第一行无前置行时返回NULL。
2. COALESCE(..., 0)：将第一行的NULL替换为0，确保第一行差值为amount - 0，符合期望结果。
3. 最终排序改为ORDER BY id, time，保证同一id的行连续排列，结果更直观。

修正后结果 #

内容的提问来源于stack exchange，提问作者ditrauth