漏斗分析和留存分析是常见的转化分析方法，被广泛应用于用户行为分析和App数据分析的流量分析、产品目标转化等数据运营与数据分析。本文将为您介绍Hologres支持的漏斗函数和留存函数，并详细说明它们的使用限制、功能介绍以及使用示例。
背景信息
事件（Event）代表了用户的某个或一系列有意义的行为，比如游戏App的下载、注册、登录等，通过分析用户的各项行为数据还原用户真实的使用过程，从而提升产品转化率，助力业务增长。常见的用户行为分析包括事件分析、漏斗分析、留存分析等，其中漏斗和留存是最常见的用户行为分析场景：
漏斗分析：是一种用来分析用户在指定阶段转化情况的分析模型，可以分析用户在各个阶段的行为转化率，然后通过转化率来衡量每一个阶段的表现，从而助力产品针对性优化体验，提升转化率。
留存分析：是一种用来分析用户参与情况或活跃程度的分析模型，考察初始行为的用户中有多少用户会进行后续行为，从而衡量产品对用户的价值。
Hologres是阿里云自研的一站式实时数仓，支持多种场景的实时数据多维分析。在用户行为分析场景上，Hologres提供多种漏斗和留存函数，快速高效的帮助业务进行用户行为分析，被广泛应用在互联网、电商、游戏等行业客户的用户分析场景中。
使用限制
仅Hologres V0.9 及以上版本支持漏斗函数（windowFunnel）和留存函数（retention）。
仅Hologres V0.10 及以上版本支持range_retention_count和range_retention_sum函数。
仅Hologres V2.1及以上版本支持区间漏斗函数（range_funnel）。
漏斗和留存等函数都需要Superuser执行以下语句开启extension。extension是DB级别的函数，一个DB只需执行一次即可。
create extension flow_analysis; --开启extension
前置场景说明
本文中的示例全部基于GitHub公开事件数据集，以此为您介绍漏斗和留存分析相关的函数用法。
数据集介绍
大量开发人员在GitHub上进行开源项目的开发工作，并在项目的开发过程中产生海量事件。GitHub会记录每次事件的类型及详情、开发者、代码仓库等信息，并开放其中的公开事件，包括加星标、提交代码等，具体事件类型请参见Webhook events and payloads。
数据集数据导入
通过Hologres将github_event数据集一键导入至Hologres实例，详情请参见一键导入公共数据集。
GitHub公开事件的建表语句如下：
BEGIN;
CREATE TABLE hologres_dataset_github_event.hologres_github_event (
id bigint,
actor_id bigint,
actor_login text,
repo_id bigint,
repo_name text,
org_id bigint,
org_login text,
type text,
created_at timestamp with time zone NOT NULL,
action text,
iss_or_pr_id bigint,
number bigint,
comment_id bigint,
commit_id text,
member_id bigint,
rev_or_push_or_rel_id bigint,
ref text,
ref_type text,
state text,
author_association text,
language text,
merged boolean,
merged_at timestamp with time zone,
additions bigint,
deletions bigint,
changed_files bigint,
push_size bigint,
push_distinct_size bigint,
hr text,
month text,
year text,
ds text
);
CALL set_table_property('hologres_dataset_github_event.hologres_github_event', 'orientation', 'column');
CALL set_table_property('hologres_dataset_github_event.hologres_github_event', 'bitmap_columns', 'actor_login,repo_name,org_login,type,action,commit_id,ref,ref_type,state,author_association,language,hr,month,year,ds');
CALL set_table_property('hologres_dataset_github_event.hologres_github_event', 'clustering_key', 'created_at:asc');
CALL set_table_property('hologres_dataset_github_event.hologres_github_event', 'dictionary_encoding_columns', 'actor_login:auto,repo_name:auto,org_login:auto,type:auto,action:auto,commit_id:auto,ref:auto,ref_type:auto,state:auto,author_association:auto,language:auto,hr:auto,month:auto,year:auto,ds:auto');
CALL set_table_property('hologres_dataset_github_event.hologres_github_event', 'distribution_key', 'id');
CALL set_table_property('hologres_dataset_github_event.hologres_github_event', 'segment_key', 'created_at');
CALL set_table_property('hologres_dataset_github_event.hologres_github_event', 'time_to_live_in_seconds', '3153600000');
COMMENT ON TABLE hologres_dataset_github_event.hologres_github_event IS NULL;
ALTER TABLE hologres_dataset_github_event.hologres_github_event OWNER TO test1_developer;
END;漏斗函数（windowFunnel）
漏斗分析是用来分析用户在指定阶段转化情况的一种分析模型，可以分析用户在各个阶段的行为转化率，帮助管理者或运营等角色通过转化率来衡量每个阶段的转化情况，从而达到优化产品，提升转化率的目的。
函数说明
漏斗函数（windowFunnel）可以搜索滑动时间窗口中的事件列表，并计算条件匹配的事件列表的最大长度。
windowFunnel会从第一个事件开始匹配，依次做最长、有序匹配，返回匹配的最大长度。一旦匹配失败，结束整个匹配。
假设在窗口足够大的条件下：
条件事件为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。
函数语法
windowFunnel(window, mode, timestamp, cond1, cond2, ..., condN)参数说明
参数
说明
window
统计的窗口时间大小，即从指定的第一个事件开始的时间，根据窗口时间提取后续的相关事件数据。
mode
模式。支持default和strict两种模式 ，默认为default。
default：在同一个窗口期内，从第一个事件开始匹配，尽量匹配多的事件。例如条件事件为c1，c2，c3，而用户数据为c1，c2，c1，c3，则返回3。
strict：
windowFunnel()仅对唯一值应用匹配条件，即遇到相同的事件就停止继续匹配。例如条件事件为c1，c2，c3，而用户数据为c1，c2，c1，c3，则返回2。
timestamp
事件发生的时间范围，支持timestamp、int、bigint类型。
cond
事件类型，代表事件的每个步骤。
使用示例
以前置场景中的GitHub公开数据集分析为例，分析一段时间内，用户按照固定转化路径的漏斗情况，使用如下SQL进行分析，SQL中的各个条件如下：
统计间隔：30分钟（即1800秒）
统计时间段：2023-07-28 10:00:00+08至2023-07-31 10:00:00+08
转化路径：共3个事件，依次发生的步骤为：CreateEvent>PushEvent>IssuesEvent
--计算每个用户的漏斗情况 SELECT actor_id, windowFunnel (1800, 'default', created_at, type = 'CreateEvent',type = 'PushEvent',type = 'IssuesEvent') AS level FROM hologres_dataset_github_event.hologres_github_event WHERE created_at >= TIMESTAMP '2023-07-28 10:00:00+08' AND created_at < TIMESTAMP '2023-07-31 10:00:00+08' GROUP BY actor_id部分显示结果如下，其中：
level=0表示用户在窗口期内没有匹配到第一个事件。
level=1表示用户在窗口期内匹配到第一个事件。
level=2表示用户在窗口期内匹配到第二个事件。
level=3表示用户在窗口期内三个事件都匹配到。
actor_id | level ----------+------ 69438111 | 0 62698183 | 0 139085682 | 1 1765075 | 1 132836353 | 2 122081842 | 2 89815869 | 3为了提高结果的阅读性，您可以通过如下SQL查看每一个步骤分别转化的用户数。
WITH level_detail AS ( SELECT level, COUNT(1) AS count_user FROM ( SELECT actor_id, windowFunnel (1800, 'default', created_at, type = 'CreateEvent', type = 'PushEvent',type = 'IssuesEvent') AS level FROM hologres_dataset_github_event.hologres_github_event WHERE created_at >= TIMESTAMP '2023-07-28 10:00:00+08' AND created_at < TIMESTAMP '2023-07-31 10:00:00+08' GROUP BY actor_id) AS basic_table GROUP BY level ORDER BY level ASC ) SELECT CASE level WHEN 0 THEN 'total' WHEN 1 THEN 'CreateEvent' WHEN 2 THEN 'PushEvent' WHEN 3 THEN 'IssuesEvent' END ,SUM(count_user) over ( ORDER BY level DESC ) FROM level_detail GROUP BY level, count_user ORDER BY level ASC;结果如下：
case | sum ------------+------ total | 864120 CreateEvent | 275053 PushEvent | 120242 IssuesEvent | 2652
区间漏斗函数（range_funnel）
业务在做漏斗分析时，通常会进行分组统计，比如按照天、小时等自定义时间窗口统计。为了更好的满足业务需求，Hologres从V2.1版本开始，在漏斗函数（windowFunnel）的基础上，额外扩展了一个区间漏斗函数（range_funnel）。
函数说明
该函数用于计算时间窗口的分组统计结果及聚合计算总结果，匹配逻辑如下：
如果条件事件为c1，c2，c3，而用户数据为c1，c2，c1，c3，则返回3。
如果有相同事件如c1，c1，c1，用户数据为c1，c2，c1，c3，则返回2。
windowFunnel函数与range_funnel函数的区别如下：
windowFunnel函数只能对输入的事件数据做一次聚合计算，结果为完整的时间区间。range_funnel函数不仅支持输出总的聚合计算结果，还能输出自定义时间窗口的分组统计结果，结果为一个数组。
windowFunnel函数不支持提取多个相同事件，而range_funnel函数支持提取多个相同事件。
函数语法
range_funnel(window, event_size, range_begin, range_end, interval, event_ts, event_bits)参数说明
参数
类型
说明
window
interval
统计的窗口时间大小，从第一个事件开始，往后推移一个窗口的区间内的事件。单位：秒。
如果window=0，会根据每个区间的起止位置截断，如果正好是每天0点，就变成了自然日。
event_size
int
要分析的事件总数。
range_begin
timestamptz/timestamp/date
分析时段的起始时间，以第一个事件开始计算。
range_end
timestamptz/timestamp/date
分析时段的终止时间，以第一个事件开始计算。
interval
interval
分析时段会以interval为粒度切分成多个连续的区间，分别进行漏斗分析并产出结果。单位：秒。
event_ts
timestamp/timestamptz
事件的发生时间字段。支持timestamp、timestamptz类型。
说明该参数从零点开始计算，因此可能会与真实时间存在一定的时差，通常用于观察天和周的趋势。
event_bits
bitmap
事件类型字段。以int32类型的bitmap表示，从低位至高位按顺序依次代表一个事件。因此，最多支持32个事件的漏斗分析。
返回结果
range_funnel输出一个int64类型的数组，bigint[]。需要注意数组的结果是一个编码值，该值按区间展示，由区间的起始时间（56bit）和提取到的事件数（8bit）两部分拼接组成，所以输出结果之后还需要对数组的内容进行解码，才能得到最终的匹配数据。
使用示例
以前置场景中的GitHub公开数据集为例，分析一段时间内，用户按照固定转化路径的漏斗情况，并且按天分组展示。使用如下SQL进行分析，SQL中的各条件如下：
统计时间间隔：1小时（3600秒）
统计时间段：2023-07-23至2023-07-25号，共计3天
转化路径：共2个事件，依次发生步骤为：CreateEvent>PushEvent
分组时间：1天（86400秒），即按天分组展示每一天的漏斗情况
由于事件类型type字段的类型为text，而range_funnel函数中事件类型event_bits字段的类型必须为32位的bitmap，因此使用bit_construct函数将事件类型转换为bitmap
--这是没有解码的结果 SELECT actor_id, range_funnel (3600, 2, '2023-07-23', '2023-07-25', 86400, created_at::timestamp, bits) AS result FROM ( SELECT actor_id, created_at::timestamp, type, bit_construct (a := type = 'CreateEvent', b := type = 'PushEvent') AS bits FROM hologres_dataset_github_event.hologres_github_event where ds >= '2023-07-23' AND ds <='2023-07-25') tt group by actor_id order by actor_id ;部分查询结果如下：
actor_id | result ----------+------ 17 |{432658022400,432680140800,432702259202,9223372036854775554} 47 |{432658022402,432680140800,432702259200,9223372036854775554} 235 |{432658022401,432680140800,432702259200,9223372036854775553}其中result字段说明如下：
结果为空代表该用户没有匹配到任何时间。
结果中包括总的漏斗结果，以及按天分组展示的漏斗结果，是一个没有解码的数组数据。
区间漏斗解码函数
区间漏斗函数的结果是经过编码的，结果可阅读性不强，需要用SQL进行解码，为了对结果做更方便的解码，从Hologres 2.1.6版本开始，支持range_funnel_time和range_funnel_level两个函数对区间漏斗函数解码。
函数说明
range_funnel_time：对区间漏斗函数（range_funnel）返回int64结果中的事件时间进行解码。range_funnel_level：对区间漏斗函数（range_funnel）返回int64结果中的事件等级进行解码。
函数语法
range_funnel_time(range_funnel()) range_funnel_level(range_funnel())参数说明
range_funnel()：range_funnel()函数输出的int64结果。使用示例
为了提高结果的阅读性，使用
range_funnel_time和range_funnel_level函数对区间漏斗函数示例中的result结果进行解码。使用如下SQL：SELECT actor_id, to_timestamp(range_funnel_time(result)) AS res_time, --时间解码 range_funnel_level(result) AS res_level --事件level解码 FROM ( SELECT actor_id, result, count(1) AS cnt FROM ( SELECT actor_id, unnest(range_funnel (3600, 2, '2023-07-23', '2023-07-26', 86400, created_at::timestamp, bits)) as result from ( SELECT actor_id, created_at::timestamp, type, bit_construct (a := type = 'CreateEvent', b := type = 'PushEvent') AS bits from hologres_dataset_github_event.hologres_github_event where ds >= '2023-07-23' AND ds <='2023-07-25' ) a GROUP BY actor_id ) a GROUP BY actor_id ,result ) a ORDER BY actor_id ,res_time LIMIT 10000;部分查询结果如下所示，由此可知每个用户每天匹配的level和次数：
actor_id | res_time | res_level ----------+---------------------+----------- 17 |2023-07-23 08:00:00 | 0 17 |2023-07-24 08:00:00 | 0 17 |2023-07-24 08:00:00 | 2 17 |\N | 0 47 |2023-07-23 08:00:00 | 0 47 |2023-07-24 08:00:00 | 0 47 |2023-07-25 08:00:00 | 2 47 |\N | 2 235 |2023-07-23 08:00:00 | 1 235 |2023-07-24 08:00:00 | 0 235 |2023-07-25 08:00:00 | 0 235 |\N | 1查出每个用户的分天漏斗结果后，您也可以根据业务需求做进一步的数据探查。
示例：查看每天步长汇总以及总的汇总数据，其中下一个level一定包含上一个level。
SELECT res_time, res_level, sum(cnt) over (partition by res_time ORDER BY res_level desc) AS res_cnt FROM ( SELECT to_timestamp(range_funnel_time(result)) AS res_time, --时间解码 range_funnel_level(result) AS res_level, --事件level解码 cnt FROM ( SELECT result, count(1) AS cnt FROM ( SELECT actor_id, unnest(range_funnel (3600, 2, '2023-07-23', '2023-07-26', 86400, created_at::timestamp, bits)) AS result FROM ( SELECT actor_id, created_at::timestamp, type, bit_construct (a := type = 'CreateEvent', b := type = 'PushEvent') AS bits FROM hologres_dataset_github_event.hologres_github_event WHERE ds >= '2023-07-23' AND ds <='2023-07-25' ) a GROUP BY actor_id ) a GROUP BY result ) a )a WHERE res_level > 0 GROUP BY res_time, res_level, cnt ORDER BY res_time, res_level;查询结果如下，其中：
结果中
\N：代表多天汇总的结果。cnt字段包括每个level的汇总数据，下一个level一定包含上一个level的数据。例如res_level为2，cnt为1，代表经历过步骤1，步骤2的用户数只有1个。
res_time |res_level | cnt --------------------+-----------+------ 2023-07-23 08:00:00 |1 |114455 2023-07-23 08:00:00 |2 |56155 2023-07-24 08:00:00 |1 |154616 2023-07-24 08:00:00 |2 |71200 2023-07-25 08:00:00 |1 |178662 2023-07-25 08:00:00 |2 |81779 \N |1 |390262 \N |2 |188125
留存函数（retention）
留存分析用于分析用户的整体参与程度、活跃程度等，常用来分析一段时间内的用户保留率，从而辅助业务做进一步的产品决策。
函数说明
该函数将一组条件作为参数，类型为1到32个UInt8类型的参数，用来表示事件是否满足特定条件。
函数语法
retention(cond1, cond2, ..., cond32);参数说明
参数
说明
cond
返回结果的表达式。返回值包括：
1，条件满足。
0，条件不满足。
使用示例
示例1：使用GitHub公开数据集，分析某一天开始，在PushEvent事件上，用户的留存情况，SQL语句如下：
SELECT sum(r[1]) as first_day, sum(r[2]) as second_day, sum(r[3]) as third_day, sum(r[4]) as fourth_day, sum(r[5]) as fifth_day, sum(r[6]) as sixth_day, sum(r[7]) as seventh_day FROM ( SELECT retention (ds = '2023-07-23' AND type = 'PushEvent',ds = '2023-07-24' AND type = 'PushEvent', ds = '2023-07-25'AND type = 'PushEvent', ds = '2023-07-26' AND type = 'PushEvent', ds = '2023-07-27'AND type = 'PushEvent' , ds = '2023-07-28'AND type = 'PushEvent', ds = '2023-07-29'AND type = 'PushEvent') AS r FROM hologres_dataset_github_event.hologres_github_event GROUP BY actor_id) tt;结果如下：
first_day |second_day | third_day | fourth_day | fifth_day |sixth_day |seventh_day ----------+-----------+-----------+------------+------------+-----------+----------- 250678 |105351 |93219 |81071 |81474 |76264 |23319示例2：分析每个用户近7天的留存情况。
--每个用户的留存情况 SELECT actor_id, retention( DATE(created_at) = DATE(TIMESTAMP '2023-07-23 10:00:00+08') , DATE(created_at) = DATE(TIMESTAMP '2023-07-23 10:00:00+08' + INTERVAL '1 day'), DATE(created_at) = DATE(TIMESTAMP '2023-07-23 10:00:00+08' + INTERVAL '2 day'), DATE(created_at) = DATE(TIMESTAMP '2023-07-23 10:00:00+08' + INTERVAL '6 day') ) AS r FROM hologres_dataset_github_event.hologres_github_event WHERE (created_at >= TIMESTAMP '2023-07-23 10:00:00+08') AND (created_at <= TIMESTAMP '2023-07-23 10:00:00+08' + INTERVAL '6 day') GROUP BY actor_id结果如下：
actor_id | r ----------+------- 122588631 |{1,1,1,0} 909706 |{1,0,1,0} 1893117 |{0,0,0,0} 56136246 |{1,0,1,0} 33266253 |{0,0,0,0} 106332614 |{0,0,0,0}
留存扩展函数
函数说明
留存分析是最常见的典型用户增长分析场景，用户经常需要绘制数据可视化图形，分析用户的留存情况。
基于该场景，Hologres构造了range_retention_count和range_retention_sum两个函数用于服务该场景。range_retention_count：用于记录每个用户的留存情况，返回值为bigint数组。不支持直接读取，但可以作为range_retention_sum的输入。
range_retention_sum：用于汇总计算所有用户每天的留存率。返回值为text数组。
函数语法
range_retention_count
range_retention_count(is_first, is_active, dt, retention_interval, retention_granularity,output_format)range_retention_sum
range_retention_sum(range_retention_count)
参数说明
参数
类型
说明
is_first
boolean
是否符合初始行为。
true：符合初始行为。
false：不符合初始行为。
is_active
boolean
是否符合后续留存行为。
true：符合后续留存行为。
false：不符合后续留存行为。
dt
date
发生行为日期。如2020-12-12
retention_interval
int[]
留存间隔，最多支持15个留存间隔。如ARRAY[1,3,5,7,15,30]
retention_granularity
text
留存粒度，支持如下三种：
day
week
month
output_format
text
输出格式，支持如下两种：
normal（默认）
expand
说明Hologres V1.1.38及以上版本支持此参数。
expand可取得每日留存明细。
使用示例
以GitHub公开数据集为例，分析用户的留存情况。
示例1：计算每个用户的留存情况，SQL语句如下：
SELECT actor_id, r FROM ( SELECT actor_id, range_retention_count(type = 'CreateEvent', type = 'PushEvent', ds::date, array[1, 2],'day','normal') AS r FROM hologres_dataset_github_event.hologres_github_event GROUP BY actor_id ) AS t ORDER BY actor_id;部分查询结果如下：
actor_id | r ----------+------- 771 |{84022445211651} 772 |{84022445211651,84026740178947,84031035146243,84048215015424} 777 |{} 797 |{84013855277059,84018150244353,84022445211648,84035330113536}
示例2：计算近7天的留存率，SQL语句如下：
WITH tbl_detail AS ( SELECT range_retention_count(is_first, is_active, dt, ARRAY[1, 3, 7],'day' ) AS detail FROM ( SELECT actor_id, created_at::DATE AS dt , CASE WHEN created_at >= timestamp '2023-07-23 00:00:00' AND created_at < timestamp '2023-07-23 00:00:00' + INTERVAL '7' day and type = 'CreateEvent' THEN true ELSE false END AS is_first , CASE WHEN created_at >= timestamp '2023-07-23 00:00:00' + INTERVAL '1' day AND created_at < timestamp '2023-07-23 00:00:00' + INTERVAL '7' day + INTERVAL '7' day THEN true ELSE false END AS is_active FROM hologres_dataset_github_event.hologres_github_event ) tbl GROUP BY actor_id ), tbl_sum AS ( SELECT regexp_split_to_array(unnest(range_retention_sum(detail)), ',') AS s FROM tbl_detail ) SELECT s[1] AS 访问日期 ,s[3]::numeric / s[2]::numeric AS 第1天 ,s[4]::numeric / s[2]::numeric AS 第3天 ,s[5]::numeric / s[2]::numeric AS 第7天 FROM tbl_sum ORDER BY s[1];查询结果如下：
访问日期 | 第1天 |第3天 | 第7天 ----------+-------------+--------------+--------- 20230723 |0.3656546240 |0.2603730723 |0.0000000000 20230724 |0.4076098204 |0.3123156723 |0.2030384953 20230725 |0.3930270566 |0.3154280149 |0.0000000000 20230726 |0.4275885111 |0.1000445494 |0.0000000000 20230727 |0.4043748374 |0.0000000000 |0.0000000000 20230728 |0.1463595703 |0.2279064106 |0.0000000000 20230729 |0.0000000000 |0.0000000000 |0.0000000000
- 本页导读 （1）