從表中查找“n”個連續的空閒號碼

我有一些這樣的數字表（狀態是免費或已分配）

id_set 號碼狀態 
-----------------------
1 000001 已分配
1 000002 免費
1 000003 已分配
1 000004 免費
1 000005 免費
1 000006 已分配
1 000007 已分配
1 000008 免費
1 000009 免費
1 000010 免費
1 000011 已分配
1 000012 已分配
1 000013 已分配
1 000014 免費
1 000015 已分配

我需要找到“n”個連續數字，所以對於 n = 3，查詢將返回

1 000008 免費
1 000009 免費
1 000010 免費

它應該只返回每個 id_set 的第一個可能組（實際上，它只會為每個查詢的 id_set 執行）

我正在檢查 WINDOW 函式，嘗試了一些類似的查詢COUNT(id_number) OVER (PARTITION BY id_set ROWS UNBOUNDED PRECEDING)，但這就是我得到的全部:) 我想不出邏輯，如何在 Postgres 中做到這一點。

我正在考慮使用 WINDOW 函式創建虛擬列，計算 status = ‘FREE’ 的每個數字的前行，然後選擇第一個數字，其中 count 等於我的“n”數字。

或者可能按狀態對數字進行分組，但只能從一個分配到另一個分配，並且只選擇包含至少“n”個數字的組

編輯

我找到了這個查詢（並對其進行了一些更改）

WITH q AS
(
 SELECT *,
        ROW_NUMBER() OVER (PARTITION BY id_set, status ORDER BY number) AS rnd,
        ROW_NUMBER() OVER (PARTITION BY id_set ORDER BY number) AS rn
 FROM numbers
)
SELECT id_set,
      MIN(number) AS first_number,
      MAX(number) AS last_number,
      status,
      COUNT(number) AS numbers_count
FROM q
GROUP BY id_set,
        rnd - rn,
        status
ORDER BY
    first_number

它產生免費/分配號碼組，但我想只從滿足條件的第一組中獲得所有號碼

SQL小提琴

這是一個差距和孤島問題。id_set假設在同一個集合中沒有間隙或重複：

WITH partitioned AS (
 SELECT
   *,
   number - ROW_NUMBER() OVER (PARTITION BY id_set) AS grp
 FROM atable
 WHERE status = 'FREE'
),
counted AS (
 SELECT
   *,
   COUNT(*) OVER (PARTITION BY id_set, grp) AS cnt
 FROM partitioned
)
SELECT
 id_set,
 number
FROM counted
WHERE cnt >= 3
;

這是此查詢的 SQL Fiddle 展示*連結： http ://sqlfiddle.com/#!1/a2633/1 。

更新

要僅返回一組，您可以再添加一輪排名：

WITH partitioned AS (
 SELECT
   *,
   number - ROW_NUMBER() OVER (PARTITION BY id_set) AS grp
 FROM atable
 WHERE status = 'FREE'
),
counted AS (
 SELECT
   *,
   COUNT(*) OVER (PARTITION BY id_set, grp) AS cnt
 FROM partitioned
),
**ranked AS (
SELECT
*,
RANK() OVER (ORDER BY id_set, grp) AS rnk
FROM counted
WHERE cnt >= 3
)**
SELECT
 id_set,
 number
FROM **ranked
WHERE rnk = 1**
;

這也是一個展示：http ://sqlfiddle.com/#!1/a2633/2 。

如果您需要將其設置為一組*，id_set*請像這樣更改RANK()呼叫：

RANK() OVER (***PARTITION BY id_set*** ORDER BY ***grp***) AS rnk

此外，您可以使查詢返回最小的匹配集（即首先嘗試返回恰好三個連續數字（如果存在，否則為四個、五個等）的第一組，如下所示：

RANK() OVER (ORDER BY ***cnt,*** id_set, grp) AS rnk

或像這樣（每個一個id_set）：

RANK() OVER (PARTITION BY id_set ORDER BY ***cnt,*** grp) AS rnk

---

• 此答案中連結的 SQL Fiddle 展示使用 9.1.8 實例，因為 9.2.1 目前似乎無法正常工作。

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