sql同比环比的语句_使用ClickHouse快速实现同比、环比分析

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

---

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

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

---

在一些提供了开窗函数的数据库中(如Oracle、Hive)，可以利用lag()、lead()函数配合over()，非常方便的实现同比和环比的查询。

大家知道，ClickHose目前是没有提供对应的over()函数的，但是借助一些特殊的函数，也能变相实现开窗的效果。

今天就在此抛砖引玉，向大家介绍如何利用 neighbor 函数，快速实现同比、环比分析。

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

neighbor(column, offset[, default_value])

其中:

column 是指定字段;

offset 是偏移量，例如 1 表示curr_row + 1，即每次向前获取一位;

-1 表示curr_row - 1 ，即每次向后获取一位;

default_value 是默认值，如果curr_row +/- 1 超过了返回结果集的边界，则使用默认值。选填参数，在默认情况下，会使用column字段数据类型的默认值。

现在用一个示例说明，假设有一份销售数据如下所示:

ch7.nauu.com :) WITH toDate('2019-01-01') AS start_date:-] SELECT:-]     toStartOfMonth(start_date + (number * 32)) AS date_time,:-]     (number+1) * 100 AS money:-] FROM numbers(16);WITH toDate('2019-01-01') AS start_dateSELECT     toStartOfMonth(start_date + (number * 32)) AS date_time,     (number + 1) * 100 AS moneyFROM numbers(16)┌──date_time─┬─money─┐│ 2019-01-01 │   100 ││ 2019-02-01 │   200 ││ 2019-03-01 │   300 ││ 2019-04-01 │   400 ││ 2019-05-01 │   500 ││ 2019-06-01 │   600 ││ 2019-07-01 │   700 ││ 2019-08-01 │   800 ││ 2019-09-01 │   900 ││ 2019-10-01 │  1000 ││ 2019-11-01 │  1100 ││ 2019-12-01 │  1200 ││ 2020-01-01 │  1300 ││ 2020-02-01 │  1400 ││ 2020-03-01 │  1500 ││ 2020-04-01 │  1600 │└────────────┴───────┘16 rows in set. Elapsed: 0.002 sec.

这份数据逐月记录了19年1月 至 20年4月的销售额。

现在我们看看 neighbor 函数有什么作用

在刚才的查询中，我们添加neighbor函数，并将offset设为-12，意思是向上取第12行的money值，即取上一年度同月份的money数:

neighbor(money, -12) AS prev_year

再次观察结果:

WITH toDate('2019-01-01') AS start_dateSELECT     toStartOfMonth(start_date + (number * 32)) AS date_time,     (number + 1) * 100 AS money,     neighbor(money, -12) AS prev_yearFROM numbers(16)┌──date_time─┬─money─┬─prev_year─┐│ 2019-01-01 │   100 │         0 │ <===================-|│ 2019-02-01 │   200 │         0 │ <=============-|     |│ 2019-03-01 │   300 │         0 │ <=======-|     |     |│ 2019-04-01 │   400 │         0 │ <=-|     |     |     |│ 2019-05-01 │   500 │         0 │    |     |     |     |│ 2019-06-01 │   600 │         0 │    |     |     |     |│ 2019-07-01 │   700 │         0 │    |     |     |     |│ 2019-08-01 │   800 │         0 │    |     |     |     |│ 2019-09-01 │   900 │         0 │    |     |     |     |│ 2019-10-01 │  1000 │         0 │    |     |     |     |│ 2019-11-01 │  1100 │         0 │    |     |     |     |│ 2019-12-01 │  1200 │         0 │    |     |     |     |│ 2020-01-01 │  1300 │       100 │    |     |     |====-|│ 2020-02-01 │  1400 │       200 │    |     |====-|│ 2020-03-01 │  1500 │       300 │    |====-|│ 2020-04-01 │  1600 │       400 │ ==-|└────────────┴───────┴───────────┘16 rows in set. Elapsed: 0.002 sec.

可以看到，prev_year即表示同期数。

现在，进一步完善SQL语句，首先按照同比公式计算比率并取整:

round((money-prev_year) / prev_year, 2))

接着，使用-999代号表示没有同比数据的情况：

if(prev_year=0, -999, round((money-prev_year) / prev_year, 2)) AS year_over_year

至此，我们就完成了同比增长率的计算。

接下来看环比计算，与同比类似，只是将offset设置成 -1 即可：

neighbor(money, -1) AS prev_month

此处的prev_month即表示上期数。

所以，最终的SQL语句如下所示：

WITH toDate('2019-01-01') AS start_dateSELECT     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_monthFROM 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. 

对于这类查询，你有更好的思路或者方法吗? 欢迎和我交流讨论 :P

欢迎大家扫码关注我的公众号和视频号:

ClickHouse的秘密基地

nauu的奇思妙想

往期精彩推荐:

1. 从ClickHouse的名字由来讲起

2. 在5000亿数据中大海捞针，需要怎样的硬件做支撑?

3. ClickHouse为何如此之快？

4. 在DB-Engines的排名不高，ClickHouse还值得关注吗？

5. 为什么要写ClickHouse这本书

6. 一分钟视频解读ClickHouse MergeTree

7. ClickHouse的前世今生-直播回放

8.ClickHouse各种MergeTree的关系与作用

9.如何在ClickHouse中查看SQL执行计划

10.使用ClickHouse Playground进行交互式学习