時間維度在OLAP中是很常用，我們一般分為日期維度和時刻維度組成。我整理了如何生成他們的語句如下
1）日期維度[Dim_Date]
View Code
IF EXISTS(
select
* FROM sys.objects WHERE object_id = OBJECT_ID(N
'
[dbo].[Dim_Date]
'
)AND type
in
(N
'
U
'
))
Drop table [dbo].[Dim_Date]
GO
CREATE TABLE [dbo].[Dim_Date](
DateKey [
int
] NOT NULL,
[TheDate] [datetime] NULL,
--
DateTime格式的日期
[TheDateName] [nvarchar](
10
)NULL, --
日期名稱
[TheYear] [smallint] NULL,
--
年份
[TheYearName] [nvarchar](
10
)NULL,--
年份名稱
[TheMonth] [smallint] NULL,
--
月份
[TheMonthName] [nvarchar](
10
)NULL,--
月份名稱
[TheDay] [smallint] NULL,
--
日
[TheDayName] [nvarchar](
10
)NULL,--
日的名稱
[TheQuarter] [smallint] NULL,
--
季度
[TheQuarterName] [nvarchar](
10
)NULL,--
季度名稱
[TheWeek] [smallint] NULL,
--
星期
[TheWeekName] [nvarchar](
10
)NULL,--
星期名稱
[Vacation_Mark] [smallint] NULL,
--
節(jié)假日標(biāo)志
[TheWW] [smallint] NULL,
--
周
[TheWWName] [nvarchar](
20
) NULL --
周名稱
)ON [PRIMARY]
DECLARE
@DateKey
int
,
@TheDate datetime,
@TheDateName nvarchar(
10
),
@TheYear smallint,
@TheYearName nvarchar(
10
),
@TheMonth smallint,
@TheMonthName nvarchar(
10
),
@TheDay smallint,
@TheDayName nvarchar(
10
),
@TheQuarter smallint,
@TheQuarterName nvarchar(
10
),
@TheWeek smallint,
@TheWeekName nvarchar(
10
),
@Vacation_Mark smallint,
@TheWW smallint,
@TheWWName nvarchar(
20
),
@dDate DATETIME,
--
存儲起始日期和結(jié)束日期
@adddays smallint
--
存儲日期增量
SELECT @adddays
=
1
--
日期增量
--_select @dDate =
'
1/1/2000
'
--
當(dāng)前日期
--WHILE @dDate <=
'
12/31/2010
'
--
結(jié)束日期
SELECT @dDate
=
'
1/1/2012
'
--
取當(dāng)前系統(tǒng)時間維度表最大日期
WHILE @dDate
<=
'
12/31/2012
'
--
結(jié)束日期:當(dāng)前日期往后順延5年，可以根據(jù)實際需求設(shè)置時長
BEGIN
select
@DateKey=cast((left(convert(nvarchar,@dDate,
23
),
4
)+substring(convert(nvarchar,@dDate,
23
),
6
,
2
)+
substring(convert(nvarchar,@dDate,
23
),
9
,
2
))
as
int
)
SELECT @TheDate
=
@dDate
SELECT @TheDateName
= REPLACE(CONVERT(nvarchar(
20
),@dDate,
111
),
'
/
'
,
'
-
'
)
SELECT @TheYear
=
DATENAME(yy, @dDate)
SELECT @TheYearName
= CAST(@TheYear
as
nvarchar)+
'
年
'
SELECT @TheMonth
=
DATENAME(mm, @dDate)
SELECT @TheMonthName
=CAST(@TheMonth
as
nvarchar)+
'
月
'
SELECT @TheDay
=
DATENAME(dd, @dDate)
SELECT @TheDayName
= CAST(@TheDay
as
nvarchar)+
'
日
'
SELECT @TheQuarter
=
DATENAME(Quarter, @dDate)
SELECT @TheQuarterName
=
'
第
'
+ CAST(DATENAME(Quarter, @dDate)
as
varchar(
1
))+
'
季度
'
SELECT @TheWeek
=
DATEPART(dw, @dDate)
SELECT @TheWeekName
=
DATENAME(dw,@dDate)
SELECT @Vacation_Mark
= CASE WHEN(@TheWeek =
1
OR @TheWeek =
7
)THEN
1
ELSE
0
END
select
@TheWW=
DATEPART(wk, @dDate)
select
@TheWWName=
'
第
'
+CAST(DATEPART(wk, @dDate)
as
varchar(
2
))+
'
周
'
INSERT INTO Dim_Date(DateKey,TheDate,TheDateName,TheYear,TheYearName,TheMonth,TheMonthName,TheDay,
TheDayName,TheQuarter,TheQuarterName,TheWeek,TheWeekName,Vacation_Mark,TheWW,TheWWName)VALUES
(@DateKey,@TheDate, @TheDateName,@TheYear,@TheYearName,@TheMonth,@TheMonthName,@TheDay,@TheDayName,@TheQuarter,
@TheQuarterName,@TheWeek,@TheWeekName,@Vacation_Mark,@TheWW,@TheWWName)
SELECT @dDate
= @dDate +
@adddays
END
GO
2）時刻維度[Dim_Time]
View Code
IF EXISTS(
select
* FROM sys.objects WHERE object_id = OBJECT_ID(N
'
[dbo].[Dim_Time]
'
)AND type
in
(N
'
U
'
))
Drop table [dbo].[Dim_Time]
CREATE TABLE [dbo].[Dim_Time](
[TimeKey]
int
IDENTITY(
1
,
1
)NOT NULL,
[TheHour] smallint NULL,
--
小時
[TheHourName] nvarchar(
5
) NULL,--
小時名稱
[HalfHour] smallint NULL,
--
半小時
[HalfHourName] nvarchar(
10
)NULL, --
半小時名稱
[Min] smallint NULL,
--
分鐘
[minName] nvarchar(
10
)NULL --
分鐘名稱
)ON [PRIMARY]
DECLARE
@dHour smallint,
@addhours smallint,
@chour nvarchar(
5
),
@dhhour smallint,
@hhourname nvarchar(
10
),
@dMin smallint,
@dMinName nvarchar(
10
)
select
@dHour =
0
--
起始小時
select
@dhhour =
1
--
起始半小時
select
@dmin =
0
--
起始分鐘
WHILE @dHour
<=
23
BEGIN
WHILE @dmin
<=
59
BEGIN
select
@dhhour =
CASE
WHEN @dMin
>=
0
and @dMin <=
29
THEN
1
WHEN @dMin
>=
30
and @dMin <=
59
THEN
2
END
select
@hhourname =
CASE
WHEN @dhhour
=
1
THEN
'
前半小時
'
WHEN @dhhour
=
2
THEN
'
后半小時
'
END
select
@dMinName = cast(@dhour
as
nvarchar)+
'
:
'
+(
case
when len(@dmin)=
1
then(
'
0
'
+cast(@dmin
as
nvarchar))
else
cast(@dmin
as
nvarchar)end)
select
@chour = cast(@dhour
as
nvarchar)+
'
:00
'
insert INTO Dim_Time(TheHour,TheHourName,HalfHour,HalfHourName,Min,MinName)VALUES
(@dHour,@chour,@dhhour,@hhourname,@dMin,@dMinName)
select
@dMin = @dMin +
1
END
select
@dmin =
0
select
@dHour = @dHour +
1
END
GO
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯(lián)系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機(jī)微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
