如何在SQL中按可配置的n个连续日期区间统计日期计数？

假设有一张存储日期的表（示例表名为date_table，日期字段为date_col），现有数据如下：

2022-01-01
2022-01-02
2022-01-03
2022-01-06
2022-01-07
2022-01-11

需求：编写SQL查询，统计指定日期范围内每个日期对应的分组计数，规则为连续的n个日期视为同一个分组（n为可配置参数），若日期中断则开启新分组，分组满n个连续日期后也开启新分组。不同n值的预期输出如下：

当n=2时（每2个连续日期为一组） #

2022-01-01 1
2022-01-02 1
2022-01-03 2
2022-01-06 3
2022-01-07 3
2022-01-11 4

当n=3时（每3个连续日期为一组） #

2022-01-01 1
2022-01-02 1
2022-01-03 1
2022-01-06 2
2022-01-07 3
2022-01-11 4

当n=4时（每4个连续日期为一组） #

2022-01-01 1
2022-01-02 2
2022-01-03 3
2022-01-06 4
2022-01-07 4
2022-01-08 4
2022-01-09 4
2022-01-10 5
2022-01-13 6

解决方案 #

步骤说明 #

1. 生成日期范围：生成指定区间内的所有日期，覆盖原表数据及中断的日期
2. 标记连续状态：判断每个日期与前一个日期是否连续，划分连续日期块
3. 计算分组编号：基于连续块和配置的n值，标记新分组起点并累计得到最终分组ID

SQL实现（以MySQL为例） #

-- 配置分组大小n
SET @n = 2;

WITH date_range AS (
    -- 生成目标日期范围内的所有日期（可手动替换起止日期）
    SELECT DATE_ADD('2022-01-01', INTERVAL seq DAY) AS current_date
    FROM (
        SELECT 0 AS seq UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
        UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
        UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11
        UNION ALL SELECT 12 UNION ALL SELECT 13
    ) s
    WHERE DATE_ADD('2022-01-01', INTERVAL seq DAY) <= '2022-01-13'
),
continuous_blocks AS (
    SELECT 
        current_date,
        -- 标记连续日期块：日期中断则开启新块
        SUM(
            CASE 
                WHEN LAG(current_date) OVER (ORDER BY current_date) IS NULL THEN 1
                WHEN DATEDIFF(current_date, LAG(current_date) OVER (ORDER BY current_date)) > 1 THEN 1
                ELSE 0
            END
        ) OVER (ORDER BY current_date) AS block_id
    FROM date_range
),
group_markers AS (
    SELECT 
        current_date,
        block_id,
        -- 标记新分组起点：块起始，或当前块内已累计n个日期
        CASE 
            WHEN ROW_NUMBER() OVER (PARTITION BY block_id ORDER BY current_date) % @n = 1 THEN 1
            ELSE 0
        END AS is_new_group
    FROM continuous_blocks
)
SELECT 
    current_date,
    -- 累计新分组标记得到最终分组编号
    SUM(is_new_group) OVER (ORDER BY current_date) AS group_id
FROM group_markers
ORDER BY current_date;

关键逻辑解释 #

• date_range：生成指定起止日期内的所有日期，确保不会遗漏中断的日期
• continuous_blocks：将连续的日期划分为独立块，日期中断时块编号递增
• group_markers：在每个连续块内，每累计n个日期就标记一个新分组起点
• 最终通过累计新分组标记，得到每个日期对应的分组编号

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