PostgresSQL-如何聚合多個連續行的組？

我有一個資料表示帶有 <tag, start & stop> 的單個表中的標記連續跨度。

下面的例子。

我試圖將多行組合成一行，條件是它們創建一個“連續跨度”。在下面的查詢中 - 我想要回傳連續跨度LEFT_MOST_CONTINUOUS最小值的功能（與最大值相同）。請注意，可能有多個連續跨度（應該具有不同的和值）。v_startRIGHT_MOST_CONTINUOUSv_stopv_startv_stop

輸入：

WITH data AS (
    SELECT *
    FROM (VALUES 
        ('a', 2, 3),
        ('a', 3, 5),
        ('a', 5, 7),
        ('a', 8, 10),
        ('a', 10, 12),
        ('a', 12, 14),
        ('b', 7, 8),
        ('b', 8, 10),
        ('b', 12, 15),
        ('c', 10, 11)
    ) AS T(tag, v_start, v_stop)
    ORDER BY tag, v_start, v_stop
)
SELECT tag,
       LEFT_MOST_CONTINUOUS(v_start) OVER (PARTITION BY tag),
       RIGHT_MOST_CONTINUOUS(v_stop) OVER (PARTITION BY tag)
FROM data
ORDER BY 1, 2, 3

我希望得到以下輸出：

"a" 2   7
"a" 8   14
"b" 7   10
"b" 12  15
"c" 10  11

因為我想將前 3 個連續的元組（用于標記“a”）合并為一個代表整個跨度的單個值；接下來的 3 個元組相同（同樣是“a”）。然后對于“b”，我們可以合并下一個 2，但忽略第三個（它是 v_start ！= 另一個的 v_stop）。和“c”沒有什么可以合并的。

幫助表示贊賞，

塔爾

uj5u.com熱心網友回復：

當標簽沒有先前的記錄或v_start大于先前的記錄時，您可以通過標記每個組的第一條記錄來使用間隙和孤島方法v_stop：

select tag, v_start, v_stop, 
         coalesce(lag(v_stop) over w < v_start, true) as is_end_grp
    from data
  window w as (partition by tag order by v_start)

使用sum()boolean is_end_grpcast to int(1 if true, 0 if false) 對組進行編號：

  select tag, sum(is_end_grp::int) over (partition by tag 
                                             order by v_start) as grp_num,
         v_start, v_stop
    from mark_gaps

聚合(tag, grp_num)將產生您想要的結果：

select tag, min(v_start) as v_start, max(v_stop) as v_stop
  from numbered_groups
 group by tag, grp_num
 order by tag, v_start

作業資料庫<>小提琴

uj5u.com熱心網友回復：

使用numbered_groups@Mike Organek 回答的邏輯。我剛從另一個地方開始

WITH data AS (
    SELECT *
    , case when lead(v_start) over(partition by tag order by v_start) = v_stop then 0 else 1 end stopcheck
    , case when lag(v_stop) over(partition by tag order by v_stop) = v_start then 0 else 1 end startcheck
    FROM (VALUES 
        ('a' , 2 , 3),
        ('a', 3, 5),
        ('a', 5, 7),
        ('a', 8, 10),
        ('a', 10, 12),
        ('a', 12, 14),
        ('b', 7, 8),
        ('b', 8, 10),
        ('b', 12, 15),
        ('c', 10, 11)
    ) AS T(tag, v_start, v_stop)
    ORDER BY tag, v_start, v_stop
)
,cnt as (
  select *
  , sum(startcheck) over (partition by tag order by v_start) grpn 
  from data)
select c1.tag, c1.v_start, c2.v_stop
from cnt c1 
inner join cnt c2 
  on c1.tag = c2.tag and c1.grpn = c2.grpn
  where c1.startcheck = 1 and c2.stopcheck = 1

這個邏輯都是基于這樣的假設，即您的資料總是從最后一行停止的地方開始，沒有重疊等。

通過相對比較前一行和下一行來創建一個startcheck和。stopcheck從這里使用另一個視窗函式sum() over對start記錄進行排序（所以我們不匹配第二批的開始到第一批的停止）

將表加入到自身匹配的類似tag和組中。過濾開始和停止記錄

uj5u.com熱心網友回復：

您可以使用以下查詢

WITH data AS (
    SELECT *
    FROM (VALUES 
        ('a', 2, 3),
        ('a', 3, 5),
        ('a', 5, 7),
        ('a', 8, 10),
        ('a', 10, 12),
        ('a', 12, 14),
        ('b', 7, 8),
        ('b', 8, 10),
        ('b', 12, 15),
        ('c', 10, 11)
    ) AS T(tag, v_start, v_stop)
    ORDER BY tag, v_start, v_stop
),
cte1 as(
   select *,
      case
        when lag(v_stop)over(partition by tag order by(select null)) = v_start
          then 0
          else 1
      end as grp
  from data
),
cte2 as(
  select *, 
         sum(grp) over (partition by tag  order by v_start) as rnk
  from cte1
)

select tag,min(v_start)v_start,max(v_stop)v_stop
from cte2
group by tag,rnk
order by tag

db<>fiddle中的演示

標籤：

上一篇：在Postgres中使用約束更新jsonb陣列值

下一篇：PSQLException：錯誤：“。”處或附近的語法錯誤