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

本文涉及的产品
阿里云百炼推荐规格 ADB PostgreSQL,4核16GB 100GB 1个月
云原生数据仓库AnalyticDB MySQL版,基础版 8ACU 100GB 1个月
简介: AnalyticDB MySQL秒级漏斗分析函数助力企业简单快速研判用户增长

业务挑战

营销域中的洞察分析/智能圈人/经营报表等场景是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,移除了分组,排序,聚合,类型转换,字符匹配等耗时操作,极大提升了计算性能。在相同实例,两种实现方式的性能(执行时间)对比如下:



*注:

测试数据可以通过点击「淘宝用户购物行为数据集」下载

测试结果可以在「云原生数据仓库AnalyticDB MySQL版」购买实例复现

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


总结

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


欢迎通过功能文档进一步了解和使用:「漏斗留存函数」


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


面向国内1000万云上开发者,阿里云推出“飞天免费试用计划”。AnalyticDB MySQL现推出免费试用3个月规格。点击「链接」即可申请免费试用,开启云上实践之旅!

相关实践学习
AnalyticDB MySQL海量数据秒级分析体验
快速上手AnalyticDB MySQL,玩转SQL开发等功能!本教程介绍如何在AnalyticDB MySQL中,一键加载内置数据集,并基于自动生成的查询脚本,运行复杂查询语句,秒级生成查询结果。
阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
云原生数据仓库AnalyticDB MySQL版是一种支持高并发低延时查询的新一代云原生数据仓库,高度兼容MySQL协议以及SQL:92、SQL:99、SQL:2003标准,可以对海量数据进行即时的多维分析透视和业务探索,快速构建企业云上数据仓库。 了解产品 https://www.aliyun.com/product/ApsaraDB/ads
相关文章
|
4月前
|
存储 运维 Kubernetes
实时数仓Hologres提升问题之调度性能如何解决
Hologres可以支持的最大节点规模是多少?
46 1
|
1月前
|
监控 数据挖掘 OLAP
深入解析:AnalyticDB中的高级查询优化与性能调优
【10月更文挑战第22天】 AnalyticDB(ADB)是阿里云推出的一款实时OLAP数据库服务,它能够处理大规模的数据分析任务,提供亚秒级的查询响应时间。对于已经熟悉AnalyticDB基本操作的用户来说,如何通过查询优化和性能调优来提高数据处理效率,是进一步提升系统性能的关键。本文将从个人的角度出发,结合实际经验,深入探讨AnalyticDB中的高级查询优化与性能调优技巧。
84 4
|
2月前
|
SQL 分布式计算 Serverless
EMR Serverless Spark:一站式全托管湖仓分析利器
本文根据2024云栖大会阿里云 EMR 团队负责人李钰(绝顶) 演讲实录整理而成
149 2
|
6月前
|
分布式计算 关系型数据库 数据挖掘
实时数仓 Hologres产品使用合集之当使用动态分区管理功能按日期进行分区后,通过主键和segment_key进行时间范围查询性能变差是什么原因
实时数仓Hologres的基本概念和特点:1.一站式实时数仓引擎:Hologres集成了数据仓库、在线分析处理(OLAP)和在线服务(Serving)能力于一体,适合实时数据分析和决策支持场景。2.兼容PostgreSQL协议:Hologres支持标准SQL(兼容PostgreSQL协议和语法),使得迁移和集成变得简单。3.海量数据处理能力:能够处理PB级数据的多维分析和即席查询,支持高并发低延迟查询。4.实时性:支持数据的实时写入、实时更新和实时分析,满足对数据新鲜度要求高的业务场景。5.与大数据生态集成:与MaxCompute、Flink、DataWorks等阿里云产品深度融合,提供离在线
|
2月前
|
存储 SQL 分布式计算
湖仓一体架构深度解析:构建企业级数据管理与分析的新基石
【10月更文挑战第7天】湖仓一体架构深度解析:构建企业级数据管理与分析的新基石
110 1
|
2月前
|
DataWorks 数据挖掘 关系型数据库
基于hologres搭建轻量OLAP分析平台解决方案评测
一文带你详细了解基于hologres搭建轻量OLAP分析平台解决方案的优与劣
338 8
|
1月前
|
SQL 监控 大数据
优化AnalyticDB性能:查询优化与资源管理
【10月更文挑战第25天】在大数据时代,实时分析和处理海量数据的能力成为了企业竞争力的重要组成部分。阿里云的AnalyticDB(ADB)是一款完全托管的实时数据仓库服务,支持PB级数据的秒级查询响应。作为一名已经有一定AnalyticDB使用经验的开发者,我发现通过合理的查询优化和资源管理可以显著提升ADB的性能。本文将从个人角度出发,分享我在实践中积累的经验,帮助读者更好地利用ADB的强大功能。
39 0
|
3月前
|
数据可视化 数据挖掘 OLAP
基于 Hologres 搭建轻量 OLAP 分析平台评测报告
【9月更文第6天】开作为互联网手游公司的产品经理和项目经理,数据分析对于我们的业务至关重要。我们一直在寻找高效、可靠的数据分析解决方案,以更好地了解玩家行为、优化游戏体验和提升运营效率。近期,我们体验并部署了《基于 Hologres 搭建轻量 OLAP 分析平台》解决方案,以下是我们对该方案的评测报告。
88 12
基于 Hologres 搭建轻量 OLAP 分析平台评测报告
|
1月前
|
数据采集 分布式计算 OLAP
最佳实践:AnalyticDB在企业级大数据分析中的应用案例
【10月更文挑战第22天】在数字化转型的大潮中,企业对数据的依赖程度越来越高。如何高效地处理和分析海量数据,从中提取有价值的洞察,成为企业竞争力的关键。作为阿里云推出的一款实时OLAP数据库服务,AnalyticDB(ADB)凭借其强大的数据处理能力和亚秒级的查询响应时间,已经在多个行业和业务场景中得到了广泛应用。本文将从个人的角度出发,分享多个成功案例,展示AnalyticDB如何助力企业在广告投放效果分析、用户行为追踪、财务报表生成等领域实现高效的数据处理与洞察发现。
54 0
|
4月前
|
OLAP
云端问道5期-基于Hologres轻量高性能OLAP分析陪跑班获奖名单公布啦!
云端问道5期-基于Hologres轻量高性能OLAP分析陪跑班获奖名单公布啦!
436 3

热门文章

最新文章

相关产品

  • 云原生数据仓库AnalyticDB MySQL版