目 录
0 需求分析
如下为平台商品促销
- 数据： 字段为品牌，打折开始日期，打折结束日期
| id | stt | edt |
| 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 |
计算每个品牌总的打折销售天数。
注意：其中的交叉日期，比如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来考察算法，本题得到的启示是如何来定位重叠区域的问题，针对这一问题，本题给了很好的方法，改方法也具有通用性，也可以用于解决业务中的问题，业务中经常会遇到如何合并重叠区间或时间段的问题，或找出重叠的时间段，类似的问题都可以用本题给的方法解决。本题的算法思维可以借助算导中的重叠区间定位算法来辅助分析，给以启示。