如何在SQL中将周数转换为ISO周数？附业务场景示例

问题背景 #

我的数据如下：

用原SQL查询得到的结果和Excel WEEKNUM函数一致，但不符合ISO周规则，我需要转换成ISO周数来匹配目标结果。

原SQL：

WITH CTE (customerID,FirstWeek,RN) AS (
        SELECT customerID,MIN(DATEPART(week,tp_date)) TransWeek,
        ROW_NUMBER() over(partition by customerID ORDER BY DATEPART(week,tp_date) asc ) FROM all_table
        GROUP BY customerID,DATEPART(week,tp_date)
    ) 
    
    SELECT CTE.customerID, CTE.FirstWeek,  
         (select TOP 1 (DATEPART(week,c.tp_date))   
            from all_table c 
                where c.customerID = CTE.customerID AND DATEPART(week,C.tp_date) > CTE.FirstWeek 
                    )   SecondWeek 
    FROM CTE  
    WHERE RN = 1

原结果：

目标ISO周结果：

修改方案 #

在SQL Server中，直接使用DATEPART(iso_week, 日期)函数就能获取符合ISO标准的周数，替换原SQL中所有DATEPART(week, ...)的位置即可。

修改后的完整SQL：

WITH CTE (customerID, FirstWeek, RN) AS (
    SELECT 
        customerID,
        MIN(DATEPART(iso_week, tp_date)) AS FirstWeek,
        ROW_NUMBER() OVER (PARTITION BY customerID ORDER BY DATEPART(iso_week, tp_date) ASC) AS RN
    FROM all_table
    GROUP BY customerID, DATEPART(iso_week, tp_date)
) 
SELECT 
    CTE.customerID, 
    CTE.FirstWeek,  
    (SELECT TOP 1 DATEPART(iso_week, c.tp_date)   
     FROM all_table c 
     WHERE c.customerID = CTE.customerID 
       AND DATEPART(iso_week, c.tp_date) > CTE.FirstWeek 
    ) AS SecondWeek 
FROM CTE  
WHERE RN = 1

关键说明 #

• ISO周的规则是每周从周一开始，且一年的第一周必须包含当年的第一个周四（即该周至少有4天在当年），这和DATEPART(week)默认的周日起始、第一周从1月1日开始的规则不同。
• 替换所有周数计算的地方，包括分组、排序和子查询的条件，确保整个逻辑都基于ISO周规则执行。

内容的提问来源于stack exchange，提问作者tasya fauzia fitriasari