使用clickhouse计算当前行与前一行之间的差异(或delta)

Question

如果能在查询过程中索引行，那就太棒了。

是否有一种方法来选择(计算)连续行之间的单列的差异?

比如说，像下面这样的查询

SELECT 
toStartOfDay(stamp) AS day,  
count(day )  AS  events ,
day[current] - day[previous] AS difference, -- how do I calculate this
day[current] / day[previous] as percent, -- and this
FROM  records
GROUP BY day 
ORDER BY day 

我想获得当前行'events'列与前一列之间的整数和百分比差，类似于以下内容:

<表类>天事件不同%tbody><<tr>2022-01-06就是197空空2022-01-07就是6564593.322022-01-08就是15-6410.022022-01-09就是780.462022-01-10就是13713019.5

我的Clickhouse版本不支持窗口函数，但是，在查看评论中提到的LAG()函数时，我发现neighbor()，这非常适合我要做的事情

SELECT 
toStartOfDay(stamp) AS day,  
count(day )  AS  events ,
(events - neighbor(events, -1)) as diff,
(events / neighbor(events, -1)) as perc
FROM  records
GROUP BY day 
ORDER BY day