clickhouse 实现同环比

背景

我们知道clickhouse一般都是处理单表的数据，经常需要实现同环比等分析场景，这里提供两种方式：
首先计算公式：

同比、环比分析是一对常见的分析指标，其增长率公式如下：

---

同比增长率 =（本期数 - 同期数) / 同期数

环比增长率 =（本期数 - 上期数) /上期数

 

1. 向大家介绍如何利用 neighbor 函数，快速实现同比、环比分析。

neighbor函数可以说是lag()与lead()的合体，它可以根据指定的offset，向前或者向后获取到相应字段的值，其完整定义如下所示:
neighbor(column, offset[, default_value])

其中:
column 是指定字段;
offset 是偏移量，例如 1 表示curr_row + 1，即每次向前获取一位;

 

WITH toDate('2019-01-01') AS start_date
SELECT 
    toStartOfMonth(start_date + (number * 32)) AS date_time, 
    (number + 1) * 100 AS money, 
    neighbor(money, -12) AS prev_year, 
    neighbor(money, -1) AS prev_month, 
    if(prev_year = 0, -999, round((money - prev_year) / prev_year, 2)) AS year_over_year, 
    if(prev_month = 0, -999, round((money - prev_month) / prev_month, 2)) AS month_over_month
FROM numbers(16)
 
┌──date_time─┬─money─┬─prev_year─┬─prev_month─┬─year_over_year─┬─month_over_month─┐
│ 2019-01-01 │   100 │         0 │          0 │           -999 │             -999 │
│ 2019-02-01 │   200 │         0 │        100 │           -999 │                1 │
│ 2019-03-01 │   300 │         0 │        200 │           -999 │              0.5 │
│ 2019-04-01 │   400 │         0 │        300 │           -999 │             0.33 │
│ 2019-05-01 │   500 │         0 │        400 │           -999 │             0.25 │
│ 2019-06-01 │   600 │         0 │        500 │           -999 │              0.2 │
│ 2019-07-01 │   700 │         0 │        600 │           -999 │             0.17 │
│ 2019-08-01 │   800 │         0 │        700 │           -999 │             0.14 │
│ 2019-09-01 │   900 │         0 │        800 │           -999 │             0.12 │
│ 2019-10-01 │  1000 │         0 │        900 │           -999 │             0.11 │
│ 2019-11-01 │  1100 │         0 │       1000 │           -999 │              0.1 │
│ 2019-12-01 │  1200 │         0 │       1100 │           -999 │             0.09 │
│ 2020-01-01 │  1300 │       100 │       1200 │             12 │             0.08 │
│ 2020-02-01 │  1400 │       200 │       1300 │              6 │             0.08 │
│ 2020-03-01 │  1500 │       300 │       1400 │              4 │             0.07 │
│ 2020-04-01 │  1600 │       400 │       1500 │              3 │             0.07 │
└────────────┴───────┴───────────┴────────────┴────────────────┴──────────────────┘
 
16 rows in set. Elapsed: 0.006 sec. 

 

 

2.使用开窗函数

当获取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等区分处理最后一次模拟考试和其他的模拟考试。有了这些信息，我们就可以获取每次模拟考试同比增加了多少分等数据了