时间维度在OLAP中是很常用,我们一般分为日期维度和时刻维度组成。我整理了如何生成他们的语句如下
1)日期维度[Dim_Date]
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUWYxYmZhR2MiNjY3QGMjFGZlNTOzUWZjJGO2YzM1MWMfdWbp9CXt92Yu4GZjlGbh5SZslmZxl3Lc9CX6MHc0RHaiojIsJye.gif)
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]
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUWYxYmZhR2MiNjY3QGMjFGZlNTOzUWZjJGO2YzM1MWMfdWbp9CXt92Yu4GZjlGbh5SZslmZxl3Lc9CX6MHc0RHaiojIsJye.gif)
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