Clickhouse windowFunnel函数使用

-- 官方文档
https://clickhouse.com/docs/zh/sql-reference/aggregate-functions/parametric-functions/#function-sequencecount
对于事件进行连续跟踪分析能力，适用漏斗或跟踪埋点数据


-- 实际应用
统计一小时内发送相同内容短信失败2次，但第三次发送成功的所有手机号。


-- CK表结构
CREATE TABLE ods.ods_sms_record_all_uuid
(
    `id` Int32,
    `sign_name` String COMMENT '短信签名',
    `mobile_md5` String COMMENT 'md5手机号',
    `method` String COMMENT '发送方式',
    `sms_type` Int32 COMMENT '短信类型',
    `content` String COMMENT '短信内容',
    `service` String COMMENT '短信服务商',
    `send_time` DateTime,
    `identify` String COMMENT '请求标识Identify',
    `success` Int16 COMMENT '是否发送成功',
    `reason` String COMMENT '发送失败原因',
    `outer_id` String,
    `uid` String COMMENT 'uuid',
    `sender` String COMMENT '短信是谁发的',
    `extra` String COMMENT '用户自定义数据',
    `tid` Int64 COMMENT '短信模板id',
    `uuid` String COMMENT '用户uuid',
    `ts` DateTime MATERIALIZED now() COMMENT '入表时间'
)
ENGINE = ReplacingMergeTree()
PARTITION BY toYYYYMM(send_time)
PRIMARY KEY id
ORDER BY id
SETTINGS index_granularity = 8192


-- Query
select mobile_md5 ,content,windowFunnel(3600)(send_time,success=-2,success = -2 ,success=2) as wf
from ods.ods_sms_record_all_uuid sr 
where send_time >='2022-07-01 00:00:00'
group by mobile_md5,content
having wf = 3;