SQL需求：将日期列转换为连续日期区间的起止日期

需要将包含ID和日期的数据集，按ID分组后提取每组内的连续日期区间，生成包含ID、区间起始日期、区间结束日期的结果表。

源数据 #

目标结果（注：原示例中ID为5的结束日期存在笔误，正确应为01/05/2022） #

解决方案（SQL实现） #

利用窗口函数和日期分组的方式识别连续区间，以下以SQL Server为例，其他数据库可调整日期转换函数：

WITH ranked_dates AS (
    SELECT
        id,
        date,
        CONVERT(DATE, date, 101) AS converted_date,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY CONVERT(DATE, date, 101)) AS rn
    FROM your_table
),
grouped_intervals AS (
    SELECT
        id,
        date,
        converted_date,
        DATEADD(DAY, -rn, converted_date) AS group_key
    FROM ranked_dates
)
SELECT
    id,
    MIN(date) AS start,
    MAX(date) AS end
FROM grouped_intervals
GROUP BY id, group_key
ORDER BY id, start;

逻辑说明 #

1. ranked_dates CTE：将字符串格式的日期转换为数据库可计算的日期类型，同时按ID分组、日期排序生成行号，为后续分组做准备。
2. grouped_intervals CTE：通过日期 - 行号的方式生成分组键——连续的日期经过计算后会得到相同的group_key，以此区分不同的连续区间。
3. 最终查询：按ID和分组键聚合，取每组的最小日期作为区间起始，最大日期作为区间结束，得到目标结果。

数据库适配提示 #

• MySQL：将CONVERT(DATE, date, 101)替换为STR_TO_DATE(date, '%m/%d/%Y')，DATEADD(DAY, -rn, converted_date)替换为DATE_SUB(converted_date, INTERVAL rn DAY)
• PostgreSQL：将CONVERT(DATE, date, 101)替换为TO_DATE(date, 'MM/DD/YYYY')，DATEADD(DAY, -rn, converted_date)替换为converted_date - INTERVAL '1 day' * rn

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