如何在SQL（SSMS）中将月度数据转换为周度数据？

需求说明 #

现有存储月度数据的表，每条记录的日期规则为：当月1日为周末时取第一个周一，否则取1日。需要将每条月度记录转换为当月所有周一的记录，对应Value保持不变。

示例输入数据 #

期望输出结果 #

解决方案：递归CTE实现 #

递归CTE是生成序列日期的高效方式，以下以SQL Server为例给出实现代码：

1. 创建测试表（可选） #

CREATE TABLE MonthlyData (
    Date DATE PRIMARY KEY,
    Value INT
);

INSERT INTO MonthlyData VALUES
('2023-02-01', 1),
('2023-03-01', 2),
('2023-04-03', 3);

2. 递归CTE转换逻辑 #

WITH WeeklyRecursive AS (
    -- 锚点：取原始月度记录的起始日期（已符合规则的周一），并计算当月最后一天
    SELECT 
        Date AS WeeklyDate,
        Value,
        EOMONTH(Date) AS MonthEnd
    FROM MonthlyData

    UNION ALL

    -- 递归：每次给当前周一加7天，生成下一个周一
    SELECT 
        DATEADD(DAY, 7, WeeklyDate) AS WeeklyDate,
        Value,
        MonthEnd
    FROM WeeklyRecursive
    -- 终止条件：生成的日期不超过当月最后一天
    WHERE DATEADD(DAY, 7, WeeklyDate) <= MonthEnd
)
-- 输出结果并排序
SELECT WeeklyDate AS Date, Value
FROM WeeklyRecursive
ORDER BY Date;

不同数据库适配说明 #

• MySQL：将EOMONTH(Date)替换为LAST_DAY(Date)，DATEADD(DAY, 7, WeeklyDate)替换为DATE_ADD(WeeklyDate, INTERVAL 7 DAY)
• PostgreSQL：EOMONTH(Date)替换为(DATE_TRUNC('MONTH', Date) + INTERVAL '1 MONTH - 1 DAY')::DATE，DATEADD(DAY,7,WeeklyDate)替换为WeeklyDate + INTERVAL '7 days'

逻辑说明 #

1. 锚点成员从原始表读取每条月度记录，同时计算当月最后一天作为递归的终止边界
2. 递归成员不断给当前周一日期加7天，生成下一个周一，直到新日期超出当月最后一天时停止
3. 最终将所有生成的周一日期与对应Value输出，排序后得到目标结果

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