ClickHouse是如何提高留存計算速度

160次閱讀

共計 6947 個字符，預計需要花費 18 分鐘才能閱讀完成。

這篇文章將為大家詳細講解有關 ClickHouse 是如何提高留存計算速度，文章內容質量較高，因此丸趣 TV 小編分享給大家做個參考，希望大家閱讀完這篇文章后對相關知識有一定的了解。

關于用戶留存是各大數據分析平臺必不可少的功能，企業一般用留存率衡量用戶的活躍情況，也是能直接反應產品功能價值的直接指標，留存率是衡量用戶質量的最重要指標之一，因此計算各種留存率是數據分析取數的最底層的基本功。所以下面舉幾個用戶留存分析的實戰例子。

1. 準備

了解目前留存率幾種常規計算方法、了解 ClickHouse 提供 retention(cond1, cond2, …) 函數計算留存率

建表：用戶基本信息表：login_event

CREATE TABLE login_event -- 用戶登錄事件
 `accountId` String COMMENT  賬號的 ID , -- 用戶唯一 ID
 `ds` Date COMMENT  日期  -- 用戶登錄日期
ENGINE = MergeTree
PARTITION BY accountId
ORDER BY accountId

導數：插入 8 月份用戶登錄數據

-- 插入數據
insert into login_event values (10001,toDate( 2020-08-01), (10001,toDate( 2020-08-08)), (10001,toDate( 2020-08-09)), (10001,toDate( 2020-08-10)), (10001,toDate( 2020-08-12)),
(10001,toDate( 2020-08-13)), (10001,toDate( 2020-08-14)), (10001,toDate( 2020-08-15)), (10001,toDate( 2020-08-16)), (10001,toDate( 2020-08-17)), (10001,toDate( 2020-08-18)),
(10001,toDate( 2020-08-20)), (10001,toDate( 2020-08-22)), (10001,toDate( 2020-08-23)), (10001,toDate( 2020-08-24)), (10002,toDate( 2020-08-20)), (10002,toDate( 2020-08-22)), (10002,toDate( 2020-08-23)), (10002,toDate( 2020-08-01)), (10002,toDate( 2020-08-11)), (10002,toDate( 2020-08-12)), (10002,toDate( 2020-08-13)), (10002,toDate( 2020-08-20)),
(10002,toDate( 2020-08-15)), (10002,toDate( 2020-08-30)), (10002,toDate( 2020-08-20)), (10002,toDate( 2020-08-01)), (10002,toDate( 2020-08-06)), (10002,toDate( 2020-08-24)), (10003,toDate( 2020-08-05)), (10003,toDate( 2020-08-08)), (10003,toDate( 2020-08-09)), (10003,toDate( 2020-08-10)), (10003,toDate( 2020-08-11)), (10003,toDate( 2020-08-13)),
(10003,toDate( 2020-08-15)), (10003,toDate( 2020-08-16)), (10003,toDate( 2020-08-18)), (10003,toDate( 2020-08-20)), (10003,toDate( 2020-08-01)), (10003,toDate( 2020-08-21)),
(10003,toDate( 2020-08-22)), (10003,toDate( 2020-08-24)), (10003,toDate( 2020-08-26)), (10003,toDate( 2020-08-25)), (10003,toDate( 2020-08-27)), (10003,toDate( 2020-08-28)),
(10003,toDate( 2020-08-29)), (10003,toDate( 2020-08-30)), (10004,toDate( 2020-08-01)), (10004,toDate( 2020-08-02)), (10004,toDate( 2020-08-03)), (10004,toDate( 2020-08-04)),
(10004,toDate( 2020-08-05)), (10004,toDate( 2020-08-08)), (10004,toDate( 2020-08-09)), (10004,toDate( 2020-08-10)), (10004,toDate( 2020-08-11)), (10004,toDate( 2020-08-14)),
(10004,toDate( 2020-08-15)), (10004,toDate( 2020-08-16)), (10004,toDate( 2020-08-17)), (10004,toDate( 2020-08-19)), (10004,toDate( 2020-08-20)), (10004,toDate( 2020-08-21)),
(10004,toDate( 2020-08-22)), (10004,toDate( 2020-08-23)), (10004,toDate( 2020-08-24)), (10004,toDate( 2020-08-23)),(10004,toDate( 2020-08-23)), (10004,toDate( 2020-08-25)),
(10004,toDate( 2020-08-27)), (10004,toDate( 2020-08-30

2. 題目分析

計算某日活躍用戶的次留、3 留、7 留、14 留、30 留，我們將問題解決分為三個步驟：

找到某日活躍用戶

找到某日活躍用戶在第 2、3、6、13、29 日的登錄情況

計算某日活躍用戶在第 2、3、6、13、29 日登錄數，計算 N 日留存率

解決方法一:

-- 計算出 2020-08-01 活躍用戶在第 2、3、6、13、29 日的留存數，計算出留存率
SELECT
 ds,
 count(accountIdD0) AS activeAccountNum,
 count(accountIdD1) / count(accountIdD0) AS ` 次留 `,
 count(accountIdD3) / count(accountIdD0) AS `3 留 `,
 count(accountIdD7) / count(accountIdD0) AS `7 留 `,
 count(accountIdD14) / count(accountIdD0) AS `14 留 `,
 count(accountIdD30) / count(accountIdD0) AS `30 留 `
( -- 使用 LEFT JOIN  找到 2020-08-01 當日活躍用戶在第 2、3、6、13、29 日的登錄用戶
 SELECT DISTINCT
 a.ds AS ds,
 a.accountIdD0 AS accountIdD0,
 IF(b.accountId =  , NULL, b.accountId) AS accountIdD1,
 IF(c.accountId =  , NULL, c.accountId) AS accountIdD3,
 IF(d.accountId =  , NULL, d.accountId) AS accountIdD7,
 IF(e.accountId =  , NULL, e.accountId) AS accountIdD14,
 IF(f.accountId =  , NULL, f.accountId) AS accountIdD30
 FROM
 (-- 找出 2020-08-01 當日活躍用戶
 SELECT DISTINCT
 ds,
 accountId AS accountIdD0
 FROM login_event
 WHERE ds =  2020-08-01 
 ORDER BY ds ASC
 ) AS a
 LEFT JOIN test.login3_event AS b ON (b.ds = addDays(a.ds, 1)) AND (a.accountIdD0 = b.accountId)
 LEFT JOIN test.login3_event AS c ON (c.ds = addDays(a.ds, 2)) AND (a.accountIdD0 = c.accountId)
 LEFT JOIN test.login3_event AS d ON (d.ds = addDays(a.ds, 6)) AND (a.accountIdD0 = d.accountId)
 LEFT JOIN test.login3_event AS e ON (e.ds = addDays(a.ds, 13)) AND (a.accountIdD0 = e.accountId)
 LEFT JOIN test.login3_event AS f ON (f.ds = addDays(a.ds, 29)) AND (a.accountIdD0 = f.accountId)
) AS temp
GROUP BY ds
-----------------------------------------
┌─────────ds─┬─activeAccountNum─┬─次留─┬──3 留─┬─7 留─┬─14 留─┬─30 留─┐
│ 2020-08-01 │ 4 │ 0.25 │ 0.25 │ 0 │ 0.5 │ 0.75 │
└────────────┴──────────────────┴──────┴──────┴─────┴──────┴──────┘
1 rows in set. Elapsed: 0.022 sec.

解決方法二：

-- 判斷 2020-08-01 活躍用戶在第 2、3、6、13、29 日的留存數，計算出留存率，計算出留存率
SELECT DISTINCT
 b.ds AS ds,
 ifnull(countDistinct(if(a.ds = b.ds, a.accountId, NULL)), 0) AS activeAccountNum,
 ifnull(countDistinct(if(a.ds = addDays(b.ds, 1), b.accountId, NULL)) / activeAccountNum, 0) AS ` 次留 `,
 ifnull(countDistinct(if(a.ds = addDays(b.ds, 2), b.accountId, NULL)) / activeAccountNum, 0) AS `3 留 `,
 ifnull(countDistinct(if(a.ds = addDays(b.ds, 6), b.accountId, NULL)) / activeAccountNum, 0) AS `7 留 `,
 ifnull(countDistinct(if(a.ds = addDays(b.ds, 13), b.accountId, NULL)) / activeAccountNum, 0) AS `14 留 `,
 ifnull(countDistinct(if(a.ds = addDays(b.ds, 29), b.accountId, NULL)) / activeAccountNum, 0) AS `30 留 `
 -- 使用 INNER JOIN 找出 2020-08-01 活躍用戶在后續 1～30 日登錄情況
 SELECT
 ds,
 accountId
 FROM login_event
 WHERE (ds  = addDays(toDate( 2020-08-01), 29)) AND (ds  =  2020-08-01)
) AS a
INNER JOIN
-- 找出 2020-08-01 當日活躍用戶
 SELECT DISTINCT
 accountId,
 ds
 FROM test.login3_event
 WHERE ds =  2020-08-01 
) AS b ON a.accountId = b.accountId
GROUP BY ds
-----------------------------------------
┌─────────ds─┬─activeAccountNum─┬─次留─┬──3 留─┬─7 留─┬─14 留─┬─30 留─┐
│ 2020-08-01 │ 4 │ 0.25 │ 0.25 │ 0 │ 0.5 │ 0.75 │
└────────────┴──────────────────┴──────┴──────┴─────┴──────┴──────┘
1 rows in set. Elapsed: 0.019 sec.

解決方法三：

-- 根據數組下標 SUM(r[index]) 獲取 2020-08-01 活躍用戶在第 2、3、6、13、29 日的留存數，計算出留存率
SELECT
 toDate(2020-08-01) AS ds,
 SUM(r[1]) AS activeAccountNum,
 SUM(r[2]) / SUM(r[1]) AS ` 次留 `,
 SUM(r[3]) / SUM(r[1]) AS `3 留 `,
 SUM(r[4]) / SUM(r[1]) AS `7 留 `,
 SUM(r[5]) / SUM(r[1]) AS `14 留 `,
 SUM(r[6]) / SUM(r[1]) AS `30 留 ` 
-- 找到 2020-08-01 活躍用戶在第 2、3、6、13、29 日的登錄情況，1/0 =   登錄 / 未登錄
 WITH toDate(2020-08-01) AS tt 
SELECT
 accountId,
 retention( toDate(ds) = tt, 
 toDate(subtractDays(ds, 1)) = tt, 
 toDate(subtractDays(ds, 2)) = tt, 
 toDate(subtractDays(ds, 6)) = tt,
 toDate(subtractDays(ds, 13)) = tt,
 toDate(subtractDays(ds, 29)) = tt
 ) AS r
 -- 找出 2020-08-01 活躍用戶在后續 1～30 日登錄數據
FROM login_event
WHERE (ds  =  2020-08-01) AND (ds  = addDays(toDate( 2020-08-01), 29))
GROUP BY accountId
GROUP BY ds

-----------------------------------------
┌─────────ds─┬─activeAccountNum─┬─次留─┬──3 留─┬─7 留─┬─14 留─┬─30 留─┐
│ 2020-08-01 │ 4 │ 0.25 │ 0.25 │ 0 │ 0.5 │ 0.75 │
└────────────┴──────────────────┴──────┴──────┴─────┴──────┴──────┘
1 rows in set. Elapsed: 0.009 sec.

3. 總結

方法一，使用傳統做法多表關聯，了解 ClickHouse 的程序猿都清楚，多表關聯是 ClickHouse 天敵，運行速度相對很慢。

方法二，使用一個表關聯，通過 IF 函數判斷日期差值，找到所需日期用戶數據，相對方法一減少了多表關聯，提高了運行速度。

方法三，使用 ClickHouse 自帶 retention 函數，retention function 是 ClickHouse 中高級聚合函數，該函數可以接受多個條件，以第一個條件結果為基準，后面各條件滿足為 1，不滿足則為 0，最后返回一個 1 和 0 組成的數組。通過統計數組中對應 1 的數量，既可計算出留存率。

三種計算方法比較而言，在海量的數據集下使用 ClickHouse 自帶 retention 留存函數運行速度更快、更高效。提升了現有技術中用戶留存率的計算方式速度慢效率低的問題, 進而達到了提高計算速度和計算效率的效果。

關于 ClickHouse 是如何提高留存計算速度就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。

正文完