>记录生活, 工作的点点滴滴...

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_id_start_Friday varchar(25) COMMENT 'ww',
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 @init_date = STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s');
set start_date = STR_TO_DATE(start_date,'%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_id_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,

-- 此处用于自定义周期, 主要处理思路为:
-- 1. weekday函数计算出当前为周几(周一0周日6, 注意周五值为4)
-- 2. 将周几平移, 通过加3后再模7, 调整为周五值为0, 周四为6
-- 3. 通过距离起始日期的天数(整数数列), 加上(7-平移值), 将每周的调整为同一值
-- 4. 调整后的值为7差值为7的数列, 模7取整
FLOOR((7-mod(weekday(start_date) +3, 7) + DATEDIFF(start_date, @init_date))/7) week_id_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);

更新列值

此处根据week_id, 添加每个周五-周四周期的起始与结束日期

UPDATE dim_date,
(
SELECT
week_id_start_Friday,
min(date_id) first_date_of_week,
max(date_id) last_date_of_week
FROM
dim_date
GROUP BY
week_id_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_id_start_Friday = v.week_id_start_Friday


发表于:2020-10-20 19:59:04浏览(1443) 评论(0) MySQL