mysql 日期维度序列生成

最新推荐文章于 2024-09-10 10:48:41 发布

jljf_hh

最新推荐文章于 2024-09-10 10:48:41 发布

阅读量645

点赞数 1

分类专栏： BI和DW知识文章标签： SQL 存储过程日期维度表数据填充数据库设计

本文链接：https://blog.csdn.net/jljf_hh/article/details/127119839

版权

BI和DW知识专栏收录该内容

3 篇文章 0 订阅

订阅专栏

建表语句：

CREATE TABLE `shence`.`Untitled`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` datetime NULL DEFAULT NULL,
  `dayid` int(11) NULL DEFAULT NULL,
  `weekid` int(11) NULL DEFAULT NULL,
  `monthid` int(11) NULL DEFAULT NULL,
  `quartid` int(11) NULL DEFAULT NULL,
  `yearid` int(11) NULL DEFAULT NULL,
  `y` int(11) NULL DEFAULT NULL,
  `m` int(11) NULL DEFAULT NULL,
  `d` int(11) NULL DEFAULT NULL,
  `yearmon` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  `weekstr` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  `quar` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  `weekday` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  `monname` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  `dayname` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1

存储过程：

CREATE DEFINER=`root`@`%` PROCEDURE `dataauto`()
BEGIN

SET @d0 = "1970-01-01";
SET @d1 = "2050-12-31";
set @date = date_sub(@d0, interval 1 day);
set @dayid = 0;
set @monthid = 0;
set @weekid = 0;
set @cur_month = 0;
set @cur_week = 0;

INSERT INTO `shence`.`dim_shence` (`date`, `dayid`, `weekid`, `monthid`, `quartid`, `yearid`, `y`, `m`, `d`, `yearmon`, `weekstr`, `quar`, `weekday`, `monname`, `dayname`) 
VALUES ('1970-01-01 00:00:00', 0, 0, 0, 0, 0, 1970, 1, 1, '1970', '1', '1', '4', 'January', 'Thursday');

	while @date  <= @d1 DO
		#Routine body goes here...
		set @dayid=@dayid+1;
		set @date := date_add(@date, interval 1 day);
				
		if @cur_month=12 and month(@date) = 1 then 
			set @monthid = @monthid+1;
			set @cur_month = month(@date);
		elseif @cur_month < month(@date) then
			set @monthid = @monthid+1;
			set @cur_month = month(@date);
		END if;
		
		if @cur_week > week(@date, 3) THEN
			set @weekid = @weekid+1;
			set @cur_week = week(@date, 3);
		elseif @cur_week < week(@date, 3) then
			set @weekid = @weekid+1;
			set @cur_week = week(@date, 3);
		END if;		
		

		insert into dim_shence(date,y,m,d,yearmon,weekstr,quar,weekday,monname,dayname,dayid,monthid,weekid) 
		select @date as date  ,
		year(@date) as y,
		month(@date) as m,
		day(@date) as d,
		date_format(@date, "%x")as yearmon,
		week(@date, 3) as weekstr,
		quarter(@date) as quar,
		weekday(@date)+1 as weekday,
		monthname(@date) as monname,
		dayname(@date) as dayname,
		@dayid as dayid,
		@monthid as monthid,
		@weekid as weekid
		from dim_shence
		where @date <= @d1
		ORDER BY date desc
		limit 1;
	END WHILE;
END