创李昱（
个【先打一波小广告】
阿里云AnalyticDB MySQL升级为湖仓一体架构，支持高吞吐离线处理和高性能在线分析，可无缝替换CDH/TDH/Databricks/Presto/Spark/Hive等。试用活动（5000ACU时+100GB存储）正在火热申请中，申请链接：https://free.aliyun.com/?searchKey=AnalyticDB%20MySQL，群号：33600023146
业务挑战
对于营销域的业务运营同学而言，“增长黑客”理论是一个耳熟能详的概念，运营同学一定希望当增长处于AARRR不同阶段时可以采取一定的措施和试验，来优化转化路径，挽回流失客户。这对数据产品的功能需求就是需要准确的计算出每个转化阶段的用户行为数据，也就是每个阶段的漏斗转化。另外，性能需求当然是越快越好了，毕竟谁也无法忍受前台UI一直处于loading状态，数据产品不能做到即席交互的话一定会遭受到很多吐槽和鄙视。
技术挑战
过去数据库产品本身通常不会关心某一个具体业务场景如何实现，通常只会提供标准SQL语义的能力，我们看一下在标准SQL语义下如何实现一个漏斗功能。假设我们有一份用户行为数据，包含“谁在什么地方做了什么”的全部信息，用户行为数据表user_behavior如下：
用户行为类型共有四种，它们分别是：
通常开发一款数据产品，我们会面对两种漏斗需求，第一种是放在数据报表首页给决策层看的，只关注每个事件的统计数据，实现起来也比较简单比如：
select event_type,count(distinct uid)from user_behavior where item_id =3838928and ts >=1511540732and ts <=1512312625groupby 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 orderby ts asc)as event_lst from t1 groupby uid ))groupby 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天，实际业务可以结合需要灵活配置*/asinteger),"default", ts, event_type ='pv', event_type ='fav', event_type ='cart', event_type ='buy')as funnel_step from user_behavior groupby uid )groupby 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倍以上的性能提升，对最终用户而言无需等待，“立刻”就能得到要分析的结果。
