前言

ClickHouse為用戶提供了豐富的多參聚合函數(shù)（parametric aggregate function）和基于數(shù)組+Lambda表達(dá)式的高階函數(shù)（higher-order function），將它們靈活使用可以達(dá)到魔法般的效果。在我們的體系中赡盘，ClickHouse定位點(diǎn)擊流數(shù)倉娶吞，所以下面舉幾個(gè)用它來做用戶行為（路徑）分析的實(shí)戰(zhàn)例子屁桑，包括：

• 路徑匹配
• 智能路徑檢測(cè)
• 有序漏斗轉(zhuǎn)化
• 用戶留存
• Session統(tǒng)計(jì)

路徑匹配

CK默認(rèn)提供了sequenceMatch函數(shù)檢查是否有事件鏈滿足輸入的模式擦耀，sequenceCount函數(shù)則統(tǒng)計(jì)滿足輸入模式的事件鏈的數(shù)量。示例：

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 │
└─────────┴───────────┘

這兩個(gè)函數(shù)都需要指定模式串溜宽、時(shí)間列和期望的事件序列（最多可指定32個(gè)事件）实苞。模式串的語法有以下三種：

• (?N)：表示時(shí)間序列中的第N個(gè)事件豺撑，從1開始。例如上述SQL中黔牵，(?2)即表示event_type = 'shtKkclick' AND column_type = 'homePage'聪轿。
• (?t op secs)：插入兩個(gè)事件之間，表示它們發(fā)生時(shí)需要滿足的時(shí)間條件（單位為秒）猾浦。例如上述SQL中陆错，(?1)(?t<=15)(?2)即表示事件1和2發(fā)生的時(shí)間間隔在15秒以內(nèi)灯抛。
• .*：表示任意的非指定事件。

智能路徑檢測(cè)

CK內(nèi)置的sequenceMatch和sequenceCount函數(shù)只能滿足部分需求音瓷，現(xiàn)有一個(gè)更復(fù)雜的需求：

給定期望的路徑終點(diǎn)对嚼、途經(jīng)點(diǎn)和最大事件時(shí)間間隔，查詢出符合條件的路徑詳情及符合路徑的用戶數(shù)（按用戶數(shù)降序排列）绳慎。

目前并沒有現(xiàn)成的函數(shù)可以直接出結(jié)果纵竖，但是我們可以曲線救國(guó)，用數(shù)組和高階函數(shù)的組合間接實(shí)現(xiàn)偷线。完整SQL語句如下磨确，略長(zhǎng)：

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;

簡(jiǎn)述思路：

1. 將用戶的行為用groupArray函數(shù)整理成<時(shí)間, <事件名, 頁面名>>的元組沽甥，并用arraySort函數(shù)按時(shí)間升序排序声邦；
2. 利用arrayEnumerate函數(shù)獲取原始行為鏈的下標(biāo)數(shù)組；
3. 利用arrayFilter和arrayDifference函數(shù)渤刃，過濾出原始行為鏈中的分界點(diǎn)下標(biāo)贾铝。分界點(diǎn)的條件是路徑終點(diǎn)或者時(shí)間差大于最大間隔禽拔；
4. 利用arrayMap和has函數(shù)獲取下標(biāo)數(shù)組的掩碼（由0和1組成的序列），用于最終切分媳瞪，1表示分界點(diǎn)；
5. 調(diào)用arraySplit函數(shù)將原始行為鏈按分界點(diǎn)切分成單次訪問的行為鏈照宝。注意該函數(shù)會(huì)將分界點(diǎn)作為新鏈的起始點(diǎn)蛇受，所以前面要將分界點(diǎn)的下標(biāo)加1；
6. 調(diào)用arrayJoin和arrayCompact函數(shù)將事件鏈的數(shù)組打平成多行單列厕鹃，并去除相鄰重復(fù)項(xiàng)兢仰。
7. 調(diào)用hasAll函數(shù)確定是否全部存在指定的途經(jīng)點(diǎn)。如果要求有任意一個(gè)途經(jīng)點(diǎn)存在即可剂碴，就換用hasAny函數(shù)把将。當(dāng)然，也可以修改WHERE謂詞來排除指定的途經(jīng)點(diǎn)忆矛。
8. 將最終結(jié)果整理成可讀的字符串察蹲，按行為鏈統(tǒng)計(jì)用戶基數(shù)，完成催训。

有序漏斗轉(zhuǎn)化

CK提供了windowFunnel函數(shù)實(shí)現(xiàn)漏斗洽议，以指定時(shí)長(zhǎng)（單位為秒）滑動(dòng)窗口按序匹配事件鏈，并返回在窗口內(nèi)轉(zhuǎn)化到的步數(shù)漫拭。如有多種匹配亚兄，以步數(shù)最大（轉(zhuǎn)換最深）的為準(zhǔn)。

通過對(duì)該步數(shù)進(jìn)行統(tǒng)計(jì)嫂侍，即可得到漏斗中每步的轉(zhuǎn)化率儿捧。SQL語句如下荚坞，查詢結(jié)果是敏感數(shù)據(jù)，不再貼出來了菲盾。

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
);

如果想要更準(zhǔn)確一些颓影，實(shí)現(xiàn)漏斗步驟之間的字段關(guān)聯(lián)（如商品詳情→加入購物車→下單三步中的商品ID關(guān)聯(lián)）怎么辦呢？可以利用housepower/olap2018項(xiàng)目中提出的xFunnel函數(shù)懒鉴。它是windowFunnel函數(shù)的鼻祖诡挂，不過需要修改ClickHouse源碼并重新編譯之，今后有時(shí)間的話會(huì)簡(jiǎn)單寫一下過程临谱。

用戶留存

retention函數(shù)可以方便地計(jì)算留存情況璃俗。該函數(shù)接受多個(gè)條件，以第一個(gè)條件的結(jié)果為基準(zhǔn)悉默，觀察后面的各個(gè)條件是否也滿足城豁，若滿足則置1，不滿足則置0抄课，最終返回0和1的數(shù)組唱星。通過統(tǒng)計(jì)1的數(shù)量，即可計(jì)算出留存率跟磨。

下面的SQL語句計(jì)算次日重復(fù)下單率與七日重復(fù)下單率（語義與留存相同）间聊。

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統(tǒng)計(jì)

Session，即"會(huì)話"抵拘，是指在指定的時(shí)間段內(nèi)在網(wǎng)站/H5/小程序/APP上發(fā)生的一系列用戶行為的集合哎榴。例如，一次會(huì)話可以包含多個(gè)頁面瀏覽僵蛛、交互事件等尚蝌。Session是具備時(shí)間屬性的，根據(jù)不同的切割規(guī)則墩瞳，可以生成不同長(zhǎng)度的Session驼壶。

可見，Session統(tǒng)計(jì)與上述智能路徑檢測(cè)的場(chǎng)景有相似之處喉酌，都需要尋找用戶行為鏈的邊界并進(jìn)行切割热凹。以下SQL語句以30分鐘為超時(shí)時(shí)間，按天統(tǒng)計(jì)所有用戶的Session總數(shù)（跨天的Session也會(huì)被切割）泪电。

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;

The End

民那周末快樂~