clickhouse开窗函数之同比环比

背景

我们知道clickhouse一般都是处理单表的数据，当获取lag(前一条)，lead(后一条)记录等数据时，和spark可以通过表连接来获取不同，clickhouse需要灵活使用开窗函数获取

技术原理

假设我们有一张每个学生模拟考试分数的表，表定义如下：

create table studentScore（
timestamp DateTime，
name String，
score int32
）engine=MergeTree()
partition by name
order by timestamp

假设我们表中的其中一个同学的数据如下所示：

此刻，我们想要获取这个学生每次考试的前一个成绩记录和后一个成绩记录，应该怎么做呢？

select timestamp,name,score,order_num,reverse_order_num,lag,lead from
(select timestamp, name, score, 
row_number() over (partition by name order by timestamp) as order_num,
row_number() over (partition by name order by timestamp desc) as reverse_order_num,
any(num) over (partition by name order by timestamp rows between 1 preceding and 1 preceding) as lag,
any(num) over (partition by name order by timestamp rows between 1 following and 1 following) as lead
from studentScore

使用row_number over以及rows between xx preceding and xx following开窗函数，我们就会得到以下的结果:

可以看到对于每条模拟考试记录，我们都能看到上一次模拟考试的记录和下一次模拟考试的记录，达到了获取同比环比记录的目的，不过大家可能还注意到一点，我们除了获取前一条模拟考试记录和下一次模拟考试的记录之外，我们还使用row_number over开窗函数额外记录了两列数据，其目的是通过这两列可以让我们区分第一条记录和最后一条记录，也就是每个学生第一次模拟考试记录和最后一次模拟考试记录，记录这个信息的作用是这样我们就可以区分哪一条记录是这个学生的第一次模拟考试记录，哪一条记录是这个学生的最后一次模拟考试记录，需要区分这两条特殊的记录是因为对于第一次模拟考试记录来说，它的前一次考试记录是不存在的，clickhouse这里用0代替，但是实际应用中我们可能需要区分这种情况，使用case when等区分处理第一次模拟考试和其他的模拟考试，同理，对于最后一次模拟考试记录来说，它的后一次考试记录是不存在的，clickhouse这里用0代替，但是实际应用中我们可能需要区分这种情况，使用case when等区分处理最后一次模拟考试和其他的模拟考试。有了这些信息，我们就可以获取每次模拟考试同比增加了多少分等数据了

参考文献:
https://blog.csdn.net/qq_42374697/article/details/115109386
https://blog.csdn.net/liuyingying0418/article/details/120269624