重置日期 POSTGRES 函数中日期之间的范围

Question

>我有一个开始日期和结束日期的日期范围，例如

'2017-01-01', '2017-01-31'
'2017-01-04', '2017-02-20'
'2017-02-21', '2017-03-29'
'2017-03-17', '2017-04-12'

我需要输出为

'2017-01-01', '2017-02-20'
'2017-02-21', '2017-04-12'

是否有可能仅使用 postgres 函数获取输出？ 有人可以帮我吗？

Answer 1

我觉得类似的问题被问了很多次，我应该在可能的逻辑背后写一个解释，所以我又加了一行来介绍这个问题(否则你可以只比较对，而不是树(：

t=# insert into t select '2017-01-08','2017-02-12';
INSERT 0 1

现在的"逻辑"：

t=# with c as (select daterange(s,e), s,e, daterange(s,e) && lead(daterange(s,e)) over (order by s) ovps from t)
select *, case when not lag(ovps) over (order by s) or row_number() over (order by s) = 1 then s end strt, case when not ovps or row_number() over (order by s) = count(1) over () then e end fnsh
from c;
        daterange        |     s      |     e      | ovps |    strt    |    fnsh
-------------------------+------------+------------+------+------------+------------
 [2017-01-01,2017-01-31) | 2017-01-01 | 2017-01-31 | t    | 2017-01-01 |
 [2017-01-04,2017-02-20) | 2017-01-04 | 2017-02-20 | t    |            |
 [2017-01-08,2017-02-12) | 2017-01-08 | 2017-02-12 | f    |            | 2017-02-12
 [2017-02-21,2017-03-29) | 2017-02-21 | 2017-03-29 | t    | 2017-02-21 |
 [2017-03-17,2017-04-12) | 2017-03-17 | 2017-04-12 |      |            | 2017-04-12
(5 rows)

我知道我在这里发明了一个轮子，但有人应该展示它是如何工作的。因此，上面的 ovps 列显示范围是否重叠，strt 和 fnsh 分别是按 s 排序的结果范围的开始和结束(这是折叠前原始数据集的原始开始(......

下一个：

t=# with c as (select daterange(s,e), s,e, daterange(s,e) && lead(daterange(s,e)) over (order by s) ovps from t)
, n as (select *, case when not lag(ovps) over (order by s) or row_number() over (order by s) = 1 then s end strt, case when not ovps or row_number() over (order by s) = count(1) over () then e end fnsh
from c)
select daterange, strt, fnsh, dense_rank() over (order by strt), dense_rank() over (order by fnsh) from n order by s;
        daterange        |    strt    |    fnsh    | dense_rank | dense_rank
-------------------------+------------+------------+------------+------------
 [2017-01-01,2017-01-31) | 2017-01-01 |            |          1 |          3
 [2017-01-04,2017-02-20) |            |            |          3 |          3
 [2017-01-08,2017-02-12) |            | 2017-02-12 |          3 |          1
 [2017-02-21,2017-03-29) | 2017-02-21 |            |          2 |          3
 [2017-03-17,2017-04-12) |            | 2017-04-12 |          3 |          2
(5 rows)

我们dense_rank获取订单中何时满足日期(最后为 NULL(。现在我们有了满足值时的顺序，我们可以得到最小值和最大值：

t=# with c as (select daterange(s,e), s,e, daterange(s,e) && lead(daterange(s,e)) over (order by s) ovps from t)
, n as (select *, case when not lag(ovps) over (order by s) or row_number() over (order by s) = 1 then s end strt, case when not ovps or row_number() over (order by s) = count(1) over () then e end fnsh
from c)
, d as (select daterange, strt, fnsh, dense_rank() over (order by strt) ds, dense_rank() over (order by fnsh) de from n order by s)
select distinct min(strt) over (partition by least(ds,de)), max(fnsh) over (partition by least(ds,de)) from d;
    min     |    max
------------+------------
            |
 2017-02-21 | 2017-04-12
 2017-01-01 | 2017-02-12
(3 rows)

现在删除空行并正确排序：

t=# with c as (select daterange(s,e), s,e, daterange(s,e) && lead(daterange(s,e)) over (order by s) ovps from t)
, n as (select *, case when not lag(ovps) over (order by s) or row_number() over (order by s) = 1 then s end strt, case when not ovps or row_number() over (order by s) = count(1) over () then e end fnsh
from c)
, d as (select daterange, strt, fnsh, dense_rank() over (order by strt) ds, dense_rank() over (order by fnsh) de from n order by s)
, f as (select distinct min(strt) over (partition by least(ds,de)), max(fnsh) over (partition by least(ds,de)) from d)
select min startc, max endc from f where min is not null order by min;
   startc   |    endc
------------+------------
 2017-01-01 | 2017-02-12
 2017-02-21 | 2017-04-12
(2 rows)

最后，我很确定你可以在数学上减少大部分"逻辑"并使其整洁......