SQL重叠交叉区间问题分析--HiveSQL面试题30

2021/7/15 19:35:59

本文主要是介绍SQL重叠交叉区间问题分析--HiveSQL面试题30,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

目 录

0 需求分析

1 数据准备

2 数据分析

3 小 结


0 需求分析

如下为平台商品促销

  • 数据: 字段为品牌,打折开始日期,打折结束日期
id stt edt
oppo2021-06-052021-06-09
oppo2021-06-112021-06-21
vivo2021-06-052021-06-15
vivo2021-06-092021-06-21
redmi2021-06-052021-06-21
redmi2021-06-092021-06-15
redmi2021-06-172021-06-26
huawei2021-06-052021-06-26
huawei2021-06-092021-06-15
huawei2021-06-172021-06-21

计算每个品牌总的打折销售天数。

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

1 数据准备

(1) 数据

vim brand.txt

oppo	2021-06-05	2021-06-09
oppo	2021-06-11	2021-06-21
vivo	2021-06-05	2021-06-15
vivo	2021-06-09	2021-06-21
redmi	2021-06-05	2021-06-21
redmi	2021-06-09	2021-06-15
redmi	2021-06-17	2021-06-26
huawei	2021-06-05	2021-06-26
huawei	2021-06-09	2021-06-15
huawei	2021-06-17	2021-06-21

(2)建表

create table if not exists brand(
 
id string,
stt string,
edt string
 
)
 
row format delimited
fields terminated by '\t'
 
;

(3) 加载数据

load data local inpath "/home/centos/dan_test/brand.txt" into table brand;

(4) 查询数据

hive> select * from brand;
OK
oppo	2021-06-05	2021-06-09
oppo	2021-06-11	2021-06-21
vivo	2021-06-05	2021-06-15
vivo	2021-06-09	2021-06-21
redmi	2021-06-05	2021-06-21
redmi	2021-06-09	2021-06-15
redmi	2021-06-17	2021-06-26
huawei	2021-06-05	2021-06-26
huawei	2021-06-09	2021-06-15
huawei	2021-06-17	2021-06-21
Time taken: 0.113 seconds, Fetched: 10 row(s)

2 数据分析

方法一:更新起始位置法

(1)比较基准点获取

问题分析:本题主要的问题点在于如何区分重叠日期

如果数据中没有重叠的日期的话,我们正常思维就是求出每行的结束日期与开始日期的差值,并加1,然后对同一品牌的数据的计算结果进行sum就可以求出结果了。.如oppo品牌的数据。

oppo    2021-06-05    2021-06-09   (9-5+1)=5
oppo    2021-06-11    2021-06-21    (21-11+1)=11

因此oppo的活动时间为11+5=16.然而本题当中缺存在着重叠的日期,而对于重叠的日期只能记为1天

我们拿vivo品牌的数据进行分析

vivo    2021-06-05    2021-06-15   15-5+1=11
vivo    2021-06-09    2021-06-21   21-9+1=13

可以看到2021-06-09到2021-06-15之间的日期是重叠的,在2021-06-05  - 2021-06-15之间已经包含进去,如果我们按照oppo的计算方法就会多算,算出的结果24明显不对,其中重叠的日期为15-9+1=7,正确的结果为24-7=17天。

如下图所示,染色区为重叠部分,通过观察可以发现,当下一条记录的stt小于上一条记录的edt的时候出现重叠。

我们拿redmi品牌数据继续分析。

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

实际中出现重叠的部分为2个,如下图

 如果按照vivo的分析方法,看下一条数据的stt是否小于上一条数据的edt,那么对于redmi品牌的数据只能分析出一个重叠区域,通过上图我们可以看出,实际上判断的是当前数据的stt是否小于之前所有记录的edt的最大值

 

 通过上述分析判断重叠部分主要思路就是:当前行的stt是否小于基准点,基准点就是除当前行外由窗口内第一条数据到当前行的前一条数据edt的最大值。也就是说如果当前行的stt小于除当前行之外的其余之前的所有行的edt最大值时,认为是重叠部分。

具体SQL实现如下:

select id
      ,stt
      ,edt
      ,max(edt) over(partition by id order by stt rows between UNBOUNDED PRECEDING and 1 PRECEDING) std_edt
from brand

 对于hive 1.2.1版本执行该SQL会报如下错误:

FAILED: SemanticException End of a WindowFrame cannot be UNBOUNDED PRECEDING

事实上,在该版本下不支持rows between UNBOUNDED PRECEDING and 1 PRECEDING这样的语法,只支持rows between UNBOUNDED PRECEDING and current row这样的语法。对于hive2以后该语法被支持,可以使用。

对于上述SQL在Hive1版本中,我们用如下SQL等效实现

select id
      ,stt
      ,edt
      ,lag_edt
      ,max(lag_edt) over(partition by id order by stt rows between 1 PRECEDING and current row) as std_edt
 from(
      select id
            ,stt
            ,edt
            ,lag(edt,1,null) over(partition by id order by stt) as lag_edt
      from brand
     ) t

计算结果如下:

--------------------------------------------------------------------------------
OK
huawei	2021-06-05	2021-06-26	NULL	NULL
huawei	2021-06-09	2021-06-15	2021-06-26	2021-06-26
huawei	2021-06-17	2021-06-21	2021-06-15	2021-06-26
oppo	2021-06-05	2021-06-09	NULL	NULL
oppo	2021-06-11	2021-06-21	2021-06-09	2021-06-09
redmi	2021-06-05	2021-06-21	NULL	NULL
redmi	2021-06-09	2021-06-15	2021-06-21	2021-06-21
redmi	2021-06-17	2021-06-26	2021-06-15	2021-06-21
vivo	2021-06-05	2021-06-15	NULL	NULL
vivo	2021-06-09	2021-06-21	2021-06-15	2021-06-15
Time taken: 22.076 seconds, Fetched: 10 row(s)

(2)重叠部分的判断及处理

通过上述分析判断重叠部分的规则为:当前行的stt小于std_edt(基准点)的时候认为重叠部分,当遇到重叠部分时候用std_edt+1的时间点更新当前stt时间点如果当前stt大于std_edt则保持当前的stt。相当于一个指针每次判断有重叠区域,不断修改其位置。具体SQL如下:

select id
      ,case when std_edt is null then stt
            when datediff(stt,std_edt)>0 then stt
       else date_add(std_edt,1)
       end as stt
      ,edt  
from(
    select id
          ,stt
          ,edt
          ,lag_edt
         ,max(lag_edt) over(partition by id order by stt rows between 1 PRECEDING and current row) as std_edt
    from(
      select id
            ,stt
            ,edt
            ,lag(edt,1,null) over(partition by id order by stt) as lag_edt
      from brand
    ) t
)m

计算结果如下:

--------------------------------------------------------------------------------
OK
huawei	2021-06-05	2021-06-26
huawei	2021-06-27	2021-06-15
huawei	2021-06-27	2021-06-21
oppo	2021-06-05	2021-06-09
oppo	2021-06-11	2021-06-21
redmi	2021-06-05	2021-06-21
redmi	2021-06-22	2021-06-15
redmi	2021-06-22	2021-06-26
vivo	2021-06-05	2021-06-15
vivo	2021-06-16	2021-06-21
Time taken: 12.054 seconds, Fetched: 10 row(s)

(3)计算结果。根据步骤2计算每一行的差值,如果存在负值说明是重叠区域,该计算部分可以忽略,然后将计算的结果进行累加。具体SQL如下

select id
      ,sum(if(diff>=0,diff+1,0)) as days
from(
    select id
          ,stt
          ,edt
          ,datediff(edt,stt) as diff
    from(
            select id
                  ,case when std_edt is null then stt
                        when datediff(stt,std_edt)>0 then stt
                   else date_add(std_edt,1)
                   end as stt
                  ,edt  
            from(
                select id
                      ,stt
                      ,edt
                      ,lag_edt
                     ,max(lag_edt) over(partition by id order by stt rows between 1 PRECEDING and current row) as std_edt
                from(
                  select id
                        ,stt
                        ,edt
                        ,lag(edt,1,null) over(partition by id order by stt) as lag_edt
                  from brand
                ) t
            )m
        ) n
    ) p
group by id

计算结果如下:

--------------------------------------------------------------------------------
OK
huawei	22
oppo	16
redmi	22
vivo	17
Time taken: 11.446 seconds, Fetched: 4 row(s)

方法2:暂未想到。

3 小 结

   本题难度比较大,实际上是通过SQL来考察算法,本题得到的启示是如何来定位重叠区域的问题,针对这一问题,本题给了很好的方法,改方法也具有通用性,也可以用于解决业务中的问题,业务中经常会遇到如何合并重叠区间或时间段的问题,或找出重叠的时间段,类似的问题都可以用本题给的方法解决。本题的算法思维可以借助算导中的重叠区间定位算法来辅助分析,给以启示。



这篇关于SQL重叠交叉区间问题分析--HiveSQL面试题30的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!


扫一扫关注最新编程教程