数仓-建设时间维度表使用MYSQL 生成基础表；

最新推荐文章于 2024-05-15 19:28:32 发布

shijiazhenghu

最新推荐文章于 2024-05-15 19:28:32 发布

阅读量556

点赞数 7

文章标签： mysql 数据库数据仓库大数据

本文链接：https://blog.csdn.net/shijiazhenghu/article/details/137735360

版权

1. MySQL-日期维度表构建过程

1.1 创建存储过程循环生成日期数据

DELIMITER $$

-- DROP PROCEDURE IF EXISTS test.proc_date_date;

CREATE PROCEDURE test.proc_date_date()
BEGIN
DECLARE end_date DATE DEFAULT DATE_FORMAT('2010-01-01','%Y-%m-%d');

TRUNCATE test.dim_date;
lp1:LOOP
INSERT INTO test.dim_date(DATE_ID)VALUES(end_date);
SET end_date=ADDDATE(end_date,1);

IF END_DATE = DATE_FORMAT('2021-01-01','%Y-%m-%d') THEN
   LEAVE lp1;
END IF;
END LOOP;

END$$

DELIMITER ;

-- 执行生成 -- 
call proc_date_date()

1.2进行相关字段生成

获取当前年月日（2018-03-05）：SELECT CONVERT(varchar(10), getdate(), 120);

获取当前年月日（20180305）：SELECT CONVERT(varchar(10), getdate(), 112);

获取当月的第几天（05）：SELECT DATENAME (day ,getdate());

获取当年的第几页月（03）：SELECT DATENAME (month ,getdate());

获取当年的第几季度（3）：SELECT DATENAME (quarter ,getdate());

获取当前的第几天：SELECT DATENAME (dayofyear ,getdate());

获取当前中第几周：SELECT DATENAME(week,GETDATE());

获取当前星期几：SELECT DATENAME(weekday,GETDATE());

获取当前几点，24小时制的：SELECT DATENAME(hour,GETDATE());

获取当前分钟数：SELECT DATENAME(minute,GETDATE());

获取当前秒数：SELECT DATENAME(second,GETDATE());

获取当前毫秒：SELECT DATENAME(millisecond,GETDATE());

--------------- 获取自然最近两周SQL --------------------

SELECT *

FROM dw.dim_date t

WHERE t.date_id <= DATE_FORMAT(NOW(),'%Y-%m-%d')

AND t.date_id >= DATE_FORMAT(CASE WHEN DAYOFWEEK(NOW()) = 1 THEN DATE_SUB(NOW(),INTERVAL 13 DAY)

WHEN DAYOFWEEK(NOW()) = 2 THEN DATE_SUB(NOW(),INTERVAL 7 DAY)

WHEN DAYOFWEEK(NOW()) = 3 THEN DATE_SUB(NOW(),INTERVAL 8 DAY)

WHEN DAYOFWEEK(NOW()) = 4 THEN DATE_SUB(NOW(),INTERVAL 9 DAY)

WHEN DAYOFWEEK(NOW()) = 5 THEN DATE_SUB(NOW(),INTERVAL 10 DAY)

WHEN DAYOFWEEK(NOW()) = 6 THEN DATE_SUB(NOW(),INTERVAL 11 DAY)

WHEN DAYOFWEEK(NOW()) = 7 THEN DATE_SUB(NOW(),INTERVAL 12 DAY)

END,'%Y-%m-%d');

-------------------- 获取自然二年周 -------------------------------

SELECT *

FROM dw.dim_date t

WHERE t.date_id <= DATE_FORMAT(NOW(),'%Y-%m-%d')

AND t.WEEK_YEAR >= DATE_FORMAT(NOW(),'%Y')-1

ORDER BY t.DATE_ID

----------------- 第一次 ----------------

select t.date_id,

t.year,

t.year_name,

t.year_mm,

t.year_mm_name,

t.year_d,

t.year_d_name,

year(t.date_id) as year1,

concat(year(t.date_id),'年') as year_name1,

substr(t.date_id,1,7) as year_mm,

concat(year(t.date_id),'年',substr(t.date_id,6,2),'月') as year_mm_name1,

dayofyear(t.date_id) as year_d1,

concat('第',dayofyear(t.date_id),'天') as year_d_name1

from dw.dim_date t

where t.date_id>='2020-12-20';

update dw.dim_date t set year=year(t.date_id),

year_name=concat(year(t.date_id),'年'),

year_mm=substr(t.date_id,1,7),

year_mm_name=concat(year(t.date_id),'年',substr(t.date_id,6,2),'月'),

year_d=dayofyear(t.date_id),

year_d_name=concat('第',dayofyear(t.date_id),'天')

where date_id>='2021-01-01';

----------------- 第二次 ----------------

select t.date_id,

t.quarter,

t.quarter_name,

t.year_q,

t.month,

t.month_name,

t.month_d,

t.month_d_name,

quarter(t.date_id) as quarter1,

concat('第',quarter(t.date_id),'季度') as quarter_name1,

concat(year(t.date_id),'Q',quarter(t.date_id)) as year_q1,

substr(t.date_id,6,2) as month1,

concat(substr(t.date_id,6,2),'月') as month_name1,

day(t.date_id) as month_d1,

concat(day(t.date_id),'天') as month_d_name1

from dw.dim_date t

where t.date_id>='2020-12-20';

update dw.dim_date t set quarter=quarter(t.date_id),

quarter_name=concat('第',quarter(t.date_id),'季度'),

year_q=concat(year(t.date_id),'Q',quarter(t.date_id)),

month=substr(t.date_id,6,2),

month_name=concat(substr(t.date_id,6,2),'月'),

month_d=day(t.date_id),

month_d_name=concat(day(t.date_id),'天')

where date_id>='2021-01-01';

----------------- 第三次 ----------------

select t.date_id,

t.week_d,

t.week_name,

t.year_w,

t.year_w_name,

t.week_of_year,

t.week_id,

t.week_year,

t.week_of_year_ly,

DAYOFWEEK(t.date_id) as week_d1,

case when DAYOFWEEK(t.date_id)='1' then '星期日'

when DAYOFWEEK(t.date_id)='2' then '星期一'

when DAYOFWEEK(t.date_id)='3' then '星期二'

when DAYOFWEEK(t.date_id)='4' then '星期三'

when DAYOFWEEK(t.date_id)='5' then '星期四'

when DAYOFWEEK(t.date_id)='6' then '星期五'

when DAYOFWEEK(t.date_id)='7' then '星期六' end

as week_name1,

WEEKOFYEAR(t.date_id) as year_w1,

case when DAYOFWEEK(t.date_id)=2 then t.date_id

when DAYOFWEEK(t.date_id)=3 then DATE_ADD(t.date_id,INTERVAL -1 day)

when DAYOFWEEK(t.date_id)=4 then DATE_ADD(t.date_id,INTERVAL -2 day)

when DAYOFWEEK(t.date_id)=5 then DATE_ADD(t.date_id,INTERVAL -3 day)

when DAYOFWEEK(t.date_id)=6 then DATE_ADD(t.date_id,INTERVAL -4 day)

when DAYOFWEEK(t.date_id)=7 then DATE_ADD(t.date_id,INTERVAL -5 day)

when DAYOFWEEK(t.date_id)=1 then DATE_ADD(t.date_id,INTERVAL -6 day)

end as week_id1

from dw.dim_date t

where t.date_id>='2020-12-20';

update dw.dim_date t set week_d=DAYOFWEEK(t.date_id),

week_name=(case when DAYOFWEEK(t.date_id)='1' then '星期日'

when DAYOFWEEK(t.date_id)='2' then '星期一'

when DAYOFWEEK(t.date_id)='3' then '星期二'

when DAYOFWEEK(t.date_id)='4' then '星期三'

when DAYOFWEEK(t.date_id)='5' then '星期四'

when DAYOFWEEK(t.date_id)='6' then '星期五'

when DAYOFWEEK(t.date_id)='7' then '星期六' end),

year_w=WEEKOFYEAR(t.date_id),

week_id=(case when DAYOFWEEK(t.date_id)=2 then t.date_id

when DAYOFWEEK(t.date_id)=3 then DATE_ADD(t.date_id,INTERVAL -1 day)

when DAYOFWEEK(t.date_id)=4 then DATE_ADD(t.date_id,INTERVAL -2 day)

when DAYOFWEEK(t.date_id)=5 then DATE_ADD(t.date_id,INTERVAL -3 day)

when DAYOFWEEK(t.date_id)=6 then DATE_ADD(t.date_id,INTERVAL -4 day)

when DAYOFWEEK(t.date_id)=7 then DATE_ADD(t.date_id,INTERVAL -5 day)

when DAYOFWEEK(t.date_id)=1 then DATE_ADD(t.date_id,INTERVAL -6 day)

end)

where date_id>='2021-01-01';

----------------- 第四次 ----------------

select t.date_id,

t.week_d,

t.week_name,

t.year_w,

t.year_w_name,

t.week_of_year,

t.week_id,

t.week_year,

t.week_of_year_ly,

concat('W',t.year_w) as year_w_name1,

concat(substr(t.week_id,1,5),'W',t.year_w) as week_of_year1,

substr(t.week_id,1,4) as week_year1,

concat((substr(t.week_id,1,4)-1),'-W',t.year_w) as week_of_year_ly1

from dw.dim_date t

where t.date_id>='2020-12-20';

update dw.dim_date t set year_w_name=concat('W',t.year_w),

week_of_year=concat(substr(t.week_id,1,5),'W',t.year_w),

week_year=substr(t.week_id,1,4),

week_of_year_ly=concat((substr(t.week_id,1,4)-1),'-W',t.year_w)

where date_id>='2021-01-01';

2. 表结构信息

CREATE TABLE `dim_date` (
  `date_id` date NOT NULL COMMENT '日期',
  `year` varchar(30) DEFAULT NULL COMMENT '年份',
  `year_name` varchar(30) DEFAULT NULL COMMENT '年份中文描述',
  `year_mm` varchar(30) DEFAULT NULL COMMENT '年月',
  `year_mm_name` varchar(30) DEFAULT NULL COMMENT '年月中文描述',
  `year_d` int(11) DEFAULT NULL COMMENT '年中第几天',
  `year_d_name` varchar(30) DEFAULT NULL COMMENT '年中第几天中文描述',
  `quarter` int(11) DEFAULT NULL COMMENT '季度',
  `quarter_name` varchar(30) DEFAULT NULL COMMENT '季度描述',
  `year_q` varchar(30) DEFAULT NULL COMMENT '年季度',
  `month` int(11) DEFAULT NULL COMMENT '月份',
  `month_name` varchar(30) DEFAULT NULL COMMENT '月份描述',
  `month_d` int(11) DEFAULT NULL COMMENT '每月第几天',
  `month_d_name` varchar(30) DEFAULT NULL COMMENT '每月第几天描述',
  `week_d` int(11) DEFAULT NULL COMMENT '每周的第几天',
  `week_name` varchar(30) DEFAULT NULL COMMENT '星期几',
  `year_w` int(11) DEFAULT NULL COMMENT '年第几周',
  `year_w_name` varchar(30) DEFAULT NULL COMMENT '年第几周描述',
  `week_of_year` varchar(30) DEFAULT NULL COMMENT '跨年周记录',
  `week_id` varchar(30) DEFAULT NULL COMMENT '周一的日期',
  `week_year` varchar(30) DEFAULT NULL COMMENT '周年',
  `week_of_year_ly` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`date_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='日期表';

shijiazhenghu

关注

7
点赞
踩
10

收藏

觉得还不错? 一键收藏
1
评论
数仓-建设时间维度表使用MYSQL 生成基础表；

------------------- 获取自然二年周 -------------------------------year_mm_name=concat(year(t.date_id),'年',substr(t.date_id,6,2),'月'),concat(year(t.date_id),'年',substr(t.date_id,6,2),'月') as year_mm_name1,year_d_name=concat('第',dayofyear(t.date_id),'天')
复制链接

扫一扫