如何通过ClickHouse SQL从时序数据生成事件流转

完全可以直接用ClickHouse的SQL语句实现这个需求，不需要借助Python脚本。核心是利用ClickHouse支持的窗口函数LAG()来获取每个用户的上一步操作。

假设你的表名为event_table，对应的SQL语句如下：

SELECT
    userid,
    prevstepNameId,
    stepNameId AS curstepNameId,
    ts
FROM (
    SELECT
        userid,
        stepNameId,
        ts,
        -- 按用户分组、按时间排序，取上一行的stepNameId作为上一步
        LAG(stepNameId) OVER (PARTITION BY userid ORDER BY ts) AS prevstepNameId
    FROM event_table
)
-- 过滤掉没有上一步的第一条记录
WHERE prevstepNameId IS NOT NULL
-- 按用户和时间排序，保证时序正确
ORDER BY userid, ts

逻辑说明： #

1. 内层子查询通过LAG()窗口函数，针对每个userid分组，按照ts（时间戳）排序，获取当前步骤的上一步操作ID，命名为prevstepNameId。
2. 外层查询过滤掉prevstepNameId为空的行——这部分是每个用户的第一个步骤，没有前驱操作，不符合你的输出要求。
3. 最后按userid和ts排序，得到与你预期完全一致的事件时序流转表。

内容的提问来源于stack exchange，提问作者Rahul