Início / dba / Perguntas / 62598
Accepted
user664833
Asked: 2014-04-05 20:01:36 +0800 CST

selecione o número especificado de IDs exclusivos em que a segunda coluna é exclusiva

  • 772

Observe o exemplo a seguir, começando na linha superior ( id=9) e vá descendo, selecionando um limite de 4linhas com sec's que ainda não vimos. Nós "selecionamos" id=9porque ainda não temos sec=1. Continuamos a descer assim, mas quando chegamos id=7, pulamos porque já temos sec=5(da carreira com id=8). Continuamos da mesma maneira e finalmente paramos id=3porque acumulamos 4linhas (nosso limite desejado).

 id | sec
----+-----
  9 |   1  <- 1
  8 |   5  <- 2
  7 |   5  # skip, already have sec=5
  6 |   4  <- 3
  5 |   1  # skip, already have sec=1
  4 |   1  # skip, already have sec=1
  3 |   3  <- 4
  2 |   2
  1 |   1

Claro que o SQLalgoritmo pode (será!) ser diferente do que eu descrevi.

Resultado desejado:

 id
----
  9
  8
  6
  3
(4 rows)

Se eu quisesse aumentar o limite de 5linhas, a linha com id=2 seria incluída nos resultados. No entanto, se eu aumentasse o limite de 6linhas, a linha com nãoid=1 seria adicionada porque já foi vista.sec=1

Nota: Embora não deva importar, estou no PostgreSQL 9.3.1 .

Caso você queira construir rapidamente a tabela para testar isso:

CREATE TABLE my_table (id serial primary key, sec integer DEFAULT 0 NOT NULL);
INSERT INTO my_table (sec) VALUES
  (1)
, (2)
, (3)
, (1)
, (1)
, (4)
, (5)
, (5)
, (1);
CREATE INDEX index_my_table_on_sec ON my_table (sec);
postgresql select

2 respostas

  1. SELECT id,
       sec
FROM (
  SELECT id,
         sec,
         row_number() OVER (PARTITION BY sec ORDER BY id DESC) AS rn
  FROM my_table
) t
WHERE rn = 1
ORDER BY id DESC 
LIMIT 4;

    Exemplo SQLFiddle: http://sqlfiddle.com/#!15/1ca01/1

    • 5
  2. Best Answer

    No Postgres, isso é mais simples com DISTINCT ON:

    SELECT *
FROM (
   SELECT DISTINCT ON (sec)
          id, sec
   FROM   tbl
   ORDER  BY sec, id DESC
   ) sub
ORDER  BY id DESC
LIMIT  4;

    Explicação detalhada nesta resposta relacionada no SO:

    Para uma mesa grande e pequenaLIMIT , nem esta nem a solução de @a_horse são muito eficientes. A subconsulta irá percorrer toda a tabela, perdendo muito tempo...

    CTE recursivo

    Eu tentei e não consegui resolver problemas semelhantes com um CTE recursivo no passado e recorri a uma solução procedural com PL/pgSQL. Exemplo:

    Finalmente, aqui está um rCTE funcional:

    WITH RECURSIVE cte AS (
   (  -- parentheses required
   SELECT id, '{}'::int[] AS last_arr, ARRAY[sec] AS arr
   FROM   tbl
   ORDER  BY id DESC
   LIMIT  1
   )
   UNION ALL
   (
   SELECT b.id, c.arr
        , CASE WHEN b.sec = ANY (c.arr) THEN c.arr ELSE b.sec  || c.arr END
   FROM   cte c
   JOIN   tbl b ON b.id < c.id
   WHERE  array_length(c.arr, 1) < 4
   ORDER  BY id DESC
   LIMIT  1
   )
   )
SELECT id, arr[1] AS sec
FROM   cte
WHERE  last_arr <> arr;

    Não é tão rápido ou elegante quanto eu esperava e nem tão rápido quanto a função abaixo, mas mais rápido que a consulta acima em meus testes.

    Função PL/pgSQL

    De longe o mais rápido :

    CREATE OR REPLACE FUNCTION f_first_uniq(_rows int)
   RETURNS TABLE (id int, sec int) AS
$func$
DECLARE
   _arr int[];
BEGIN
   FOR id, sec IN
      SELECT t.id, t.sec FROM tbl t ORDER BY t.id DESC
   LOOP
      IF sec = ANY (_arr) THEN
         -- do nothing
      ELSE
         RETURN NEXT;
         _arr := _arr || sec;
         EXIT WHEN array_length(_arr, 1) >= _rows;
      END IF;
   END LOOP;
END
$func$  LANGUAGE plpgsql;

    Ligar:

    SELECT * FROM f_first_uniq(4);

    SQL Fiddle demonstrando todos os três.

    Pode ser feito para funcionar para qualquer tabela com nomes de tabela e coluna como parâmetros e SQL dinâmico com EXECUTE...

    Porque se importar?

    Em uma tabela de teste com apenas 30klinhas, a função executou 2000x mais rápido que a consulta acima (que já executou ~ 30% mais rápido que a versão de a_horse). Essa diferença cresce com o tamanho da mesa. O desempenho da função é quase constante, enquanto o desempenho da consulta piora progressivamente, pois tenta encontrar valores distintos em toda a tabela primeiro. Tente isso em uma tabela com um milhão de linhas...

    • 5

relate perguntas