根据日历表生成财年及周编号

2020-03-20

概述

数仓中经常会有一张日历维表，里面预存了很多日期截取和计算相关的字段，便于与事实表关联之后进行各类基于日期相关的分析。
现假定已有日历维表字段有限，要求增加财年字段，并增加财年对应的周编号（周编号的逻辑有3种，后续将逐一说明和实现）。
本文以SQL Server 2016为例，示例如何不使用循环，以纯集合思维的数据库编程方法实现以上需求。
仅给出最关键的Select语句，为表加字段的SQL操作忽略。

生成测试数据

-- 基础日历表
DROP TABLE IF EXISTS Dim_Calendar;
CREATE TABLE Dim_Calendar (
     DateKey INT PRIMARY KEY
    ,DATE DATE
    ,Cal_Week_Num INT
)
-- 插入从2018年到2025年的日期
SET DATEFIRST 1;
SET NOCOUNT ON;
DECLARE @date DATE = '20180101';
WHILE @date <= '20251231'
BEGIN
    INSERT INTO Dim_Calendar(DateKey,Date,Cal_Week_Num) 
        VALUES(CONVERT(VARCHAR(8),@date,112),@date,DATEPART(WEEK,@date))
    SET @date = DATEADD(DAY,1,@date)
END

根据SQL Server建议，Date类型字面量用yyyyMMdd格式，可防止不同语言上下文日期解析不一致的问题

数据预览：

生成财年

财年（Fiscal Year）周期很多国家和地区都不同，这里假定每年10月1日到次年的9月30日为一个财年。
SQL写法如下（Fiscal_Year字段）：

SELECT DateKey,Date
	  ,CASE WHEN MONTH(Date) BETWEEN 1 AND 9 THEN YEAR(Date) ELSE YEAR(Date) + 1 END AS Fiscal_Year
FROM Dim_Calendar;

然后增加Fiscal_Year字段，按以上逻辑Update即可:

ALTER TABLE Dim_Calendar ADD Fiscal_Year INT;
GO
UPDATE Dim_Calendar 
SET Fiscal_Year = CASE WHEN MONTH(Date) BETWEEN 1 AND 9 THEN YEAR(Date) ELSE YEAR(Date) + 1 END;
GO

生成财年周编号

每个财年的哪一天开始算第一周？是否与当天是星期几相关？
以上问题的不同回答，可对应出多种周号生成规则，这里例举三种规则。

规则一

规则：每个财年的第一个周一开始算第一周，第一个周一之前的日期算上个财年最后一周
思路：从开始日期到当前日期，有多少个周一，就为第几周

可使用SUM开窗函数计算从开始日期到当时日期有多少个周一，SQL写法如下：

SET DATEFIRST 1;
;WITH cte_MonDay_CNT AS
(
    SELECT Date,Fiscal_Year,DATEPART(dw,Date) AS Week_Day
          ,SUM(CASE DATEPART(dw,Date) WHEN 1 THEN 1 ELSE 0 END) OVER(PARTITION BY Fiscal_Year ORDER BY DATEKEY) AS Monday_CNT
    FROM Dim_Calendar
    WHERE Fiscal_Year >= 2019
)
SELECT   
     a.Date
    ,Week_Day
    ,a.Fiscal_Year
    -- 若年初不是从周一开始，则为上一年最后一周，LAG(x,N)代表结果集中前N行的数据
    ,CASE WHEN a.Monday_CNT = 0 THEN LAG(a.Monday_CNT,Week_Day - 1)  Over(order by Date) ELSE a.Monday_CNT END AS Fiscal_Week_Num
    ,CASE WHEN a.Monday_CNT = 0 THEN LAG(a.Fiscal_Year,Week_Day - 1) Over(order by Date) ELSE a.Fiscal_Year END AS Fiscal_Week_Num_Year 
FROM cte_MonDay_CNT a;

如下图，2019-09-30为周一，接下来的6天虽然是财年2020，但却是财年2019的最后一周。

规则二

规则：每个财年的第一天到第一个周日算第一周，最后一个周一到年尾算一周
思路：周号 = 从开始日期到当前日期累计出现的周日的个数 + 1，若当前日期即为周日，则不用 + 1

同样会用到SUM开窗函数：

SET DATEFIRST 1;
;With cte_SunDay_CNT AS
(
    SELECT Date,Fiscal_Year,DATEPART(dw, Date) AS Week_Day
          ,SUM(CASE DATEPART(dw, Date) WHEN 7 THEN 1 ELSE 0 END) OVER (PARTITION BY Fiscal_Year ORDER BY DATEKEY) AS Sunday_CNT
    FROM Dim_Calendar
    WHERE Fiscal_Year >= 2019
)
SELECT a.Date,a.Fiscal_Year,a.Week_Day
      ,CASE Week_Day WHEN 7 THEN a.Sunday_CNT ELSE a.Sunday_CNT + 1 END AS Fiscal_Week_Num
FROM cte_SunDay_CNT AS a;

如下图，连续周号不会跨财年，2020财年最后一周只有3天，2021财年第一周也只有3天：

规则三

规则：每个财年的第1天算第一周的第1天，第8天算第二周的第一天，之后依此类推
思路：财年首日起，每7天算一周

将一个区间分成N个份，容易想到用NTILE函数，将一年分为53份的SQL写法：

SELECT Date,Fiscal_Year
      ,NTILE(53) OVER(PARTITION BY Fiscal_Year ORDER BY Date) AS Fiscal_Week_Num
FROM Dim_Calendar
WHERE Fiscal_Year >= 2019

可是这样是不对的，NTILE仅适合等分的情形。
由于365或366无法被53整除，从第48周起，每周的天数变为6天，不符合要求：

正确写法是用财年内的日期序号除7之后向上取整，SQL如下：

-- 每7天为一个新的周，除以7向上取整
SELECT a.Date,a.Fiscal_Year, CEILING(a.Day_of_FiscalYear/7.0) AS Fiscal_Week_Num
FROM (
    SELECT Date,Fiscal_Year,ROW_NUMBER() OVER(PARTITION BY Fiscal_Year ORDER BY Date) AS Day_of_FiscalYear
    FROM Dim_Calendar
    WHERE Fiscal_Year>= 2019
) AS a

这样分成的53周，最后一周可能只有1天或2天：

总结

灵活使用SQL Server的开窗函数，使用面向集合的编程范式去实现一系列数据操作。