基于无独立日期列的预计算周表实现26周滚动统计的求助

• 原有查询存在逻辑硬过滤缺陷：编写的筛选条件中同时存在YEAR(DATEADD(WEEK, -26, GETDATE())) = YEAR(GETDATE()) - 1和AND YEAR = YEAR(GETDATE())两个互斥判断，后者直接将所有上一年度的数据全部过滤，无论前者的判断是否成立，都不可能调取到上一年度的目标周数据
• 字段粒度匹配错误：预计算表仅存储独立的年、月、周标记值，无连续日期锚点，直接套用基于实际日期计算的时间范围、单年份筛选逻辑，完全适配不上周维度的连续滚动要求
• 初始查询的统计逻辑不匹配：用月份差计算6个月的时间范围，和连续26周的统计规则存在粒度偏差，不同年度的同月份对应的周数不固定，会出现有效周数不足、跨期错位的问题

因为无法直接用日期函数筛选，核心思路是先把表内分散的YEAR + WEEK字段转换为可连续排序的周序标识，再截取匹配26周长度的连续区间做统计，具体逻辑如下：

• 逐行生成全局连续周序：以年度为单位累加周序号，考虑到部分年度会有1-53周的日历规则，按单年度最多53周的规则做累加，避免周序断档
• 定位滚动区间边界：取表内存储的最新有效周的序号作为区间终点，往前累计25个周序号作为区间起点，最终凑足连续26个周的有效统计范围
• 筛选区间内的周度数据完成聚合，去掉所有限制单一年度的硬过滤条件

参考可直接适配的查询代码：

WITH week_ordinal AS (
    SELECT
        CLOSED,
        WEEK,
        MONTH,
        YEAR,
        -- 全局连续周序计算：按年累加，预留53周的容错空间
        (YEAR - (SELECT MIN(YEAR) FROM E_GROUPE_INTERVENANT_SEMAINE_HISTORY)) * 53 + WEEK AS global_week_seq
    FROM E_GROUPE_INTERVENANT_SEMAINE_HISTORY
),
range_boundary AS (
    SELECT
        MAX(global_week_seq) AS seq_end,
        MAX(global_week_seq) - 25 AS seq_start -- 减25保证区间覆盖连续26周
    FROM week_ordinal
)
SELECT
    SUM(CLOSED) AS rolling_26w_closed,
    WEEK,
    MONTH,
    YEAR
FROM week_ordinal w
CROSS JOIN range_boundary r
WHERE w.global_week_seq BETWEEN r.seq_start AND r.seq_end
GROUP BY WEEK, MONTH, YEAR
-- 跨年度排序需按年、周顺序联合排序，避免同周数跨年度的排序错位
ORDER BY YEAR, WEEK

• 核心踩坑点：如果不解除单年度的硬过滤限制，无论怎么调整日期判断逻辑，都无法调取到跨年度的历史周数据
• 全局周序计算时预留53周的冗余值，比按单年度52周计算的连续性更强，能兼容年度日历规则带来的周数波动
• 排序时禁止仅按WEEK字段排序，因为不同年度的WEEK值都是从1开始编号，单字段排序会把跨年度的同周数据混排，必须联合YEAR字段做升序排序
• 统计口径完全对齐预计算表的沉淀规则，不要把原生工单查询的日期判断逻辑直接套用到这张预聚合表上，避免口径和管理员预计算的规则出现偏差

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