clickhouse 数据模型之有序漏斗分析(windowFunnel)

最新推荐文章于 2023-05-30 11:06:29 发布

小吴-斌

最新推荐文章于 2023-05-30 11:06:29 发布

阅读量3.5k

点赞数 1

分类专栏：大数据

本文链接：https://blog.csdn.net/qq_23564667/article/details/117115865

版权

大数据专栏收录该内容

7 篇文章

订阅专栏

这篇博客介绍了ClickHouse中的有序漏斗分析方法windowFunnel，它用于跟踪用户事件链并确保所有操作按时间顺序发生且无断层。windowFunnel函数计算在指定时间窗口内满足条件的最长事件链的长度。例如，在用户行为数据中，从'书城'到'支付'的完整流程被分析，展示用户转化路径的有效性。示例查询展示了如何使用windowFunnel计算不同用户完成漏斗步骤的数量。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

什么是有序漏斗，有序漏斗需要满足所有用户事件链上的操作都是逡巡时间先后关系的，且漏斗事件不能有断层，触达当前事件层的用户也需要经历前面的事件层

介绍

windowFunnel
搜索滑动时间窗中的事件链，并计算从链中发生的最大事件数。

该函数采用如下算法：

该函数搜索触发链中的第一个条件并将事件计数器设置为1。这是滑动窗口启动的时刻。

如果来自链的事件在窗口内顺序发生，则计数器将递增。如果事件序列中断，则计数器不会增加。

如果数据在不同的完成点具有多个事件链，则该函数将仅输出最长链的大小。

语法

windowFunnel(window, [mode])(timestamp, cond1, cond2, ..., condN)

参数

window — 滑动窗户的大小，单位是秒。
mode - 这是一个可选的参数。
‘strict’ - 当 ‘strict’ 设置时，windowFunnel()仅对唯一值应用匹配条件。
timestamp — 包含时间的列。数据类型支持：日期, 日期时间和其他无符号整数类型（请注意，即使时间戳支持 UInt64 类型，它的值不能超过Int64最大值，即2^63-1）。
cond — 事件链的约束条件。 UInt8 类型。

返回值

滑动时间窗口内连续触发条件链的最大数目。
对选择中的所有链进行了分析。

类型: Integer.

列子

-- 创建一张用户行为表， 
CREATE TABLE default.user_action
(
    `uid` Int32,
    `event_type` String,
    `ctime` DateTime 
)
ENGINE = MergeTree()
PARTITION BY uid
ORDER BY uid
SETTINGS index_granularity = 8192

-- 插入数据
INSERT INTO default.user_action 
VALUES
(1000,'书城',toDateTime('2020-05-20 11:00:00')),
(1000,'阅读',toDateTime('2020-05-20 12:00:00')),
(1000,'点击',toDateTime('2020-05-21 15:00:00')),
(1000,'下单',toDateTime('2020-05-22 01:00:00')),
(1000,'支付',toDateTime('2020-05-23 12:00:00')),
(1002,'书城',toDateTime('2020-05-20 11:00:00')),
(1002,'下单',toDateTime('2020-05-20 15:00:00')),
(1002,'支付',toDateTime('2020-05-20 18:00:00')),
(1003,'书城',toDateTime('2020-05-20 11:00:00')),
(1003,'阅读',toDateTime('2020-05-21 12:00:00')),
(1003,'点击',toDateTime('2020-05-21 16:00:00')),
(1004,'书城',toDateTime('2020-05-20 11:00:00')),
(1004,'阅读',toDateTime('2020-05-20 11:00:00')),
(1004,'点击',toDateTime('2020-05-20 16:00:00')),
(1005,'书城',toDateTime('2020-05-20 11:00:00')),
(1005,'阅读',toDateTime('2020-05-20 11:00:00')),
(1005,'点击',toDateTime('2020-05-20 15:00:00')),
(1005,'下单',toDateTime('2020-05-20 16:00:00')),
(1006,'书城',toDateTime('2020-05-20 11:00:00')),
(1006,'阅读',toDateTime('2020-05-20 09:00:00')),
(1006,'点击',toDateTime('2020-05-20 12:00:00')),
(1006,'下单',toDateTime('2020-05-20 13:00:00'));

-- 查询
 SELECT 
    uid,
    windowFunnel(6880000000)(ctime, event_type = '书城',event_type = '阅读', event_type = '点击', event_type = '下单', event_type = '支付') AS levelnum
FROM default.user_action  
GROUP BY uid
ORDER BY uid ASC

在这里插入图片描述

模型

继续使用上面的测试数据，通过数组的高阶函数对上述结果数据进行二次加工处理以获取完整漏斗展示效果。

SELECT level_index,count(1) FROM
(
    SELECT  uid, 
        arrayWithConstant(level, 1) levels, 
        arrayJoin(arrayEnumerate( levels )) level_index
      FROM (
         SELECT 
		    uid,
		    windowFunnel(6880000000)(ctime, event_type = '书城',event_type = '阅读', event_type = '点击', event_type = '下单', event_type = '支付') AS level
		FROM default.user_action  
		GROUP BY uid
		ORDER BY uid ASC  
    )
)
group by level_index
ORDER BY level_index

在这里插入图片描述