数据仓库的时间维度表 Sql

最新推荐文章于 2024-09-13 14:08:55 发布

原创最新推荐文章于 2024-09-13 14:08:55 发布 · 5.5k 阅读

2 ·

CC 4.0 BY-SA版权

BI 专栏收录该内容

11 篇文章

订阅专栏

本文介绍了一段用于批量处理数据并构建日期维度的代码片段，包括将开始日期从'2011-1-1'更新到'2015-12-31'，并在此期间插入到数据仓库中的'Dim_Date'表中，通过多种日期属性（如年份、季度、月份等）进行详细的数据组织。

Declare @BeginDate Datetime
Set @BeginDate='2011-1-1'
While @BeginDate<'2015-12-31'
Begin
Insert Into [InstagramDataDW].[dbo].[Dim_Date]
Select CONVERT(varchar(10),@BeginDate,112) As DateKey,Year(@BeginDate) as Year,
Case When Month(@BeginDate)>6 then 2 Else 1 End As HalfYear,
DATEPART(QUARTER,@BeginDate) As Quarter,
Case When DATEPART(QUARTER,@BeginDate)=1 Then 'one'
When DATEPART(QUARTER,@BeginDate)=2 Then 'two'
When DATEPART(QUARTER,@BeginDate)=3 Then 'three'
Else 'four' End As QuarterEN,
Month(@BeginDate) As Month,
DATEPART(WEEK,@BeginDate) As [WeekOfYear],
DateName(WEEKDAY,@BeginDate) As Week,
Day(@BeginDate) As Day,
CONVERT(varchar(10),@BeginDate,120) As Date
Set @BeginDate=DATEADD(DAY,1,@BeginDate)
End