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. 按照 品牌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 :

解题思路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