HiveSQL-窗口查询-区间合并

区间合并在业务需求开发中偶尔可以预见，LeetCode中第56题便是该场景的题。本文从该题解法开始，引申到该问题的HiveSQL实现（毕竟本人是数据开发，很多需求都得用SQL实现）。

Java实现

给出一个区间的集合，请合并所有重叠的区间。
示例1

输入: intervals = [[1,3],[2,6],[8,10],[15,18]]
输出: [[1,6],[8,10],[15,18]]
解释: 区间 [1,3] 和 [2,6] 重叠, 将它们合并为 [1,6]

示例2

输入: intervals = [[1,4],[4,5]]
输出: [[1,5]]
解释: 区间 [1,4] 和 [4,5] 可被视为重叠区间。

区间合并的思路总结起来就是，当前区间的左边界若小于前面区间的右边界，则与前面的区间合并为一个区间，且合并后的区间的右边界更新为当前区间的右边与之前右边界中的较大值；否则当前区间不能合并。Java代码如下所示：

public int[][] merge(int[][] intervals) {
        if(intervals == null || intervals.length <= 0) return new int[][]{};
        int n = intervals.length;
        Arrays.sort(intervals, Comparator.comparingInt(o -> o[0]));

        List<int[]> res = new ArrayList<>(n);
        int preMaxRight = intervals[0][1];
        int preMinLeft = intervals[0][0];

        for(int i = 1; i < n; i++) {
            if(intervals[i][0] <= preMaxRight) {
                preMaxRight = Math.max(preMaxRight, intervals[i][1]);
            } else {
                res.add(new int[]{preMinLeft, preMaxRight});
                preMaxRight = intervals[i][1];
                preMinLeft = intervals[i][0];
            }
        }
        res.add(new int[]{preMinLeft, preMaxRight});
        return res.toArray(new int[res.size()][]);
    }

Sql实现

区间合并问题的解法涉及当前数据与其之前一定范围数据的累计值（前面区间的右边界）的比较计算，在Sql中用窗口查询来解决此类问题。另外，解决这个问题有个技巧：

1. 在用以上思路判断是否合并的时候，我们用一个标志列标志当前区间是否是一个新的区间的开始，用1表示；否则当前区间需要与其前面的区间合并，用0表示。
2. 再一次用窗口查询，运用sum函数，计算从窗口开始到当前行的标志列的和，和相等的即为同一窗口的数据（因为不是合并后区间开始位置的原始区间的标志位为0，累积到当前的sum值一定与其所属的合并后区间的开始原始区间相同）。
   假设表t中有两列start_date与end_date分别表示原始区间的左右界，banner与position为业务相关的字段；需求是将同一banner且同一position下的区间进行合并，则用Sql对区间进行合并的解法：

with grp_starts as (
  select banner, position, start_date, end_date,
  case
    when start_date > max(end_date) over(
      partition by banner, position 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 banner, position, start_date, end_date,
  sum(grp_start) over(
    partition by banner, position order by start_date, end_date
    rows between unbounded preceding and current row
  ) grp
  from grp_starts
)

select banner, position,
min(start_date) start_date,
max(end_date) end_date
from grps
group by banner, position, grp
order by 1, 2, 3;