如何编写SQL获取每个Notification ID对应的最早/最晚Signup记录？

可以通过窗口函数获取每条通知的上一条通知时间，再关联注册表筛选对应区间的记录，最后聚合统计结果，SQL语句如下：

WITH notification_with_prev AS (
    SELECT
        ID,
        Notification_Timestamp,
        -- 为第一条通知设置极早的默认时间，确保所有早于它的注册记录都能被匹配
        LAG(Notification_Timestamp, 1, '1970-01-01') OVER (ORDER BY ID) AS previous_notification_time
    FROM notifications
)
SELECT
    n.ID,
    n.Notification_Timestamp,
    MIN(s.Signup_Timestamp) AS Earliest_Signup_Timestamp,
    MAX(s.Signup_Timestamp) AS Latest_Signup_Timestamp
FROM notification_with_prev n
LEFT JOIN signups s
    ON s.Signup_Timestamp > n.previous_notification_time
    AND s.Signup_Timestamp < n.Notification_Timestamp
GROUP BY n.ID, n.Notification_Timestamp
ORDER BY n.ID;

逻辑说明 #

1. CTE预处理：用LAG()窗口函数按ID排序，给每条通知添加上一条通知的时间。第一条通知没有上一条记录，用1970-01-01作为默认值，覆盖所有早于第一条通知的注册记录。
2. 关联筛选：左连接注册表，只保留注册时间落在「上一条通知时间」和「当前通知时间」之间的记录。
3. 聚合统计：按通知ID和通知时间分组，用MIN()和MAX()分别计算对应区间内最早、最晚的注册时间。

执行后得到的结果（修正了示例中ID1的笔误，原注册数据中无2022-09-09的记录）：

如果需要强制显示通知前一天作为最晚时间（即使区间内无注册），可以将MAX()部分修改为：

COALESCE(MAX(s.Signup_Timestamp), DATE_SUB(n.Notification_Timestamp, INTERVAL 1 DAY))

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