如何在ClickHouse中按条件补全会话连接与断开记录行？

我在ClickHouse中有一张存储系统Connect（连接）与Disconnect（断开）事件的表，执行查询 select timestamp, username, event from table 得到如下结果：

会话必须在当日结束前标记为已完成：若用户在某日处于Connect状态且当日无后续Disconnect事件，需通过查询添加一条当日23:59的Disconnect事件行，同时添加次日00:00的Connect事件行。例如示例表中用户1在2022年12月20日未结束会话，期望得到如下结果：

请问是否可以修改查询实现上述需求？我知道ClickHouse不像PostgreSQL或SQL Server那样常用，提供PostgreSQL方言的代码即可，我会自行适配ClickHouse。

可以通过以下SQL实现需求，核心逻辑是识别用户每日未闭合的会话，生成补充事件后与原数据合并：

WITH original_events AS (
    SELECT 
        timestamp,
        username,
        event,
        DATE(timestamp) AS event_date
    FROM your_table_name  -- 替换为实际表名
),
user_daily_final_state AS (
    SELECT DISTINCT
        username,
        event_date,
        LAST_VALUE(event) OVER (
            PARTITION BY username, event_date 
            ORDER BY timestamp 
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS last_event
    FROM original_events
),
supplementary_events AS (
    -- 生成当日23:59的Disconnect事件
    SELECT
        (event_date + INTERVAL '23 hours 59 minutes')::TIMESTAMP AS timestamp,
        username,
        'Disconnect' AS event
    FROM user_daily_final_state
    WHERE last_event = 'Connect'
    
    UNION ALL
    
    -- 生成次日00:00的Connect事件
    SELECT
        (event_date + INTERVAL '1 day')::TIMESTAMP AS timestamp,
        username,
        'Connect' AS event
    FROM user_daily_final_state
    WHERE last_event = 'Connect'
)
-- 合并原数据与补充数据，按用户和时间排序
SELECT timestamp, username, event
FROM (
    SELECT timestamp, username, event FROM original_events
    UNION ALL
    SELECT timestamp, username, event FROM supplementary_events
) combined_data
ORDER BY username, timestamp;

代码说明 #

1. original_events：预处理原数据，提取每条事件对应的日期；
2. user_daily_final_state：通过窗口函数LAST_VALUE获取每个用户每日的最后一条事件类型，判断是否为未闭合的Connect状态；
3. supplementary_events：针对每日最后事件是Connect的用户，生成需要补充的两条事件；
4. 最后合并所有数据并按用户、时间排序，得到符合要求的结果。

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