mysql中创建维表_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;

运行 call f_dim_date('2015-01-01',365) 生成维表

添加维表属性

添加列:

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