最真实的大数据SQL面试题（二）

13. 抽象分组–断点排序

表名：t13
表字段及内容：

a    b
2014  1
2015  1
2016  1
2017  0
2018  0
2019  -1
2020  -1
2021  -1
2022  1
2023  1

问题一：断点排序

输出结果如下所示：

a    b    c 
2014  1    1
2015  1    2
2016  1    3
2017  0    1
2018  0    2
2019  -1   1
2020  -1   2
2021  -1   3
2022  1    1
2023  1    2

参考答案:

select  
  a,
  b,
  row_number() over( partition by b,repair_a order by a asc) as c--按照b列和[b的组首]分组，排序
from 
(
  select  
    a,
    b,
    a-b_rn as repair_a--根据b列值出现的次序,修复a列值为b首次出现的a列值,称为b的[组首]
  from 
  (
   select 
     a,
     b,
     row_number() over( partition by b order by  a  asc ) as b_rn--按b列分组,按a列排序,得到b列各值出现的次序
   from t13 
  )tmp1
)tmp2--注意，如果不同的b列值，可能出现同样的组首值，但组首值需要和a列值 一并参与分组，故并不影响排序。
order by a asc; 

14. 业务逻辑的分类与抽象–时效

日期表：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

问题一：计算上表中从申请到通过占用的工作时长

输出结果如下所示：

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;

15. 时间序列–进度及剩余

表名：t15
表字段及内容：

date_id      is_work
2017-07-30      0
2017-07-31      1
2017-08-01      1
2017-08-02      1
2017-08-03      1
2017-08-04      1
2017-08-05      0
2017-08-06      0
2017-08-07      1

问题一：求每天的累计周工作日，剩余周工作日

输出结果如下所示：

date_id      week_to_work  week_left_work
2017-07-31      1             4
2017-08-01      2             3
2017-08-02      3             2
2017-08-03      4             1
2017-08-04      5             0
2017-08-05      5             0
2017-08-06      5             0

参考答案:
此处给出两种解法，其一：

select 
 date_id
,case date_format(date_id,'u')
    when 1 then 1