date dimension generate procedure

dim.date.sql

##DELIMITER $$

DROP TABLE IF EXISTS `date_dimension`;
CREATE TABLE `date_dimension` (
  `datekey` int(10) unsigned NOT NULL,
  `date` date NOT NULL,
  `day_of_week` tinyint(4) unsigned NOT NULL COMMENT '一星期中的第几天',
  `day_num_in_month` tinyint(4) unsigned NOT NULL COMMENT '一个月中的第几天',
  `day_num_overall` mediumint(6) unsigned NOT NULL COMMENT '日计数',
  `day_name` varchar(9) NOT NULL COMMENT '星期几',
  `day_abbrev` char(3) NOT NULL COMMENT '星期缩写',
  `weekday_flag` tinyint(4) unsigned NOT NULL COMMENT '周末标记',
  `week_num_in_month` tinyint(4) unsigned NOT NULL COMMENT '当月的第几周',
  `week_num_in_year` tinyint(4) unsigned NOT NULL COMMENT '一年中的第几周',
  `week_num_overall` smallint(6) unsigned NOT NULL COMMENT '周计数',
  `week_begin_date` date NOT NULL COMMENT '本周起始日期',
  `week_end_date` date NOT NULL COMMENT '本周结束日期',
  `week_begin_date_key` int(10) unsigned NOT NULL COMMENT '本周起始日期date_key',
  `week_end_date_key` int(10) unsigned NOT NULL COMMENT '本周起始日期date_key',
  `monthly_week_num_overall` smallint(6) unsigned NOT NULL COMMENT '自然月周计数',
  `halfmonth_num_overall` smallint(6) unsigned NOT NULL COMMENT '半月计数',
  `month` tinyint(4) unsigned NOT NULL COMMENT '月',
  `month_num_overall` smallint(6) unsigned NOT NULL COMMENT '月计数',
  `month_name` varchar(9) NOT NULL COMMENT '月名称',
  `month_abbrev` char(3) NOT NULL COMMENT '月名称缩写',
  `quarter` tinyint(4) unsigned NOT NULL COMMENT '季度',
  `quarter_num_overall` smallint(6) unsigned NOT NULL COMMENT '季度计数',
  `year` smallint(6) unsigned NOT NULL COMMENT '年',
  `yearq` smallint(6) unsigned NOT NULL COMMENT '年季度',
  `yearmo` mediumint(8) unsigned NOT NULL COMMENT '年月',
  `quarter_name` varchar(9) NOT NULL COMMENT '季度全名',
  `month_of_year_name` varchar(9) NOT NULL COMMENT '月份全名',
  PRIMARY KEY (`datekey`),
  KEY `date` (`date`),
  KEY `yearmo` (`yearmo`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='时间维度表';

DROP PROCEDURE IF EXISTS make_date_dimension;

DELIMITER $$
CREATE PROCEDURE `make_date_dimension`()
BEGIN
SET @interday = 1; 
SET @initDate = '2010-01-01';
SET @theDate = @initDate; 

SET @day_num_overall = 0;
SET @init_week_begin_date = DATE_SUB(@initDate, interval DAYOFWEEK(DATE_SUB(@initDate, interval 1 day)) day);
SET @init_year_month = EXTRACT(YEAR_MONTH FROM @initDate);
SET @halfmonth_num_overall = 0;
SET @quarter_num_overall = 0;

SET @last_week_num_in_month = 1;
SET @monthly_week_num_overall = 1;

WHILE @theDate < '2020-01-01' DO
#BEGIN
  SET @datekey = CAST(DATE(@theDate) AS UNSIGNED); #DATE(@theDate) + 0;
  SET @date = @theDate;
  SET @day_of_week = DAYOFWEEK(DATE_SUB(@theDate, interval 1 day));
  SET @day_num_in_month = DAY(@theDate);
  SET @day_num_overall = @day_num_overall + 1;
  SET @day_name = DAYNAME(@thedate);
  SET @day_abbrev = SUBSTRING(@day_name, 1, 3);
  
  SET @weekday_flag = IF(@day_of_week in (6,7), 1, 0);
  SET @week_num_in_year = WEEK(@theDate);
  SET @week_num_in_year = IF(@day_of_week = 7, @week_num_in_year - 1, @week_num_in_year);
  SET @week_begin_date = DATE_SUB(@theDate, interval @day_of_week - 1 day);
  SET @week_end_date = DATE_ADD(@week_begin_date, interval 6 day);
  SET @week_begin_date_key = CAST(DATE(@week_begin_date) AS UNSIGNED); # DATE(week_begin_date) + 0;
  SET @week_end_date_key = CAST(DATE(@week_end_date) AS UNSIGNED); # DATE(week_begin_date) + 0;
  SET @week_num_overall = FLOOR(DATEDIFF(@week_begin_date, @init_week_begin_date) / 7);
  
  SET @week_num_in_month = ceil((@day_num_in_month - @day_of_week)/7.0 + 1);
  SET @monthly_week_num_overall = @monthly_week_num_overall + if(@last_week_num_in_month!=@week_num_in_month, 1, 0);
  SET @last_week_num_in_month = @week_num_in_month;

  SET @halfmonth_num_overall = @halfmonth_num_overall + (@day_num_in_month in (1, 16));
  SET @month = MONTH(@theDate);
  SET @month_name = MONTHNAME(@theDate);
  SET @month_abbrev = SUBSTRING(@month_name, 1, 3);
  SET @quarter = QUARTER(@theDate);
  SET @quarter_num_overall = @quarter_num_overall + (QUARTER(@theDate) != QUARTER(DATE_SUB(@theDate, interval 1 day))); 
  SET @year = YEAR(@theDate);
  SET @yearq = YEAR(@theDate)*10 + QUARTER(@theDate);
  SET @yearmo = EXTRACT(YEAR_MONTH FROM @theDate);
  SET @month_num_overall = PERIOD_DIFF(@yearmo, @init_year_month);
  SET @quarter_name = concat(@year, 'Q', @quarter);
  SET @month_of_year_name = concat(@month_abbrev, ', ', @year);

  REPLACE INTO date_dimension(
    datekey, 
    date, 
    day_of_week, 
    day_num_in_month,
    day_num_overall, 
    day_name, 
    day_abbrev, 
    weekday_flag, 
    week_num_in_month,
    week_num_in_year, 
    week_num_overall, 
    week_begin_date,
    week_end_date,
    week_begin_date_key,
    week_end_date_key,
    monthly_week_num_overall,
    halfmonth_num_overall, 
    month, 
    month_name, 
    month_abbrev, 
    month_num_overall, 
    quarter, 
    quarter_num_overall, 
    year,
    yearq,
    yearmo,
    quarter_name, 
    month_of_year_name
  )
  VALUES(
    @datekey, 
    @date, 
    @day_of_week, 
    @day_num_in_month, 
    @day_num_overall, 
    @day_name, 
    @day_abbrev, 
    @weekday_flag,
    @week_num_in_month,
    @week_num_in_year, 
    @week_num_overall, 
    @week_begin_date,
    @week_end_date,
    @week_begin_date_key,
    @week_end_date_key,
    @monthly_week_num_overall,
    @halfmonth_num_overall, 
    @month, 
    @month_name, 
    @month_abbrev, 
    @month_num_overall, 
    @quarter, 
    @quarter_num_overall, 
    @year, 
    @yearq, 
    @yearmo,
    @quarter_name, 
    @month_of_year_name
  );

  SET @theDate = date_add(@theDate, interval @interday day);
END WHILE;
END$$
DELIMITER ;

CALL make_date_dimension;