SQL - 生成日期维度表,生成日期配置表

最新推荐文章于 2023-08-05 08:52:25 发布

今天的砖很烫手

最新推荐文章于 2023-08-05 08:52:25 发布

阅读量1.9k

点赞数 1

分类专栏： SQL

本文链接：https://blog.csdn.net/weixin_43125677/article/details/103406411

版权

SQL 专栏收录该内容

28 篇文章 1 订阅

订阅专栏

SQL - 生成日期维度表,生成日期配置表

建表

CREATE TABLE DIM_DATETIME(
			calendar_id number NOT NULL,
			date_name varchar2(200),
			date_name_cn varchar2(200),
			calendar_date date,
			current_day number(10,0),
			month_id number,
			month_name varchar2(200),
			month_name_cn varchar2(200),
			month_number number(10,0),
			month_name_short varchar2(200),
			month_name_short_cn varchar2(200),
			days_in_month number(10,0),
			first_of_month number(10,0),
			last_month_id number(10,0),
			month_end number(10,0),
			quarter_id number,
			quarter_name varchar2(200),
			quarter_name_cn varchar2(200),
			quarter_name_short varchar2(200),
			quarter_name_short_cn varchar2(200),
			year_id number,
			year_name varchar2(200),
			year_name_cn varchar2(200),
			year_week_name varchar2(500),
			create_date timestamp,
			month_week_num varchar2(100),
			month_week_begin varchar2(100),
			month_week_end varchar2(100),
			half_year varchar2(100),
			weekend_flag varchar2(100),
			holidays_flag varchar2(100),
			workday_flag varchar2(100)
);

样例数据

select
      to_number(to_char(sysdate,'yyyyMMdd')) calendar_id,
      to_char(sysdate,'yyyy-MM-dd') date_name,
      to_char(sysdate,'yyyy')||'年'||to_char(sysdate,'MM')||'月'||to_char(sysdate,'dd')||'日' date_name_cn,
      trunc(sysdate) calendar_date, 
      to_number(to_char(sysdate,'dd')) current_day,
      to_number(to_char(sysdate,'yyyyMM')) month_id, 
      to_char(sysdate,'yyyy-MM') month_name,
      to_char(sysdate,'yyyy')||'年'||to_char(sysdate,'MM')||'月' month_name_cn,
      to_number(to_char(sysdate,'MM')) month_number,  
      to_char(sysdate,'MM') month_name_short,
      to_char(sysdate,'MM')||'月' month_name_short_cn,
      to_number(to_char(last_day(sysdate),'dd')) days_in_month, 
      to_number(to_char(trunc(sysdate,'MM'),'yyyyMMdd')) first_of_month, 
      to_number(to_char(add_months(sysdate,-1),'yyyyMM')) last_month_id,
      to_number(to_char(last_day(sysdate),'yyyyMMdd')) month_end,
      to_number(to_char(sysdate,'yyyyq')) quarter_id, 
      to_char(sysdate,'yyyy-q') quarter_name,
      to_char(sysdate,'yyyy')||'年'||to_char(sysdate,'q')||'季度' quarter_name_cn,
      to_char(sysdate,'q') quarter_name_short,
      to_char(sysdate,'q')||'季度' quarter_name_short_cn,
      to_number(to_char(sysdate,'yyyy')) year_id,
      to_char(sysdate,'yyyy') year_name,
      to_char(sysdate,'yyyy')||'年' year_name_cn,
      to_char(sysdate,'yyyy')||'年'||to_char(sysdate,'iw')||'周' year_week_name,
      systimestamp create_date, 
      to_number(to_char(sysdate-1,'d')) month_week_num,
      to_char(trunc(sysdate,'d')+1,'yyyy-MM-dd') month_week_begin,
      to_char(trunc(sysdate,'d')+7,'yyyy-MM-dd') month_week_end,
      case when to_number(to_char(sysdate,'MM'))<7 then to_char(sysdate,'yyyy')||'年上半年' else to_char(sysdate,'yyyy')||'年下半年' end half_year,
      null weekend_flag,
      null holidays_flag,
      case when to_number(to_char(sysdate,'d')) in (1,7) then '非工作日' else '工作日' end workday_flag
  from dual

测试插入数据


----------------------------通过循环向里面插入数据-------------------------
DECLARE 
  BeginDate date:=to_date('20180101','yyyy-mm-dd');  
  EndDate date:=to_date('20190101','yyyy-mm-dd');
  
  BEGIN
    WHILE BeginDate<=EndDate LOOP
    INSERT INTO DIM_DATETIM 
    (calendar_id  ,
      date_name  ,
      date_name_cn ,
      calendar_date  ,
      current_day  ,
      month_id  ,
      month_name  ,
      month_name_cn  ,
      month_number  ,
      month_name_short  ,
      month_name_short_cn ,
      days_in_month ,
      first_of_month ,
      last_month_id ,
      month_end  ,
      quarter_id  ,
      quarter_name  ,
      quarter_name_cn  ,
      quarter_name_short ,
      quarter_name_short_cn ,
      year_id  ,
      year_name  ,
      year_name_cn  ,
      year_week_name  ,
      create_date  ,
      month_week_num  ,
      month_week_begin ,
      month_week_end ,
      half_year  ,
      weekend_flag  ,
      holidays_flag,
      workday_flag 
         )
values(
      to_number(to_char(BeginDate,'yyyyMMdd')) ,
      to_char(BeginDate,'yyyy-MM-dd') ,
      to_char(BeginDate,'yyyy')||'年'||to_char(sysdate,'MM')||'月'||to_char(sysdate,'dd')||'日' ,
      trunc(BeginDate) , 
      to_number(to_char(BeginDate,'dd')) ,
      to_number(to_char(BeginDate,'yyyyMM')) , 
      to_char(BeginDate,'yyyy-MM') ,
      to_char(BeginDate,'yyyy')||'年'||to_char(BeginDate,'MM')||'月' ,
      to_number(to_char(BeginDate,'MM')) ,  
      to_char(BeginDate,'MM') ,
      to_char(BeginDate,'MM')||'月' ,
      to_number(to_char(last_day(BeginDate),'dd')) , 
      to_number(to_char(trunc(BeginDate,'MM'),'yyyyMMdd')) , 
      to_number(to_char(add_months(BeginDate,-1),'yyyyMM')) ,
      to_number(to_char(last_day(BeginDate),'yyyyMMdd')) ,
      to_number(to_char(BeginDate,'yyyyq')) , 
      to_char(BeginDate,'yyyy-q') ,
      to_char(BeginDate,'yyyy')||'年'||to_char(BeginDate,'q')||'季度' ,
      to_char(BeginDate,'q') ,
      to_char(BeginDate,'q')||'季度' ,
      to_number(to_char(BeginDate,'yyyy')) ,
      to_char(BeginDate,'yyyy') ,
      to_char(BeginDate,'yyyy')||'年' ,
      to_char(BeginDate,'yyyy')||'年'||to_char(BeginDate,'iw')||'周' ,
      systimestamp , 
      to_number(to_char(BeginDate-1,'d')) ,
      to_char(trunc(BeginDate,'d')+1,'yyyy-MM-dd') ,
      to_char(trunc(BeginDate,'d')+7,'yyyy-MM-dd') ,
      case when to_number(to_char(BeginDate,'MM'))<7 then to_char(BeginDate,'yyyy')||'年上半年' else to_char(BeginDate,'yyyy')||'年下半年' end ,
         null ,
      null ,
      case when to_number(to_char(BeginDate,'d')) in (1,7) then '非工作日' else '工作日' end 
   );
   --每次提交数据
   commit;
    BeginDate:=BeginDate+1;
  END LOOP;
  end;
  --循环执行完，要提交