时间维度在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,
--
节假日标志
[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,
--
存储起始日期和结束日期
@adddays smallint
--
存储日期增量
SELECT @adddays
=
1
--
日期增量
--_select @dDate =
'
1/1/2000
'
--
当前日期
--WHILE @dDate <=
'
12/31/2010
'
--
结束日期
SELECT @dDate
=
'
1/1/2012
'
--
取当前系统时间维度表最大日期
WHILE @dDate
<=
'
12/31/2012
'
--
结束日期:当前日期往后顺延5年，可以根据实际需求设置时长
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
