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

目 录

0 需求分析

1 数据准备

2 数据分析

3 小 结

0 需求分析

如下为平台商品促销

• 数据： 字段为品牌，打折开始日期，打折结束日期

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

注意：其中的交叉日期，比如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的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！