Oracle生成日期维度表

时间：2021-08-22 14:07:17

因业务需求，需要生成当前年份的年、季度、月份、天等维度表数据。
效果如下：
Oracle生成日期维度表
Oracle生成日期维度表
Oracle生成日期维度表
Oracle生成日期维度表

创建表结构：

-- Create table
create table DIM_YEAR
(
  year_id     NUMBER not null,
  year_name   VARCHAR2(40),
  start_time  DATE,
  end_time    DATE,
  day_num     NUMBER,
  update_time DATE
);
-- Add comments to the table 
comment on table DIM_YEAR
is '年份表';
-- Add comments to the columns 
comment on column DIM_YEAR.year_id
is '年份编号';
comment on column DIM_YEAR.year_name
is '年份名';
comment on column DIM_YEAR.start_time
is '起始时间';
comment on column DIM_YEAR.end_time
is '结束时间';
comment on column DIM_YEAR.day_num
is '天数';
comment on column DIM_YEAR.update_time
is '载入时间';
-- Create/Recreate primary, unique and foreign key constraints 
alter table DIM_YEAR
add constraint PK_DIM_YEAR primary key (YEAR_ID);
-- Create table
create table DIM_QUARTER
(
  quarter_id   NUMBER not null,
  quarter_name VARCHAR2(40),
  year_id      NUMBER,
  quarter_desc VARCHAR2(40),
  start_time   DATE,
  end_time     DATE,
  day_num      NUMBER,
  update_time  DATE
);
-- Add comments to the table 
comment on table DIM_QUARTER
is '季度表';
-- Add comments to the columns 
comment on column DIM_QUARTER.quarter_id
is '季度编号';
comment on column DIM_QUARTER.quarter_name
is '季度名';
comment on column DIM_QUARTER.year_id
is '年份编号';
comment on column DIM_QUARTER.quarter_desc
is '季度描述';
comment on column DIM_QUARTER.start_time
is '起始时间';
comment on column DIM_QUARTER.end_time
is '结束时间';
comment on column DIM_QUARTER.day_num
is '天数';
comment on column DIM_QUARTER.update_time
is '载入时间';
-- Create/Recreate primary, unique and foreign key constraints 
alter table DIM_QUARTER
add constraint PK_DIM_QUARTER primary key (QUARTER_ID);
-- Create table
create table DIM_MONTH
(
  month_id    NUMBER not null,
  month_name  VARCHAR2(100),
  quarter_id  NUMBER,
  month_desc  VARCHAR2(100),
  start_time  DATE,
  end_time    DATE,
  day_num     NUMBER,
  update_time DATE
);
-- Add comments to the table 
comment on table DIM_MONTH
is '月份表';
-- Add comments to the columns 
comment on column DIM_MONTH.month_id
is '月份编号';
comment on column DIM_MONTH.month_name
is '月份名';
comment on column DIM_MONTH.quarter_id
is '季度编号';
comment on column DIM_MONTH.month_desc
is '月份描述';
comment on column DIM_MONTH.start_time
is '开始时间';
comment on column DIM_MONTH.end_time
is '结束时间';
comment on column DIM_MONTH.day_num
is '天数';
comment on column DIM_MONTH.update_time
is '载入时间';
-- Create/Recreate primary, unique and foreign key constraints 
alter table DIM_MONTH
add constraint PK_DIM_MONTH primary key (MONTH_ID);
-- Create table
create table DIM_DAY
(
  day_id      NUMBER not null,
  day         DATE,
  day_name    VARCHAR2(100),
  start_time  DATE,
  end_time    DATE,
  day_num     NUMBER,
  month_id    NUMBER,
  work        VARCHAR2(32),
  week        VARCHAR2(32),
  week_num    NUMBER,
  update_time DATE
);
-- Add comments to the table 
comment on table DIM_DAY
is '日期表';
-- Add comments to the columns 
comment on column DIM_DAY.day_id
is '日期编号';
comment on column DIM_DAY.day
is '日期';
comment on column DIM_DAY.day_name
is '日期名';
comment on column DIM_DAY.start_time
is '开始时间';
comment on column DIM_DAY.end_time
is '结束时间';
comment on column DIM_DAY.day_num
is '天数';
comment on column DIM_DAY.month_id
is '月份编号';
comment on column DIM_DAY.work
is '工作日';
comment on column DIM_DAY.week
is '周几';
comment on column DIM_DAY.week_num
is '本年度第几周';
comment on column DIM_DAY.update_time
is '载入时间';
-- Create/Recreate primary, unique and foreign key constraints 
alter table DIM_DAY
  add constraint PK_DIM_DAY primary key (DAY_ID);

生成当年数据，SQL如下：

--插入月份数据
begin
for t2 in (select to_char(add_months(trunc(sysdate,'yyyy'), level-1), 'yyyymm') month from dual
connect by level <= 1 * 12) loop
insert into dim_month
SELECT TO_NUMBER(TO_CHAR(to_date(t2.month,'yyyymm'), 'YYYYMM')) MONTH_ID,
 TO_CHAR(to_date(t2.month,'yyyymm'), 'MM') || '月' MONTH_NAME,
 (CASE WHEN TO_CHAR(to_date(t2.month,'yyyymm'), 'MM') <=3 then to_char(to_date(t2.month,'yyyymm'),'yyyy')||'001'
when TO_CHAR(to_date(t2.month,'yyyymm'), 'MM') between 04 and 06 then to_char(to_date(t2.month,'yyyymm'),'yyyy')||'002'
when TO_CHAR(to_date(t2.month,'yyyymm'), 'MM') between 07 and 09 then to_char(to_date(t2.month,'yyyymm'),'yyyy')||'003'
when TO_CHAR(to_date(t2.month,'yyyymm'), 'MM') >=10 then to_char(to_date(t2.month,'yyyymm'),'yyyy')||'004' end) QUARTER_ID,
NULL MONTH_DESC,
 TRUNC(to_date(t2.month,'yyyymm'), 'MM') START_TIME,
 to_date(to_char(LAST_DAY(to_date(t2.month,'yyyymm')),'yyyy/mm/dd')||' 23:59:59','yyyy/mm/dd hh24:mi:ss') END_TIME,
 LAST_DAY(to_date(t2.month,'yyyymm')) - TRUNC(to_date(t2.month,'yyyymm'), 'MM')+1 DAY_NUM,
 SYSDATE UPDATE_TIME
FROM DUAL;
end loop;
commit;
--end;

--插入日数据
--begin
  for t2 in (select to_char(trunc(sysdate,'yyyy')-1 + level, 'yyyymmdd') day from dual
connect by level <= to_date(to_char(sysdate,'yyyy')||'1231', 'yyyymmdd') - to_date(to_char(trunc(sysdate,'yyyy')-1,'yyyymmdd'), 'yyyymmdd')) loop
insert into dim_day
SELECT TO_NUMBER(t2.day) DAY_ID,
 TO_DATE(t2.day,'YYYY/MM/DD') DAY,
 SUBSTR(t2.day,7,2) || '日' MONTH_NAME,
 TO_DATE(t2.day,'YYYY/MM/DD') START_TIME,
 TO_DATE(TO_CHAR(TO_DATE(t2.day,'YYYY/MM/DD'),'YYYY/MM/DD')||' 23:59:59','yyyy/mm/dd hh24:mi:ss') END_TIME,
1 DAY_NUM,
 SUBSTR(t2.day,1，6) MONTH_ID,
NULL WORK,
 to_char(to_date(t2.day,'yyyy-mm-dd'), 'day') WEEK，
 to_char(to_date(t2.day,'yyyymmdd'),'WW') WEEK_NUM,
 SYSDATE UPDATE_TIME
FROM DUAL;
end loop;
commit;
--end;

--插入季度数据
--begin
  FOR T2 IN (select TO_NUMBER(to_char(sysdate,'yyyy')) year from dual) LOOP
INSERT INTO DIM_QUARTER
select T2.YEAR||'001' QUARTER_ID,
'第1季度' QUERATER_NAME,
 T2.YEAR YEAR_ID,
NULL QUARTER_DESC,
 TO_DATE(T2.YEAR||'/1/1','YYYY/MM/DD') START_TIME,
 TO_DATE(T2.YEAR||'/3/31 23:59:59','YYYY/MM/DD HH24:MI:SS') END_TIME,
 TO_DATE(T2.YEAR||'/3/31','YYYY/MM/DD')-TO_DATE(T2.YEAR||'/1/1','YYYY/MM/DD') DAY_NUM,
 SYSDATE UPDATE_TIME
from dual
union
select T2.YEAR||'002' QUARTER_ID,
'第2季度' QUERATER_NAME,
 T2.YEAR YEAR_ID,
NULL QUARTER_DESC,
 TO_DATE(T2.YEAR||'/4/1','YYYY/MM/DD') START_TIME,
 TO_DATE(T2.YEAR||'/6/30 23:59:59','YYYY/MM/DD HH24:MI:SS') END_TIME,
 TO_DATE(T2.YEAR||'/6/30','YYYY/MM/DD')-TO_DATE(T2.YEAR||'/4/1','YYYY/MM/DD') DAY_NUM,
 SYSDATE UPDATE_TIME
from dual
union
select T2.YEAR||'003' QUARTER_ID,
'第3季度' QUERATER_NAME,
 T2.YEAR YEAR_ID,
NULL QUARTER_DESC,
 TO_DATE(T2.YEAR||'/7/1','YYYY/MM/DD') START_TIME,
 TO_DATE(T2.YEAR||'/9/30 23:59:59','YYYY/MM/DD HH24:MI:SS') END_TIME,
 TO_DATE(T2.YEAR||'/9/30','YYYY/MM/DD')-TO_DATE(T2.YEAR||'/7/1','YYYY/MM/DD') DAY_NUM,
 SYSDATE UPDATE_TIME
from dual
union
select T2.YEAR||'004' QUARTER_ID,
'第4季度' QUERATER_NAME,
 T2.YEAR YEAR_ID,
NULL QUARTER_DESC,
 TO_DATE(T2.YEAR||'/10/1','YYYY/MM/DD') START_TIME,
 TO_DATE(T2.YEAR||'/12/31 23:59:59','YYYY/MM/DD HH24:MI:SS') END_TIME,
 TO_DATE(T2.YEAR||'/12/31','YYYY/MM/DD')-TO_DATE(T2.YEAR||'/10/1','YYYY/MM/DD') DAY_NUM,
 SYSDATE UPDATE_TIME
from dual;
END LOOP;
COMMIT;
--插入年份数据
insert into DIM_YEAR
SELECT TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')) YEAR_ID,
 TO_CHAR(SYSDATE, 'YYYY') || '年' YEAR_NAME,
 TRUNC(SYSDATE, 'YYYY') START_TIME,
 to_date(to_char(ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), 12)-1,'yyyy/mm/dd')||' 23:59:59','yyyy/mm/dd hh24:mi:ss') END_TIME,
 ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), 12) - TRUNC(SYSDATE, 'YYYY') DAY_NUM,
 SYSDATE LOAD_TIME
FROM DUAL;
commit;
END;

轻喷~~~

标签：

相关文章