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

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

介绍

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