时间维度表的创建
时间维度是数据仓库最常用的维度，时间维度表创建可以用下面的代码。
方法一：使用临时表
/*起始年后的 152 年 365*151 +37 = 55152 /
/*先建好临时表 以用作主键编号 */
select top 55152 identity(int,1,1) as iid
into #tmp
from sysobjects a,sysobjects b,sysobjects c
/*写好数据select into 时间维度表 */
select
iid as 时间主键,
dateadd(day,iid-1,'1949-01-01') as 时间,
year(dateadd(day,iid-1,'1949-01-01')) as 年份,
month(dateadd(day,iid-1,'1949-01-01')) as 月份,
day(dateadd(day,iid-1,'1949-01-01')) as 日期,
datepart(quarter,(dateadd(day,iid-1,'1949-01-01'))) as 季度,
datepart(weekday,(dateadd(day,iid-1,'1949-01-01'))) as 星期,
day(dateadd(day,iid-1,'1949-01-01')) as 月的第几天,
datepart(week,(dateadd(day,iid-1,'1949-01-01'))) as 年的第几周
into tj_web_time_dimension
from #tmp
/*设置主键*/
alter table tj_web_time_dimension add constraint addPrimarykey primary key(时间主键)
方法二： 使用循环
CREATE TABLE [dbo].[tj_web_time_dimension](
[时间主键] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED ,
[时间] [datetime] NULL,
[年份] [int] NULL,
[月份] [int] NULL,
[日期] [int] NULL,
[季度] [int] NULL,
[星期] [int] NULL,
[月的第几天] [int] NULL,
[年的第几周] [int] NULL,
)
declare @beginDate datetime, @endDate datetime, @tempdate datetime
set @beginDate = '2000-1-1'
set @endDate = '2010-1-1'
set @tempDate = @beginDate
while @tempDate <= @endDate
begin
insert into tj_web_time_dimension([时间] ,[年份],[月份] ,[日期] ,[季度] ,[星期],[月的第几天],[年的第几周])
values(
@tempDate,
year(@tempDate),
month(@tempDate),
day(@tempDate),
datepart(quarter,@tempDate),
datepart(weekday,@tempDate),
day(@tempDate),
datepart(week,@tempDate)
)
set @tempDate = dateadd(day,1,@tempDate)
end