為每組行聚合行

我正在嘗試解決一個用過程語言很容易解決的問題，但我無法以有效的方式用 SQL 解決它。

讓我先解釋一下這個問題。我有一系列事件發生在某個時間點。為簡單起見，我們假設每個事件都發生在不同的時間點。一個事件由一個數字表示。以以下數據為例：

create table event
(
   time   time,
   status integer
);

insert into event
values
('12:00', 0),
('13:00', 8),
('14:00', 4),
('15:00', 2),
('16:00', 0),
('17:00', 9),
('18:00', 5),
('19:00', 8),
('20:00', 0),
('21:00', 1),
('22:00', 3),
('23:00', 0);

現在，循環被定義為在兩個狀態為狀態的事件之間發生的一系列事件0。所以，對於上面的數據，我有以下循環：

cycle 1: 0 -> 8 -> 4 -> 2 -> 0
cycle 2: 0 -> 9 -> 5 -> 8 -> 0
cycle 3: 0 -> 1 -> 3 -> 0

目標是找到這些循環。

我有一個可行的解決方案（見fiddle），它如下所示：

with
cycle_boundary(begin_time, end_time) as
(
   select begin_time, end_time
   from   (
            select    time, lead(time) over(order by time)
            from      event
            where     status = 0
          ) as cycle(begin_time, end_time)
   where  end_time is not null
)
select     begin_time, end_time, array_agg(row(time, status)) as events
from       cycle_boundary
cross join event
where      cycle_boundary.begin_time < event.time and
          cycle_boundary.end_time > event.time
group by   cycle_boundary.begin_time,
          cycle_boundary.end_time;

這輸出：

begin_time  end_time    events
12:00:00    16:00:00    {"(13:00:00,8)","(14:00:00,4)","(15:00:00,2)"}
16:00:00    20:00:00    {"(17:00:00,9)","(18:00:00,5)","(19:00:00,8)"}
20:00:00    23:00:00    {"(21:00:00,1)","(22:00:00,3)"}

問題是這個解決方案效率很低。首先，我掃描完整的事件以找到邊界（這是兩個帶有 status 的後續事件0），然後，我掃描這些邊界以找到包含的事件。這基本上是一個嵌套循環，所以O(n^2).

在過程語言中，這可以在O(n)事件排序的前提下輕鬆解決（如果有索引，我們也可以在數據庫中實現event(time)）：遍歷有序事件並收集事件（在臨時集合）只要我們沒有遇到狀態為的事件0；一旦我們遇到這樣的事件，我們輸出到目前為止收集的事件並清除這個臨時集合。

所以我的問題歸結為：我們如何O(n)在 SQL 中解決這個問題？我認為其中一個問題是FILTER聚合視窗函式的子句沒有在 PostgreSQL 中實現，但這在這裡可能無關緊要。

您可以使用status設置組，然後獲取每個組的最小和最大時間。

如果有序列號id（PK），並且可以用來設置命令，也許可以得到更好的性能。

由於每個中間人都status=0屬於兩組，我添加了一個新列，其中包含下一行的時間以獲得最大（時間）。

with ev as
(
 select
     time, status,
     lead(time) over (order by time) as next_time,
     sum(case when status = 0 then 1 else 0 end) over (order by id) as grp
 from
     event
)
select 
   min(time) as min_time,
   max(next_time) as max_time,
   array_agg(row(time, status)) filter (where status <> 0) as events
from 
   ev
group by
   grp
order by
   grp;

min_time | 最大時間 | 事件 
:------- | :------- | :---------------------------------------------
12:00:00 | 16:00:00 | {"(13:00:00,8)","(14:00:00,4)","(15:00:00,2)"}
16:00:00 | 20:00:00 | {"(17:00:00,9)","(18:00:00,5)","(19:00:00,8)"}
20:00:00 | 23:00:00 | {"(21:00:00,1)","(22:00:00,3)"} 
23:00:00 | *空* | *零*                                          

db<>在這裡擺弄

引用自：https://dba.stackexchange.com/questions/270157