SCD2模型下按发货日期分组的周度统计T-SQL实现咨询

核心思路 #

通过生成目标统计周范围，关联SCD2结构的ORDERS表筛选每个周内的订单，并仅保留每个订单在对应周内的最新版本，最终聚合周度订单总额。无需依赖快照表，直接通过SCD2的生效时间区间实现版本筛选。

前提假设 #

• ORDERS表为标准SCD2结构：包含order_id（订单主键）、order_amount（订单金额）、ship_date（发货日期）、start_date（版本生效起始日）、end_date（版本生效截止日，当前版本用'9999-12-31'标记）。
• DIMDATE表包含date（日期主键）、week_of_year（年周编号）、calendar_year（年份）字段，用于周维度信息补充。
• 统计周定义为周一至周日，以周日作为周结束日期。

完整T-SQL代码 #

SET DATEFIRST 7; -- 设定周日为一周的最后一天，确保周计算逻辑正确

WITH target_weeks AS (
    -- 递归生成统计范围：当前周日往前12周 → 当前周日往后52周，共64个周
    SELECT 
        DATEADD(week, -12, DATEADD(day, 7 - DATEPART(weekday, GETDATE()), GETDATE())) AS week_end_date
    UNION ALL
    SELECT 
        DATEADD(week, 1, week_end_date)
    FROM target_weeks
    WHERE week_end_date <= DATEADD(week, 52, DATEADD(day, 7 - DATEPART(weekday, GETDATE()), GETDATE()))
),
week_date_ranges AS (
    -- 关联DIMDATE获取周维度信息，同时计算周起始日期（周一）
    SELECT 
        tw.week_end_date,
        DATEADD(day, -6, tw.week_end_date) AS week_start_date,
        d.calendar_year,
        d.week_of_year
    FROM target_weeks tw
    JOIN DIMDATE d ON d.date = tw.week_end_date
),
order_weekly_latest_versions AS (
    -- 筛选每个统计周内的订单，并标记每个订单的最新版本
    SELECT 
        wdr.week_end_date,
        wdr.week_start_date,
        wdr.calendar_year,
        wdr.week_of_year,
        o.order_id,
        o.order_amount,
        -- 按订单分组，取统计周内生效时间最晚的版本
        ROW_NUMBER() OVER (PARTITION BY wdr.week_end_date, o.order_id ORDER BY o.start_date DESC) AS version_rank
    FROM week_date_ranges wdr
    JOIN ORDERS o 
        ON o.ship_date BETWEEN wdr.week_start_date AND wdr.week_end_date -- 发货日期落在当前统计周内
        AND o.start_date <= wdr.week_end_date -- 订单版本在周结束前已生效
        AND (o.end_date >= wdr.week_start_date OR o.end_date = '9999-12-31') -- 订单版本在周内有效（历史版本覆盖周起始，当前版本永久有效）
)
-- 聚合每个统计周的订单总额
SELECT 
    week_end_date,
    week_start_date,
    calendar_year,
    week_of_year,
    SUM(order_amount) AS weekly_order_total
FROM order_weekly_latest_versions
WHERE version_rank = 1 -- 仅保留每个订单在对应周的最新版本
GROUP BY week_end_date, week_start_date, calendar_year, week_of_year
ORDER BY week_end_date;

代码解释 #

1. target_weeks CTE：递归生成所需的64个统计周的结束日期（周日），以当前系统日期计算的周日为基准，向前扩展12周、向后扩展52周。
2. week_date_ranges CTE：将周结束日期关联DIMDATE表，获取周对应的年份、年周编号，并计算周起始日期（周一）。
3. order_weekly_latest_versions CTE：关联ORDERS表筛选发货日期在当前周内的订单，通过ROW_NUMBER()函数为每个订单在对应周内的所有版本排序，标记最新版本（version_rank = 1）。
4. 最终聚合：过滤出每个订单的最新版本后，按周分组计算订单金额总和，得到过去12周+未来52周的周度统计结果。

调整说明 #

• 若统计周定义为其他区间（如周日至周六），需修改week_start_date的计算逻辑，并调整DATEFIRST设置。
• 若ORDERS表包含is_current字段，可在筛选条件中加入OR (is_current = 1 AND wdr.week_end_date > GETDATE())优化未来周的查询性能。

内容的提问来源于stack exchange，提问作者a.Smith