解决工厂日、工厂月、工厂年问题全过程及其在数仓建模过程中的运用

解决工厂日、工厂月、工厂年问题全过程及其在数仓建模过程中的运用

前言：

本文解决的问题场景运用面较窄，但是涉及存储过程、维度建模、星型模型反规范化等方面的综合运用，创作目的在于为此类问题打开一个角度，供借鉴。

背景：

在某制造业工厂进行数仓建设时，遇到这样一个问题：工厂日的一天开始是早上8点，结束是第二天早上8点；工厂月的开始是上个月的26号，结束是这个月的25号(25号是工厂日的25号)；对应的，工厂年的开始是去年的12月26号，结束是今年的12月25号。

问题分析：

• 给定一个时间 2023-12-28 06:52:43 ，判断在 [2023-12-27 08:00:00, 2023-12-28 08:00:00) 区间，所以该时间是工厂日的 2023-12-27 。对于工厂日的逻辑解决，很容易想到，通过正常时间往后推移8小时，即为工厂日的时间区间。
• 在工厂日的判断基础上，对于日期 2023-12-27，判断在 [2023-12-26, 2024-01-25) 区间，所以该工厂日期的工厂月是 2024-01。对于工厂月的逻辑解决，可以一分为2来判断，如果年月日中的日是01-25号，那么年月就是工厂年月；如果年月日中的日是26-31号，那么年月加一个月就是工厂年月。
• 在工厂月判断的基础上，对于工厂年月 2024-01，即为正常年月的 2024-01，通过工厂月的判断已经把工厂年也判断出来了。
• 相应的工厂周、工厂季也是一样的判断。

逻辑并不复杂，但在数仓里用到时间维度的事实表几乎覆盖了99%，如此高频率的维度出现，每次出现都要对时间进行逻辑判断，显然是十分不合理的。

容易想到从维度建模的角度，将工厂年月日维度化，建立工厂万年历，对于给定的一个日期都能准确拿到其对于该工厂的年月日信息是什么。

解决方案：

1. 通过mysql数据库写存储过程，建立工厂万年历表 fab_calendar；
2. 通过数据同步，将该表同步到数仓里使用，建立日期维度表 dim_fab_calendar_all，每次通过正常的日期与工厂日关联，即可获得工厂年月（对于具体到某一天时间的判断还是通过推移8小时判断）。

存储过程解析：

mysql建表 fab_calendar

create table db1.fab_calendar 
(
    fab_day  varchar(40) comment '工厂日',
    fab_week varchar(40) comment '工厂周',
    fab_month varchar(40) comment '工厂月',
    fab_quarter varchar(40) comment '工厂季',
    fab_year varchar(40) comment '工厂年'
);

创建临时表 fab_day_t

create table db1.fab_day_t
(
    fab_day  DATE comment '工厂日'
);

mysql存储过程设计

create procedure func(in p_in int) 
begin 
    declare var int;   
    set var=0;   
    while var < p_in do  
        insert into db1.fab_day_t (fab_day)
        values (
        date_add(date_format('2015-01-01','%Y-%m-%d'),interval var day)
        );  
    	set var=var+1;  
    end while;  
end;  

过程调用

call func(365*20); -- 从2015-01-01开始创建了365*20天

通过查询插入表 fab_calendar

insert into db1.fab_calendar 
select 
    t1.fab_day,
    weekofyear(t1.fab_day) as fab_week,
    if(CAST(SUBSTR(t1.fab_day,7,2) as signed) < 26, SUBSTR(t1.fab_day,1,6), date_format(date_add(t1.fab_day,interval 1 month),'%Y%m')) as fab_month,
    floor(substr(if(CAST(SUBSTR(t1.fab_day,7,2) as signed) < 26, SUBSTR(t1.fab_day,1,6), date_format(date_add(t1.fab_day,interval 1 month),'%Y%m')),5,2)/3.1+1) as fab_quarter,
    substr( if(CAST(SUBSTR(t1.fab_day,7,2) as signed) < 26, SUBSTR(t1.fab_day,1,6), date_format(date_add(t1.fab_day,interval 1 month),'%Y%m')),1,4) as fab_year
from (
    select 
    	date_format(t.fab_day,'%Y%m%d') as fab_day  
    from db1.fab_day_t t
) t1;

结果展示