按周一至周日统计周数据，解决SQL跨年周日期重叠问题

你的问题出在DATEPART(WEEK)的年度归属判断上——它只按日期的自然年度筛选，却忽略了跨年周的实际归属逻辑。比如2023年1月1日实际属于2022年的最后一周，但原SQL的WHERE条件会把这条数据拉进2023年的统计结果里，导致周分组混乱。

给你两个可行的解决思路：

方法一：基于周一起始日的年度归属分组 #

先计算每个日期对应的周起始（周一），用这个起始日的年份作为周的归属年度，彻底避免跨年数据错位：

WITH weekly_survey AS (
    SELECT
        responsedate,
        ov_rating,
        recommend,
        -- 计算当前日期所在周的周一（周一为周起始）
        DATEADD(DAY, DATEDIFF(DAY, 0, responsedate) / 7 * 7, 0) AS week_start
    FROM survey
    WHERE responsedate <= GETDATE()
)
SELECT
    DATEPART(WEEK, week_start) AS week_number,
    week_start AS from_date,
    DATEADD(DAY, 6, week_start) AS to_date,
    ROUND((COUNT(CASE WHEN ov_rating >=8 THEN 1 END)*100.0 / NULLIF(COUNT(ov_rating),0)),2) AS overall_rating,
    ROUND(
        (COUNT(CASE WHEN recommend >=9 THEN 1 END)*100.0 / NULLIF(COUNT(recommend),0)) -
        (COUNT(CASE WHEN recommend BETWEEN 1 AND 6 THEN 1 END)*100.0 / NULLIF(COUNT(recommend),0)),
        2
    ) AS recommend_score
FROM weekly_survey
-- 按周起始日的年份筛选当前年度数据
WHERE YEAR(week_start) = YEAR(GETDATE())
GROUP BY week_start
ORDER BY from_date;

方法二：使用ISO标准周（推荐） #

ISO周规则天然以周一为起始，且年度归属以周内的周四为准，能完美处理跨年周的归属问题：

SELECT
    DATEPART(ISO_WEEK, responsedate) AS week_number,
    -- 计算周起始（周一）
    DATEADD(DAY, 1 - DATEPART(WEEKDAY, responsedate), responsedate) AS from_date,
    -- 计算周结束（周日）
    DATEADD(DAY, 7 - DATEPART(WEEKDAY, responsedate), responsedate) AS to_date,
    ROUND((COUNT(CASE WHEN ov_rating >=8 THEN 1 END)*100.0 / NULLIF(COUNT(ov_rating),0)),2) AS overall_rating,
    ROUND(
        (COUNT(CASE WHEN recommend >=9 THEN 1 END)*100.0 / NULLIF(COUNT(recommend),0)) -
        (COUNT(CASE WHEN recommend BETWEEN 1 AND 6 THEN 1 END)*100.0 / NULLIF(COUNT(recommend),0)),
        2
    ) AS recommend_score
FROM survey
-- 用ISO年份筛选，自动排除归属去年的跨年周数据
WHERE DATEPART(ISO_YEAR, responsedate) = YEAR(GETDATE())
AND responsedate <= GETDATE()
GROUP BY DATEPART(ISO_YEAR, responsedate), DATEPART(ISO_WEEK, responsedate)
ORDER BY from_date;

关键说明 #

• 方法一中，通过周起始日的年份筛选，确保跨年日期不会被错误归入当前年度统计。
• 方法二的ISO周符合国际通用统计规则，适合需要长期稳定周统计的场景。

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