SQL无循环实现按X天合并日期点生成日期范围起始点

需求说明 #

给定一组带日期的记录，规则为：迭代选取当前未分组的最小日期，将所有与该日期相差X天内的记录归入同一组（组起始为该最小日期），重复此操作直至所有记录处理完毕。例如X=10天时，示例数据会按2023-01-01、2023-01-12、2023-02-01、2023-03-01四个起始点分组。

解决方案思路 #

这类迭代分组问题无需使用WHILE等显式循环，可通过递归CTE（集合式递归，非逐行循环）或窗口函数累积计算实现，两种方案均为数据库原生支持的高效集合操作。

1. SQL Server 实现 #

假设源表为date_data，包含id（唯一标识）和target_date（目标日期）字段，X=10天。

方案一：递归CTE

WITH recursive_groups AS (
    -- 初始步骤：取全局最小日期作为首个组的起始，关联所有符合条件的记录
    SELECT 
        id,
        target_date,
        MIN(target_date) OVER () AS group_start
    FROM date_data
    WHERE DATEDIFF(day, MIN(target_date) OVER (), target_date) <= 10

    UNION ALL

    -- 递归步骤：从剩余记录中取最小日期作为新组起始，关联符合条件的记录
    SELECT 
        d.id,
        d.target_date,
        current_min.group_start
    FROM date_data d
    CROSS JOIN (
        SELECT MIN(target_date) AS group_start
        FROM date_data
        WHERE id NOT IN (SELECT id FROM recursive_groups)
    ) current_min
    WHERE 
        id NOT IN (SELECT id FROM recursive_groups)
        AND DATEDIFF(day, current_min.group_start, d.target_date) <= 10
)
-- 输出最终分组结果
SELECT 
    id,
    target_date,
    group_start AS final_group_start
FROM recursive_groups
ORDER BY id;

方案二：窗口函数累积标记（更高效）

适合大数据量场景，避免递归开销：

WITH ordered_dates AS (
    -- 按日期排序，计算当前日期与前一个记录的日期差
    SELECT 
        id,
        target_date,
        DATEDIFF(day, LAG(target_date) OVER (ORDER BY target_date), target_date) AS diff_prev
    FROM date_data
),
group_markers AS (
    -- 标记新组：当前日期与前一个记录差超过X天，或为第一条记录
    SELECT 
        *,
        SUM(CASE WHEN diff_prev > 10 OR diff_prev IS NULL THEN 1 ELSE 0 END) 
            OVER (ORDER BY target_date) AS group_id
    FROM ordered_dates
)
-- 按组ID取最小日期作为组起始
SELECT 
    id,
    target_date,
    MIN(target_date) OVER (PARTITION BY group_id) AS final_group_start
FROM group_markers
ORDER BY id;

2. BigQuery 实现 #

假设源表为project.dataset.date_data，X=10天。

方案一：递归CTE

WITH RECURSIVE recursive_groups AS (
    -- 初始步骤：关联全局最小日期对应的所有符合条件的记录
    SELECT 
        id,
        target_date,
        MIN(target_date) OVER () AS group_start
    FROM `project.dataset.date_data`
    WHERE DATE_DIFF(target_date, MIN(target_date) OVER (), DAY) <= 10

    UNION ALL

    -- 递归步骤：处理剩余记录，取新的最小日期作为组起始
    SELECT 
        d.id,
        d.target_date,
        current_min.group_start
    FROM `project.dataset.date_data` d
    CROSS JOIN (
        SELECT MIN(target_date) AS group_start
        FROM `project.dataset.date_data`
        WHERE id NOT IN (SELECT id FROM recursive_groups)
    ) current_min
    WHERE 
        id NOT IN (SELECT id FROM recursive_groups)
        AND DATE_DIFF(d.target_date, current_min.group_start, DAY) <= 10
)
SELECT 
    id,
    target_date,
    group_start AS final_group_start
FROM recursive_groups
ORDER BY id;

方案二：窗口函数向前填充（高效且简洁）

利用BigQuery的LAST_VALUE函数实现组起始的向前填充：

WITH ordered_dates AS (
    -- 按日期排序，标记新组的起始日期
    SELECT 
        id,
        target_date,
        CASE 
            WHEN DATE_DIFF(target_date, LAG(target_date) OVER (ORDER BY target_date), DAY) > 10 
                OR LAG(target_date) OVER (ORDER BY target_date) IS NULL
            THEN target_date
            ELSE NULL
        END AS group_start_marker
    FROM `project.dataset.date_data`
)
-- 向前填充组起始标记，得到每个记录的组起始日期
SELECT 
    id,
    target_date,
    LAST_VALUE(group_start_marker IGNORE NULLS) OVER (ORDER BY target_date) AS final_group_start
FROM ordered_dates
ORDER BY id;

示例验证 #

使用示例数据执行上述SQL后，将得到符合需求的分组结果：

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