使用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_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.            

對于這類查詢，你有更好的思路或者方法嗎? 歡迎和我交流讨論 :P