PostgreSQL按Entry分组匹配Add/Remove事件对的SQL实现求助

假设你的事件表名为entry_events，可以通过以下SQL生成包含Entry、Added、Removed的结果表，满足你的查询需求：

WITH add_events AS (
    SELECT 
        Entry,
        Timestamp AS Added,
        -- 匹配同条目下当前Add之后的第一个Remove事件时间
        LEAD(Timestamp) OVER (
            PARTITION BY Entry 
            ORDER BY Timestamp
        ) AS Removed
    FROM entry_events
    WHERE Event = 'Add'
),
final_pairs AS (
    SELECT
        Entry,
        Added,
        -- 无对应Remove的条目，将Removed设为远期时间表示持续存在
        COALESCE(Removed, '9999-12-31'::timestamp) AS Removed
    FROM add_events
)
-- 过滤异常数据（如Remove早于Add的错误记录）
SELECT * 
FROM final_pairs
WHERE Removed >= Added;

逻辑说明 #

1. 提取Add事件并匹配后续Remove
   先筛选所有Event = 'Add'的记录，通过LEAD()窗口函数按Entry分组、时间排序，获取当前Add事件之后的第一个事件时间——这就是该条目对应的移除时间。

2. 处理未移除的条目
   对没有后续Remove事件的条目，用COALESCE将Removed设为'9999-12-31'，表示该条目至今仍在列表中。

3. 过滤异常数据
   通过WHERE Removed >= Added排除不符合业务逻辑的错误数据（比如先Remove后Add的情况）。

查询验证 #

按你的需求查询某条目在指定时间是否存在：

SELECT * 
FROM final_pairs
WHERE Entry = 'Dog' 
  AND '2021-01-01'::timestamp BETWEEN Added AND Removed;

持久化结果 #

如果需要长期使用这个结果，可以创建视图：

CREATE VIEW entry_existence AS
WITH add_events AS (
    SELECT 
        Entry,
        Timestamp AS Added,
        LEAD(Timestamp) OVER (
            PARTITION BY Entry 
            ORDER BY Timestamp
        ) AS Removed
    FROM entry_events
    WHERE Event = 'Add'
)
SELECT
    Entry,
    Added,
    COALESCE(Removed, '9999-12-31'::timestamp) AS Removed
FROM add_events
WHERE COALESCE(Removed, '9999-12-31'::timestamp) >= Added;

特殊情况处理 #

如果存在同一条目连续Add的情况（重复添加），当前逻辑会将每个Add与后续第一个Remove配对，符合“后Add激活条目”的常规业务逻辑。若需合并连续Add（仅保留最早Add时间），可先对连续Add去重：

WITH dedup_adds AS (
    SELECT
        Entry,
        Timestamp,
        -- 标记连续Add的第一条记录
        CASE 
            WHEN LAG(Event) OVER (PARTITION BY Entry ORDER BY Timestamp) = 'Add' 
            THEN FALSE 
            ELSE TRUE 
        END AS is_first_add
    FROM entry_events
    WHERE Event = 'Add'
),
add_events AS (
    SELECT 
        Entry,
        Timestamp AS Added,
        LEAD(Timestamp) OVER (
            PARTITION BY Entry 
            ORDER BY Timestamp
        ) AS Removed
    FROM dedup_adds
    WHERE is_first_add = TRUE
)
-- 后续逻辑同之前的final_pairs部分
SELECT
    Entry,
    Added,
    COALESCE(Removed, '9999-12-31'::timestamp) AS Removed
FROM add_events
WHERE COALESCE(Removed, '9999-12-31'::timestamp) >= Added;

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