求职面试SQL难题：如何查询连续日期的同类事件？

问题场景 #

需求是统计连续日期发生的同类事件的起止区间，输入数据如下：

预期输出：

为什么直接用MIN/MAX分组不行 #

直接按event_name分组取MIN(event_date)和MAX(event_date)会把所有同类型事件的日期合并，比如succeded的最小日期是2023-06-01，最大是2023-06-07，会错误地将两段不连续的区间合并成一个，不符合需求。

正确解法：差分组法 #

核心思路是给连续的同类型事件分配同一个分组标识，再按分组统计起止日期。具体步骤如下：

1. 生成分组标识 #

通过窗口函数ROW_NUMBER()按event_name分区、日期排序，然后用日期减去行号对应的天数。连续的同事件日期，这个差值会保持一致，非连续的则会变化：

SELECT 
    event_date,
    event_name,
    DATE_SUB(event_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY event_name ORDER BY event_date) DAY) AS group_id
FROM your_table;

执行后结果示例：

2. 按分组统计起止日期 #

基于上面的结果，按group_id和event_name分组，取最小和最大日期：

SELECT
    MIN(event_date) AS start_date,
    MAX(event_date) AS end_date,
    event_name
FROM (
    SELECT 
        event_date,
        event_name,
        DATE_SUB(event_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY event_name ORDER BY event_date) DAY) AS group_id
    FROM your_table
) t
GROUP BY group_id, event_name
ORDER BY start_date;

不同数据库的适配 #

• PostgreSQL：将DATE_SUB(event_date, INTERVAL ROW_NUMBER() ... DAY)替换为event_date - INTERVAL '1 day' * ROW_NUMBER() OVER (PARTITION BY event_name ORDER BY event_date)
• SQL Server：替换为DATEADD(day, -ROW_NUMBER() OVER (PARTITION BY event_name ORDER BY event_date), event_date)

关键逻辑说明 #

连续日期中，每个日期比前一天大1，对应的行号也比前一行大1，因此日期与行号的差值固定；当事件类型切换后，下一次同类型事件的行号会继续递增，差值发生变化，从而形成独立的分组，确保统计的是连续区间。

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