clickhouse Parametric Aggregate Functions的使用

sequenceMatch

   sequenceMatch(pattern)(timestamp, cond1, cond2, …)

  检查序列是否包含与模式匹配的事件链

    参数

pattern 模式字符串  

• (?N) — 在位置N匹配条件参数。 条件在编号 [1, 32] 范围。 例如, (?1) 匹配传递给 cond1 参数。

• .* — 匹配任何事件的数字。 不需要条件参数来匹配这个模式。

• (?t operator value) — 分开两个事件的时间。 例如： (?1)(?t>1800)(?2) 匹配彼此发生超过1800秒的事件。 这些事件之间可以存在任意数量的任何事件。 您可以使用 >=, >, <, <= 运算符。

timestamp  包含时间的列。典型的时间类型是： Date 和 DateTime。您还可以使用任何支持的 UInt 数据类型。

cond1, cond2 — 事件链的约束条件。 数据类型是： UInt8。 最多可以传递32个条件参数。 该函数只考虑这些条件中描述的事件。 如果序列包含未在条件中描述的数据，则函数将跳过这些数据。

返回值 UInt8 

   1|0  -> 模式匹配 |模式不匹配

  案例

-- 建表语句
create  table action_log(
    id   UInt16,
    ref_date  DateTime,
    action  String
)engine=TinyLog()
-- 插入数据
insert into action_log values (1,toDateTime('2021-03-19 14:00:00'),'浏览'),(1,toDateTime('2021-03-19 14:03:00'),'收藏'),(1,toDateTime('2021-03-19 14:02:00'),'支付');

查询是否存在浏览之后支付记录，      sequenceMatch函数中又4个参数，1为匹配表达式，2为时间字段，3为条件1  4为条件2

SELECT sequenceMatch('(?1)(?2)')(ref_date , action = '浏览', action = '支付') as cnt  FROM action_log 

 看了官方文档发现这个函数有个地方需要理解一下， 

SELECT sequenceMatch('(?1)(?2)')(ref_date , action = '浏览',action='收藏', action = '支付')   FROM action_log 

当前结果返回的为0，事件根据时间执行的顺序如下 浏览->支付->收藏，  理论上浏览->收藏 条件成立

我的理解：， 查询语句中sequenceMatch，会根据条件去筛选记录，根据时间字段，对结果进行排序，之后再根据匹配模式进行匹配， 由于浏览和收藏之间存在支付，(?1)(?2)  条件1和条件2 没有连续，导致结果不正确

如何验证逻辑是否正确呢，2个方案 

1.   将支付条件替换为，查询不到的记录，这个结果如果为1 代表 ，则可以说明支付条件再上面语句中确实有过滤作用，导致1，2条件不连续
2. 匹配模式中加.*代表  条件1 和条件2 中可存在任何原始

SELECT sequenceMatch('(?1)(?2)')(ref_date , action = '浏览',action='收藏', action = '付款')   FROM action_log 

SELECT sequenceMatch('(?1).*(?2)')(ref_date , action = '浏览',action='收藏', action = '支付')   FROM action_log 

sequenceCount

sequenceCount(pattern)(time, cond1, cond2, …)

参数和sequenceMatch是一样的，返回值不一样，sequenceMatch查询到有结果就返回，而sequenceCount则是统计匹配模式匹配的次数

insert into action_log values (2,toDateTime('2021-03-19 14:00:00'),'浏览'),(2,toDateTime('2021-03-19 14:04:00'),'支付'),(2,toDateTime('2021-03-19 14:05:00'),'收藏');

查询浏览->支付的事件 分用户合计次数，如果要算用户个数可以用sequenceMatch

select sum(cnt) from (
SELECT sequenceCount('(?1)(?2)')(ref_date , action = '浏览',action='支付') as cnt   FROM action_log 
group by id 
) a

windowFunnel

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

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

该函数采用如下算法：

• 该函数搜索触发链中的第一个条件并将事件计数器设置为1。 事件执行顺序是按照参数顺序，会依次往下匹配。

• 窗口时间是整个窗口的时间，

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

参数

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

返回值

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

类型: UInt64

案例

 直接复用上述表和数据， 查询300s内 事件顺序为浏览->支付->收藏  

SELECT  windowFunnel(300)(ref_date,action = '浏览',action='支付',action='收藏') from action_log al where id=2

Retention

该函数将一组条件作为参数，条件成立返回1，不成立返回0，前提：条件1必须成立，后面的条件才会成立，除条件1 后面条件

retention(cond1, cond2, ..., cond32);

参数

• cond — 返回 UInt8 结果（1或0）的表达式。

返回值

数组为1或0。

• 1 — 条件满足。
• 0 — 条件不满足。

类型: UInt8.

案例

CREATE TABLE retention_test(date Date, uid Int32) ENGINE = Memory;
INSERT INTO retention_test SELECT '2020-01-01', number FROM numbers(5);
INSERT INTO retention_test SELECT '2020-01-02', number FROM numbers(10);
INSERT INTO retention_test SELECT '2020-01-03', number FROM numbers(15);
INSERT INTO retention_test  SELECT '2020-01-01',  10;

 

2. 按唯一ID uid 对用户进行分组，使用 retention 功能。

SELECT
    uid,
    retention(date = '2020-01-01', date = '2020-01-02', date = '2020-01-03') AS r
FROM retention_test
WHERE date IN ('2020-01-01', '2020-01-02', '2020-01-03')
GROUP BY uid
ORDER BY uid ASC

3. 计算每天的现场访问总数。

SELECT
    sum(r[1]) AS r1,
    sum(r[2]) AS r2,
    sum(r[3]) AS r3
FROM
(
    SELECT
        uid,
        retention(date = '2020-01-01', date = '2020-01-02', date = '2020-01-03') AS r
    FROM retention_test
    WHERE date IN ('2020-01-01', '2020-01-02', '2020-01-03')
    GROUP BY uid
)

• r1-2020-01-01期间访问该网站的独立访问者数量（ cond1 条件）。
• r2-在2020-01-01和2020-01-02之间的特定时间段内访问该网站的唯一访问者的数量 (cond1 和 cond2 条件）。
• r3-在2020-01-01和2020-01-03之间的特定时间段内访问该网站的唯一访问者的数量 (cond1 和 cond3 条件）。

 

很多内容都是摘抄官方文档的，特别是有些描述性的内容，不过也加入了自己的理解，官方文档确实写的挺好的，中文版感觉理解起来还有要点差异，以下是连接

https://clickhouse.tech/docs/zh/sql-reference/aggregate-functions/parametric-functions/#retention