SQLite中如何合并列中连续重复的多个相同值为单个值？

我执行了以下SQL查询，按日期分组拼接事件ID：

SELECT 
    strftime('%Y-%m-%d', start_time) as day, group_concat(event_id, ' | ') as events
FROM
    events_table
WHERE 
    start_time BETWEEN '2022-01-01 00:00:00' and '2022-03-31 23:59:59'
    and event_id is not null
GROUP by day

查询返回结果如下：

每日内的事件已按start_time排序，但我期望得到如下结果（合并连续重复的event_id）：

需求是将列中连续的相同值合并为单个，避免连续重复。

解决方案 #

利用SQLite的LAG窗口函数标记并筛选掉每日内连续重复的event_id，再进行分组拼接：

WITH ordered_events AS (
    SELECT 
        strftime('%Y-%m-%d', start_time) AS day,
        event_id,
        start_time,
        -- 获取同一日期内前一个事件的ID
        LAG(event_id) OVER (PARTITION BY strftime('%Y-%m-%d', start_time) ORDER BY start_time) AS prev_event
    FROM events_table
    WHERE 
        start_time BETWEEN '2022-01-01 00:00:00' AND '2022-03-31 23:59:59'
        AND event_id IS NOT NULL
),
filtered_events AS (
    -- 只保留与前一个事件ID不同的行，或当天的第一个事件
    SELECT day, event_id
    FROM ordered_events
    WHERE event_id != prev_event OR prev_event IS NULL
)
SELECT 
    day,
    group_concat(event_id, ' | ') AS events
FROM filtered_events
GROUP BY day
ORDER BY day;

说明 #

1. ordered_events CTE：给每个事件标记出同一日期内的前一个事件ID，同时保留日期、事件ID和时间排序依据。
2. filtered_events CTE：筛选掉连续重复的事件，只保留每个连续重复序列的第一个事件。
3. 最后对筛选后的结果执行group_concat，得到无连续重复的事件拼接字符串，且保持原时间顺序。

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