使用递归公用表表达式(CTE)创建简单的日期维

在这篇文章中,我将解释如何创建一个简单的日期维度以在数据仓库和BI解决方案中使用它。因此,如果您需要一种快速简便的方法来制作一个支持最常用的日期元素(例如,

·         整数日期键

·         不同的日期格式

·         25美分硬币

·         月份名称

·         周数

·         一周中的天

·         一年中的一天

·         是月底的一天

·         不可用(不适用)行

由于许多人仍在使用SQL Server 2008及更早版本,因此我放置了支持SQL Server 2008的代码。 以及SQL Server2012。但是,我评论了2008年的内容。

我本人一直在寻找一个简单的日期维度,但我在Internet上找到了很多。但是,我对大多数解决方案不满意的是,这些解决方案都是使用while循环逐行地插入数据,这在第一次填充表时似乎有点慢。这是我决定重新发明轮子并制造出既简单又易于实现,同时又运行很快的东西的第一个原因。

现在,让我们谈谈解决方案。以下代码使用递归公用表表达式(CTE)生成日期维,我称其为DimDate。

首先创建一个DimDate。

 

如果 存在 (选择 1 系统.对象 哪里 object_id = OBJECT_ID(N’DimDate’))

创建 [dbo].[DimDate](

       [DateAlternateKey] [日期] 空值,

       [UKDateFormat] [varchar](10) 空值,

       [USDateFormat] [varchar](10) 空值,

       [GermanDateFormat] [varchar](10) 空值,

       [FullDate] [varchar](11) 空值,

       [DateKey] [int] 空值 ,

       [年份] [int] 空值,

       [季度] [int] 空值,

       [QuarterDescription] [varchar](6) 空值,

       [月] [varchar](9) 空值,

       [MonthNumber] [int] 空值,

       [MonthYear] [varchar](7) 空值,

       [WeekNumber] [int] 空值,

       [周号说明] [varchar](5) 空值,

       [DayOfWeek] [varchar](10) 空值,

       [DayOfMonth] [int] 空值,

       [DayOfYear] [int] 空值,

       [EndOfMonth] [int] 空值,

       [IsDayEndOfMonth] [varchar](3) 空值

) [主]

 

 

笔记:

·         如果出于任何原因,您不想先创建DimDate然后再填充它,只需取消注释“–into 点心日期代码中的一行。

·         如果您决定先创建DimDate表,则取消注释 –插入[dbo]。[DimDate]线。

·         开始日期设置为 ‘1990-01-01’ 并将结束日期定义为 ‘2030-12-31’.

·         由于CTE中默认的最大递归数为100,因此 选项 (最大递归0) 应该添加到代码中。

·         为了支持不可用(N / A)日期,如果要实现SSAS表格模型,则使用 ‘1900-01-01’ 联盟 所有,否则您可以使用 空值 代替。

·         您可以在解决方案中添加您自己国家的公共假期,但是,我这样做并不是为了简化解决方案。

·         您也可以计算财政(财务)日期,但是为了简化起见,我也没有这样做。 (但是,将来我可能会在解决方案中添加财务日期。谁知道?!)

 

; 点心日期CTE

(选择 (‘1990-01-01’ 日期) 日期_

 联盟 所有

 选择 约会(,1,点心日期CTE.日期_) 日期_ 点心日期CTE 哪里 日期_ <‘2030-12-31’

)

–插入[dbo]。[DimDate]–如果现有DimDate,请使用此部分

选择 日期_ DateAlternateKey

       , 兑换(VARCHAR(10), 日期_, 103) UKDateFormat —英国/法国日期格式

       , 兑换(VARCHAR(10), 日期_, 101) USDateFormat

       , 兑换(VARCHAR(10), 日期_, 104) GermanDateFormat

       , 兑换(VARCHAR(11), 日期_, 106) 完整日期

       , (兑换(VARCHAR(8),日期_, 112) 整型) 日期键

       , (日期_)

       , 日期NAME(qq, 日期_) 25美分硬币

       –, ‘Qtr ‘+ 投(DATENAME(qq,DATE_)作为VARCHAR(1))QuarterDescription—对于SQL Server 2008和更早版本

       , 康卡特(‘Qtr ‘, 日期NAME(qq, 日期_)) 季度说明  –2012年及以上受支持

       , 日期NAME(M,日期_)

       , (日期_) 月数

       , 对(兑换(VARCHAR(10), 日期_, 103), 7) 年月

       , 日期NAME(,日期_) 周数

       –, ‘Wk ‘+ 投(DATENAME(wk,DATE_)作为VARCHAR(2))WeekNumber描述—对于SQL Server 2008和更早版本

       , 康卡特(‘Wk ‘, 日期NAME(,日期_)) 周号说明  –2012年及以上受支持

       , 日期NAME(dw,日期_) 日间周

       , 日期PART(dd,日期_) DayOfMonth

       , 日期NAME(dy,日期_) 一年中的一天

       –,DATEPART(dd,DATEADD(dd,-1,DATEADD(mm,DATEDIFF(mm,0,DATE _)+ 1,0)))EndOfMonth—对于SQL Server 2008和更早版本

       –,当DATEPART(dd,DATEADD(dd,-1,DATEADD(mm,DATEDIFF(mm,0,DATE _)+ 1,0)))= 日期PART(dd,DATE_)THEN时‘Yes’ ELSE ‘No’END IsDayEndOfMonth—对于SQL Server 2008和更早版本

       , 日期PART(dd,EOMONTH(日期_)) 月底 –2012年及以上受支持

       , IIF(日期PART(dd,EOMONTH(日期_))= 日期PART(dd,日期_), ‘Yes’,‘No’) IsDayEndOfMonth –2012年及以上受支持

–INTO 点心日期 –使用此行创建一个新的DimDate表

点心日期CTE

联盟 所有 –Supporting 不适用 dates

选择 ‘1900-01-01’ –对于SSAS表格模型,这应该是1900-01-01。否则,可以使用NULL代替。

       , ‘N/A’

       , ‘N/A’

       , ‘N/A’

       , ‘N/A’

       , 1

       , 1

       , 1

       , ‘N/A’

       , ‘N/A’

       , 1

       , ‘N/A’

       , 1

       , ‘N/A’

       , ‘N/A’

       , 1

       , 1

       , 1

       , ‘N/A’

选项 (MAXRECURSION 0)

 

结果应该是这样的:

 

图片

请享用!

One thought on “使用递归公用表表达式(CTE)创建简单的日期维

有什么想法吗?在这里与我们分享:

该网站使用Akismet减少垃圾邮件。 了解如何处理您的评论数据.