基于ClickHouse的用戶行為分析實踐是怎樣的

基于ClickHouse的用戶行為分析實踐是怎樣的，很多新手對此不是很清楚，為了幫助大家解決這個難題，下面小編將為大家詳細講解，有這方面需求的人可以來學習下，希望你能有所收獲。

前言

ClickHouse為用戶提供了豐富的多參聚合函數（parametric aggregate function）和基于數組+Lambda表達式的高階函數（higher-order function），將它們靈活使用可以達到魔法般的效果。在我們的體系中，ClickHouse定位點擊流數倉。

路徑匹配

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_matchFROM ods.analytics_access_log_allWHERE 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_countFROM ods.analytics_access_log_allWHERE 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_countFROM (  WITHtoUInt32(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  SELECTuser_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_idFROM ods.analytics_access_log_allWHERE 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 = 1GROUP BY result_chainORDER BY user_count DESC LIMIT 20;

簡述思路：

1. 將用戶的行為用groupArray函數整理成<時間, <事件名, 頁面名>>的元組，并用arraySort函數按時間升序排序；

2. 利用arrayEnumerate函數獲取原始行為鏈的下標數組；

3. 利用arrayFilter和arrayDifference函數，過濾出原始行為鏈中的分界點下標。分界點的條件是路徑終點或者時間差大于最大間隔；

4. 利用arrayMap和has函數獲取下標數組的掩碼（由0和1組成的序列），用于最終切分，1表示分界點；

5. 調用arraySplit函數將原始行為鏈按分界點切分成單次訪問的行為鏈。注意該函數會將分界點作為新鏈的起始點，所以前面要將分界點的下標加1；

6. 調用arrayJoin和arrayCompact函數將事件鏈的數組打平成多行單列，并去除相鄰重復項。

7. 調用hasAll函數確定是否全部存在指定的途經點。如果要求有任意一個途經點存在即可，就換用hasAny函數。當然，也可以修改WHERE謂詞來排除指定的途經點。

8. 將最終結果整理成可讀的字符串，按行為鏈統計用戶基數，完成。

有序漏斗轉化

CK提供了windowFunnel函數實現漏斗，以指定時長（單位為秒）滑動窗口按序匹配事件鏈，并返回在窗口內轉化到的步數。如有多種匹配，以步數最大（轉換最深）的為準。

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

SELECT 
  level,user_count,conv_rate_percentFROM (  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 levelFROM (      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_ratioFROM (  WITH toDate('2020-06-24') AS first_date  SELECTuser_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。

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

SELECT 
  ts_date,  sum(length(session_gaps)) AS session_cntFROM (  WITHarraySort(groupArray(toUInt32(ts_date_time))) AS times,
    arrayDifference(times) AS times_diff  SELECTts_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;

看完上述內容是否對您有幫助呢？如果還想對相關知識有進一步的了解或閱讀更多相關文章，請關注億速云行業資訊頻道，感謝您對億速云的支持。