SQL SERVER - 把星期一(周一)当作每个星期的开始在一年中求取周数

开篇介绍

今天想写一个有关计算 Week Number 的函数，刚开始觉得应该很简单，凭着感觉七写八写到最后发现越写越乱，到最后搞了快两个小时以为解决了，结果一测还有好多数据不正确。非常有挫败感！感觉很不服气，觉得很丢人，跑出去站了会，冷静下来，重新拿起纸笔认真的分析了一下，连写到测试快半个小时还是解决了。

在 SQL Server 中默认情况下，每周的开始都是从周日开始算起的。但是在国内也有不一样的要求，比如按照习惯往往要求每周从周一算起。这样一来之前在数据仓库中的 Week Number 可能就不准确了，因为可能很多时候都是按默认方式来生成 Week Number 的。

解决的方式

这里有三种方式可以解决这个问题：

第一种方式是直接通过 SET DATEFIRST VALUE 来更改重新生成新的 DimDate，然后每次需要单独计算 Week Number 的时候根据 Date Key 关联一下就可以了，但这样就需要不断 JOIN DimDate，每一条记录都要 LookUp 一遍，不太好。

第二种方式就是在存储过程中需要使用到  Week Number 的时候，就先设置一下 SET DATEFIRST 然后在使用 DATEPART() 函数来获取 Week Number。这种方式需要每次都显示的  SET DATEFIRST ，不太方便。

第三种方式就是直接写一个函数，每次调用一下就可以了。

关于使用 SET DATEFIRST <VALUE> - <VALUE> 的值从 1 到 7，即周一到周日，默认值是 7。

-- The default first date in a week is Sunday, the value is 7

SELECT @@DATEFIRST  



-- Default DATEFIRST is Sunday

SELECT DATENAME(WEEK,'2013-12-31') AS WeekName  -- 53

SELECT DATENAME(WEEK,'2014-01-01') AS WeekName  -- 1

SELECT DATENAME(WEEK,'2014-01-05') AS WeekName  -- 2



-- Change the DATEFIRST to 1, Monday will be the first day of week.

SET DATEFIRST 1 



SELECT @@DATEFIRST  -- 1



-- After change the DATEFIRST to Monday

SELECT DATENAME(WEEK,'2013-12-31') AS WeekName  -- 53

SELECT DATENAME(WEEK,'2014-01-01') AS WeekName  -- 1

SELECT DATENAME(WEEK,'2014-01-05') AS WeekName  -- 1

要注意的是 SET DATEFIRST 只在当前执行中有效，也就说比如新开一个查询页面继续查询 SELECT @@DATEFIRST 则还是显示默认值 7。

在创建时间维度的代码中添加 SET DATEFIRST 1，表示每周以周一开始。

USE BIWORK_SSIS

GO  

SET NOCOUNT ON 



-- 设置每周的起始天为周一

SET DATEFIRST 1



IF OBJECT_ID('DimDateStartWithMonday','U') IS NOT NULL

DROP TABLE DimDateStartWithMonday

GO



CREATE TABLE DimDateStartWithMonday

(

    DateKey INT PRIMARY KEY,

    FullDate DATE NOT NULL,

    [DateName] NVARCHAR(20),

    DayNumberOfWeek TINYINT NOT NULL,

    DayNameOfWeek NVARCHAR(10) NOT NULL,

    DayNumberOfMonth TINYINT NOT NULL,

    DayNumberOfYear SMALLINT NOT NULL, 

    WeekNumberOfYear TINYINT NOT NULL,

    EnglishMonthName NVARCHAR(10) NOT NULL,

    MonthNumberOfYear TINYINT NOT NULL,

    CalendarQuarter TINYINT NOT NULL,

    CalendarSemester TINYINT NOT NULL,

    CalendarYear SMALLINT NOT NULL 

)



DECLARE @StartDate DATETIME

DECLARE @EndDate DATETIME



SELECT @StartDate = '2001-01-01',

       @EndDate = '2035-12-31'



WHILE (@StartDate <= @EndDate)

BEGIN

    INSERT INTO DimDateStartWithMonday 

    (

        DateKey,

        FullDate,

        [DateName],

        DayNumberOfWeek,

        DayNameOfWeek,

        DayNumberOfMonth,

        DayNumberOfYear, 

        WeekNumberOfYear,

        EnglishMonthName, 

        MonthNumberOfYear,

        CalendarQuarter,

        CalendarSemester,

        CalendarYear 

    )

    SELECT CAST(CONVERT(VARCHAR(8),@StartDate,112) AS INT) AS DateKey,

           CONVERT(VARCHAR(10), @StartDate,20) AS FullDate,

           CONVERT(VARCHAR(20), @StartDate,106) AS [DateName],

           DATEPART(DW,@StartDate) AS DayNumberOfWeek,

           DATENAME(DW,@StartDate) AS DayNameOfWeek,

           DATENAME(DD,@StartDate) AS [DayOfMonth],

           DATENAME(DY,@StartDate) AS [DayOfYear],  

           DATEPART(WW,@StartDate) AS WeekNumberOfYear,

           DATENAME(MM,@StartDate) AS EnglishMonthName,

           DATEPART(MM,@StartDate) AS MonthNumberOfYear,

           DATEPART(QQ,@StartDate) AS CalendarQuarter,

           CASE WHEN DATEPART(MM,@StartDate) BETWEEN 1 AND 6

                    THEN 1

                ELSE 2

           END AS CalendarSemester,

           DATEPART(YY,@StartDate) AS CalendarYear 

            

    SET @StartDate = @StartDate + 1

END

GO

最后是函数，这个函数麻烦的地方就是要考虑周日的情况。默认情况下，周日是每个星期的第一天，但是这里改成了周一是每周的第一天，逻辑上就会复杂很多。

比如，2012-01-01 是周日，2012-01-02 是周一。按默认情况，这两天的 Week Number 都是 1，但是这里需要把 2012-01-02 的 Week Number 变成 2。

比如，2011-01-01 是周六，2012-01-02 是周日。按默认情况，周六的 Week Number 是 1， 周日的是 2。但是这里需要把周六和周日的都变成 1， 周一的变成 2。

除此之外，还要考虑之后的每一个周日与周一的交替情况。

USE BIWORK_SSIS

GO



IF OBJECT_ID('ETLWORK_GETWEEKNUMBER','FN') IS NOT NULL

DROP FUNCTION ETLWORK_GETWEEKNUMBER

GO

 

CREATE FUNCTION ETLWORK_GETWEEKNUMBER(@DATE DATETIME)  

RETURNS INTEGER

AS

BEGIN 



    DECLARE @FIRST_DATE_OF_YEAR DATETIME = DATEADD(YYYY,DATEDIFF(YYYY,0,@DATE),0) 

    -- DECLARE @MONDAY_OF_WEEK DATETIME = DATEADD(WK,DATEDIFF(WK,0,@DATE),0) 

    -- DECLARE @PREVIOUS_DATE DATETIME = DATEADD(DAY,-1,@DATE)

    DECLARE @WEEK_NUMBER INTEGER



    -- 如果当前时间是当前年的第一天

    IF @DATE = @FIRST_DATE_OF_YEAR

        SET @WEEK_NUMBER = 1 

    -- 星期天是年第一天的情况

    ELSE IF (DATEPART(WEEKDAY,@DATE) = 1 AND DATEDIFF(DAYOFYEAR,@FIRST_DATE_OF_YEAR,@DATE)/7 + 1 = DATEPART(WEEK,@DATE))  

        SET @WEEK_NUMBER = DATEPART(WEEK,@DATE)  

    -- 星期天不是年第一天的情况

    ELSE IF (DATEPART(WEEKDAY,@DATE) = 1 AND DATEDIFF(DAYOFYEAR,@FIRST_DATE_OF_YEAR,@DATE)/7 + 1 <> DATEPART(WEEK,@DATE)) 

        SET @WEEK_NUMBER = DATEPART(WEEK,@DATE) - 1 

    -- 如果当前天的上一个周日小于年第一天

    ELSE IF DATEADD(DAY,-1,DATEADD(WK,DATEDIFF(WK,0,@DATE),0)) < @FIRST_DATE_OF_YEAR 

        SET @WEEK_NUMBER = 1 

    -- 当前天前面的一个周日正好是以周日为开始年的 7 倍的天数

    ELSE IF DATEDIFF(DAYOFYEAR,@FIRST_DATE_OF_YEAR,DATEADD(DAY,-1,DATEADD(WK,DATEDIFF(WK,0,@DATE),0) ))/7 + 1 = DATEPART(WEEK,@DATE) 

        SET @WEEK_NUMBER = DATEPART(WEEK,@DATE) + 1  

    ELSE 

        SET @WEEK_NUMBER = DATEPART(WEEK,@DATE)   

           

    RETURN @WEEK_NUMBER

END

GO

为了方便理解，可以查看下面的查询。

DECLARE @DATE DATETIME = '2012-01-29'

DECLARE @FIRST_DATE_OF_YEAR DATETIME = DATEADD(YYYY,DATEDIFF(YYYY,0,@DATE),0) 



SELECT DATEPART(WEEK,@DATE), -- 一年中的周数，默认以周日开始 

       DATEADD(WK,DATEDIFF(WK,0,@DATE),0), -- 当前周的周一，默认从周日开始，但是仍然找周一

       DATEADD(DAY,-1,DATEADD(WK,DATEDIFF(WK,0,@DATE),0)), -- 当前周先找周一，然后往前一天找到周日

       DATEDIFF(DAYOFYEAR,@FIRST_DATE_OF_YEAR,DATEADD(DAY,-1,DATEADD(WK,DATEDIFF(WK,0,@DATE),0))), -- 当前天离年第一天的间隔

       DATEDIFF(DAYOFYEAR,@FIRST_DATE_OF_YEAR,DATEADD(DAY,-1,DATEADD(WK,DATEDIFF(WK,0,@DATE),0) ))/7 + 1  -- 按天计算的周数

测试一下，查找不匹配的 Week Number - 30多年的数据结果都匹配，记得要新开一个页面，以免之前 SET DATEFIRST 的影响。

查询部分数据的 WeekNumber。

当然，我感觉写的还是有点复杂，谁解决过类似问题的，期望有人能提出更简洁的写法。