数据仓库日期维度表 Oracle，DB2，Sqlserver

浏览: 4688

  数据仓库是BI平台的基础，主要有两大作用：

1、统一的，高质量的集团信息视图，更好的保护企业数据资产

2、为数据分析和挖掘提供数据基础。

   数据仓库中典型的设计方案是维度建模，有兴趣的话大家可以看看这本书。

1.png

    维度建模中不管是什么行业都绕不开日期维度表。日期维度表或者说全部的维度表，作用都是为了能更好的展现和分析数据。 比如零售行业中要展示每年工作日和周末的销售情况，每个季度销售情况。不用日期维度表，也能展现，只是实现起来稍微麻烦些。

    下面将提供oracle，DB2，Sqlserver数据仓库日期维度表的初始化，里面的字段可能考虑不全，欢迎大家补充。节假日和是否放假这两个字段的更新见文章

python 爬虫更新日期维度表中的节假日

---------------------------------------------------------------------oracle--------------------------------------------------------------

2.png

----日期维度表

create table DIM_DATE(

--2016-08-13

DateKey date,

--2016年8月13日

DateCH NVARCHAR2(16),

--2016

DimYear NVARCHAR2(8),

--季度

Quar int,

QuarCH NVARCHAR2(16),

QuarEN NVARCHAR2(8),

--2016-08

YearMonth NVARCHAR2(8),

--2016年8月

YearMonthCH VARCHAR2(20),

--08

DimMonth NVARCHAR2(8),

--8月

MonthCH NVARCHAR2(8),

--Aug

MonthEN NVARCHAR2(8),

--日 13

DimDay  NVARCHAR2(8),

--旬

Ten  int,

TenCH NVARCHAR2(8),

--周W12

DimWeek NVARCHAR2(8),

--星期几

WeekDayCH NVARCHAR2(8),

WeekDayEN NVARCHAR2(8),

--是否节假日

IsHolidays int check(IsHolidays=0 or IsHolidays=1)

)

---DROP TABLE DIM_DATE



----------------------------通过循环向里面插入数据-------------------------

DECLARE 

  BeginDate date:=to_date('20120101','yyyy-mm-dd');  

  EndDate date:=to_date('20190101','yyyy-mm-dd');

  DateKey date;

  DateCH NVARCHAR2(16);

  DimYear NVARCHAR2(8);

  Quar int;

QuarCH NVARCHAR2(16);

QuarEN NVARCHAR2(8);

YearMonth NVARCHAR2(8);

YearMonthCH VARCHAR(20);

DimMonth NVARCHAR2(8);

MonthCH NVARCHAR2(8);

MonthEN NVARCHAR2(8);

DimDay  NVARCHAR2(8);

Ten  int;

TenCH NVARCHAR2(8);

DimWeek NVARCHAR2(8);

WeekDayCH NVARCHAR2(8);

WeekDayEN NVARCHAR2(8);

IsHolidays int;

BEGIN

  WHILE BeginDate<=EndDate LOOP

     DateKey:=BeginDate;

     DateCH:=to_char(BeginDate,'yyyy')||'年'||to_char(BeginDate,'mm')||'月'||to_char(BeginDate,'dd')||'日';

     DimYear:=to_char(BeginDate,'yyyy');

     Quar:=to_char(BeginDate, 'q');

    CASE to_char(BeginDate, 'q')

      WHEN '1' THEN QuarCH:='第一季度';

      WHEN '2' THEN QuarCH:='第二季度';

      WHEN '3' THEN QuarCH:='第三季度';

      ELSE QuarCH:='第四季度';

      END CASE;

    CASE to_char(BeginDate, 'q')

      WHEN '1' THEN QuarEN:='Q1';

      WHEN '2' THEN QuarEN:='Q2';

      WHEN '3' THEN QuarEN:='Q3';

      ELSE QuarEN:='Q4';

      END CASE;   

    YearMonth:=to_char(BeginDate,'yyyy-mm');

    YearMonthCH:=to_char(BeginDate,'yyyy')||'年'||to_char(BeginDate,'mm')||'月';

    DimMonth:=to_char(BeginDate,'mm');  

    MonthCH:=to_char(BeginDate,'mm')||'月';

    CASE to_char(BeginDate,'mm')

      WHEN '01' THEN MonthEN:='Jan';

      WHEN '02' THEN  MonthEN:='Feb';

      WHEN '03' THEN  MonthEN:='Mar';

      WHEN '04' THEN  MonthEN:='Apr';

      WHEN '05' THEN  MonthEN:='May';

      WHEN '06' THEN  MonthEN:='Jun';

      WHEN '07' THEN  MonthEN:='Jul';

      WHEN '08' THEN  MonthEN:='Aug';

      WHEN '09' THEN  MonthEN:='Sept';

      WHEN '10' THEN  MonthEN:='Oct';

      WHEN '11' THEN  MonthEN:='Nov';

      ELSE  MonthEN:='Dec';

      END CASE;

     DimDay:=to_char(BeginDate,'dd');



  IF to_Number(to_char(BeginDate,'dd')) <=10  THEN  Ten:=1;

    ELSIF   to_Number(to_char(BeginDate,'dd')) >20 THEN Ten:=2;

     ELSE Ten:=3;

   END IF;

    IF to_Number(to_char(BeginDate,'dd')) <=10  THEN  TenCH:='上旬';

    ELSIF   to_Number(to_char(BeginDate,'dd')) >20 THEN TenCH:='中旬';

     ELSE TenCH:='下旬';

   END IF;   

    DimWeek:=to_char(BeginDate,'fmww');

    WeekDayCH:=to_char(BeginDate, 'day');

    case to_char(BeginDate, 'day')

      WHEN '星期一' then WeekDayEN:='Mon';

      WHEN '星期二' then WeekDayEN:='Tue';

      WHEN '星期三' then WeekDayEN:='Wed';

      WHEN '星期四' then WeekDayEN:='Thu';

      WHEN '星期五' then WeekDayEN:='Fri';

      WHEN '星期六' then WeekDayEN:='Sat';

      ELSE WeekDayEN:='Sun';

      END CASE;    

    IsHolidays:=0;



insert into DIM_DATE (DateKey,DateCH,DimYear,Quar,QuarCH,QuarEN,YearMonth,YearMonthCH,DimMonth,MonthCH,MonthEN,

DimDay,Ten,TenCH,DimWeek,WeekDayCH,WeekDayEN,IsHolidays) 

values(DateKey,DateCH,DimYear,Quar,QuarCH,QuarEN,YearMonth,YearMonthCH,DimMonth,MonthCH,MonthEN,

DimDay,Ten,TenCH,DimWeek,WeekDayCH,WeekDayEN,IsHolidays);



 BeginDate:=BeginDate+1;

    END LOOP;

    end;

--循环执行完，要提交

commit;



---TRUNCATE TABLE DIM_DATE

---------------------------------------------------------------------DB2--------------------------------------------------------------

3.png

----日期维度表

create table DIM_DATE(

--2016-08-13

DateKey date,

--2016年8月13日

DateCH VARCHAR(26),

--2016

DimYear VARCHAR(8),

--季度

Quar VARCHAR(8),

QuarCH VARCHAR(16),

QuarEN VARCHAR(8),

--2016-08

YearMonth VARCHAR(16),

--2016年8月

YearMonthCH VARCHAR(20),

--08

DimMonth VARCHAR(8),

--8月

MonthCH VARCHAR(8),

--Aug

MonthEN VARCHAR(8),

--日 13

DimDay  VARCHAR(8),

--旬

TenCH  VARCHAR(8),

TenEH VARCHAR(18),

--周W12

DimWeek VARCHAR(8),

--星期几

WeekDayCH VARCHAR(18),

WeekDayEN VARCHAR(18),

--是否节假日

IsHolidays int check(IsHolidays=0 or IsHolidays=1)

);



--drop table DIM_DATE;

begin atomic

declare indate date;

set indate=to_date('2015-01-01','yyyy-mm-dd');

while indate<=to_date('2016-01-01','yyyy-mm-dd') do

insert into DIM_DATE values(indate,year(indate)||'年'||month(indate)||'月'||day(indate)||'日',

year(indate),to_char(indate, 'q'),

 case to_char(indate, 'q') when '1' then '第一季度'  when '2' then '第二季度'  when '3' then '第三季度'else '第四季度' end,

case to_char(indate, 'q') when '1' then 'Q1'  when '2' then 'Q2'  when '3' then 'Q3'else 'Q4' end,

year(indate)||'-'||month(indate),

year(indate)||'年'||month(indate)||'月',

to_char(month(indate)),

month(indate)||'月',

case month(indate)

when '1' then  'Jan'

when '02' then 'Feb'

when '03' then 'Mar'

when '04' then 'Apr'

when '05' then 'May'

when '06' then 'Jun'

when '07' then 'Jul'

when '08' then 'Aug'

when '09' then 'Sept'

when '10' then 'Oct'

when '11' then 'Nov'

else 'Dec' end,

day(indate),

case  when day(indate)<=10  then '上旬'

  when day(indate)>20 then '下旬'

 else '中旬' end,

case  when day(indate)<=10  then 'early month'

  when day(indate)>20 then 'late month'

 else 'mid month' end,

 week(indate),

 case DAYOFWEEK_ISO(indate)

 when 1 then '星期一'

 when 2 then '星期二'

 when 3 then '星期三'

 when 4 then '星期四'

 when 5 then '星期五'

 when 6 then '星期六'

 else '星期天' end,

 DAYNAME(indate),

 0

);

set indate=indate+1 days;

end while;

end ;

---------------------------------------------------------------------SQLServer--------------------------------------------------------------

4.png

create table DIM_DATE(

--2016-08-13

DateKey date,

--2016年8月13日

DateCH NVARCHAR(26),

--2016

DimYear NVARCHAR(8),

--季度

Quar int,

QuarCH NVARCHAR(16),

QuarEN NVARCHAR(8),

--2016-08

YearMonth NVARCHAR(16),

--2016年8月

YearMonthCH VARCHAR(20),

--08

DimMonth NVARCHAR(8),

--8月

MonthCH NVARCHAR(8),

--Aug

MonthEN NVARCHAR(8),

--日 13

DimDay  NVARCHAR(8),

--旬

Ten  int,

TenCH NVARCHAR(8),

--周W12

DimWeek NVARCHAR(8),

--星期几

WeekDayCH NVARCHAR(18),

WeekDayEN NVARCHAR(18),

--是否节假日

IsHolidays int check(IsHolidays=0 or IsHolidays=1)

)

--drop table DIM_DATE;

DECLARE @BeginDate DATE;    

    

SELECT @BeginDate = '20131230';    

WHILE @BeginDate<='20171231'    

BEGIN    

INSERT INTO Dim_Date    

  

SELECT    

CONVERT(varchar(10),@BeginDate,112)                    AS DateKey,

CONVERT(varchar(6),YEAR(@BeginDate))+'年'+CONVERT(varchar(6),MONTH(@BeginDate))+'月'+

CONVERT(varchar(6),Day(@BeginDate))+'日'  AS DateCH,

YEAR(@BeginDate)                                        AS DimYear,       

  

Datepart(QUARTER,@BeginDate)                            AS Qu,    

CASE    

WHEN Datepart(QUARTER,@BeginDate)=1 then '第一季度'    

WHEN Datepart(QUARTER,@BeginDate)=2 then '第二季度'    

WHEN Datepart(QUARTER,@BeginDate)=3 then '第三季度'    

ELSE '第四季度'    

 END                                                    AS  QuCN,    

 CASE    

WHEN Datepart(QUARTER,@BeginDate)=1 then 'Q1'    

WHEN Datepart(QUARTER,@BeginDate)=2 then 'Q2'    

WHEN Datepart(QUARTER,@BeginDate)=3 then 'Q3'    

ELSE 'Q4'    

 END                                                    AS  QuEN,    

  --CONVERT(varchar(6),YEAR(@BeginDate))+'-'+CONVERT(varchar(6),MONTH(@BeginDate))                AS YearMonth,

  left(CONVERT(char(10), @BeginDate, 120),7)                                                       AS YearMonth,

  CONVERT(varchar(6),YEAR(@BeginDate))+'年'+CONVERT(varchar(6),MONTH(@BeginDate))+'月'          AS YearMonthCH,

 MONTH(@BeginDate)                                      AS DimMonth,    

Day(@BeginDate)                                         AS DimDay, 

 

 

 case    

 when MONTH(@BeginDate)= 1 then '一月'    

 when MONTH(@BeginDate)= 2 then '二月'    

 when MONTH(@BeginDate)= 3 then '三月'    

 when MONTH(@BeginDate)= 4 then '四月'    

 when MONTH(@BeginDate)= 5 then '五月'    

 when MONTH(@BeginDate)= 6 then '六月'    

 when MONTH(@BeginDate)= 7 then '七月'    

 when MONTH(@BeginDate)= 8 then '八月'    

 when MONTH(@BeginDate)= 9 then '九月'    

 when MONTH(@BeginDate)= 10 then '十月'    

 when MONTH(@BeginDate)= 11 then '十一月'    

 else '十二月'    

 end                                                   AS MonthCN,    

 case    

 when MONTH(@BeginDate)= 1 then 'Jan'    

 when MONTH(@BeginDate)= 2 then 'Feb'    

 when MONTH(@BeginDate)= 3 then 'Mar'    

 when MONTH(@BeginDate)= 4 then 'Apr'    

 when MONTH(@BeginDate)= 5 then 'May'    

 when MONTH(@BeginDate)= 6 then 'Jun'    

 when MONTH(@BeginDate)= 7 then 'Jul'    

 when MONTH(@BeginDate)= 8 then 'Aug'    

 when MONTH(@BeginDate)= 9 then 'Sept'    

 when MONTH(@BeginDate)= 10 then 'Oct'    

 when MONTH(@BeginDate)= 11 then 'Nov'    

 else 'Dec'    

 end                                                  AS MonthEN,    

 CASE    

 when DATEPART(DAY,@BeginDate)<=10 THEN 1    

 WHEN DATEPART(DAY,@BeginDate)>20 THEN 3    

 ELSE 2                  

 END                                                  AS Ten,    

 CASE    

 when DATEPART(DAY,@BeginDate)<=10 THEN '上旬'    

 WHEN DATEPART(DAY,@BeginDate)>20 THEN '下旬'    

 ELSE '中旬'                  

 END                                                  AS  TenCN,    

 DATEPART(WEEK,@BeginDate)-1                          AS Dim_Week,    

 DATENAME(WEEKDAY,@BeginDate)                         AS WeekDayCN,    

 case    

 when DATENAME(WEEKDAY,@BeginDate)= '星期一' then 'Mon'    

 when DATENAME(WEEKDAY,@BeginDate)= '星期二' then 'Tue'    

 when DATENAME(WEEKDAY,@BeginDate)= '星期三' then 'Wed'    

 when DATENAME(WEEKDAY,@BeginDate)= '星期四' then 'Thu'    

 when DATENAME(WEEKDAY,@BeginDate)= '星期五' then 'Fri'   

 when DATENAME(WEEKDAY,@BeginDate)= '星期六' then 'Sat'     

 else 'Sun'    

 end                                                  AS WeekDayEN,  

   

 0 as IsDayOff

 SET @BeginDate=DATEADD(DAY,1,@BeginDate);    

 end;

Cognos,BI技术交流群 347488829

Tableau,BI技术交流群 161474757

推荐 14
梁勇 BAO胖子 黄cindt 飞舞的锄头 郑大鹏 jsjhegang Jason_Huang 星痕 yj470830995 chen uynkfeng 笑与梦 同样温柔 岚欣 fstar
本文由 攻城的狮子 创作，采用 知识共享署名-相同方式共享 3.0 中国大陆许可协议 进行许可。
转载、引用前需联系作者，并署名作者且注明文章出处。
本站文章版权归原作者及原出处所有 。内容为作者个人观点， 并不代表本站赞同其观点和对其真实性负责。本站是一个个人学习交流的平台，并不用于任何商业目的，如果有任何问题，请及时联系我们，我们将根据著作权人的要求，立即更正或者删除有关内容。本站拥有对此声明的最终解释权。

8 个评论

黄cindt

黄cindt

很有用的
2016-08-22 10:22
冷文强

冷文强

很好的哟
2016-08-22 15:30
jsjhegang

jsjhegang

表已经建好，如果有工作日节假日标志，上一工作日，下一工作日感觉实用性更好，不过这样 就需要维护该表了
2016-08-25 22:19
wrh860927

wrh860927

这个sqlserver的周有问题啊，一年第一周怎么是0呢？
2016-09-30 16:41
攻城的狮子

攻城的狮子 回复 wrh860927

当时这个维度是按老外的标准涉及到，每周是从星期天开始，比如2016-01-01和2016-01-02 它们其实是属于15年最后一周的。
2016-10-05 10:33
攻城的狮子

攻城的狮子 回复 攻城的狮子

根据实际情况用updat改下就行了
2016-10-05 10:37
星痕

星痕

有没有mysql的
2016-10-13 11:53
笑与梦 同样温柔

笑与梦 同样温柔

帮了很大的忙~大赞！
2016-11-30 10:22

要回复文章请先登录或注册