窗口漏斗函数的基本语法和示例 - 日志服务

日志服务提供窗口漏斗函数，可用于分析用户行为、APP流量、产品目标转化等数据。本文介绍窗口漏斗函数的基本语法和示例。

日志服务支持如下窗口漏斗函数。

重要在日志服务分析语句中，表示字符串的字符必须使用单引号（''）包裹，无符号包裹或被双引号（""）包裹的字符表示字段名或列名。例如：'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，因此不计入结果。

窗口漏斗函数

语法

日志服务支持如下两种格式的窗口漏斗函数。

如果数据中已定义事件列（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类型。推荐使用日志服务内置的时间字段__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']

示例

某电商店铺举行了一次推广活动，现通过窗口漏斗函数分析本次推广活动的转化效果（转化路径为浏览商品、加入购物车、购买商品）。日志服务采集到的日志样例如下：

一条日志记录的字段示例：behavior_type:pv、category_id:149192、item_id:2073959、timestamp:1511860012、user_id:137617。

日志字段	说明
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，表示该用户浏览了商品以及将商品加入购物车，但是未购买。
查询结果共1000条记录，以user_id和levels两列表格呈现。

示例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
  )

查询和分析结果
- 表格结果以表格形式展示，包含 levels、count、total、绝对转化率、相对转化率 五列，共 4 行数据：level 0 的 count 为 2072，total 为 515266，绝对转化率 100.0；level 1 的 count 为 138491，total 为 513194，绝对转化率约 99.60；level 2 的 count 为 198642，total 为 374703，绝对转化率约 72.72；level 3 的 count 为 176061，total 为 176061，绝对转化率约 34.17，相对转化率约 46.99。
- 漏斗图