如何選擇具有順序數據的塊並聚合 id

我有下表：

id(int) startDate(timestamp)         endDate(timestamp)            plan_id(int)  planned(bool)  machine(int)
--------------------------------------------------------------------------------------------------------------
 2005  '2019-01-16 08:29:24.872736'  '2019-01-16 08:30:23.529706'    34          true              6
 2004  '2019-01-16 08:19:28.011148'  '2019-01-16 08:29:22.680828'    34          true              6
 2003  '2019-01-16 08:18:27.074312'  '2019-01-16 08:19:25.753475'    34          true              6
 2002  '2019-01-16 08:08:30.206288'  '2019-01-16 08:18:24.856308'    34          true              6
 2001  '2019-01-16 08:07:29.163124'  '2019-01-16 08:08:27.949013'    34          true              6
 2000  '2019-01-16 07:59:03.221309'  '2019-01-16 08:00:14.654391'    null        false             7
 1999  '2019-01-16 08:00:00.986367'  '2019-01-16 08:00:03.221309'    null        false             6
 1998  '2019-01-16 07:57:30.711044'  '2019-01-16 07:59:58.778444'    null        false             6
 1997  '2019-01-16 07:56:32.466508'  '2019-01-16 07:57:28.489287'    null        false             6
 1996  '2019-01-16 07:50:06.887349'  '2019-01-16 07:56:30.237725'    null        false             6
 1995  '2019-01-16 07:46:34.327582'  '2019-01-16 07:50:04.619592'    33          true              6
 1994  '2019-01-16 07:45:33.813483'  '2019-01-16 07:46:32.014849'    33          true              6
 1993  '2019-01-16 07:24:39.267365'  '2019-01-16 07:39:23.786911'    null        false             6
 1992  '2019-01-16 07:23:39.646218'  '2019-01-16 07:24:37.093414'    null        false             6
 1991  '2019-01-16 07:13:41.166337'  '2019-01-16 07:23:37.403375'    null        false             6
 1990  '2019-01-16 07:12:39.961234'  '2019-01-16 07:13:38.907838'    null        false             6
 1989  '2019-01-16 07:10:46.984236'  '2019-01-16 07:12:37.647108'    null        false             6
 1988  '2019-01-15 17:05:59.832834'  '2019-01-15 17:08:21.603931'    31          true              6
 1987  '2019-01-15 17:04:59.567046'  '2019-01-15 17:05:57.565188'    31          true              6
 1986  '2019-01-15 17:00:01.411266'  '2019-01-15 17:10:57.255158'    31          true              7

我必須為特定機器選擇計劃外記錄塊的 ID。我一直在嘗試使用視窗功能，不幸的是，我無法弄清楚它的邏輯！

這裡的問題是，由於我們有不同的機器，我們不能依賴順序 id，只是endDate一個序列的 id 非常接近下一個startDate（可以設置一個容差常數，例如 3 秒）。

我想查詢結果是：塊的最小值startDate、最大值endDate和 ID。對於這個帶有 的範例machine = 6，它將是：

blockStartDate                blockEndDate                  ids
-------------------------------------------------------------------------------
"2019-01-16 07:50:06.887349" "2019-01-16 08:00:03.221309" [1999,1998,1997,1996]
"2019-01-16 07:10:46.984236" "2019-01-16 07:39:23.786911" [1989,1990,1991,1992,1993]

請注意，在這種情況下，答案具有順序 ID，但並非總是如此。我正在努力提供真實數據，其中 2 台機器同時生成數據並且 id 變得無用。

如果塊是通過連續遞增的 ID（或連續遞增startDate或endDate，相同的原則）用相同的machineand定義的NOT planned，忽略可能的間隙，只用行分隔planned IS NOT FALSE，那麼不需要推測 end 和 begin 之間的容差，你可以使用這：

SELECT min(startDate) AS block_start_date
    , max(endDate)   AS block_end_date
    , array_agg(id)  AS ids
FROM (
  SELECT id, startDate, endDate, planned
       , row_number() OVER (ORDER BY id)
       - row_number() OVER (PARTITION BY planned ORDER BY id) AS grp
  FROM   tbl
  WHERE  machine = 6
  ORDER  BY id  --  to get sorted arrays
  ) sub
WHERE  NOT planned
GROUP  BY grp;

db<>fiddle here（建立在 McNets 的小提琴上）

基本：

• 選擇最長的連續序列

請注意，這將返回統一排序的數組，這與您的範例不同排序順序不同。

旁白：planned好像說的不多plan_id IS NOT NULL。如果是這樣，您可以完全刪除多餘的列。冗餘列只會增加成本。

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