基于week_end_date生成含每日日期的扩展数据表需求

原始数据表 #

关键说明 #

• 不同ID的周结束日期对应不同星期：ID1为周五，ID2为周日，ID3为周六
• 需要生成每个周结束日期区间内（含起止日期）的所有日期，并新增is_week_end_date字段标记该日期是否为周结束日期

目标数据表样式 #

解决方案（SQL实现） #

核心思路 #

通过生成日期序列，结合原始表的周结束日期，扩展出每个ID对应的所有日期，并标记是否为周结束日期。以下以主流数据库为例提供实现代码：

PostgreSQL版本

WITH original_data AS (
    SELECT 
        id,
        TO_DATE(week_end_date, 'MM/DD/YYYY') AS week_end_date
    FROM your_table_name
),
date_ranges AS (
    SELECT 
        id,
        week_end_date,
        LAG(week_end_date) OVER (PARTITION BY id ORDER BY week_end_date) AS prev_week_end
    FROM original_data
),
expanded_dates AS (
    SELECT 
        id,
        generate_series(
            COALESCE(prev_week_end + INTERVAL '1 day', week_end_date),
            week_end_date,
            INTERVAL '1 day'
        )::DATE AS day_date
    FROM date_ranges
)
SELECT 
    ed.id,
    TO_CHAR(ed.day_date, 'MM/DD/YYYY') AS day_date,
    CASE WHEN od.week_end_date IS NOT NULL THEN 'Yes' ELSE 'No' END AS is_week_end_date
FROM expanded_dates ed
LEFT JOIN original_data od ON ed.id = od.id AND ed.day_date = od.week_end_date
ORDER BY ed.id, ed.day_date;

MySQL版本

WITH RECURSIVE date_series AS (
    SELECT MIN(STR_TO_DATE(week_end_date, '%m/%d/%Y')) AS day_date FROM your_table_name
    UNION ALL
    SELECT day_date + INTERVAL 1 DAY FROM date_series WHERE day_date < (SELECT MAX(STR_TO_DATE(week_end_date, '%m/%d/%Y')) FROM your_table_name)
),
original_data AS (
    SELECT id, STR_TO_DATE(week_end_date, '%m/%d/%Y') AS week_end_date FROM your_table_name
),
id_date_pairs AS (
    SELECT DISTINCT od.id, ds.day_date FROM original_data od CROSS JOIN date_series ds
)
SELECT 
    id,
    DATE_FORMAT(day_date, '%m/%d/%Y') AS day_date,
    CASE WHEN od.week_end_date IS NOT NULL THEN 'Yes' ELSE 'No' END AS is_week_end_date
FROM id_date_pairs idp
LEFT JOIN original_data od ON idp.id = od.id AND idp.day_date = od.week_end_date
WHERE idp.day_date BETWEEN (SELECT MIN(week_end_date) FROM original_data WHERE id = idp.id) AND (SELECT MAX(week_end_date) FROM original_data WHERE id = idp.id)
ORDER BY id, day_date;

代码说明 #

1. 日期类型转换：先将原始表中的字符串日期转换为数据库可识别的日期类型，避免计算错误。
2. 区间确定：通过窗口函数（PostgreSQL）或递归CTE（MySQL）生成每个ID对应的日期区间，确保覆盖所有周结束日期之间的日期。
3. 日期扩展：生成区间内的所有日期，再与原始表关联，标记出周结束日期。

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