助你成为数据分析达人-带你透彻的了解clickhouse实现同比环比分析
助你成为数据分析达人-带你透彻的了解clickhouse实现同比环比分析
网上很多人分享了同比环比的所谓的开窗函数（"neighbor"），在某些业务需求上可能能方便的使用neighbor函数来实现，但是某些业务上其实没那么方便，下面我先给大家分析一下neighbor的使用，以及使用它存在的缺点。
所谓同比和环比
1、同比发展速度，是指本期发展水平与上年同期发展水平对比，而达到的相对发展速度。
2、环比发展速度，是指报告期水平与前一时期水平对比，表明现象逐期的发展速度。
同比增长率和环比的增长率计算如下
同比增长率 =（本期数 - 同期数) / 同期数
环比增长率 =（本期数 - 上期数) /上期数
neighbor使用语法如下：
neighbor(value, offset[, default_value])
neighbor其实主要是基于value进行移位，这样能实现同比环比的功能
column 是指定字段。
offset 是偏移量，例如 1 表示curr_row + 1，即每次向前获取一位;
-1 表示curr_row - 1 ，即每次向后获取一位。
default_value 是默认值，如果curr_row +/- 1 超过了返回结果集的边界，则使用默认值。选填参数，在默认情况下，会使用column字段数据类型的默认值。
下面我们通过一个例子来了解同比环比的使用
例子：我们需要计算2021-08-01到2021-08-10的去年同比每天的收入，和上个月的环比收入。如果我要使用neighbor函数，则我需要计算从去年2020-08-01到2021-08-10的每天的数据。我的sql如下：
WITH toDate('2020-08-01') AS start_date
SELECT
toStartOfDay(start_date + (number * 1)) AS date_time,
(number + 1) * 100 AS money,
neighbor(money, -365) AS prev_year,
neighbor(money, -1) AS prev_day,
if(
prev_year = 0,
-999,
round((money - prev_year) / prev_year, 2)
) AS year_over_year,
if(
prev_day = 0,
-999,
round((money - prev_day) / prev_day, 4)
) AS day_over_day
FROM
numbers(375)
执行结果如下：
好了这里其实比较明显能看出来问题了，比如我们同比并且环比天的数据，我用这个函数，则需要把这么多天的数据全部查出来，这个时候是非常耗资源的，我是不建议这么使用的。
关于同比环比实现，我更加倾向于通过逻辑代码来实现，或者自己写sql来实现。如果用sql，上面的例子我会多查出来一天，同时查一下同期去年的数据，然后合并起来计算。
select
a.date_time day,
a.money,
b.money prev_year,
neighbor(money, -1) AS prev_day,
if(
prev_year = 0,
-999,
round((money - prev_year) / prev_year, 2)
) AS year_over_year,
if(
prev_day = 0,
-999,
round((money - prev_day) / prev_day, 4)
) AS day_over_day
from
(
WITH toDate('2021-07-31') AS start_date
SELECT
toStartOfDay(start_date + (number * 1)) AS date_time,
(number + 1) * 110 AS money
FROM
numbers(11)
) a
left join (
WITH toDate('2020-08-01') AS start_date
SELECT
toStartOfDay(start_date + (number * 1)) AS date_time,
(number + 1) * 100 AS money
FROM
numbers(10)
) b on a.date_time = addYears(b.date_time, 1)
上面的sql的结果如下，虽然包含了计算的前一天，但是可以通过子查询再查一遍就可以过滤“2021-07-31 00:00:00”这条数据了，这样结果就出来了。
总结：
neighbor计算环比是非常方便的，但是如果计算同比可能会涉及到计算的时间统计长度很长，比较耗资源，可以采用分段查询结果合并汇总结果。
腾讯云开发者
