oracle中的维度,Oracle DB 创造时间维度

╰+攻爆jí腚メ 2022-10-21 13:52 225阅读 0赞

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

方面从多个日期角度：

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

(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;

oracle中的维度,Oracle DB 创造时间维度

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 Oracle 数据仓库简单时间维度表的创建

相关 spark-sql创建时间维度表

相关 oracle中的维度,Oracle DB 创造时间维度

相关时间维度创建

相关维度和指标

相关维度和指标

相关 NumPy中的维度Axis

相关 Pytorch 维度拼接与维度拆分

相关维度解释

相关数据降维度