時間維度在OLAP中是很常用,我們一般分為日期維度和時刻維度組成。我整理了如何生成他們的語句如下
1)日期維度[Dim_Date]

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]

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ī)微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
