同比、环比分析是一对常见的分析指标,其增长率公式如下:
同比增长率 =(本期数 - 同期数) / 同期数
环比增长率 =(本期数 - 上期数) /上期数
在一些提供了开窗函数的数据库中(如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_date SELECT toStartOfMonth(start_date + (number * 32)) AS date_time, (number + 1) * 100 AS money FROM 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_date SELECT toStartOfMonth(start_date + (number * 32)) AS date_time, (number + 1) * 100 AS money, neighbor(money, -12) AS prev_year FROM 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_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.