SQL使用Group by结合case实现跨年周按年度天数占比标注周数需求

前置条件：周统计规则为周日作为一周的第一天

需求说明 #

你需要对#TT表的D_Date字段计算周数，规则如下：

• 普通周直接返回正常周数
• 跨年周若新年侧天数少于4天，新年对应日期的周数标注为0，而非新年第一周
• 示例场景：2020-12-27到2021-01-02属于同一周，2021年仅占2天，因此2021-01-01、2021-01-02周数标注为0

现有问题 #

原有按年、周数分组减1的写法，会同步降低最早年份的周数，不符合需求。

解决方案 #

以下为SQL Server环境下的实现代码：

-- 先设置周起始为周日，保证周计算规则符合要求
SET DATEFIRST 7;

WITH DateWithWeekAttr AS (
    SELECT 
        D_Date,
        -- 计算当前日期所属周的周日（一周起始日）
        DATEADD(DAY, -(DATEPART(WEEKDAY, D_Date) - 1), D_Date) AS week_start,
        DATEPART(YEAR, D_Date) AS date_year
    FROM #TT
),
WeekYearDays AS (
    -- 统计每个周在不同年份的天数
    SELECT 
        week_start,
        date_year,
        COUNT(*) AS day_cnt
    FROM DateWithWeekAttr
    GROUP BY week_start, date_year
),
WeekCrossInfo AS (
    -- 统计每个周的跨年属性
    SELECT 
        week_start,
        COUNT(DISTINCT date_year) AS is_cross_year,
        MIN(date_year) AS old_year,
        MAX(date_year) AS new_year
    FROM DateWithWeekAttr
    GROUP BY week_start
)
SELECT 
    a.D_Date,
    CASE
        -- 非跨年周直接返回正常周数
        WHEN w.is_cross_year = 1 THEN DATEPART(WEEK, a.D_Date)
        -- 跨年周的旧年部分返回正常周数
        WHEN a.date_year = w.old_year THEN DATEPART(WEEK, a.D_Date)
        -- 跨年周的新年部分，天数不足4天返回0，否则返回1
        ELSE CASE 
            WHEN (SELECT day_cnt FROM WeekYearDays WHERE week_start = a.week_start AND date_year = w.new_year) < 4 
            THEN 0 
            ELSE 1 
        END
    END AS week_num
FROM DateWithWeekAttr a
INNER JOIN WeekCrossInfo w ON a.week_start = w.week_start
ORDER BY a.D_Date;

效果验证 #

针对你提供的样本数据，上述代码返回结果为：

完全符合需求，且不会影响非跨年周、跨年周密年侧的周数计算。

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