Oracle DB 创建时间维度

ogyrmghtzl

　　在数据仓库的创建过程中，往往需要创建日期维度来为以后的数据分析来服务。

方面从多个日期角度：

如：年-月-日，年-季度-月-日，年-周-日

(1) 创建表的脚本如下
　　（存储过程的创建过程中有一步操作是向time_dimension表中插入数据，所以首先需要创建好此表）

create table TIME_DIMENSION
(
the_date     NUMBER not null,
date_name    NVARCHAR2(15),
the_year     NUMBER,
year_name    NVARCHAR2(10),
the_quarter  VARCHAR2(10),
quarter_name NVARCHAR2(10),
the_month    NUMBER,
month_name   NVARCHAR2(10),
the_week     NUMBER,
week_name    NVARCHAR2(10),
week_day     NVARCHAR2(10)
)
tablespace DEV1_ODI_USER
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64
next 1
minextents 1
maxextents unlimited
);

　　(2) 存储过程脚本如下

create or replace PROCEDURE
SP_CREATE_TIME_DIMENSION(begin_date in varchar2,end_date in varchar2) is
dDate date;
v_the_date number;
v_the_year varchar2(4);
v_the_quarter varchar2(2);
v_the_month varchar2(10);
v_the_month2 varchar2(2);
v_the_week varchar2(2);
v_the_day varchar2(10);
v_the_day2 varchar2(2);
v_week_day nvarchar2(10);
adddays int;
BEGIN
adddays := 1 ;
dDate := to_date(begin_date,'yyyymmdd');
WHILE (dDate <= to_date(end_date,'yyyymmdd'))
loop
v_the_date := to_number(to_char(dDate,'yyyymmdd'));--key值
v_the_year:= to_char(dDate, 'yyyy');--年
v_the_quarter := to_char(dDate, 'q');--季度
v_the_month:=to_char(dDate, 'mm');--月份(字符型)
v_the_month2:=to_number(to_char(dDate, 'mm'));--月份(数字型)
v_the_day:=to_char(dDate, 'dd');--日(字符型)
v_the_day2:=to_char(dDate, 'dd');
v_the_week:= to_char(dDate,'fmww');--年的第几周
v_week_day := to_char(dDate, 'day'); --星期几

insert into time_dimension(the_date,date_name,the_year,year_name,
the_quarter,quarter_name,the_month,
month_name,the_week,week_name,week_day)
values(v_the_date,v_the_year||'年'||v_the_month2||'月'||v_the_day2||'日',v_the_year,v_the_year||'年',
v_the_year||'Q'||v_the_quarter,v_the_year||'年'||v_the_quarter||'季度',to_number(v_the_year||v_the_month),
v_the_year||'年'||v_the_month2||'月',v_the_week,'第'||v_the_week||'周',
v_week_day);
dDate :=  dDate + adddays;
END loop;
end SP_CREATE_TIME_DIMENSION;

　　(3) 命令窗口的执行脚本如下

SQL> exec SP_CREATE_TIME_DIMENSION(20140101,20140630);
PL/SQL procedure successfully completed
SQL> commit;
Commit complete
SQL> select * from time_dimension;