HIVE 技巧积累之合并重叠日期

1. 原始数据：

   

2020-09-01	2020-09-11
2020-09-07	2020-09-20
2020-09-24	2020-10-25
2020-09-25	2020-10-11
2020-09-26	2020-09-27
2020-09-30	2020-10-25
2020-10-30	2020-11-30
2020-10-31	2020-11-29
2020-10-31	2020-12-01

2. 需求

  公司在某一时期搞一系列活动，在活动周期内，计算客户在这段时间内是否连续每天都参加活动

  比如一到五月，每个月隔几天就出新的活动，比如A活动有十天[09-01,09-11]，B活动有五天[09-07,09-20]，中间有重叠的日期，这样算客户[09-01,09-20]这段时间都是在参加活动

  需求需要将这个客户的参加活动时间周期统计出来

3. 分析

  这里面主要牵扯两个问题：一，几个时间段日期有重叠，二，单独没重叠的日期。

  判断日期是否有重叠，根据开始日期start_time进行升序排序，取出每个区间中开始日期最小值和结束日期最大值

  首先我们需要将所属同一区间的数据整理出来，使用开窗函数依次对比开始日期start_time和结束日期 end_time，找到start_time大于之前end_time中的最大值的记录，打标记为1

 

 

 这样就将区间区分出来了

然后取区间中的最小start_time和最大end_time即可

  上代码：

 with a_starts as (  --计算区间并打上结束标记
    select 
      start_time , 
      end_time ,
      CASE 
         when start_time > max(lag_end_time) over(
           order by start_time, end_time
           rows between unbounded preceding and current row
         )
         then 1 else 0
      END a_start
    from (
    select 
      start_time,
      end_time,
      lag(start_time, 1) over(order by start_time, end_time) lag_start_time,
      lag(end_time, 1)  over(order by start_time, end_time) lag_end_time
    from a 
    ) t1
)
, a1 as (    --将区间内标记求和，作为分组依据
  select start_time, end_time,
  sum(a_start) over(   
    order by start_time, end_time
    rows between unbounded preceding and current row
  ) grp
  from a_starts
)
select 
  min(start_time) start_time,
  max(end_time) end_time
from a1
  group by grp
  order by 1, 2;

结果：

 

 

感谢知乎博主的分享：https://zhuanlan.zhihu.com/p/68630630