Mysql CTE 递归应用案例 创建时间维度表

2020年05月19日 1220点热度 0人点赞 0条评论

CTE的递归功能很强大,可以有很多实际的运用场景。比如我们经常用到的日期维度表。这个日期维度表在PowerBI上很常见。以前可以在网上找日期维度表或者用EXCEL写公式生成。通过CTE的递归功能可以很快的生成。

可以自己定义开始时间和结束时间,非常的方面。我这里设置的是2020年1月1号开始到当前日期。完全可以根据实际需要自定义。


set @dt = '2020-1-1'; with recursive CTE as ( select @dt as DateID ,year(@dt) as year_num ,month(@dt) as month_num ,day(@dt) as day_num ,CASE QUARTER(@dt) WHEN 1 THEN N'Q1' WHEN 2 THEN N'Q2' WHEN 3 THEN N'Q3' ELSE N'Q4' END AS Quarter_num ,CASE dayofweek(@dt) WHEN 1 THEN N'星期一' WHEN 2 THEN N'星期二' WHEN 3 THEN N'星期三' WHEN 4 THEN N'星期四' WHEN 5 THEN N'星期五' WHEN 6 THEN N'星期六' ELSE N'星期日' END AS WeekDay_num ,WEEK(@dt,1) AS Week_num union all select DATE_ADD(DateID,INTERVAL 1 DAY) ,year(DATE_ADD(DateID,INTERVAL 1 DAY)) ,month(DATE_ADD(DateID,INTERVAL 1 DAY)) ,day(DATE_ADD(DateID,INTERVAL 1 DAY)) ,CASE QUARTER(DATE_ADD(DateID,INTERVAL 1 DAY)) WHEN 1 THEN N'Q1' WHEN 2 THEN N'Q2' WHEN 3 THEN N'Q3' ELSE N'Q4' END AS Quarter_num ,CASE dayofweek(DATE_ADD(DateID,INTERVAL 1 DAY)) WHEN 1 THEN N'星期一' WHEN 2 THEN N'星期二' WHEN 3 THEN N'星期三' WHEN 4 THEN N'星期四' WHEN 5 THEN N'星期五' WHEN 6 THEN N'星期六' ELSE N'星期日' END AS WeekDay_num ,WEEK(DATE_ADD(DateID,INTERVAL 1 DAY),1) from CTE where DateID<CURDATE() ) SELECT * FROM CTE;

执行结果如下:
Y5N5nK.png

KOgoal

文章评论