在postgres中合并日期范围以消除重叠 [英] Coalescing date ranges in postgres to eliminate overlaps

问题描述

如果我有一个postgres表a：

If I have a postgres table a:

  member |    start   |    end
---------+------------+------------
    1    | 2015-01-01 | 2015-05-01
---------+------------+------------
    1    | 2015-03-01 | 2015-06-01
---------+------------+------------
    2    | 2015-01-01 | 2015-05-01
---------+------------+------------
    2    | 2015-06-01 | 2015-08-01

我如何合并日期以消除类似的重叠范围：

How would I coalesce dates to eliminate overlapping ranges like this:

  member |    start   |    end
---------+------------+------------
    1    | 2015-01-01 | 2015-06-01
---------+------------+------------
    2    | 2015-01-01 | 2015-05-01
---------+------------+------------
    2    | 2015-06-01 | 2015-08-01

推荐答案

在 chop 将CTE原始范围切成较小的，不相交（但可能相邻）的范围。它们是从原始范围的所有端点（从起点到终点）构造的。

In the chop CTE original ranges are "chopped" into smaller, non-intersecting (but possibly adjacent) ranges. They are constructed from all the end points of the original ranges, both start and finish.

主要选择方式如下（从内而外读取）：

Main select works as follows (read it from the inside out):

1. 与先前的范围相邻时，该范围的相邻标志为零（假设范围按其开始日期排序）。


2. 相邻标志的累加总和为我们提供一个分组值：所有相邻范围将具有相同的总和。


3. 最外面的块仅计算相邻组的边界值

窗口函数 ...

with chop as (
  select member,
         pt as start,
         lead(pt) over (partition by member order by pt) finish,
         (
           select count(*)
           from   a
           where  b.member = a.member
           and    b.pt >= a.start
           and    b.pt < a.finish
         ) need_it
  from   (
           select member, start pt from a
           union
           select member, finish pt from a
         ) b
)
-- 3
select member,
       min(start),
       max(finish)
from   (
         -- 2
         select member,
                start,
                finish,
                sum(adjacent) over (partition by member order by start) grp
         from   (
                  -- 1
                  select member,
                         start,
                         finish,
                         case
                           when start <= lag(finish) over (partition by member order by start)
                           then 0
                           else 1
                         end adjacent
                  from   chop
                  where  need_it > 0
                ) t
       ) q
group by member,
         grp
order by member,
         min(start);

我将 end 重命名为完成，因为 end 是关键字。

I renamed end to finish because end is a keyword.

这篇关于在postgres中合并日期范围以消除重叠的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！