如何用SQL生成指定时间范围内的周分段数据

问题描述 #

现有一张表（假设表名为date_ranges），存储数据如下：

需要编写SQL语句，生成每个id对应时间范围内的所有周分段数据，预期输出如下：

解决方案 #

以下是适用于MySQL 8.0+、PostgreSQL、SQL Server等主流数据库的递归CTE实现方案：

核心思路 #

1. 统一时间范围：处理begin_date晚于end_date的异常情况，取两者的最小值作为实际起始日期，最大值作为实际结束日期。
2. 递归生成周起始：用递归CTE生成覆盖整个时间范围的所有周起始日期（以周一为周起始）。
3. 格式化输出：计算每个周的结束日期（起始日+6天），并转换为指定的dd.mm.yyyy字符串格式。

SQL代码 #

WITH date_ranges_clean AS (
    -- 修正日期顺序，确保start_date <= end_date
    SELECT 
        id,
        STR_TO_DATE(LEAST(begin_date, end_date), '%d.%m.%Y') AS start_date,
        STR_TO_DATE(GREATEST(begin_date, end_date), '%d.%m.%Y') AS end_date
    FROM date_ranges
),
weekly_ranges AS (
    -- 递归生成所有周起始日期
    SELECT 
        id,
        -- 获取start_date所在周的周一
        DATE_SUB(start_date, INTERVAL WEEKDAY(start_date) DAY) AS week_start
    FROM date_ranges_clean
    UNION ALL
    SELECT 
        id,
        DATE_ADD(week_start, INTERVAL 7 DAY) AS week_start
    FROM weekly_ranges
    JOIN date_ranges_clean USING(id)
    -- 终止条件：周起始不超过结束日期所在周的周日
    WHERE DATE_ADD(week_start, INTERVAL 7 DAY) <= DATE_ADD(DATE_SUB(end_date, INTERVAL WEEKDAY(end_date) DAY), INTERVAL 6 DAY)
)
SELECT 
    id,
    CONCAT(
        DATE_FORMAT(week_start, '%d.%m.%Y'),
        ' -  ',
        DATE_FORMAT(DATE_ADD(week_start, INTERVAL 6 DAY), '%d.%m.%Y')
    ) AS week_from_till
FROM weekly_ranges
ORDER BY id, week_start;

关键细节说明 #

• 日期格式转换：STR_TO_DATE将字符串日期转为数据库可识别的日期类型，DATE_FORMAT把日期转回dd.mm.yyyy格式。
• 周起始调整：如果你的业务周起始是周日，把DATE_SUB(start_date, INTERVAL WEEKDAY(start_date) DAY)改为DATE_SUB(start_date, INTERVAL (WEEKDAY(start_date)+1) MOD 7 DAY)即可。
• 数据库适配：
  • PostgreSQL：替换STR_TO_DATE为TO_DATE，DATE_SUB/DATE_ADD改为- INTERVAL 'X DAY'/+ INTERVAL 'X DAY'，WEEKDAY替换为EXTRACT(DOW FROM start_date)-1（PostgreSQL中DOW0代表周日）。
  • SQL Server：替换STR_TO_DATE为CONVERT(DATE, begin_date, 104)，DATE_SUB/DATE_ADD改为DATEADD(DAY, -DATEPART(WEEKDAY, start_date)+1, start_date)，注意SQL Server的周起始规则可能需根据系统设置调整。

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