为何查询返回2021年第53周数据？公历该年仅含52周

问题根源 #

你遇到的核心问题是公历年份与ISO周的年份归属不匹配：

• ISO周的规则是每周从周一开始，且一年的第1周必须包含当年的第一个周四（即该周至少有4天属于当年）。
• 2020年的第53周覆盖日期为2020年12月28日至2021年1月3日，其中2021年1月1日-3日的公历年份是2021，但它们的ISO周数仍为53，且对应的ISO年份是2020。
• 你的查询用YEAR(DateTime)（公历年份）分组，把这三天的数据归到了2021年的分组下，同时搭配DATEPART(ISO_WEEK, DateTime)得到的53周数，最终导致2021年的结果里出现了第53周的数值。

解决方法 #

改用ISO标准的年份（DATEPART(ISO_YEAR, DateTime)）来替代公历年份进行分组、筛选和字段输出，确保周数与年份的归属完全匹配。

修改后的SQL代码：

SELECT
    *
FROM (
    SELECT
        DATEPART(ISO_YEAR, DateTime) AS Year, -- 使用ISO年份而非公历年份
        'WEEK ' + RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(ISO_WEEK,DateTime)), 2) AS Week,
        MAX(Value) - MIN(Value) AS Value
    FROM
        DummyData.dbo.SampleData
    WHERE
        TagName LIKE '%ENERGY_SAMPLE_1%' AND
        DATEPART(ISO_YEAR,DateTime) >= 2020 AND -- 筛选条件也改用ISO年份
        DATEPART(ISO_YEAR,DateTime) <= 2022
    GROUP BY
        DATEPART(ISO_YEAR, DateTime), -- 分组依据改为ISO年份
        DATEPART(ISO_WEEK,DateTime)

) AS SourceTable
PIVOT (
    SUM(Value)
    FOR Week IN (
        --[WEEK 01], [WEEK 02], [WEEK 03], [WEEK 04], [WEEK 05], [WEEK 06], [WEEK 07],
        --[WEEK 08], [WEEK 09], [WEEK 10], [WEEK 11], [WEEK 12], [WEEK 13], [WEEK 14],
        --[WEEK 15], [WEEK 16], [WEEK 17], [WEEK 18], [WEEK 19], [WEEK 20], [WEEK 21],
        --[WEEK 22], [WEEK 23], [WEEK 24], [WEEK 25], [WEEK 26], [WEEK 27], [WEEK 28],
        --[WEEK 29], [WEEK 30], [WEEK 31], [WEEK 32], [WEEK 33], [WEEK 34], [WEEK 35],
        --[WEEK 36], [WEEK 37], [WEEK 38], [WEEK 39], [WEEK 40], [WEEK 41], [WEEK 42],
        --[WEEK 43], [WEEK 44], [WEEK 45], [WEEK 46], [WEEK 47], [WEEK 48], [WEEK 49],
        [WEEK 50], [WEEK 51], [WEEK 52], 
        [WEEK 53]
    )
) AS PivotTable;

效果说明 #

修改后，2021年1月1-3日的数据会被正确归到2020年的第53周，2021年的分组结果里就不会出现第53周的数值，完全符合你预期的2021年仅有52周的规则。

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