1 大数据开发之审核时长剔除非工作时长

本文链接：https://blog.csdn.net/sewulan9170/article/details/125803487

该博客讨论了如何在业务等待运营审核时长中剔除非工作时间，包括工作日的特定时段以及周末和法定节假日。通过SQL查询，利用date_diff和posexplode函数拆分日期差，并结合日历表排除非工作时间，同时考虑调休日。最终目的是准确计算出扣除非工作时间后的审核时长，以影响业务与运营的OKR。

摘要由CSDN通过智能技术生成

场景：业务等待运营审核时长以及运营审核的时长需要剔除非工作时间，时长直接影响业务与运营的OKR，因此需要将非工作时长剔除。

非工作时间定义：工作日的18点之后，9点之前；周末以及法定节假日。

另外：需要补充法定节假日的调休日，作为正常工作日

datachange_createtime为提交时间，datachange_lasttime为审核时间，需要将这中间的非工作时间剔除。

select posexplode( split(repeat('d', datediff('2022-07-14 16:02:05', '2022-07-11 12:32:09')), 'd')) as (pos,col1)

pos	col1
0		
1		
2		
3

使用posexplode将日期的差值拆开，获取到提交日期到审核日期之间差值的索引，即pos字段，col1的存在是因为posexplode的输出长度为2，有一列是索引，一列是输出值，此处输出值为空。

date(datachange_createtime) as submit_date
date_add(submit_date,a.pos) as audit_start_indate

根据索引与提交日期相加，获取中间的每一个日期即audit_start_indate，再判断中间每一天的类型，通过日历表剔除周末和节假日，补充调休日，此处调休日期用一张表来存储。

select label_code
      ,sum(a.firstday_interval) + sum(a.lastday_interval) + sum(a.indate_interval) as audit_interval_wd
from 
( 
    select label_code
        , case when a.submit_date = a.audit_start_indate and a.audit_type = 0 then  	-- * 提交日为工作日且当天开始审核
    				case when (date_format(a.datachange_createtime, 'HH:mm') between '09' and '18') and (date_format(a.datachange_lasttime, 'HH:mm') between '09' and '18') 
    				     then a.audit_interval
    				     when (date_format(a.datachange_createtime, 'HH:mm') between '09' and '18') 
    				     then greatest( unix_timestamp(date_format(a.datachange_createtime, 'yyyy-MM-dd 18:00:00')) - unix_timestamp(a.datachange_createtime), 0 )
    				     when (date_format(a.datachange_lasttime, 'HH:mm') between '09' and '18') 
    				     then greatest( unix_timestamp(a.datachange_lasttime) - unix_timestamp(date_format(a.datachange_createtime, 'yyyy-MM-dd 09:00:00')), 0 ) 
    				     when date_format(a.datachange_createtime, 'HH:mm') > '18' or date_format(a.datachange_lasttime, 'HH:mm') < '09' then 0
    				     else 32400 end
               when a.submit_date = a.audit_start_indate and a.audit_type > 0 then    -- * 提交日为工作日且非天开始审核
    				case when date_format(a.datachange_createtime, 'HH:mm') between '09' and '18'
                     	 then greatest( unix_timestamp(date_format(a.datachange_createtime, 'yyyy-MM-dd 18:00:00')) - unix_timestamp(a.datachange_createtime), 0 )
    				     when date_format(a.datachange_createtime, 'HH:mm') > '18' then 0
    				     else 32400 end
               else 0 end as firstday_interval
    
        , case when a.audit_date = a.audit_start_indate and a.audit_type > 0 then     -- * 开始审核日为工作日且非当天开始审核
    			    case when date_format(a.datachange_lasttime, 'HH:mm') between '09' and '18'
                         then greatest( unix_timestamp(a.datachange_lasttime) - unix_timestamp(date_format(a.datachange_lasttime, 'yyyy-MM-dd 09:00:00')), 0 )
    				     when date_format(a.datachange_lasttime, 'HH:mm') < '09' then 0
    			         else 32400 end
               else 0 end as lastday_interval
    
        , if(a.submit_date = a.audit_start_indate or a.audit_date = a.audit_start_indate, 0, 8*3600) as indate_interval
    from  
    (
        select label_code
              ,datachange_createtime   -- 提交时间
              ,datachange_lasttime     -- 审核时间
              ,submit_date             -- 提交日期
              ,audit_date              -- 审核日期
              ,audit_interval          -- 审核时长，不剔除非工作时间，含非工作时间
              ,audit_type              -- 审核完成类型，0是当天，>0不是当天
              ,date_add(submit_date,a.pos) as audit_start_indate 
        from 
        (
            select label_code
                  ,datachange_createtime
                  ,datachange_lasttime
                  ,zt_audit_interval as audit_interval 
                  ,zt_audit_type as audit_type
                  ,date(datachange_createtime) as submit_date  -- 提交日期
                  ,date(datachange_lasttime) as audit_date     -- 审核日期
                  ,posexplode( split(repeat('d', datediff(datachange_lasttime, datachange_createtime)), 'd')) as (pos, col1)
            from 
            (
                select *                
                from db.table 
                where d="yyyy-MM-dd"
            ) a 
        ) a 
    ) a     
    left join db.calendar b
    on a.audit_start_indate = b.datesolar
    and b.isadjusted = '是'     -- * 法定节假日
    left join db.calendar c
    on a.audit_start_indate = c.datesolar
    and c.isweekend = 1 -- * 双休日
    and c.datesolar not in 
    (
        select rest_workday
		from db.rest_workday
           --   '2021-02-07', '2021-02-20'    -- * 春节调休
           -- , '2021-04-25', '2021-05-08'    -- * 劳动节调休
           -- , '2021-09-18'                  -- * 中秋节调休
           -- , '2021-09-26', '2021-10-09'    -- * 国庆节调休
           -- , '2022-01-29', '2021-01-30'	   -- * 春节调休
           -- , '2022-04-02'				   -- * 清明调休
           -- , '2022-04-24', '2022-05-07'	   -- * 劳动节调休
           -- , '2022-10-08', '2022-10-09'	   -- * 国庆节调休
    )
    where nvl( b.datesolar, c.datesolar ) is null  
) a 
group by label_code