时间维度创建

淩亂°似流年 2022-08-13 12:46 167阅读 0赞

转一篇文章，写的关于时间维度创建的方法，非常快。

原文：[http://space.itpub.net/14321372/viewspace-567541][http_space.itpub.net_14321372_viewspace-567541]

在实施**商业智能**项目的时候最常用到的一个维度表就属**时间维度**表了。下面给出一个时间维度表的例子，并利用自定义函数为时间维度表填充数据。

1.  创建一个时间维度表:

> CREATE TABLE \[dbo\].\[DATE\_DIMENSION\](  
> \[DateID\] \[int\] NULL,  
> \[Date\] \[smalldatetime\] NULL,  
> \[Year\] \[smallint\] NULL,  
> \[Month\] \[tinyint\] NULL,  
> \[WeekDay\] \[tinyint\] NULL,  
> \[DayOfMonth\] \[tinyint\] NULL,  
> \[Quarter\] \[tinyint\] NULL,  
> \[MonthName\] \[varchar\](9) NULL,  
> \[DayName\] \[varchar\](9) NULL,  
> \[QuarterName\] \[varchar\](6) NULL,  
> \[DayOfYear\] \[smallint\] NULL
> 
> ) ON \[PRIMARY\]

2. 利用自定义函数为时间维度表填充数据:

> 自定义函数:
> 
> CREATE FUNCTION \[dbo\].\[DATELIST\]  
> (  
> @BEGIN\_DATE AS DATETIME,  
> @END\_TIME AS DATETIME  
> )  
> RETURNS @TEMPTABLE TABLE  
> (  
> DateID INT,  
> Date SMALLDATETIME,  
> Year SMALLINT,  
> Month TINYINT,  
> WeekDay TINYINT,  
> DayOfMonth TINYINT,  
> Quarter TINYINT,  
> MonthName varchar (9),  
> DayName varchar (9),  
> QuarterName varchar (6),  
> DayOfYear smallint  
> )  
> AS  
> BEGIN  
> WHILE(@BEGIN\_DATE<=@END\_TIME)  
> BEGIN  
> INSERT INTO @TEMPTABLE(DateID,Date,Year,Month,WeekDay,DayOfMonth,Quarter,MonthName,DayName,QuarterName,DayOfYear)  
> VALUES(  
> CONVERT(INT,CONVERT(VARCHAR, @BEGIN\_DATE, 112)),  
> @BEGIN\_DATE,  
> DATEPART(YEAR,@BEGIN\_DATE),  
> DATEPART(MONTH,@BEGIN\_DATE),  
> DATEPART(DW,@BEGIN\_DATE),  
> DATEPART(D,@BEGIN\_DATE),  
> DATEPART(Q,@BEGIN\_DATE),  
> CASE DATEPART(MM,@BEGIN\_DATE)  
> WHEN 1 THEN 'January'  
> WHEN 2 THEN 'February'  
> WHEN 3 THEN 'March'  
> WHEN 4 THEN 'April'  
> WHEN 5 THEN 'May'  
> WHEN 6 THEN 'June'  
> WHEN 7 THEN 'July'  
> WHEN 8 THEN 'August'  
> WHEN 9 THEN 'September'  
> WHEN 10 THEN 'October'  
> WHEN 11 THEN 'November'  
> ELSE 'December'  
> END,  
> CASE DATEPART(DW,@BEGIN\_DATE)  
> WHEN 1 THEN 'Sunday'  
> WHEN 2 THEN 'Monday'  
> WHEN 3 THEN 'Tuesday'  
> WHEN 4 THEN 'Wednesday'  
> WHEN 5 THEN 'Thursday'  
> WHEN 6 THEN 'Friday'  
> ELSE 'Saturday'  
> END,  
> CASE DATEPART(Q,@BEGIN\_DATE)  
> WHEN 1 THEN 'First'  
> WHEN 2 THEN 'Second'  
> WHEN 3 THEN 'Third'  
> ELSE 'Fourth'  
> END,  
> DATEPART(DY,@BEGIN\_DATE)  
> )  
> SET @BEGIN\_DATE=@BEGIN\_DATE+1  
> END  
> RETURN  
> END
> 
> **测试**:
> 
> SELECT \* FROM DATELIST('20090101','20091231')
> 
> [![14321372_200903161654181.thumb.jpg][]][14321372_200903161654181.thumb.jpg 1]

[http_space.itpub.net_14321372_viewspace-567541]: http://space.itpub.net/14321372/viewspace-567541
[14321372_200903161654181.thumb.jpg]: http://space.itpub.net/attachments/2009/03/14321372_200903161654181.thumb.jpg
[14321372_200903161654181.thumb.jpg 1]: http://space.itpub.net/batch.download.php?aid=13178