在postgres中合并日期范围以消除重叠

debugcn 发表于 Dev

用户

如果我有一个postgres表，请执行以下操作：

  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

我如何合并日期以消除像这样的重叠范围：

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

埃戈尔·罗戈夫（Egor Rogov）

在chopCTE中，原始范围被“切成”较小的，不相交（但可能相邻）的范围。它们是从原始范围的所有端点（包括起点和终点）构建的。

Main select的工作方式如下（从内而外读取）：

当一个范围与先前的范围相邻时，该范围的相邻标志为零（假设范围按其开始日期排序）。
相邻标志的累加总和为我们提供了一个分组值：所有相邻范围将具有相同的总和。
最外面的块仅计算相邻范围范围的边界值。

窗口功能的黑魔法...

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，finish因为它end是一个关键字。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。