mysql创建日期维表

drop TABLE IF EXISTS dim_date;
CREATE TABLE dim_date
(date_key BIGINT COMMENT 'primary key',
date_id date COMMENT 'yyyy-mm-dd',
date_short varchar(25) COMMENT 'yyyymmdd',
date_long varchar(25) COMMENT 'yyyy年mm月dd日',

week_short_start_Friday varchar(25) COMMENT 'ww',
week_of_year_short_start_Friday varchar(25) COMMENT 'yyyyww',
week_of_year_long_start_Friday varchar(25) COMMENT '2016年第00周',

week_short varchar(25) COMMENT 'ww',
week_of_year_short varchar(25) COMMENT 'yyyyww',
week_of_year_long varchar(25) COMMENT '2016年第00周',
month_short VARCHAR(25) COMMENT 'mm',
month_of_year varchar(25) COMMENT 'yyyy-mm',
month_of_year_short varchar(25) COMMENT 'yyyymm',
month_of_year_long varchar(25) COMMENT 'yyyy年第mm月',
quarter_short TINYINT COMMENT '1,2,3,4',
quarter_long varchar(25) COMMENT 'Q1,Q2,Q3,Q4',
quarter_of_year_short varchar(25) COMMENT 'yyyyq',
quarter_of_year_long varchar(25) COMMENT 'yyyy年第q季度',
year_short varchar(25) COMMENT 'yyyy',
year_long varchar(25) COMMENT 'yyyy年',
is_holiday TINYINT COMMENT '1,0',
description varchar(255) COMMENT '备注',
PRIMARY KEY (`date_key`)  
)ENGINE=MyIsam DEFAULT CHARSET=utf8;

create procedure f_dim_date(in start_date VARCHAR(20), in date_count int)
begin
    declare i int;
    set i=0;
    set start_date = STR_TO_DATE('2018-09-09','%Y-%m-%d %H:%i:%s');
    DELETE from dim_date;
    while i < date_count DO
        INSERT into dim_date
        (date_key,
        date_id,
        date_short,
        date_long,
        week_short_start_Friday,
        week_of_year_short_start_Friday,
        week_of_year_long_start_Friday,
        week_short,
        week_of_year_short,
        week_of_year_long,
        month_short,
        month_of_year,
        month_of_year_short,
        month_of_year_long,
        quarter_short,
        quarter_long,
        quarter_of_year_short,
        quarter_of_year_long,
        year_short,
        year_long,
        is_holiday,
        description)
        SELECT
            i date_key,
            start_date date_id,
            DATE_FORMAT(start_date,'%Y%m%d') date_short,
            DATE_FORMAT(start_date,'%Y年%m月%d日') date_long,

            DATE_FORMAT(DATE_ADD(start_date,interval 4 day),'%u') week_short_start_Friday,
            DATE_FORMAT(start_date,'%Y%u') week_of_year_short_start_Friday,
            DATE_FORMAT(start_date,'%Y年第%u周') week_of_year_long_start_Friday,

            DATE_FORMAT(start_date,'%u') week_short,
            DATE_FORMAT(start_date,'%Y%u') week_of_year_short,
            DATE_FORMAT(start_date,'%Y年第%u周') week_of_year_long,
            DATE_FORMAT(start_date,'%m') month_short,
            DATE_FORMAT(start_date,'%Y-%m') month_of_year,
            DATE_FORMAT(start_date,'%Y%m') month_of_year_short,
            DATE_FORMAT(start_date,'%Y年第%m月') month_of_year_long,
            quarter(start_date) quarter_short,
            CONCAT('Q',quarter(start_date)) quarter_long,
            CONCAT(DATE_FORMAT(start_date,'%Y'),quarter(start_date)) quarter_of_year_short,
            CONCAT(DATE_FORMAT(start_date,'%Y'),'年第',quarter(start_date),'季度') quarter_of_year_long,
            DATE_FORMAT(start_date,'%Y') year_short,
            DATE_FORMAT(start_date,'%Y年') year_long,
            CASE WHEN DAYOFWEEK(start_date)=1 or DAYOFWEEK(start_date)=7 THEN 1 else 0 END is_holiday,
            '' description
        from dual;
        set i=i+1;
        set start_date = date_add(start_date,interval 1 day);
    end while;
end;

ALTER TABLE dim_date ADD COLUMN first_date_of_week varchar(25);
ALTER TABLE dim_date ADD COLUMN last_date_of_week varchar(25);

UPDATE dim_date,
 (
    SELECT
        week_of_year_short_start_Friday,
        min(date_id) first_date_of_week,
        max(date_id) last_date_of_week
    FROM
        dim_date
    GROUP BY
        week_of_year_short_start_Friday
) v
SET dim_date.first_date_of_week = v.first_date_of_week,
 dim_date.last_date_of_week = v.last_date_of_week
WHERE
    dim_date.week_of_year_short_start_Friday = v.week_of_year_short_start_Friday