PostgreSQL将多个期间合并为一个 [英] postgresql combining several periods into one

问题描述

我正在尝试合并范围。

WITH a AS (
select '2017-09-16 07:12:57' as begat,'2017-09-16 11:30:22' as endat
union 
select '2017-09-18 17:05:21' ,'2017-09-19 13:18:01'
union 
select '2017-09-19 15:34:40' ,'2017-09-22 13:29:37'
union 
select '2017-09-22 12:24:16' ,'2017-09-22 13:18:29' 
union 
select '2017-09-28 09:48:54' ,'2017-09-28 13:39:13'
union 
select '2017-09-20 13:52:43' ,'2017-09-20 14:14:43' 
  ), b AS (
   SELECT *, lag(endat) OVER (ORDER BY begat) < begat OR NULL AS step
   FROM   a
   )
, c AS (
   SELECT *, count(step) OVER (ORDER BY begat) AS grp
   FROM   b
   )
SELECT min(begat), coalesce( max(endat), 'infinity' ) AS range
FROM   c
GROUP  BY grp
ORDER  BY 1 

结果

   1 "2017-09-16 07:12:57";"2017-09-16 11:30:22"
   2 "2017-09-18 17:05:21";"2017-09-19 13:18:01"
   3 "2017-09-19 15:34:40";"2017-09-22 13:29:37"
   4 "2017-09-22 12:24:16";"2017-09-22 13:18:29"
   5 "2017-09-28 09:48:54";"2017-09-28 13:39:13"

位置3,4相交（endata>下一个生）
如何使所有相交的并集成为一个大间隔

positions 3,4 intersect (endata> next begat) How do I make the union of all the intersections into one large interval

我需要结果

   1 "2017-09-16 07:12:57";"2017-09-16 11:30:22"
   2 "2017-09-18 17:05:21";"2017-09-19 13:18:01"
   3 "2017-09-19 15:34:40";"2017-09-22 13:29:37"
   4 "2017-09-28 09:48:54";"2017-09-28 13:39:13"

推荐答案

嘿，我会建议使用以下过程：

Hey I would suggest using the following process :

1-标识一行何时是新行，因此将不重叠的值（CTE b）赋值为1

1- Identify when a row is new, so you give a value of 1 to values that do not overlap (CTE b)

2-将彼此重叠的行排在一起。这样，您可以看到有一个公共标识符，该标识符将使您能够最大化和最小生成和终止（CTE c）

2- Sequence together the rows that have overlaps with others. This way you can see have a common identifier that will allow you to MAX and MIN begat and endat (CTE c)

3-对于每个序列，给出生成的MIN和endat的最大值，这样您将获得最终值

3- For each sequence, give the MIN of begat and the MAX of endat so you will have your final values

WITH a AS (
select '2017-09-16 07:12:57' as begat,'2017-09-16 11:30:22' as endat
union 
select '2017-09-18 17:05:21' ,'2017-09-19 13:18:01'
union 
select '2017-09-19 15:34:40' ,'2017-09-22 13:29:37'
union 
select '2017-09-22 12:24:16' ,'2017-09-22 13:18:29' 
union 
select '2017-09-28 09:48:54' ,'2017-09-28 13:39:13'
union 
select '2017-09-20 13:52:43' ,'2017-09-20 14:14:43' 
  )
, b AS (
SELECT
    begat
  , endat
  , (begat > MAX(endat) OVER w IS TRUE)::INT is_new
FROM a
WINDOW w AS (ORDER BY begat ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
) 
, c AS (
SELECT
    begat
  , endat
  , SUM((is_new)) OVER (ORDER BY begat) seq
FROM b
)
SELECT
    MIN(begat) beg_at
  , MAX(endat) end_at
FROM c
GROUP BY seq

这篇关于PostgreSQL将多个期间合并为一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！