目录

• 0- 描述
• 1- 问题一

0- 描述

描述：业务逻辑的分类与抽象–时效
日期表：d_date
表字段及内容：

date_id      is_work
2017-04-13       1
2017-04-14       1
2017-04-15       0
2017-04-16       0
2017-04-17       1

工作日：周一至周五09:30-18:30

客户申请表：t14
表字段及内容：

a      b       c
1     申请   2017-04-14 18:03:00
1     通过   2017-04-17 09:43:00
2     申请   2017-04-13 17:02:00
2     通过   2017-04-15 09:42:00

1- 问题一

描述：计算上表中从申请到通过占用的工作时长
输出结果如下所示：

a         d
1        0.67h
2       10.67h 

参考答案：

select 
    a,
    round(sum(diff)/3600,2) as d
from (
    select 
        a,
        apply_time,
        pass_time,
        dates,
        rn,
        ct,
        is_work,
        case when is_work=1 and rn=1 then unix_timestamp(concat(dates,' 18:30:00'),'yyyy-MM-dd HH:mm:ss')-unix_timestamp(apply_time,'yyyy-MM-dd HH:mm:ss')
            when is_work=0 then 0
            when is_work=1 and rn=ct then unix_timestamp(pass_time,'yyyy-MM-dd HH:mm:ss')-unix_timestamp(concat(dates,' 09:30:00'),'yyyy-MM-dd HH:mm:ss')
            when is_work=1 and rn!=ct then 9*3600
        end diff
    from (
        select 
            a,
            apply_time,
            pass_time,
            time_diff,
            day_diff,
            rn,
            ct,
            date_add(start,rn-1) dates
        from (
            select 
                a,
                apply_time,
                pass_time,
                time_diff,
                day_diff,
                strs,
                start,
                row_number() over(partition by a) as rn,
                count(*) over(partition by a) as ct
            from (
                select 
                    a,
                    apply_time,
                    pass_time,
                    time_diff,
                    day_diff,
                    substr(repeat(concat(substr(apply_time,1,10),','),day_diff+1),1,11*(day_diff+1)-1) strs
                from (
                    select 
                        a,
                        apply_time,
                        pass_time,
                        unix_timestamp(pass_time,'yyyy-MM-dd HH:mm:ss')-unix_timestamp(apply_time,'yyyy-MM-dd HH:mm:ss') time_diff,
                        datediff(substr(pass_time,1,10),substr(apply_time,1,10)) day_diff
                    from (
                        select 
                            a,
                            max(case when b='申请' then c end) apply_time,
                            max(case when b='通过' then c end) pass_time
                        from t14
                        group by a
                    ) tmp1
                ) tmp2
            ) tmp3 
            lateral view explode(split(strs,",")) t as start
        ) tmp4
    ) tmp5
    join d_date 
    on tmp5.dates = d_date.date_id
) tmp6
group by a;