Znajdź „n” kolejne bezpłatne numery z tabeli

Mam tabelę z takimi liczbami (status jest BEZPŁATNY lub PRZYPISANY)

status numeru id_set         
-----------------------
1 000001 PRZYPISANY
1 000002 ZA DARMO
1 000003 PRZYPISANE
1 000004 ZA DARMO
1 000005 ZA DARMO
1 000006 PRZYPISANY
1 000007 PRZYPISANY
1 000008 ZA DARMO
1 000009 ZA DARMO
1 000010 ZA DARMO
1 000011 PRZYPISANY
1 000012 PRZYPISANY
1 000013 PRZYPISANY
1 000014 ZA DARMO
1 000015 PRZYPISANY

i muszę znaleźć „n” kolejne liczby, więc dla n = 3 zapytanie zwróci

1 000008 ZA DARMO
1 000009 ZA DARMO
1 000010 ZA DARMO

Powinien zwracać tylko pierwszą możliwą grupę każdego zestawu id_set (w rzeczywistości byłby wykonywany tylko dla zestawu id_set na zapytanie)

Sprawdzałem funkcje WINDOW, próbowałem zapytań COUNT(id_number) OVER (PARTITION BY id_set ROWS UNBOUNDED PRECEDING), ale to wszystko, co mam :) Nie mogłem wymyślić logiki, jak to zrobić w Postgresie.

Myślałem o stworzeniu wirtualnej kolumny za pomocą funkcji WINDOW, zliczającej poprzednie wiersze dla każdej liczby, gdzie status = „FREE”, a następnie wybierz pierwszą liczbę, gdzie liczba jest równa mojej „n”.

A może grupuj numery według statusu, ale tylko od jednego PRZYPISANEGO do innego PRZYPISANEGO i wybierz tylko grupy zawierające co najmniej „n” liczby

EDYTOWAĆ

Znalazłem to zapytanie (i trochę je zmieniłem)

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

który tworzy grupy DARMOWYCH / PRZYPISANYCH liczb, ale chciałbym mieć wszystkie liczby tylko z pierwszej grupy, która spełnia warunek

SQL Fiddle

Jest to problem luk i wysp . Zakładając, że nie ma luk ani duplikatów w tym samym id_setzestawie:

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
;

Oto link demonstracyjny SQL Fiddle ^* dla tego zapytania: http://sqlfiddle.com/#!1/a2633/1 .

AKTUALIZACJA

Aby zwrócić tylko jeden zestaw, możesz dodać jeszcze jedną rundę rankingu:

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
;

Oto też wersja demonstracyjna tego: http://sqlfiddle.com/#!1/a2633/2 .

Jeśli kiedykolwiek będziesz musiał ustawić jeden zestaw naid_set , zmień RANK()połączenie w następujący sposób:

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

Dodatkowo możesz sprawić, że zapytanie zwróci najmniejszy pasujący zestaw (tj. Najpierw spróbuj zwrócić pierwszy zestaw dokładnie trzech kolejnych liczb, jeśli istnieje, w przeciwnym razie cztery, pięć itd.), Tak jak poniżej:

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

lub tak (jeden na id_set):

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

_{* Dema Fiddle SQL połączone w tej odpowiedzi używają instancji 9.1.8, ponieważ wersja 9.2.1 nie wydaje się w tej chwili działać.}

Prosty i szybki wariant:

SELECT min(number) AS first_number, count(*) AS ct_free
FROM (
    SELECT *, number - row_number() OVER (PARTITION BY id_set ORDER BY number) AS grp
    FROM   tbl
    WHERE  status = 'FREE'
    ) x
GROUP  BY grp
HAVING count(*) >= 3  -- minimum length of sequence only goes here
ORDER  BY grp
LIMIT  1;

• Wymaga ciągłej sekwencji liczb w number(jak podano w pytaniu).

• Działa dla dowolnej liczby możliwych wartości statuspoza 'FREE', nawet z NULL.

• Główną cechą jest to, aby odjąć row_number()od numberpo wyeliminowaniu niekwalifikujących wiersze. Kolejne liczby kończą się w tym samym grp- i grpgwarantuje się, że będą w porządku rosnącym .

• Następnie możesz GROUP BY grpi policzyć członków. Ponieważ wydaje się, że chcesz pierwszego wystąpienia, ORDER BY grp LIMIT 1a otrzymasz pozycję początkową i długość sekwencji (może być> = n ).

Zestaw wierszy

Aby uzyskać rzeczywisty zestaw liczb, nie przeglądaj tabeli innym razem. Znacznie taniej z generate_series():

SELECT generate_series(first_number, first_number + ct_free - 1)
    -- generate_series(first_number, first_number + 3 - 1) -- only 3
FROM  (
   SELECT min(number) AS first_number, count(*) AS ct_free
   FROM  (
      SELECT *, number - row_number() OVER (PARTITION BY id_set ORDER BY number) AS grp
      FROM   tbl
      WHERE  status = 'FREE'
      ) x
   GROUP  BY grp
   HAVING count(*) >= 3
   ORDER  BY grp
   LIMIT  1
   ) y;

Jeśli faktycznie chcesz ciąg z zerami wiodącymi, taki jak w przykładowych wartościach, użyj to_char()z FMmodyfikatorem (tryb wypełnienia):

SELECT to_char(generate_series(8, 11), 'FM000000')

SQL Fiddle z rozszerzonym przypadkiem testowym i obydwoma zapytaniami.

Ściśle związana odpowiedź:

• Wybierz najdłuższą ciągłą sekwencję

Jest to dość ogólny sposób na zrobienie tego.

Pamiętaj, że to zależy od tego, czy numberkolumna jest po kolei. Jeśli nie jest to funkcja Windows i / lub rozwiązanie typu CTE, prawdopodobnie będzie potrzebne:

SELECT 
    number
FROM
    mytable m
CROSS JOIN
   (SELECT 3 AS consec) x
WHERE 
    EXISTS
       (SELECT 1 
        FROM mytable
        WHERE number = m.number - x.consec + 1
        AND status = 'FREE')
    AND NOT EXISTS
       (SELECT 1 
        FROM mytable
        WHERE number BETWEEN m.number - x.consec + 1 AND m.number
        AND status = 'ASSIGNED')

Zwróci tylko pierwszą z 3 liczb. Nie wymaga, aby wartości numberbyły następujące po sobie. Testowane w SQL-Fiddle :

WITH cte3 AS
( SELECT
    *,
    COUNT(CASE WHEN status = 'FREE' THEN 1 END) 
        OVER (PARTITION BY id_set ORDER BY number
              ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING)
      AS cnt
  FROM atable
)
SELECT
  id_set, number
FROM cte3
WHERE cnt = 3 ;

I to pokaże wszystkie liczby (gdzie są 3 lub więcej kolejnych 'FREE'pozycji):

WITH cte3 AS
( SELECT
    *,
    COUNT(CASE WHEN status = 'FREE' THEN 1 END) 
        OVER (PARTITION BY id_set ORDER BY number
              ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING)
      AS cnt
  FROM atable
)
, cte4 AS
( SELECT
    *, 
    MAX(cnt) 
        OVER (PARTITION BY id_set ORDER BY number
              ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
      AS maxcnt
  FROM cte3
)
SELECT
  id_set, number
FROM cte4
WHERE maxcnt >= 3 ;

select r1.number from some_table r1, 
some_table r2,
some_table r3,
some_table r4 
where r3.number <= r2.number 
and r3.number >= r1.number 
and r3.status = 'FREE' 
and r2.number = r1.number + 4 
and r4.number <= r2.number 
and r4.number >= r1.number 
and r4.status = 'ASSIGNED'
group by r1.number, r2.number having count(r3.number) = 5 and count(r4.number) = 0 order by r1.number asc limit 1 ;

W tym przypadku 5 kolejnych liczb - dlatego różnica musi wynosić 4 lub innymi słowy count(r3.number) = ni r2.number = r1.number + n - 1.

Z połączeniami:

select r1.number 
from some_table r1 join 
 some_table r2 on (r2.number = r1.number + :n -1) join
 some_table r3 on (r3.number <= r2.number and r3.number >= r1.number) join
 some_table r4 on (r4.number <= r2.number and r4.number >= r1.number)
where  
 r3.status = 'FREE' and
 r4.status = 'ASSIGNED'
group by r1.number, r2.number having count(r3.number) = :n and count(r4.number) = 0 order by r1.number asc limit 1 ;

CREATE TABLE #ConsecFreeNums
(
     id_set BIGINT
    ,number VARCHAR(10)
    ,status VARCHAR(10)
)

CREATE TABLE #ConsecFreeNumsResult
(
     Seq    INT
    ,id_set BIGINT
    ,number VARCHAR(10)
    ,status VARCHAR(10)
)

INSERT #ConsecFreeNums
SELECT 1, '000002', 'FREE' UNION
SELECT 1, '000003', 'ASSIGNED' UNION
SELECT 1, '000004', 'FREE' UNION
SELECT 1, '000005', 'FREE' UNION
SELECT 1, '000006', 'ASSIGNED' UNION
SELECT 1, '000007', 'ASSIGNED' UNION
SELECT 1, '000008', 'FREE' UNION
SELECT 1, '000009', 'FREE' UNION
SELECT 1, '000010', 'FREE' UNION
SELECT 1, '000011', 'ASSIGNED' UNION
SELECT 1, '000012', 'ASSIGNED' UNION
SELECT 1, '000013', 'ASSIGNED' UNION
SELECT 1, '000014', 'FREE' UNION
SELECT 1, '000015', 'ASSIGNED'

DECLARE @id_set AS BIGINT, @number VARCHAR(10), @status VARCHAR(10), @number_count INT, @number_count_check INT

DECLARE ConsecFreeNumsCursor CURSOR FAST_FORWARD FOR
SELECT
       id_set
      ,number
      ,status
 FROM
      #ConsecFreeNums
WHERE id_set = 1
ORDER BY number

OPEN ConsecFreeNumsCursor

FETCH NEXT FROM ConsecFreeNumsCursor INTO @id_set, @number, @status

SET @number_count_check = 3
SET @number_count = 0

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @status = 'ASSIGNED'
    BEGIN
        IF @number_count = @number_count_check
        BEGIN
            SELECT 'Results'
            SELECT * FROM #ConsecFreeNumsResult ORDER BY number
            BREAK
        END
        SET @number_count = 0
        TRUNCATE TABLE #ConsecFreeNumsResult
    END
    ELSE
    BEGIN
        SET @number_count = @number_count + 1
        INSERT #ConsecFreeNumsResult SELECT @number_count, @id_set, @number, @status
    END
    FETCH NEXT FROM ConsecFreeNumsCursor INTO @id_set, @number, @status
END

CLOSE ConsecFreeNumsCursor
DEALLOCATE ConsecFreeNumsCursor

DROP TABLE #ConsecFreeNums
DROP TABLE #ConsecFreeNumsResult