Início / dba / Perguntas / 36943
Accepted
boobiq
Asked: 2013-03-19 08:37:06 +0800 CST

Encontre "n" números livres consecutivos da tabela

  • 772

Eu tenho algumas tabelas com números como este (status é FREE ou ASSIGNED)

status do número id_set         
-----------------------
1 000001 ATRIBUÍDO
1 000002 GRÁTIS
1 000003 ATRIBUÍDO
1 000004 GRÁTIS
1 000005 GRÁTIS
1 000006 ATRIBUÍDO
1 000007 ATRIBUÍDO
1 000008 GRÁTIS
1 000009 GRÁTIS
1 000010 GRÁTIS
1 000011 ATRIBUÍDO
1 000012 ATRIBUÍDO
1 000013 ATRIBUÍDO
1 000014 GRÁTIS
1 000015 ATRIBUÍDO

e eu preciso encontrar "n" números consecutivos, então para n = 3, a consulta retornaria

1 000008 GRÁTIS
1 000009 GRÁTIS
1 000010 GRÁTIS

Deve retornar apenas o primeiro grupo possível de cada id_set (na verdade, seria executado apenas para id_set por consulta)

Eu estava verificando as funções do WINDOW, tentei algumas consultas como COUNT(id_number) OVER (PARTITION BY id_set ROWS UNBOUNDED PRECEDING), mas foi tudo o que consegui :) Não consegui pensar em lógica, como fazer isso no Postgres.

Eu estava pensando em criar uma coluna virtual usando funções WINDOW contando linhas anteriores para cada número em que status = 'FREE' e, em seguida, selecione o primeiro número, onde contagem é igual ao meu número "n".

Ou talvez agrupe números por status, mas apenas de um ASSIGNED para outro ASSIGNED e selecione apenas grupos contendo pelo menos "n" números

EDITAR

Eu encontrei esta consulta (e mudei um pouco)

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

que produz grupos de números FREE/ASSIGNED, mas gostaria de ter todos os números apenas do primeiro grupo que atende à condição

SQL Fiddle

postgresql group-by

6 respostas

  1. Best Answer

    Este é um problema . Supondo que não haja lacunas ou duplicatas no mesmo id_setconjunto:

    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
;

    Aqui está um link de demonstração do SQL Fiddle * para esta consulta: http://sqlfiddle.com/#!1/a2633/1 .

    ATUALIZAR

    Para retornar apenas um conjunto, você pode adicionar mais uma rodada de classificação:

    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
;

    Aqui está uma demonstração para este também: http://sqlfiddle.com/#!1/a2633/2 .

    Se você precisar fazer um conjunto porid_set , altere a RANK()chamada assim:

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

    Além disso, você pode fazer com que a consulta retorne o menor conjunto correspondente (ou seja, primeiro tente retornar o primeiro conjunto de exatamente três números consecutivos, se existir, caso contrário, quatro, cinco etc.), assim:

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

    ou assim (um por id_set):

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

    * As demos do SQL Fiddle vinculadas nesta resposta usam a instância 9.1.8, pois a 9.2.1 não parece estar funcionando no momento.

    • 20

  2. Uma variante simples e rápida :

    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;

    • Requer uma sequência sem intervalos de números number(conforme fornecido na pergunta).

    • Funciona para qualquer número de valores possíveis em statusalém 'FREE', mesmo com NULL.

    • A principal característica é subtrair depois row_number()de numbereliminar as linhas não qualificadas. Números consecutivos terminam no mesmo grp– e grptambém é garantido que estejam em ordem crescente .

    • Então você pode GROUP BY grpe contar os membros. Como você parece querer a primeira ocorrência ORDER BY grp LIMIT 1e obtém a posição inicial e o comprimento da sequência (pode ser >= n ).

    Conjunto de linhas

    Para obter um conjunto real de números, não procure a tabela outra vez. Muito mais barato com 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;

    Se você realmente deseja uma string com zeros à esquerda como você exibe em seus valores de exemplo, use to_char()com o FMmodificador (modo de preenchimento):

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

    SQL Fiddle com caso de teste estendido e ambas as consultas.

    Resposta intimamente relacionada:

    • 10

  3. Esta é uma maneira bastante genérica de fazer isso.

    Tenha em mente que depende de sua numbercoluna ser consecutiva. Se não for uma função de janela e/ou solução de tipo CTE provavelmente será necessária:

    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')
    • 8

  4. Isso retornará apenas o primeiro dos 3 números. Não requer que os valores de numbersejam consecutivos. Testado no 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 ;

    E isso mostrará todos os números (onde houver 3 ou mais 'FREE'posições consecutivas):

    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 ;
    • 5 
  5. 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 ;

    Neste caso 5 números consecutivos - portanto a diferença deve ser 4 ou em outras palavras count(r3.number) = ne r2.number = r1.number + n - 1.

    Com junções:

    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 ;
    • 0 
  6. 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
    • -1

relate perguntas