日期维度表

日期维度表.sql

DROP TABLE IF EXISTS dim_date;

CREATE TABLE dim_date (
	the_date datetime NOT NULL ,
  date varchar(20) NOT NULL  ,
	the_year char(6) ,
	the_halfyear char(6),
	the_quarter char(6),
	the_month char(6),
	the_day char(4),
	the_week char(6),
	the_yearweek char(12),
	key_quarter smallint,
	key_month smallint,
	key_day smallint,
	key_week smallint,
	key_yearweek smallint,
  key_weekyear smallint,
  PRIMARY KEY ( date )  
);


DROP PROCEDURE IF EXISTS usp_Inidimdate; 

CREATE PROCEDURE usp_Inidimdate(in FromDate datetime, in ToDate datetime)
BEGIN
  delete from dim_date
   where the_date between FromDate and ToDate;

  set @tempdate=FromDate;

  while (@tempdate<=ToDate) do
    insert into dim_date (the_date,date,the_year,key_quarter,key_month,key_day,key_week,key_yearweek,key_weekyear) 
    values (@tempdate,date_format(@tempdate,'%Y-%m-%d'),year(@tempdate),quarter(@tempdate),
            month(@tempdate),day(@tempdate),WEEKDAY(@tempdate),
            week(@tempdate),year(@tempdate));
    set @tempdate=DATE_ADD(@tempdate,INTERVAL 1 DAY);
  end while; 

  update dim_date
     set the_halfyear='上半年'
   where key_quarter in ('1','2');

  update dim_date
     set the_halfyear='下半年'
   where key_quarter in ('3','4');

  update dim_date
     set the_quarter='一季度'
   where key_quarter='1';

  update dim_date
     set the_quarter='二季度'
   where key_quarter='2';

  update dim_date
     set the_quarter='三季度'
   where key_quarter='3';

  update dim_date
     set the_quarter='四季度'
   where key_quarter='4';

  update dim_date
     set the_month='一月'
   where key_month='1';

  update dim_date
     set the_month='二月'
   where key_month='2';

  update dim_date
     set the_month='三月'
   where key_month='3';

  update dim_date
     set the_month='四月'
   where key_month='4';

  update dim_date
     set the_month='五月'
   where key_month='5';

  update dim_date
     set the_month='六月'
   where key_month='6';

  update dim_date
     set the_month='七月'
   where key_month='7';

  update dim_date
     set the_month='八月'
   where key_month='8';

  update dim_date
     set the_month='九月'
   where key_month='9';

  update dim_date
     set the_month='十月'
   where key_month='10';

  update dim_date
     set the_month='十一月'
   where key_month='11';

  update dim_date
     set the_month='十二月'
   where key_month='12';

  update dim_date
     set the_week='星期日'
   where key_week='6';

  update dim_date
     set the_week='星期一'
   where key_week='0';

  update dim_date
     set the_week='星期二'
   where key_week='1';

  update dim_date
     set the_week='星期三'
   where key_week='2';

  update dim_date
     set the_week='星期四'
   where key_week='3';

  update dim_date
     set the_week='星期五'
   where key_week='4';

  update dim_date
     set the_week='星期六'
   where key_week='5';

  update dim_date
     set the_year=CONCAT(the_year,'年');

  update dim_date
     set the_day=CONCAT(cast(key_day as char),'日');

  update dim_date
     set key_weekyear=key_weekyear+1, key_yearweek=1
   where weekday(date_format(date,'%Y-12-31'))<>5
     and week(date)=week(date_format(date,'%Y-12-31'));

  update dim_date
     set key_yearweek=key_yearweek+1
   where weekday(DATE_SUB(date,INTERVAL dayofyear(date)-1 DAY))<>6
     and not (key_month=12 and key_yearweek=1); 

  update dim_date
     set the_yearweek=CONCAT(cast(key_weekyear as char),'第',cast(key_yearweek as char),'周');

END;


call usp_Inidimdate('2015-01-01','2020-12-31');