視窗漏鬥函數的基本文法和樣本 - Simple Log Service

Log Service提供視窗漏鬥函數，可用於分析使用者行為、APP流量、產品目標轉化等資料。本文介紹視窗漏鬥函數的基本文法和樣本。

Log Service支援如下視窗漏鬥函數。

重要在Log Service分析語句中，表示字串的字元必須使用單引號（''）包裹，無符號包裹或被雙引號（""）包裹的字元表示欄位名或列名。例如：'status'表示字串status，status或"status"表示日誌欄位status。

函數名稱

文法

說明

支援SQL

支援SPL

window_funnel函數

window_funnel(sliding_window, timestamp, event_id, ARRAY[event_list01, event_list02...])

在滑動的時間視窗中搜尋事件鏈並計算事件鏈中發生的最大連續的事件數目。

如果資料中已定義事件列（event_id），可選擇該文法。

√

window_funnel(sliding_window, timestamp, ARRAY[event_id=event_list01, event_id=event_list02...])

在滑動的時間視窗中搜尋事件鏈並計算事件鏈中發生的最大連續的事件數目。

如果您想要自訂事件的非枚舉值，可選擇該文法，更具有靈活性。

√

原理

視窗漏鬥函數用於在滑動的時間視窗中搜尋事件鏈並計算事件鏈中發生的最大連續的事件數目。根據您定義的事件鏈，從第一個事件開始匹配，依次做有序最長的匹配，返回最大連續事件數目。

視窗漏鬥函數所採用的演算法的詳細說明如下：

從事件鏈中的第一個事件開始並將事件計算機設定為1，然後開啟滑動視窗。
在滑動視窗內，如果事件鏈中的後續事件按順序發生，則事件計數器依次遞增。
在滑動視窗內，如果事件序列中斷，則停止本輪搜尋，事件計數器不再增加，並開始新一輪的搜尋，直到搜尋結束。
結束搜尋時，如果存在多個搜尋結果，則函數將返回最大值，即最長事件鏈的大小。

例如：您定義的滑動時間視窗為100秒，事件鏈及其順序為事件1->事件2->事件3->事件4->事件5，實際發生的事件序列為事件1->事件2->事件4->事件5->事件1->事件3，則函數傳回值為2，即表示在100秒內按照您定義的事件鏈發生的最大連續事件數目為2（事件1->事件2）。

重要

搜尋必須從第一個事件開始。例如實際發生的事件序列為事件2->事件3->事件4，則函數傳回值為0。
搜尋必須有序，不能跳過事件鏈中的某個事件。例如事件4也在滑動視窗內，但是未發生事件3，因此不計入結果。

視窗漏鬥函數

文法

Log Service支援如下兩種格式的視窗漏鬥函數。

如果資料中已定義事件列（event_id），可選擇如下文法。

window_funnel(sliding_window, timestamp, event_id, ARRAY[event_list01, event_list02...])

如果您想要自訂事件的非枚舉值，可選擇如下文法，更具有靈活性。

window_funnel(sliding_window, timestamp, ARRAY[event_id=event_list01, event_id=event_list02...])

參數說明

參數	說明
sliding_window	滑動的時間視窗，單位為秒。參數值為bigint類型。
timestamp	時間戳記，單位為秒。參數值為bigint類型。推薦使用Log Service內建的時間欄位__time__。
event_id	日誌欄位名，該欄位值表示事件，例如：A、B、C。參數值為varchar類型。
event_list	自訂的事件鏈，最多支援32個事件。參數值為array類型。例如： ARRAY['A', 'B', 'C'] ARRAY[event_id='A', event_id='B', event_id='C']

樣本

某電商店鋪舉行了一次推廣活動，現通過視窗漏鬥函數分析本次推廣活動的轉化效果（轉化路徑為瀏覽商品、加入購物車、購買商品）。Log Service採集到的日誌範例如下：

漏鬥函數

日誌欄位	說明
behavior_type	使用者行為類型。包括 pv：瀏覽商品。 cart：將商品加入購物車。 buy：購買商品。
category_id	商品類目ID。
item_id	商品ID。
timestamp	使用者行為發生的時間點。
user_id	使用者ID。

樣本1

分析使用者在24小時內的購買行為。

查詢和分析語句

* |
SELECT
  user_id,
  window_funnel(
    86400,
    timestamp,
    ARRAY [behavior_type='pv', behavior_type='cart',behavior_type='buy']
  ) AS levels
GROUP BY
  user_id
ORDER BY
  user_id
LIMIT
  1000

查詢和分析結果
- 例如使用者24的levels值為3，表示該使用者按照瀏覽商品、將商品加入購物車、購買商品的順序，完成了購買。
- 例如使用者14的levels值為2，表示該使用者瀏覽了商品以及將商品加入購物車，但是未購買。

樣本2

分析不同使用者行動對應的人數。

查詢和分析語句

* |
SELECT
  levels,
  count,
  sum(count) over(
    ORDER BY
      levels DESC
  ) AS total
FROM  (
    SELECT
      levels,
      count(1) AS count
    FROM      (
        SELECT
          user_id,
          window_funnel(
            86400,
            timestamp,
            ARRAY [behavior_type='pv', behavior_type='cart',behavior_type='buy']
          ) AS levels
        FROM          log
        GROUP BY
          user_id
      )
    GROUP BY
      levels
    ORDER BY
      levels
  )

查詢和分析結果
- 瀏覽了商品的人數為513194，其中瀏覽商品後就離開的人數為138491。
- 將商品加入購物車的人數為374703，其中加入購物車後離開的人數為198642人。
- 購買商品的人數為176061。

樣本3

計算本次推廣活動的轉化率。

絕對轉化率：每步使用者行為的人數佔總人數的比例。
相對轉化率：每步使用者行為的人數占上一步人數的比例。

查詢和分析語句

* |
SELECT
  *,
  100.0 * total /(sum(count) over()) AS "絕對轉化率",
  if(
    lag(total, 1, 0) over() = 0,
    100,
    (100.0 * total / lag(total, 1, 0) over())
  ) AS "相對轉化率"
FROM  (
    SELECT
      levels,
      count,
      sum(count) over(
        ORDER BY
          levels DESC
      ) AS total
    FROM      (
        SELECT
          levels,
          count(1) AS count
        FROM          (
            SELECT
              user_id,
              window_funnel(
                86400,
                timestamp,
                ARRAY [behavior_type='pv', behavior_type='cart',behavior_type='buy']
              ) AS levels
            FROM              log
            GROUP BY
              user_id
          )
        GROUP BY
          levels
      )
    ORDER BY
      levels
  )

查詢和分析結果
- 表格
- 漏斗圖