hive_面试题 【打折日期交叉问题】

需求描述 : 计算每个品牌总的打折销售天数，注意其中的交叉日期
    比如 vivo 品牌,第一次活动时间为 2021-06-05 到 2021-06-15
                  第二次活动时间为  2021-06-09 到 2021-06-21 其中 9 号到 15 号为重复天数,只统计一次
                  即 vivo 总打折天数为 2021-06-05 到 2021-06-21 共计 17 天

-- 数据准备

-- DDL
create table test4 (
`id` string comment '品牌id',
`stt` string comment '打折开始日期',
`edt` string comment '打折结束日期')
 comment '商品打折记录表'
row format delimited fields terminated by '\t'
lines terminated by '\n' stored as orc;

insert overwrite table test4
select 'oppo', '2021-06-05', '2021-06-09' union all
select 'oppo', '2021-06-11', '2021-06-21' union all
select 'vivo', '2021-06-05', '2021-06-15' union all
select 'vivo', '2021-06-09', '2021-06-21' union all
select 'vivo', '2021-06-22', '2021-06-25' union all
select 'redmi', '2021-06-05', '2021-06-21' union all
select 'redmi', '2021-06-09', '2021-06-15' union all
select 'redmi', '2021-06-17', '2021-06-26' union all
select 'huawei', '2021-06-05', '2021-06-26' union all
select 'huawei', '2021-06-09', '2021-06-15' union all
select 'huawei', '2021-06-17', '2021-06-21';

解题思路1(连续阶段分组 最大最小值差值法):

-- 基础数据
1----3
  2----4
    3--------------------6
       4--5
                               8--------10

-- 转换结果
1------------------------6
                               8--------10

结果为 : (6-1)+1 + (10-8)+1 = 9

-- 具体步骤
1. 按照 品牌id分组,对 打折开始日期 正序排序(如下图)
2. 对每条记录 打标记(是否交叉)
     交叉判断依据 :  分组内的 max(首行~当前行的前一行的edt) 是否比 当前行stt大
                        大于等于 则表示交叉 打标记为0
                        小于    则表示不交叉 打标记为1
3. 对打的标记 累计求和(区分出不同的交叉时间段),分成不同的组
4. 取 同组内的 min(stt)、max(edt)、求时间差
5. 对 同组内的 时间差求和

1-------------x     0                   0
    2--------------y  0                 0
        3----i  0                       0
                     4--------z     1      1
                         5-------m 0    1

sql实现 :

sql实现 :


-- 为条记录,打标签(当前行是否为交叉行)
select
id
,stt
,edt
-- 对每条记录打标记(当前行是否交叉)
,case when datediff(
    max(edt) over (partition by id order by stt
    rows between unbounded preceding and 1 preceding) -- 首行到当前行的前一行
    ,stt)  >=0 then 0 -- 是交叉行
    else 1 -- 不是交叉行
end as if_cross
from test4;

id      stt             edt         if_cross
huawei  2021-06-05      2021-06-26      1
huawei  2021-06-09      2021-06-15      0
huawei  2021-06-17      2021-06-21      0

oppo    2021-06-05      2021-06-09      1
oppo    2021-06-11      2021-06-21      1

redmi   2021-06-05      2021-06-21      1
redmi   2021-06-09      2021-06-15      0
redmi   2021-06-17      2021-06-26      0

vivo    2021-06-05      2021-06-15      1
vivo    2021-06-09      2021-06-21      0
vivo    2021-06-22      2021-06-25      1

-- 最终SQL
with t1 as (
-- 为条记录,打标签(当前行是否为交叉行)
select
id
,stt
,edt
-- 对每条记录打标记(当前行是否交叉)
,case when datediff(
    max(edt) over (partition by id order by stt
    rows between unbounded preceding and 1 preceding) -- 首行到当前行的前一行
    ,stt)  >=0 then 0 -- 是交叉行
    else 1 -- 不是交叉行
end as if_cross
from test4),
t2 as (
-- 对打的标记 累计求和(区分出不同的交叉时间段),分成不同的组
select
id
,stt
,edt
,sum(if_cross) over(partition by id order by stt asc) as cross_group
from t1),
t3 as (
-- 对 每个id求和
select
id
,cross_group
,min(stt)
,max(edt)
,datediff(max(edt),min(stt)) + 1 as days
from t2
group by
id
,cross_group)
select
id
,sum(days)
from t3
group by id
;

-- 查询结果
id      _c1
huawei  22
oppo    16
redmi   22
vivo    21

Time taken: 38.432 seconds, Fetched: 4 row(s)

解题思路2(拆分距离 求和法):

-- 基础数据
1----3
  2----4
    3--------------------6
       4--5  0
                               8--------10

-- 转换结果
1----3 拆分成 1,3
  2----4 拆分成 4,4
    3--------------------6 拆分成 5,6
       4--5 拆分成  拆分成 0
                               8--------10 拆分成 8,10

结果为 :  (3-1)+1 + (4-4)+1 + (6-5)+1 + ? + (10-8)+1 = 3 + 1 + 2 + 3 = 9

sql实现(方式1) :

-- 拆分距离 求和法
select
id
,sum(days)
from (
         select id
              , stt
              , edt
              , pre_max_day
              , case
                    when pre_max_day is null then datediff(edt, stt) + 1
                    when stt < pre_max_day and edt > pre_max_day then datediff(edt, date_add(pre_max_day, 1)) + 1
                    when stt > pre_max_day then datediff(edt, stt) + 1
                    else 0
             end as days

         from (
                  select id
                       , stt
                       , edt
                       , max(edt) over (partition by id order by stt asc
                      rows between unbounded preceding and 1 preceding
                      ) as pre_max_day
                  from test4
              ) as t1
     ) as t2
group by id
;

-- 查询结果
id      _c1
huawei  22
oppo    16
redmi   22
vivo    21
Time taken: 23.858 seconds, Fetched: 4 row(s)

sql实现(方式2) :

解题思路2 :
id      stt             edt             maxEdt         stt              days
vivo    2021-06-05      2021-06-15      null           2021-06-05       11
vivo    2021-06-09      2021-06-21      2021-06-15     2021-06-16       6
vivo    2021-06-22      2021-06-25      2021-06-21     2021-06-22       4


5)最终HQL
select
    id,
    sum(if(days>=0,days+1,0)) days
from
    (select
    id,
    datediff(edt,stt) days
from
    (select
    id,
    if(maxEdt is null,stt,if(stt>maxEdt,stt,date_add(maxEdt,1))) stt,
    edt
from
    (select
    id,
    stt,
    edt,
    max(edt) over(partition by id order by stt rows between UNBOUNDED PRECEDING and 1 PRECEDING) maxEdt
from test4
        )t1)t2)t3
group by id;

id      days
huawei  22
oppo    16
redmi   22
vivo    21

Time taken: 27.447 seconds, Fetched: 4 row(s)

解题思路3(自定义函数 去重法):

    1. 自定义udf函数 getdays
        功能描述 :
                输入 : 开始日期,结束日期
                返回 : [开始日期...结束日期] 的数组
        示例 :
            -- 导入udf jar包
            add jar /root/ hive-udf-1.0-SNAPSHOT.jar;
            create temporary function getdays as "com.dxm.udf.getdays";

            select  getdays('2021-06-05','2021-06-08');
            结果 : ["2021-06-05","2021-06-06","2021-06-07","2021-06-08"]
        udf源码 :
            https://www.cnblogs.com/bajiaotai/p/15861277.html
    2. 思路说明 :
            1. 将 vivo,2021-06-05,2021-06-15 遍历成多行
            2. 对遍历的day,去重即可

查询sql

-- 查询sql
select
id
,count(distinct day)
from test4
lateral view explode(getdays(stt,edt)) tmp as day
group by id
;

-- 查询结果
id      _c1
huawei  22
oppo    16
redmi   22
vivo    21