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

Mysql创建时间维表

说明

时间维处理时间维度信息, 主要为小时/分钟

日期维表

drop TABLE IF EXISTS dim_time;
CREATE TABLE dim_time
(time_key BIGINT not null COMMENT 'primary key',
time_id time COMMENT '%H:%i:%s',
hour_short SMALLINT COMMENT '0,1,2,3...23',
hour_short_f SMALLINT COMMENT '1,2,3...24',
hour_slot varchar(6) COMMENT '0-1,1-2,2-3,...,23-24',

hour_of_day varchar(5) COMMENT '00,01,02,03...23',
hour_of_day_l varchar(5) COMMENT '0,1,2,3...23',
minute_id varchar(5) COMMENT '0-59',
description varchar(255) COMMENT '备注',
PRIMARY KEY (`time_key`)  
)ENGINE=MyIsam DEFAULT CHARSET=utf8;

日期维存储过程

create procedure f_dim_time()
begin

	DECLARE date_time datetime;
	declare i int;
	set i=0;
	set date_time = STR_TO_DATE('2018-09-09','%Y-%m-%d %H:%i:%s');
	DELETE from dim_time;
	
	while i < 86400 DO
		INSERT into dim_time
			(time_key,
			time_id,
			hour_short,
			hour_short_f,
			hour_slot,
			hour_of_day,
			hour_of_day_l,
			minute_id,
			description)
		SELECT
			i time_key,
			TIME(date_time) time_id,
			FLOOR(i/3600) hour_short,
			FLOOR(i/3600)+1 hour_short_f,
			CONCAT(FLOOR(i/3600),'~',FLOOR(i/3600)+1) hour_slot,
			DATE_FORMAT(date_time,'%H') hour_of_day,
			DATE_FORMAT(date_time,'%k') hour_of_day_l,
			DATE_FORMAT(date_time,'%i') minute_id,

			'' description
		from dual;
		set i=i+1;
	set date_time = DATE_ADD('2018-01-01 00:00:00',INTERVAL i SECOND);
	end while;
end

发表于:2021-04-22 10:56:41浏览(309) 评论(1) MySQL

下一篇:没有啦

 评论记录