如何在SQL中查找特定连续日期区间的最大与最小日期

测试数据 #

先创建测试表并插入示例数据：

CREATE TABLE PersonDates (
    PersonId INT,
    FromDate DATE,
    ToDate DATE
);

INSERT INTO PersonDates VALUES
(1, '2023-01-01', '2023-01-05'),
(1, '2023-01-06', '2023-01-10'),
(1, '2023-01-11', '2023-01-15'),
(1, '2023-01-16', '2023-01-20'),
(1, '2023-01-21', '2023-01-25'),
(2, '2023-02-01', '2023-02-03'),
(2, '2023-02-05', '2023-02-08');

解决方案 #

用窗口函数实现连续区间分组，再直接计算每个区间的首尾日期：

WITH GroupedDates AS (
    SELECT 
        *,
        -- 生成分组ID：同一PersonId下，连续的行归为同一组
        SUM(CASE 
            -- 该PersonId的第一行标记为新组
            WHEN LAG(ToDate) OVER (PARTITION BY PersonId ORDER BY FromDate) IS NULL THEN 1
            -- 当前行FromDate是上一行ToDate的次日，属于同一组
            WHEN FromDate = DATEADD(DAY, 1, LAG(ToDate) OVER (PARTITION BY PersonId ORDER BY FromDate)) THEN 0
            -- 不连续则标记为新组
            ELSE 1
        END) OVER (PARTITION BY PersonId ORDER BY FromDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS GroupId
    FROM PersonDates
)
SELECT 
    PersonId,
    FromDate,
    ToDate,
    -- 取当前组的最小FromDate作为区间起始日期
    MIN(FromDate) OVER (PARTITION BY PersonId, GroupId) AS MinDate,
    -- 取当前组的最大ToDate作为区间结束日期
    MAX(ToDate) OVER (PARTITION BY PersonId, GroupId) AS MaxDate
FROM GroupedDates
ORDER BY PersonId, FromDate;

逻辑说明 #

1. 分组标识生成：通过LAG函数获取同一PersonId的上一行结束日期，判断当前行是否与上一行连续。连续行的分组ID保持一致，不连续则生成新的分组ID。
2. 区间首尾计算：基于分组ID，用MIN和MAX窗口函数直接计算每个组的起始和结束日期，自动关联到组内每一行。

执行后，每行都会显示所属连续区间的MinDate和MaxDate，比如PersonId=1的第2-5行，会统一显示MinDate='2023-01-06'、MaxDate='2023-01-25'。

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