10倍性能提升，一文读懂AnalyticDB秒级漏斗分析函数

业务挑战

营销域中的洞察分析/智能圈人/经营报表等场景是OLAP分析型数据库的重要应用场景，云原生数据仓库AnalyticDB MySQL在淘宝、饿了么、菜鸟、优酷、盒马等业务的营销场景有比较长时间的积累和沉淀，我们将通过一系列文章来介绍AnalyticDB MySQL在营销域数据产品中的落地与应用，本文主要介绍**“漏斗分析”**的实现与应用。

对于营销域的业务运营同学而言，“增长黑客”理论是一个耳熟能详的概念，运营同学一定希望当增长处于AARRR不同阶段时可以采取一定的措施和试验，来优化转化路径，挽回流失客户。这对数据产品的功能需求就是需要准确的计算出每个转化阶段的用户行为数据，也就是每个阶段的漏斗转化。另外，性能需求当然是越快越好了，毕竟谁也无法忍受前台UI一直处于loading状态，数据产品不能做到即席交互的话一定会遭受到很多吐槽和鄙视。

技术挑战

过去数据库产品本身通常不会关心某一个具体业务场景如何实现，通常只会提供标准SQL语义的能力，我们看一下在标准SQL语义下如何实现一个漏斗功能。假设我们有一份用户行为数据，包含“谁在什么地方做了什么”的全部信息，用户行为数据表user_behavior如下：

用户行为类型共有四种，它们分别是：

通常开发一款数据产品，我们会面对两种漏斗需求，第一种是放在数据报表首页给决策层看的，只关注每个事件的统计数据，实现起来也比较简单比如：

select
  event_type,
  count(distinct uid)
from
  user_behavior
where
  item_id = 3838928
  and ts >= 1511540732
  and ts <= 1512312625
group by
  event_type

这种漏斗只能展示事件的粗粒度统计信息，无法分析出事件前后的因果关系和行为路径，比如用户购买一个商品的路径可能是点击商品(pv)->添加购物车(cart)->购买(buy)， 也可能是点击商品(pv)->收藏(fav)->添加购物车(cart)->购买(buy)，我们可以统计出每个事件独立的用户数，但是有多少用户是直接购买？多少用户是先收藏再购买，无法表达出来，也就是对于每个用户的事件序列“pv,pv,fav,cart,buy,pv,pv,buy,pv,cart,buy”,我们需要找出每个用户是否满足某个连续事件序列比如“pv,fav,cart,buy”，通常在实际的业务需求中，更多的是需要满足非连续子序列比如“pv...fav...cart..buy”。如下图：

然而，遗憾的是，在数据库产品中通常不会提供这种提供子序列匹配功能的聚合函数，据笔者了解，一个可能的解法是通过字符串匹配函数来实现，首先将每个用户的事件序列转化成一个字符串，然后通过对每个字符串和目标序列的字符串做匹配。具体实现的SQL伪代码如下：

/*将 符 合 目 标 事 件 条 件 的 数 据 转 成 一 个 事 件 标 志 ， 比 如 e1 ， e2 ， e3*/
with t1 as (
  select
    uid,
    ts,
    case event_type when "pv" then "e1" 
    when "fav" then "e2" 
    when "cart" then "e3" 
    when "buy" then "e4" 
    else "ex" end as event_code
  from
    user_behavior
)
/*统 计 每 个 层 级 的 用 户 数*/
select
  level,
  count(distinct uid)
from
  (
    select
      uid,
      /*计 算 每 个 用 户 的 最 大 子 列*/
      case
      when event_lst like "%e1%e2%e3%e4%" then "level_4"
      when event_lst like "%e1%e2%e3%" then "level_3"
      when event_lst like "%e1%e2%" then "level_2"
      when event_lst like "%e1%" then "level_1"
      else "level_0" end as level
    from
      (
        /*将 用 户 的 事 件 聚 合 成 事 件 序 列*/
        SELECT
          uid,
          GROUP_CONCAT(
            event_code
            order by
              ts asc
          ) as event_lst
        from
          t1
        group by
          uid
      )
  )
  group by level

以上的实现涉及到几个性能瓶颈：

1. 组内排序聚合GROUP_CONCAT(event_code order by event_time asc)，由于真实业务场景中，存在干扰数据（比如刷单用户有很多异常事件），导致GROUP_CONCAT()计算量巨大，同时也会加剧后续（步骤2）中的计算处理负担。

2. 字符串模糊匹配 case event_lst like "%e1%e2%e3%e4%" then "level_4"，注意通常这一步会成为cpu的消耗大户，当匹配的层级大于5个之后会极大地影响查询性能。

3. 整个计算过程中的类型转换，排序，分组等操作也会极大降低执行效率。

另外，可以看到，这种实现虽然大致实现了功能，但是SQL异常复杂，还没有结合其他用户属性，比如用户标签表做关联查询等等，扩展能力很有限。

AnalyticDB MySQL优化方法

针对上述漏斗场景的痛点问题，AnalyticDB MySQL针对性的引入了window_funnel 函数，函数定义如下：

▶︎ 函数说明： 漏斗函数（window_funnel）可以搜索滑动时间窗口中的事件列表，并计算条件匹配的事件列表的最大长度。搜索事件列表，从第一个事件开始匹配，依次做最长、有序匹配，返回匹配的最大长度。一旦匹配失败，结束整个匹配。假设在窗口足够大的条件下：

● 条件事件为c1，c2，c3，而用户数据为c1，c2，c3，c4，最终匹配到c1，c2，c3，函数返回值为3。

● 条件事件为c1，c2，c3，而用户数据为c4，c3，c2，c1，最终匹配到c1，函数返回值为1。

● 条件事件为c1，c2，c3，而用户数据为c4，c3，最终没有匹配到事件，函数返回值为0。

▶︎ 函数语法 ： window_funnel (window, mode, timestamp, cond1, cond2, ..., condN)

▶︎ 参数说明

基于window_funnel函数，我们来实现漏斗计算逻辑，SQL如下：

select
  funnel_step,
  count(1)
from
  (
    /*直接计算每个用户满足的行为序列*/
    select
      uid,
      window_funnel(
        cast(86400000 /*语义上对齐标准SQL实现设置为1000天，实际业务可以结合需要灵活配置*/ as integer),
        "default",
        ts,
        event_type = 'pv',
        event_type = 'fav',
        event_type = 'cart',
        event_type = 'buy'
      ) as funnel_step
    from
      user_behavior
    group by
      uid
  )
group by
  funnel_step;

可以看到，相比使用标准SQL实现的方式：

1. AnalyticDB MySQL内置的window_funnel 函数将所有计算逻辑都封装到了一个聚合函数中，可以极大简化SQL逻辑，降低业务实现复杂程度，利于代码维护和扩展。

2. window_funnel 支持滑动窗口设置，统计在时间窗口内满足行为序列的用户，用户可以灵活设置窗口大小，而使用标准SQL的方式难以实现相同语义。

3. 同时，优化后的实现对user_behavior表只有一次group by，移除了分组，排序，聚合，类型转换，字符匹配等耗时操作，极大提升了计算性能。在相同实例，两种实现方式的性能(执行时间)对比如下：

注：

测试数据可以通过 ​​https://tianchi.aliyun.com/dataset/649​​下载

测试结果可以在 ​​https://www.aliyun.com/product/ApsaraDB/ads​​ 购买实例复现

测试实例规格为：C系列(高性能版)，4组work 96core

总结

本文描述了洞察分析-漏斗分析的场景，如何在AnalyticDB MySQL中使用window_funnel函数来实现漏斗计算的功能，通过上面的分析我们可以看到，相比于传统SQL的实现方式，window_funnel函数可以降低SQL复杂度，**有更丰富的滑动窗口语义，有更好的查询性能，查询性能不会随着漏斗层级的加深而变深，对于漏斗层级很深的场景有10倍以上**的性能提升，对最终用户而言无需等待，“立刻”就能得到要分析的结果。

欢迎通过功能文档进一步了解和使用：

​​https://help.aliyun.com/document_detail/603324.html​​

AnalyticDB MySQL新推出了湖仓版，目前已正式商用。对于低成本离线处理ETL有需求，同时又需要使用高性能在线分析支撑BI报表/交互式查询/APP应用的用户，欢迎进行试用申请。

文章转载自公众号：阿里云瑶池数据库