【转】在 SQL Server 中创建日期维度（日历表）

【转】在 SQL Server 中创建日期维度（日历表）

原文地址

介绍

日历或日期维度表对于数据分析和报告至关重要。它允许用户执行诸如计算两个日期之间的工作日或分析一段时间内的数据（例如，年同比、月比）等作。在本文中，我们将引导您在 SQL Server 中创建日期维度表，该表将跨越 30 年，从 2010 年 1 月 1 日到 2039 年 12 月 31 日。这将是各种业务场景中报告和分析的实用解决方案。

我们还将通过包含有用的元数据（例如会计年度数据）来增强表，这将使其更易于在实际应用程序和报告中使用。

例子

1 使用递归 CTE 生成日期序列

代码语言：txt复制

-- Prevent set or regional settings from interfering with the interpretation of dates/literals
SET DATEFIRST 7, -- 1 = Monday, 7 = Sunday
    DATEFORMAT mdy, 
    LANGUAGE   US_ENGLISH;
DECLARE @StartDate  date = '20100101';
DECLARE @CutoffDate date = DATEADD(DAY, -1, DATEADD(YEAR, 30, @StartDate));
;WITH seq(n) AS 
(
  SELECT 0 UNION ALL 
  SELECT n + 1 FROM seq
  WHERE n < DATEDIFF(DAY, @StartDate, @CutoffDate)
)
SELECT n FROM seq
ORDER BY n 
OPTION (MAXRECURSION 0);

说明：

SET DATEFIRST 7：这可确保将星期日视为一周的第一天。

MAXRECURSION 0：默认情况下，SQL Server 将递归限制为 100 次迭代。将此设置为 0 将删除此限制，从而允许我们生成完整的日期范围。

递归 CTE （WITH seq）：CTE 生成一个数字序列，其中每个数字代表范围内的一天。

效果

2 从序列生成日期

生成数字序列后，我们需要将这些数字转换为实际的日期值。这是通过将序列中的每个数字添加到开始日期来完成的。

将数字转换为日期的代码：

代码语言：txt复制

DECLARE @StartDate  date = '20100101';
DECLARE @CutoffDate date = DATEADD(DAY, -1, DATEADD(YEAR, 30, @StartDate));
;WITH seq(n) AS 
(
  SELECT 0 UNION ALL SELECT n + 1 FROM seq
  WHERE n < DATEDIFF(DAY, @StartDate, @CutoffDate)
),
d(d) AS 
(
  SELECT DATEADD(DAY, n, @StartDate) FROM seq
)
SELECT d FROM d
ORDER BY d
OPTION (MAXRECURSION 0);

说明：

DATEADD（DAY， n， @StartDate）：此函数将天数 （n） 与开始日期 （@StartDate） 相加，从而得到 2010 年 1 月 1 日至 2039 年 12 月 31 日之间的日期列表。

效果

3 使用 SQL Server 2022+ 生成日期序列（为简单起见）

如果您使用的是 SQL Server 2022 或 Azure SQL 数据库，则可以使用GENERATE_SERIES函数简化流程。此函数生成数字序列，而无需递归 CTE。

日期生成代码 （SQL Server 2022+）：

代码语言：txt复制

DECLARE @StartDate  date = '20100101', @years int = 30;
;WITH seq(n) AS 
(
  SELECT n = value 
  FROM GENERATE_SERIES(0, 
    DATEDIFF(DAY, @StartDate, DATEADD(YEAR, @years, @StartDate))-1)
)
SELECT n FROM seq
ORDER BY n;

说明：

GENERATE_SERIES：此函数可以有效地生成一个从 0 到开始日期和结束日期之间的总天数（在本例中为 30 年）的数字序列。这比使用递归 CTE 要简洁得多。

效果

使用其他元数据增强日期序列

现在，我们有一个基本的日期列表，可以使用其他元数据（如日期名称、月份名称、季度信息和其他有用的报告属性）来丰富它。

代码语言：txt复制

DECLARE @StartDate  date = '20100101';
DECLARE @CutoffDate date = DATEADD(DAY, -1, DATEADD(YEAR, 30, @StartDate));
;WITH seq(n) AS 
(
  SELECT 0 UNION ALL SELECT n + 1 FROM seq
  WHERE n < DATEDIFF(DAY, @StartDate, @CutoffDate)
),
d(d) AS 
(
  SELECT DATEADD(DAY, n, @StartDate) FROM seq
),
src AS
(
  SELECT
    TheDate         = CONVERT(date, d),
    TheDay          = DATEPART(DAY,       d),
    TheDayName      = DATENAME(WEEKDAY,   d),
    TheWeek         = DATEPART(WEEK,      d),
    TheISOWeek      = DATEPART(ISO_WEEK,  d),
    TheDayOfWeek    = DATEPART(WEEKDAY,   d),
    TheMonth        = DATEPART(MONTH,     d),
    TheMonthName    = DATENAME(MONTH,     d),
    TheQuarter      = DATEPART(Quarter,   d),
    TheYear         = DATEPART(YEAR,      d),
    TheFirstOfMonth = DATEFROMPARTS(YEAR(d), MONTH(d), 1),
    TheLastOfYear   = DATEFROMPARTS(YEAR(d), 12, 31),
    TheDayOfYear    = DATEPART(DAYOFYEAR, d)
  FROM d
)
SELECT * FROM src
  ORDER BY TheDate
  OPTION (MAXRECURSION 0);

说明：

• DATEPART（）：提取日期的特定部分（如日、月、年、季度）。
• DATENAME（）：返回日期或月份的名称。
• DATEFROMPARTS（）：帮助生成当月的第一天或每年的最后一天。

效果

添加会计年度调整

许多企业的财政年度与日历年不一致。例如，会计年度可能从 10 月开始。我们可以调整 Date Dimension 表以包含会计年度信息。

会计年度调整代码：

代码语言：txt复制

;WITH q AS (SELECT d FROM 
(
    VALUES('20200101'),
          ('20200401'),
          ('20200701'),
          ('20201001')
    ) AS d(d))
SELECT
  d, 
  StandardQuarter        = DATEPART(QUARTER, d),
  LateFiscalQuarter      = DATEPART(QUARTER, DATEADD(MONTH, -9, d)),
  LateFiscalQuarterYear  = YEAR(DATEADD(MONTH, -9, d)),
  EarlyFiscalQuarter     = DATEPART(QUARTER, DATEADD(MONTH, 3, d)),
  EarlyFiscalQuarterYear = YEAR(DATEADD(MONTH, 3, d))
FROM q;

说明：

我们使用 DATEADD 来调整日期以适应会计年度模型。在此示例中，会计年度从 10 月开始，因此我们通过减去或添加月份来调整季度以正确分类季度。

效果

5 完成日期维度表

在本节中，我们将通过添加更多高级功能来进一步增强表格，例如识别周末、计算每月的第一天和最后一天以及创建对报告有用的日期格式。

具有高级功能的完整日期维度：

代码语言：txt复制

DECLARE @StartDate  date = '20100101';
DECLARE @CutoffDate date = DATEADD(DAY, -1, DATEADD(YEAR, 30, @StartDate));
;WITH seq(n) AS 
(
  SELECT 0 UNION ALL SELECT n + 1 FROM seq
  WHERE n < DATEDIFF(DAY, @StartDate, @CutoffDate)
),
d(d) AS 
(
  SELECT DATEADD(DAY, n, @StartDate) FROM seq
),
src AS
(
  SELECT
    TheDate         = CONVERT(date, d),
    TheDay          = DATEPART(DAY,       d),
    TheDayName      = DATENAME(WEEKDAY,   d),
    TheWeek         = DATEPART(WEEK,      d),
    TheISOWeek      = DATEPART(ISO_WEEK,  d),
    TheDayOfWeek    = DATEPART(WEEKDAY,   d),
    TheMonth        = DATEPART(MONTH,     d),
    TheMonthName    = DATENAME(MONTH,     d),
    TheQuarter      = DATEPART(Quarter,   d),
    TheYear         = DATEPART(YEAR,      d),
    TheFirstOfMonth = DATEFROMPARTS(YEAR(d), MONTH(d), 1),
    TheLastOfYear   = DATEFROMPARTS(YEAR(d), 12, 31),
    TheDayOfYear    = DATEPART(DAYOFYEAR, d)
  FROM d
),
dim AS
(
  SELECT
    TheDate, 
    TheDay,
    TheDaySuffix        = CONVERT(char(2), CASE WHEN TheDay / 10 = 1 THEN 'th' ELSE 
                            CASE RIGHT(TheDay, 1) WHEN '1' THEN 'st' WHEN '2' THEN 'nd' 
                            WHEN '3' THEN 'rd' ELSE 'th' END END),
    TheDayName,
    TheDayOfWeek,
    TheDayOfWeekInMonth = CONVERT(tinyint, ROW_NUMBER() OVER 
                            (PARTITION BY TheFirstOfMonth, TheDayOfWeek ORDER BY TheDate)),
    TheDayOfYear,
    IsWeekend           = CASE WHEN TheDayOfWeek IN (CASE @@DATEFIRST WHEN 1 THEN 6 WHEN 7 THEN 1 END,7) 
                            THEN 1 ELSE 0 END,
    TheWeek,
    TheISOweek,
    TheFirstOfWeek      = DATEADD(DAY, 1 - TheDayOfWeek, TheDate),
    TheLastOfWeek       = DATEADD(DAY, 6, DATEADD(DAY, 1 - TheDayOfWeek, TheDate)),
    TheWeekOfMonth      = CONVERT(tinyint, DENSE_RANK() OVER 
                            (PARTITION BY TheYear, TheMonth ORDER BY TheWeek)),
    TheMonth,
    TheMonthName,
    TheFirstOfMonth,
    TheLastOfMonth      = MAX(TheDate) OVER (PARTITION BY TheYear, TheMonth),
    TheFirstOfNextMonth = DATEADD(MONTH, 1, TheFirstOfMonth),
    TheLastOfNextMonth  = DATEADD(DAY, -1, DATEADD(MONTH, 2, TheFirstOfMonth)),
    TheQuarter,
    TheFirstOfQuarter   = MIN(TheDate) OVER (PARTITION BY TheYear, TheQuarter),
    TheLastOfQuarter    = MAX(TheDate) OVER (PARTITION BY TheYear, TheQuarter),
    TheYear,
    TheISOYear          = TheYear - CASE WHEN TheMonth = 1 AND TheISOWeek > 51 THEN 1 
                            WHEN TheMonth = 12 AND TheISOWeek = 1  THEN -1 ELSE 0 END,      
    TheFirstOfYear      = DATEFROMPARTS(TheYear, 1,  1),
    TheLastOfYear,
    IsLeapYear          = CONVERT(bit, CASE WHEN (TheYear % 400 = 0) 
                            OR (TheYear % 4 = 0 AND TheYear % 100 <> 0) 
                            THEN 1 ELSE 0 END),
    Has53Weeks          = CASE WHEN DATEPART(WEEK,     TheLastOfYear) = 53 THEN 1 ELSE 0 END,
    Has53ISOWeeks       = CASE WHEN DATEPART(ISO_WEEK, TheLastOfYear) = 53 THEN 1 ELSE 0 END,
    MMYYYY              = CONVERT(char(2), CONVERT(char(8), TheDate, 101))
                          + CONVERT(char(4), TheYear),
    Style101            = CONVERT(char(10), TheDate, 101),
    Style103            = CONVERT(char(10), TheDate, 103),
    Style112            = CONVERT(char(8),  TheDate, 112),
    Style120            = CONVERT(char(10), TheDate, 120)
  FROM src
)
SELECT * FROM dim
  ORDER BY TheDate
  OPTION (MAXRECURSION 0);

说明：

此代码生成一个全面的 Date Dimension 表，其中包含以下增强功能：

• Weekend Indicator（周末指示器）：确定这一天是否为周末。
• 每月的第一天和最后一天：标识该月的第一天和最后一天。
• Fiscal Adjustments（财务调整）：标识季度和会计年度特定信息。
• 多种日期格式：支持多种日期格式样式（例如 Style101、Style112）。

结论

在本文中，我们介绍了如何在 SQL Server 中创建健壮的日期维度表。从使用递归 CTE 或 GENERATE_SERIES 函数（在 SQL Server 2022+ 中）生成日期范围，到添加有用的元数据（如日期名称、会计年度调整），我们创建了一个可用于实际报告和分析场景的表。

通过遵循本指南，您现在为构建根据您的报告需求量身定制的动态日期维度表奠定了坚实的基础。无论您是处理日历数据、财务数据还是基于日期的自定义报表，此日期维度表都将作为 SQL Server 环境中的宝贵资源。