时间维度表创建的脚本
根据不同的业务需求选择需要的字段即可!
在MySQL里面创建存储过程
--创建dim数据库,使用utf8编码
create database dim DEFAULT CHARACTER SET utf8;
--切换到dim数据库
use dim;
--创建dim_day表
create table dim_day(
DAY_ID varchar(100),
DAY_SHORT_DESC varchar(100),
DAY_LONG_DESC varchar(100),
WEEK_ID varchar(100),
WEEK_LONG_DESC varchar(100),
MONTH_ID varchar(100),
MONTH_SHORT_DESC varchar(100),
MONTH_LONG_DESC varchar(100),
QUARTER_ID varchar(100),
QUARTER_LONG_DESC varchar(100),
YEAR_ID varchar(100),
YEAR_LONG_DESC varchar(100)
)
--查看dim_day的结构
show full columns from dim_day;
$$ --写$$ 表示完结
+-------------------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------------------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| DAY_ID | varchar(100) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
| DAY_SHORT_DESC | varchar(100) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
| DAY_LONG_DESC | varchar(100) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
| WEEK_ID | varchar(100) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
| WEEK_LONG_DESC | varchar(100) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
| MONTH_ID | varchar(100) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
| MONTH_SHORT_DESC | varchar(100) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
| MONTH_LONG_DESC | varchar(100) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
| QUARTER_ID | varchar(100) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
| QUARTER_LONG_DESC | varchar(100) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
| YEAR_ID | varchar(100) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
| YEAR_LONG_DESC | varchar(100) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
+-------------------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
--创建一个存储过程,实现时间维度的创建
DELIMITER $$ -- 存储过程的开始 也可以用 ||
create procedure dim_day(in start_date VARCHAR(20),in date_count int)
begin
declare i int;
set i=0;
DELETE from dim_day;
while i<date_count do
INSERT into dim_day
(DAY_ID,DAY_SHORT_DESC,DAY_LONG_DESC,WEEK_ID,WEEK_LONG_DESC,MONTH_ID,MONTH_SHORT_DESC,MONTH_LONG_DESC,QUARTER_ID,QUARTER_LONG_DESC,YEAR_ID,YEAR_LONG_DESC)
SELECT
REPLACE(start_date,'-','') DAY_ID,
DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y-%m-%d') DAY_SHORT_DESC,
DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y年%m月%d日') DAY_LONG_DESC,
DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y%u') WEEK_ID,
DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y年第%u周') WEEK_LONG_DESC,
DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y%m') MONTH_ID,
DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y-%m') MONTH_SHORT_DESC,
DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y年第%m月') MONTH_LONG_DESC,
CONCAT(DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y'),quarter(STR_TO_DATE( start_date,'%Y-%m-%d %H:%i:%s'))) QUARTER_ID,
CONCAT(DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y'),'年第',quarter(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s')),'季度') QUARTER_LONG_DESC,
DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y') YEAR_ID,
DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y年') YEAR_LONG_DESC
from dual;
set i=i+1;
set start_date = DATE_FORMAT(date_add(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),interval 1 day),'%Y-%m-%d');
end while;
end
$$
--生成2019年的时间维度
call dim_day('2019-01-01',365)
--查询生成的一年的数据
select * from dim_day;
$$
+----------+----------------+-------------------+---------+-----------------+----------+------------------+-----------------+------------+-------------------+---------+----------------+
| DAY_ID | DAY_SHORT_DESC | DAY_LONG_DESC | WEEK_ID | WEEK_LONG_DESC | MONTH_ID | MONTH_SHORT_DESC | MONTH_LONG_DESC | QUARTER_ID | QUARTER_LONG_DESC | YEAR_ID | YEAR_LONG_DESC |
+----------+----------------+-------------------+---------+-----------------+----------+------------------+-----------------+------------+-------------------+---------+----------------+
| 20190101 | 2019-01-01 | 2019年01月01日 | 201901 | 2019年第01周 | 201901 | 2019-01 | 2019年第01月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190102 | 2019-01-02 | 2019年01月02日 | 201901 | 2019年第01周 | 201901 | 2019-01 | 2019年第01月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190103 | 2019-01-03 | 2019年01月03日 | 201901 | 2019年第01周 | 201901 | 2019-01 | 2019年第01月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190104 | 2019-01-04 | 2019年01月04日 | 201901 | 2019年第01周 | 201901 | 2019-01 | 2019年第01月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190105 | 2019-01-05 | 2019年01月05日 | 201901 | 2019年第01周 | 201901 | 2019-01 | 2019年第01月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190106 | 2019-01-06 | 2019年01月06日 | 201901 | 2019年第01周 | 201901 | 2019-01 | 2019年第01月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190107 | 2019-01-07 | 2019年01月07日 | 201902 | 2019年第02周 | 201901 | 2019-01 | 2019年第01月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190108 | 2019-01-08 | 2019年01月08日 | 201902 | 2019年第02周 | 201901 | 2019-01 | 2019年第01月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190109 | 2019-01-09 | 2019年01月09日 | 201902 | 2019年第02周 | 201901 | 2019-01 | 2019年第01月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190110 | 2019-01-10 | 2019年01月10日 | 201902 | 2019年第02周 | 201901 | 2019-01 | 2019年第01月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190111 | 2019-01-11 | 2019年01月11日 | 201902 | 2019年第02周 | 201901 | 2019-01 | 2019年第01月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190112 | 2019-01-12 | 2019年01月12日 | 201902 | 2019年第02周 | 201901 | 2019-01 | 2019年第01月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190113 | 2019-01-13 | 2019年01月13日 | 201902 | 2019年第02周 | 201901 | 2019-01 | 2019年第01月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190114 | 2019-01-14 | 2019年01月14日 | 201903 | 2019年第03周 | 201901 | 2019-01 | 2019年第01月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190115 | 2019-01-15 | 2019年01月15日 | 201903 | 2019年第03周 | 201901 | 2019-01 | 2019年第01月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190116 | 2019-01-16 | 2019年01月16日 | 201903 | 2019年第03周 | 201901 | 2019-01 | 2019年第01月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190117 | 2019-01-17 | 2019年01月17日 | 201903 | 2019年第03周 | 201901 | 2019-01 | 2019年第01月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190118 | 2019-01-18 | 2019年01月18日 | 201903 | 2019年第03周 | 201901 | 2019-01 | 2019年第01月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190119 | 2019-01-19 | 2019年01月19日 | 201903 | 2019年第03周 | 201901 | 2019-01 | 2019年第01月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190120 | 2019-01-20 | 2019年01月20日 | 201903 | 2019年第03周 | 201901 | 2019-01 | 2019年第01月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190121 | 2019-01-21 | 2019年01月21日 | 201904 | 2019年第04周 | 201901 | 2019-01 | 2019年第01月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190122 | 2019-01-22 | 2019年01月22日 | 201904 | 2019年第04周 | 201901 | 2019-01 | 2019年第01月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190123 | 2019-01-23 | 2019年01月23日 | 201904 | 2019年第04周 | 201901 | 2019-01 | 2019年第01月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190124 | 2019-01-24 | 2019年01月24日 | 201904 | 2019年第04周 | 201901 | 2019-01 | 2019年第01月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190125 | 2019-01-25 | 2019年01月25日 | 201904 | 2019年第04周 | 201901 | 2019-01 | 2019年第01月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190126 | 2019-01-26 | 2019年01月26日 | 201904 | 2019年第04周 | 201901 | 2019-01 | 2019年第01月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190127 | 2019-01-27 | 2019年01月27日 | 201904 | 2019年第04周 | 201901 | 2019-01 | 2019年第01月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190128 | 2019-01-28 | 2019年01月28日 | 201905 | 2019年第05周 | 201901 | 2019-01 | 2019年第01月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190129 | 2019-01-29 | 2019年01月29日 | 201905 | 2019年第05周 | 201901 | 2019-01 | 2019年第01月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190130 | 2019-01-30 | 2019年01月30日 | 201905 | 2019年第05周 | 201901 | 2019-01 | 2019年第01月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190131 | 2019-01-31 | 2019年01月31日 | 201905 | 2019年第05周 | 201901 | 2019-01 | 2019年第01月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190201 | 2019-02-01 | 2019年02月01日 | 201905 | 2019年第05周 | 201902 | 2019-02 | 2019年第02月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190202 | 2019-02-02 | 2019年02月02日 | 201905 | 2019年第05周 | 201902 | 2019-02 | 2019年第02月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190203 | 2019-02-03 | 2019年02月03日 | 201905 | 2019年第05周 | 201902 | 2019-02 | 2019年第02月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190204 | 2019-02-04 | 2019年02月04日 | 201906 | 2019年第06周 | 201902 | 2019-02 | 2019年第02月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190205 | 2019-02-05 | 2019年02月05日 | 201906 | 2019年第06周 | 201902 | 2019-02 | 2019年第02月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190206 | 2019-02-06 | 2019年02月06日 | 201906 | 2019年第06周 | 201902 | 2019-02 | 2019年第02月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190207 | 2019-02-07 | 2019年02月07日 | 201906 | 2019年第06周 | 201902 | 2019-02 | 2019年第02月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190208 | 2019-02-08 | 2019年02月08日 | 201906 | 2019年第06周 | 201902 | 2019-02 | 2019年第02月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190209 | 2019-02-09 | 2019年02月09日 | 201906 | 2019年第06周 | 201902 | 2019-02 | 2019年第02月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190210 | 2019-02-10 | 2019年02月10日 | 201906 | 2019年第06周 | 201902 | 2019-02 | 2019年第02月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190211 | 2019-02-11 | 2019年02月11日 | 201907 | 2019年第07周 | 201902 | 2019-02 | 2019年第02月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190212 | 2019-02-12 | 2019年02月12日 | 201907 | 2019年第07周 | 201902 | 2019-02 | 2019年第02月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190213 | 2019-02-13 | 2019年02月13日 | 201907 | 2019年第07周 | 201902 | 2019-02 | 2019年第02月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190214 | 2019-02-14 | 2019年02月14日 | 201907 | 2019年第07周 | 201902 | 2019-02 | 2019年第02月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190215 | 2019-02-15 | 2019年02月15日 | 201907 | 2019年第07周 | 201902 | 2019-02 | 2019年第02月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190216 | 2019-02-16 | 2019年02月16日 | 201907 | 2019年第07周 | 201902 | 2019-02 | 2019年第02月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190217 | 2019-02-17 | 2019年02月17日 | 201907 | 2019年第07周 | 201902 | 2019-02 | 2019年第02月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190218 | 2019-02-18 | 2019年02月18日 | 201908 | 2019年第08周 | 201902 | 2019-02 | 2019年第02月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190219 | 2019-02-19 | 2019年02月19日 | 201908 | 2019年第08周 | 201902 | 2019-02 | 2019年第02月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190220 | 2019-02-20 | 2019年02月20日 | 201908 | 2019年第08周 | 201902 | 2019-02 | 2019年第02月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190221 | 2019-02-21 | 2019年02月21日 | 201908 | 2019年第08周 | 201902 | 2019-02 | 2019年第02月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190222 | 2019-02-22 | 2019年02月22日 | 201908 | 2019年第08周 | 201902 | 2019-02 | 2019年第02月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190223 | 2019-02-23 | 2019年02月23日 | 201908 | 2019年第08周 | 201902 | 2019-02 | 2019年第02月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190224 | 2019-02-24 | 2019年02月24日 | 201908 | 2019年第08周 | 201902 | 2019-02 | 2019年第02月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190225 | 2019-02-25 | 2019年02月25日 | 201909 | 2019年第09周 | 201902 | 2019-02 | 2019年第02月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190226 | 2019-02-26 | 2019年02月26日 | 201909 | 2019年第09周 | 201902 | 2019-02 | 2019年第02月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190227 | 2019-02-27 | 2019年02月27日 | 201909 | 2019年第09周 | 201902 | 2019-02 | 2019年第02月 | 20191 | 2019年第1季度 | 2019 | 2019年 |
| 20190228 | 2019-