数据仓库的日历表 [英] Calendar table for Data Warehouse
本文介绍了数据仓库的日历表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
对于我的数据仓库,我将创建一个日历表,如下所示:
For my data warehouse, I am creating a calendar table as follows:
SET NOCOUNT ON
DROP Table dbo.Calendar
GO
Create Table dbo.Calendar
(
CalendarId Integer NOT NULL,
DateValue Date NOT NULL,
DayNumberOfWeek Integer NOT NULL,
NameOfDay VarChar (10) NOT NULL,
NameOfMonth VarChar (10) NOT NULL,
WeekOfYear Integer NOT NULL,
JulianDay Integer NOT NULL,
USAIsBankHoliday Bit NOT NULL,
USADayName VarChar (100) NULL,
)
ALTER TABLE dbo.Calendar ADD CONSTRAINT
DF_Calendar_USAIsBankHoliday DEFAULT 0 FOR USAIsBankHoliday
GO
ALTER TABLE dbo.Calendar ADD CONSTRAINT
DF_Calendar_USADayName DEFAULT '' FOR USADayName
GO
Declare @StartDate DateTime = '01/01/2000'
Declare @EndDate DateTime = '01/01/2020'
While @StartDate < @EndDate
Begin
INSERT INTO dbo.Calendar
(
CalendarId,
DateValue,
WeekOfYear,
DayNumberOfWeek,
NameOfDay,
NameOfMonth,
JulianDay
)
Values
(
YEAR (@StartDate) * 10000 + MONTH (@StartDate) * 100 + Day (@StartDate), --CalendarId
@StartDate, -- DateValue
DATEPART (ww, @StartDate), -- WeekOfYear
DATEPART (dw, @StartDate), -- DayNumberOfWeek
DATENAME (dw, @StartDate), -- NameOfDay
DATENAME (M, @StartDate), -- NameOfMonth
DATEPART (dy, @StartDate) -- JulianDay
)
Set @StartDate += 1
End
--=========================== Weekends
-- saturday and sunday
UPDATE dbo.Calendar SET USAIsBankHoliday = 1, USADayName += 'Weekend, ' WHERE DayNumberOfWeek IN (1, 7)
--=========================== Bank Holidays
-- new years day
UPDATE dbo.Calendar SET USAIsBankHoliday = 1, USADayName += 'New Year''s Day, ' WHERE (CalendarId % 2000) IN (101)
-- memorial day (last Monday in May)
UPDATE dbo.Calendar
SET USAIsBankHoliday = 1,
USADayName += 'Memorial Day, '
WHERE 1=1
AND CalendarId IN
(
SELECT MAX (CalendarId)
FROM dbo.Calendar
WHERE MONTH (DateValue) = 5
AND DATEPART (DW, DateValue)=2
GROUP BY YEAR (datevalue)
)
-- independence day
UPDATE dbo.Calendar SET USAIsBankHoliday = 1, USADayName += 'Independence Day, ' WHERE (CalendarId % 2000) IN (704)
-- labor day (first Monday in September)
UPDATE dbo.Calendar
SET USAIsBankHoliday = 1,
USADayName += 'Labor Day, '
WHERE 1=1
AND CalendarId IN
(
SELECT MIN (CalendarId)
FROM dbo.Calendar
WHERE MONTH (DateValue) = 9
AND DATEPART (DW, DateValue)=2
GROUP BY YEAR (datevalue)
)
-- thanksgiving day (fourth Thursday in November)
UPDATE dbo.Calendar
SET USAIsBankHoliday = 1,
USADayName += 'Thanksgiving Day, '
WHERE 1=1
AND CalendarId IN
(
SELECT Max (CalendarId)-2
FROM dbo.Calendar
WHERE MONTH (DateValue) = 11
AND DATEPART (DW, DateValue)=7
GROUP BY YEAR (datevalue)
)
-- christmas
UPDATE dbo.Calendar SET USAIsBankHoliday = 1, USADayName += 'Christmas Day, ' WHERE (CalendarId % 2000) IN (1225)
--=========================== Other named days
-- new years eve
UPDATE dbo.Calendar SET USADayName += 'New Year''s Eve, ' WHERE (CalendarId % 2000) IN (1231)
-- black friday (day after thanksgiving day)
UPDATE dbo.Calendar SET USADayName += 'Black Friday, ' WHERE CalendarId IN (SELECT CalendarId+1 From dbo.Calendar Where USADayName like '%Thanksgiving%')
-- christmas eve
UPDATE dbo.Calendar SET USADayName += 'Christmas Eve, ' WHERE (CalendarId % 2000) IN (1224)
-- boxing day
UPDATE dbo.Calendar SET USADayName += 'Boxing Day, ' WHERE (CalendarId % 2000) IN (1226)
--=========================== Remove trailing comma
UPDATE dbo.Calendar SET USADayName = SubString (USADayName, 1, LEN (USADayName) -1) WHERE LEN (USADayName) > 2
SELECT * FROM dbo.Calendar
这是此命令的输出
Here is the output of this command
我已经看到数据架构师以各种方式实现了类似的结构。
I have seen similar structures implemented in various flavours by data architects.
我的问题是:还有哪些其他数据仓库/尺寸样式有用的信息可以添加到此表结构中吗?
My question is: What other data warehousing / dimension style useful information can I add to this table structure?
推荐答案
- 四分之一
- 年份
- 财务/会计年度
- 财务/会计季度
- isWeekend
- isWeekday
- isWorkDay
- WeekId(从年初开始的几周)
- isLastDayofMonth
- DaysSince(例如,自2000年1月1日以来的天数)
- Quarter
- Year
- Financial/Accounting Year
- Financial/Accounting Quarter
- isWeekend
- isWeekday
- isWorkDay
- WeekId (weeks since start of year)
- isLastDayofMonth
- DaysSince (e.g. days since 1/1/2000)
这篇关于数据仓库的日历表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文