ClickHouse中如何实现严格连续事件序列匹配统计连续页面跳转
ClickHouse 严格连续事件序列匹配实现方案
核心逻辑
ClickHouse 原生的 sequenceMatch、windowFunnel 序列匹配函数,默认允许目标事件序列中间插入任意无关事件,无法直接满足「事件之间无插入、严格相邻」的匹配要求。这类场景无需强行套用内置序列函数,直接通过窗口函数对同一用户的事件按发生时序做相邻配对,逐对校验规则即可实现严格连续的统计需求。
实现步骤
- 按用户维度对事件依据
eventsequence(事件发生序号)做升序排序,通过lagInFrame窗口函数提取相邻事件的前后关联信息,为每个事件生成对应的「上一跳URL」「上一跳事件序号」字段。 - 逐对校验相邻事件的合规性:
- 首先判断相邻事件的序号差为1,从根源上排除中间插入其他事件的情况
- 再校验相邻两个事件的URL是否符合目标路径规则(示例中为同基础路径、查询参数存在差异的跳转)
- 统计所有满足校验规则的相邻配对总数,即为严格连续跳转的总次数。
示例场景参考实现
针对给出的事件序列,可直接运行如下SQL得到预期结果:
WITH user_event_flow AS ( SELECT 1 AS user_id, arrayJoin([ ('/someurl/page?a=1', 1), ('/someurl/page?a=2', 2), ('/someurl/page?a=3', 4), ('/someurl/page?a=4', 5), ('/someurl/page?a=4', 6) ]) AS url_seq, url_seq.1 AS url, url_seq.2 AS event_seq ) SELECT countIf( event_seq - prev_event_seq = 1 AND startsWith(url, '/someurl/page') AND startsWith(prev_url, '/someurl/page') ) AS valid_strict_jump_count FROM ( SELECT *, lagInFrame(url) OVER ( PARTITION BY user_id ORDER BY event_seq ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ) AS prev_url, lagInFrame(event_seq) OVER ( PARTITION BY user_id ORDER BY event_seq ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ) AS prev_event_seq FROM user_event_flow ) WHERE prev_event_seq IS NOT NULL;
结果对照
SQL执行后输出的有效跳转数为3,和预期场景完全匹配:
- 序号1→2的跳转:序号差为1,属于目标路径,计入统计
- 序号2→4的跳转:序号差为2,中间存在时序断点,不满足严格连续要求,不计入
- 序号4→5的跳转:序号差为1,属于目标路径,计入统计
- 序号5→6的跳转:序号差为1,属于目标路径,计入统计
扩展适配
如果需要匹配更长的严格连续序列(比如连续3个以上事件的无插入跳转),只需要叠加多层lagInFrame窗口函数,依次提取往前数2位、3位的事件信息,逐次校验相邻事件的序号差为1即可。这种方式的匹配规则完全可控,不存在内置序列函数默认的宽松匹配问题,适配精度要求高的统计场景。
内容的提问来源于stack exchange,提问作者redsquare
相关产品推荐
相关产品推荐

