基于ClickHouse的使用者行為分析實踐

點選上方藍色字型，選擇“設為星标”

回複”資源“擷取更多資源

前言

ClickHouse為使用者提供了豐富的多參聚合函數（parametric aggregate function）和基于數組+Lambda表達式的高階函數（higher-order function），将它們靈活使用可以達到魔法般的效果。在我們的體系中，ClickHouse定位點選流數倉，是以下面舉幾個用它來做使用者行為（路徑）分析的實戰例子，包括：

路徑比對
智能路徑檢測
有序漏鬥轉化
使用者留存
Session統計

路徑比對

CK預設提供了sequenceMatch函數檢查是否有事件鍊滿足輸入的模式，sequenceCount函數則統計滿足輸入模式的事件鍊的數量。示例：

SELECT 
  site_id,
  sequenceMatch('(?1)(?t<=15)(?2).*(?3)')(
    ts_date_time,
    event_type = 'shtLogon',
    event_type = 'shtKkclick' AND column_type = 'homePage',
    event_type = 'shtAddCart'
  ) AS is_match
FROM ods.analytics_access_log_all
WHERE ts_date >= '2020-07-01'
AND site_id IN (10266,10022,10339,10030)
GROUP BY site_id;

┌─site_id─┬─is_match─┐
│   10030 │        1 │
│   10339 │        1 │
│   10266 │        1 │
│   10022 │        1 │
└─────────┴──────────┘

SELECT 
  site_id,
  sequenceCount('(?1)(?t<=15)(?2).*(?3)')(
    ts_date_time,
    event_type = 'shtLogon',
    event_type = 'shtKkclick' AND column_type = 'homePage',
    event_type = 'shtAddCart'
  ) AS seq_count
FROM ods.analytics_access_log_all
WHERE ts_date >= '2020-07-01'
AND site_id IN (10266,10022,10339,10030)
GROUP BY site_id;

┌─site_id─┬─seq_count─┐
│   10030 │     33611 │
│   10339 │     14045 │
│   10266 │     74542 │
│   10022 │     31534 │
└─────────┴───────────┘

這兩個函數都需要指定模式串、時間列和期望的事件序列（最多可指定32個事件）。模式串的文法有以下三種：

(?N) ：表示時間序列中的第N個事件，從1開始。例如上述SQL中， (?2) 即表示 event_type = 'shtKkclick' AND column_type = 'homePage' 。
(?t op secs) ：插入兩個事件之間，表示它們發生時需要滿足的時間條件（機關為秒）。例如上述SQL中， (?1)(?t<=15)(?2) 即表示事件1和2發生的時間間隔在15秒以内。
.* ：表示任意的非指定事件。

智能路徑檢測

CK内置的sequenceMatch和sequenceCount函數隻能滿足部分需求，現有一個更複雜的需求：

給定期望的路徑終點、途經點和最大事件時間間隔，查詢出符合條件的路徑詳情及符合路徑的使用者數（按使用者數降序排列）。

目前并沒有現成的函數可以直接出結果，但是我們可以曲線救國，用數組和高階函數的組合間接實作。完整SQL語句如下，略長：

SELECT
  result_chain,
  uniqCombined(user_id) AS user_count
FROM (
  WITH
    toUInt32(maxIf(ts_date_time, event_type = 'shtOrderDone')) AS end_event_maxt,
    arrayCompact(arraySort(
      x -> x.1,
      arrayFilter(
        x -> x.1 <= end_event_maxt,
        groupArray((toUInt32(ts_date_time), (event_type, column_type)))
      )
    )) AS sorted_events,
    arrayEnumerate(sorted_events) AS event_idxs,
    arrayFilter(
      (x, y, z) -> z.1 <= end_event_maxt AND (z.2.1 = 'shtOrderDone' OR y > 600),
      event_idxs,
      arrayDifference(sorted_events.1),
      sorted_events
    ) AS gap_idxs,
    arrayMap(x -> x + 1, gap_idxs) AS gap_idxs_,
    arrayMap(x -> if(has(gap_idxs_, x), 1, 0), event_idxs) AS gap_masks,
    arraySplit((x, y) -> y, sorted_events, gap_masks) AS split_events
  SELECT
    user_id,
    arrayJoin(split_events) AS event_chain_,
    arrayCompact(event_chain_.2) AS event_chain,
    hasAll(event_chain, [('shtKkClick', 'homePage')]) AS has_midway_hit,
    arrayStringConcat(arrayMap(
      x -> concat(x.1, '#', x.2),
      event_chain
    ), ' -> ') AS result_chain
  FROM (
    SELECT ts_date,ts_date_time,event_type,column_type,user_id
    FROM ods.analytics_access_log_all
    WHERE ts_date >= '2020-06-30' AND ts_date <= '2020-07-02'
    AND site_id IN (10266,10022,10339,10030)
  )
  GROUP BY user_id
  HAVING length(event_chain) > 1
)
WHERE event_chain[length(event_chain)].1 = 'shtOrderDone'
AND has_midway_hit = 1
GROUP BY result_chain
ORDER BY user_count DESC LIMIT 20;

簡述思路：

将使用者的行為用groupArray函數整理成<時間, <事件名, 頁面名>>的元組，并用arraySort函數按時間升序排序；
利用arrayEnumerate函數擷取原始行為鍊的下标數組；
利用arrayFilter和arrayDifference函數，過濾出原始行為鍊中的分界點下标。分界點的條件是路徑終點或者時間差大于最大間隔；
利用arrayMap和has函數擷取下标數組的掩碼（由0和1組成的序列），用于最終切分，1表示分界點；
調用arraySplit函數将原始行為鍊按分界點切分成單次通路的行為鍊。注意該函數會将分界點作為新鍊的起始點，是以前面要将分界點的下标加1；
調用arrayJoin和arrayCompact函數将事件鍊的數組打平成多行單列，并去除相鄰重複項。
調用hasAll函數确定是否全部存在指定的途經點。如果要求有任意一個途經點存在即可，就換用hasAny函數。當然，也可以修改WHERE謂詞來排除指定的途經點。
将最終結果整理成可讀的字元串，按行為鍊統計使用者基數，完成。

有序漏鬥轉化

CK提供了windowFunnel函數實作漏鬥，以指定時長（機關為秒）滑動視窗按序比對事件鍊，并傳回在視窗内轉化到的步數。如有多種比對，以步數最大（轉換最深）的為準。

通過對該步數進行統計，即可得到漏鬥中每步的轉化率。SQL語句如下，查詢結果是敏感資料，不再貼出來了。

SELECT 
  level,user_count,conv_rate_percent
FROM (
  SELECT 
    level,
    uniqCombined(user_id) AS user_count,
    neighbor(user_count, -1) AS prev_user_count,
    if (prev_user_count = 0, -1, round(user_count / prev_user_count * 100, 3)) AS conv_rate_percent
  FROM (
    SELECT
      user_id,
      windowFunnel(900)(
        ts_date_time,
        event_type = 'shtLogon',
        event_type = 'shtKkClick' AND column_type = 'homePage',
        event_type = 'shtOpenGoodsDetail',
        event_type = 'shtAddCart',
        event_type = 'shtOrderDone'
      ) AS level
    FROM (
      SELECT ts_date,ts_date_time,event_type,column_type,user_id
      FROM ods.analytics_access_log_all
      WHERE ts_date >= '2020-06-30' AND ts_date <= '2020-07-02'
      AND site_id IN (10266,10022,10339,10030)
    )
    GROUP BY user_id
  )
  WHERE level > 0
  GROUP BY level
  ORDER BY level ASC
);

如果想要更準确一些，實作漏鬥步驟之間的字段關聯（如商品詳情→加入購物車→下單三步中的商品ID關聯）怎麼辦呢？可以利用https://github.com/housepower/olap2018項目中提出的xFunnel函數。它是windowFunnel函數的鼻祖，不過需要修改ClickHouse源碼并重新編譯之，今後有時間的話會簡單寫一下過程。

使用者留存

retention函數可以友善地計算留存情況。該函數接受多個條件，以第一個條件的結果為基準，觀察後面的各個條件是否也滿足，若滿足則置1，不滿足則置0，最終傳回0和1的數組。通過統計1的數量，即可計算出留存率。

下面的SQL語句計算次日重複下單率與七日重複下單率（語義與留存相同）。

SELECT
  sum(ret[1]) AS original,
  sum(ret[2]) AS next_day_ret,
  round(next_day_ret / original * 100, 3) AS next_day_ratio,
  sum(ret[3]) AS seven_day_ret,
  round(seven_day_ret / original * 100, 3) AS seven_day_ratio
FROM (
  WITH toDate('2020-06-24') AS first_date
  SELECT
    user_id,
    retention(
      ts_date = first_date,
      ts_date = first_date + INTERVAL 1 DAY,
      ts_date = first_date + INTERVAL 7 DAY
    ) AS ret
  FROM ods.ms_order_done_all
  WHERE ts_date >= first_date AND ts_date <= first_date + INTERVAL 7 DAY
  GROUP BY user_id
);

Session統計

Session，即"會話"，是指在指定的時間段内在網站/H5/小程式/APP上發生的一系列使用者行為的集合。例如，一次會話可以包含多個頁面浏覽、互動事件等。Session是具備時間屬性的，根據不同的切割規則，可以生成不同長度的Session。

基于ClickHouse的使用者行為分析實踐

可見，Session統計與上述智能路徑檢測的場景有相似之處，都需要尋找使用者行為鍊的邊界并進行切割。以下SQL語句以30分鐘為逾時時間，按天統計所有使用者的Session總數（跨天的Session也會被切割）。

SELECT 
  ts_date,
  sum(length(session_gaps)) AS session_cnt
FROM (
  WITH
    arraySort(groupArray(toUInt32(ts_date_time))) AS times,
    arrayDifference(times) AS times_diff
  SELECT
    ts_date,
    arrayFilter(x -> x > 1800, times_diff) AS session_gaps
  FROM ods.analytics_access_log_all
  WHERE ts_date >= '2020-06-30'
  GROUP BY ts_date,user_id
)
GROUP BY ts_date;

基于ClickHouse的使用者行為分析實踐

本文為大資料技術與架構整理，原作者獨家授權。未經原作者允許轉載追究侵權責任。

編輯｜胡曉

微信公衆号｜import_bigdata

歡迎點贊+收藏+轉發朋友圈素質三連

基于ClickHouse的使用者行為分析實踐

文章不錯？點個【在看】吧！ ????

基于ClickHouse的使用者行為分析實踐

前言

路徑比對

智能路徑檢測

有序漏鬥轉化

使用者留存

Session統計

繼續閱讀

無法解析的外部符号 wmain，該符号在函數 "void cdecl mainCRTStartupHelper(struct HINSTANCE *,unsigned short con......

TestLink導出用例轉換工具(XML2Excel)

JVM記憶體管理、直接記憶體和垃圾回收

YAML簡介和PyYAML安全操作YAML支援的類型YAML的優點：yaml的基本文法python操作

Small tricks

libsvm for python 安裝

學習軟體測試基礎測試第七天

Zeppelin 配置通路 REST APIApache Zeppelin Configuration REST API

【Torch】最簡潔logging使用指南

27. Remove Element(清單)題目代碼

Cloud Studio初體驗

使用 ctypes 進行 Python 和 C 的混合程式設計

【python】【資料處理】畫多元資料分布圖

【python】netconf協定對接管理裝置

「Python 網絡自動化」NETCONF —— Python 使用 NETCONF 管理配置 H3C 網絡裝置

在python中建立excel并寫入