分享OLAP的时间维度设计语句
2012-11-07 14:33 李明成 阅读(1109) 评论(0) 编辑 收藏 举报时间维度在OLAP中是很常用,我们一般分为日期维度和时刻维度组成。我整理了如何生成他们的语句如下
1)日期维度[Dim_Date]
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.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://images.cnblogs.com/OutliningIndicators/ContractedBlock.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