clickhouse（二、高階函數應用retention計算留存數）

文章目錄

• • 前言
  • 準備
  • 查詢
  • 結論

前言

clickhouse提供retention(cond1, cond2, …)函數友善計算使用者留存率，當然也可以應用在其他需求上。

準備

• 建表

CREATE TABLE login_log -- 使用者登入日志
(
uid Int32,  -- 使用者唯一id
login_time DateTime -- 使用者登入時間
)  
ENGINE = MergeTree PARTITION BY uid order by uid;
           

• 導數

-- 随意發揮就好
insert into login_log 
values
(1101,toDateTime('2019-06-21 22:00:00')),
(1101,toDateTime('2019-06-20 22:00:00')),
(1101,toDateTime('2019-06-19 22:00:00')),
(1101,toDateTime('2019-06-17 22:00:00')),
(1101,toDateTime('2019-06-16 22:00:00')),
(1101,toDateTime('2019-06-21 22:00:00')),
(1101,toDateTime('2019-06-21 23:00:00')),
(1101,toDateTime('2019-06-20 23:00:00')),
(1101,toDateTime('2019-06-19 23:00:00')),
(1101,toDateTime('2019-06-17 23:00:00')),
(1101,toDateTime('2019-06-16 23:00:00')),
(1101,toDateTime('2019-06-21 23:00:00')),
(4101,toDateTime('2019-06-20 22:00:00')),
(4101,toDateTime('2019-06-19 22:00:00')),
(4101,toDateTime('2019-06-17 22:00:00')),
(4101,toDateTime('2019-06-16 22:00:00')),
(2201,toDateTime('2019-06-21 22:00:00')),
(2201,toDateTime('2019-06-20 22:00:00')),
(2201,toDateTime('2019-06-21 23:00:00')),
(2201,toDateTime('2019-06-20 23:00:00')),
(3301,toDateTime('2019-06-21 22:00:00')),
(3301,toDateTime('2019-06-19 22:00:00')),
(3301,toDateTime('2019-06-18 23:00:00')),
(3301,toDateTime('2019-06-17 23:00:00')),
(3301,toDateTime('2019-06-16 23:00:00')),
(3301,toDateTime('2019-06-15 23:00:00'));
           

查詢

-- 查詢使用者在2019-06-17 至 2019-06-21 号登入情況
select 
uid,
retention(
    toDate(login_time) = '2019-06-21',
    toDate(addDays(login_time,1)) = '2019-06-21',
    toDate(addDays(login_time,2)) = '2019-06-21',
    toDate(addDays(login_time,3)) = '2019-06-21',
    toDate(addDays(login_time,4)) = '2019-06-21'
    ) as r
from login_log ll 
group by ll.uid

結果：r數組表示每天登入情況，1/0 => 登入/未登入，
-----------------
┌──uid─┬─r───────────┐
│ 1101 │ [1,1,1,0,1] │
│ 2201 │ [1,1,0,0,0] │
│ 3301 │ [1,0,1,1,1] │
│ 4101 │ [0,0,0,0,0] │
└──────┴─────────────┘
4 rows in set. Elapsed: 0.014 sec. 
           

值得注意的是uid=4101的使用者，明明有登入記錄，但資料全是0。這是因為當retention(cond1, cond2, …)函數中，第一個表達cond1如果不滿足，那麼後面所有的結果就都是0。

4101的使用者2019-06-21當天沒登入，所有他後面的資料全部為0，那如果不想出現這種情況怎麼做了，既然cond是條件判斷，所有隻需要将第一個表達式cond1直接設定成1即可。

結論

看完retention函數計算出的資料結構後，我們知道就是一個Array類型字段，那麼就可以根據自身需求充分利用了。比如計算2019-06-21當天的使用者量就是sum(r[1])即可。

下一節 我們來看下各叢集間如何進行資料同步傳輸。