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

目前网上流传着一个段子，说算法工程师实际上就是 SQL boy，数据分析师是 PPT boy。艺术来源于现实，实际上的我们真的有很多时间在写 SQL 出数据，或者是针对 bad case 做数据的进一步分析。

这不，近期这边接到的一个需求就是对玩家的某项行为进行统计。一般来讲，掌握基本 SQL 的技巧，这些需求的难度都不大。但是这个需求需要将玩家用户的多个重叠日期进行拉伸去重。这一下可难到大伙儿。在自个儿思考无果，团队讨论之后也没啥直接的办法。

在网上搜索一番后，很多都不是很对应。不过好在几轮筛选，找到了一个类似的需求。原文链接在这里：🔗。为了方便后来的人，在这里做个分析记录，以及后面举一反三该怎么做。毕竟这些东西很少出现在教程和课本里，但是当业务方有这个需求的时候，常常又很紧急，容不得细思慢想。

问题定义：

在解决一个问题之前，我们需要先明确定义问题。这里的问题是对多个重叠日期，用 SQL 将其进行去重，并在 HIVE 环境中使用。

对于日期情况的定义

这里采用穷举法，可以得出以下13类情况：

问题简化

解决问题的核心是简化问题。这个问题看起来情况众多，实际上，对于我们的任务，只有两种情况：一个是两个日期有重叠；一个是两个日期没有重叠。

对于不同的情况，要做不同的处理。重叠日期取最大最小日期即可，非重叠的分段取。剩下的即是通过工具去实现逻辑。

数据准备

这里采用原作的方式定义数据，创建出上面的13中情况。实际上，如果你的格式和下面的类似，做出对应的调整即可。

drop table t purge;
create table t (
  test_case varchar2(32) not null,
  start_date date not null,
  end_date date not null
);
Insert into t values ('01:precedes',to_date('01','DD'),to_date('02','DD'));
Insert into t values ('01:precedes',to_date('03','DD'),to_date('04','DD'));
Insert into t values ('02:meets',to_date('01','DD'),to_date('02','DD'));
Insert into t values ('02:meets',to_date('02','DD'),to_date('03','DD'));
Insert into t values ('03:overlaps',to_date('01','DD'),to_date('03','DD'));
Insert into t values ('03:overlaps',to_date('02','DD'),to_date('04','DD'));
Insert into t values ('04:finished by',to_date('01','DD'),to_date('03','DD'));
Insert into t values ('04:finished by',to_date('02','DD'),to_date('03','DD'));
Insert into t values ('05:contains',to_date('01','DD'),to_date('04','DD'));
Insert into t values ('05:contains',to_date('02','DD'),to_date('03','DD'));
Insert into t values ('06:starts',to_date('01','DD'),to_date('02','DD'));
Insert into t values ('06:starts',to_date('01','DD'),to_date('03','DD'));
Insert into t values ('07:equals',to_date('01','DD'),to_date('02','DD'));
Insert into t values ('07:equals',to_date('01','DD'),to_date('02','DD'));
Insert into t values ('08:started by',to_date('01','DD'),to_date('03','DD'));
Insert into t values ('08:started by',to_date('01','DD'),to_date('02','DD'));
Insert into t values ('09:during',to_date('02','DD'),to_date('03','DD'));
Insert into t values ('09:during',to_date('01','DD'),to_date('04','DD'));
Insert into t values ('10:finishes',to_date('02','DD'),to_date('03','DD'));
Insert into t values ('10:finishes',to_date('01','DD'),to_date('03','DD'));
Insert into t values ('11:overlapped by',to_date('02','DD'),to_date('04','DD'));
Insert into t values ('11:overlapped by',to_date('01','DD'),to_date('03','DD'));
Insert into t values ('12:met by',to_date('02','DD'),to_date('03','DD'));
Insert into t values ('12:met by',to_date('01','DD'),to_date('02','DD'));
Insert into t values ('13:preceded by',to_date('03','DD'),to_date('04','DD'));
Insert into t values ('13:preceded by',to_date('01','DD'),to_date('02','DD'));
commit;

定义出来的数据如下

解决方案

with grp_starts as (
  select test_case, start_date, end_date,
  case
    when start_date > max(end_date) over(
      partition by test_case order by start_date, end_date
      rows between unbounded preceding and 1 preceding
    )
    then 1 else 0
  end grp_start
  from t
)
, grps as (
  select test_case, start_date, end_date,
  sum(grp_start) over(
    partition by test_case order by start_date, end_date
    rows between unbounded preceding and current row
  ) grp
  from grp_starts
)
select test_case,
min(start_date) start_date,
max(end_date) end_date
from grps
group by test_case, grp
order by 1, 2;

这里的的核心思想同上：判断日期是否重叠，重叠日期取最大最小的日期，非重叠日期分段取

这里使用了 with 技巧，通过该技巧，将数据临时存储至内存，加速了执行速度。这句话是网上能直接搜到的，但实际上它还有另一个功能上的作用。即抛开性能作用来说，with 语法能够对先前的查询结果进行下一步的处理。这意味着不用频繁的建立中间表，省去空间或是简化了逻辑的复杂度。

这里主要分为三段：

判断日期是否重叠：对数据以 test_case 为划分依据，按开始日期和结束日期排序。比较当前开始日期和之前的结束日期，若开始日期大于最大的结束日期，说明两日期无重叠，grp_start 标志位记为1，反之记为0。
对相邻重叠日期做区分：按同样的划分排序方式。对当前日期及以前的 grp_start 求和，由于重叠日期被标记为0，因而最后相邻重叠日期的 grp 标志位将相同，其余日期各不相同。起到区分不重叠日期，合并重叠日期的作用。
分组选取最大最小值，得到结果：按 test_case 与 grp 分组，选取各组最小和最大的日期，最后按第一列和第二列排序，得到目标结果。

小结：通过拆分的思想+窗口函数的帮助，出色完成组织交代的任务，在 HIVE 中实现了合并重叠日期这一任务。但同时也要注意，在功能保证的同时，该脚本如同算法一样，也是有边界的。比如当 end_date 为空时，将会出现错误。这种情况最好先把空值填充为特殊值 ‘9999-12-31’，结束后再转换为空值。

总结

在 HIVE 中合并重叠日期，一开始看起来很难，但实际上学会分析问题，分解问题，简化问题，其实不难。网上有很多有用的资源，要学会合理利用。在实现基本需求的同时，给自己留些时间，深挖背后的原理，将会给自己、同时也是给组织带来持续性的效率提升。

例如：搞完这一套后，虽说重写祖传代码不太现实，但对于新的需求，可能会思考 with 语法带来的好处，将其运用到业务中去，减少中间表冗余，简化逻辑，增强 sql 代码可读性。