ti-enxame.com

Eliminar linhas duplicadas em uma instrução PostgreSQL SELECT

Esta é a minha consulta:

SELECT autor.entwickler,anwendung.name
  FROM autor 
  left join anwendung
    on anwendung.name = autor.anwendung;

 entwickler |    name     
------------+-------------
 Benutzer 1 | Anwendung 1
 Benutzer 2 | Anwendung 1
 Benutzer 2 | Anwendung 2
 Benutzer 1 | Anwendung 3
 Benutzer 1 | Anwendung 4
 Benutzer 2 | Anwendung 4
(6 rows)

Eu quero manter uma linha para cada valor distinto no campo name e descartar os outros assim:

 entwickler |    name     
------------+-------------
 Benutzer 1 | Anwendung 1
 Benutzer 2 | Anwendung 2
 Benutzer 1 | Anwendung 3
 Benutzer 1 | Anwendung 4

No MySQL, eu faria apenas:

SELECT autor.entwickler,anwendung.name
  FROM autor
  left join anwendung
    on anwendung.name = autor.anwendung
 GROUP BY anwendung.name;

Mas o PostgreSQL me dá esse erro:

ERRO: a coluna "autor.entwickler" deve aparecer na cláusula GROUP BY ou ser usada em uma função agregada LINHA 1: SELECT autor.entwickler FROM autor left join anwendung on a ...

Entendo perfeitamente o erro e assumo que a implementação do mysql é menos compatível com o SQL do que a implementação do postgres. Mas como posso obter o resultado desejado?

18
The Surrican

Atualmente, o PostgreSQL não permite declarações ambíguas GROUP BY Em que os resultados dependem da ordem em que a tabela é varrida, do plano usado etc. É assim que o padrão diz que deve funcionar com o AFAIK, mas alguns bancos de dados (como versões do MySQL anterior a 5.7) permite consultas mais flexíveis que apenas selecionam o primeiro valor encontrado para os elementos que aparecem na lista SELECT, mas não em GROUP BY.

No PostgreSQL, você deve usar DISTINCT ON para esse tipo de consulta.

Você deseja escrever algo como:

SELECT DISTINCT ON (anwendung.name) anwendung.name, autor.entwickler
FROM author 
left join anwendung on anwendung.name = autor.anwendung;

(Sintaxe corrigida com base no comentário de acompanhamento)

É um pouco como a pseudo-função ANY_VALUE(...) do MySQL 5.7 para group by, Mas ao contrário - diz que os valores na cláusula distinct on Devem ser únicos e qualquer valor é aceitável para as colunas não especificadas.

A menos que haja um ORDER BY, Não há garantia quanto aos valores selecionados. Você normalmente deve ter um ORDER BY Para previsibilidade.

Também foi observado que o uso de um agregado como min() ou max() funcionaria. Embora isso seja verdade - e levará a resultados confiáveis ​​e previsíveis, ao contrário do uso de DISTINCT ON Ou de um ambigioso GROUP BY - ele tem um custo de desempenho devido à necessidade de classificação ou agregação extra, e apenas funciona para tipos de dados ordinais.

35
Craig Ringer

A resposta de Craig e sua consulta resultante nos comentários compartilham a mesma falha: A tabela anwendung está no lado direito de um LEFT JOIN, o que contradiz sua intenção óbvia. Você se preocupa com anwendung.name E escolhe autor.entwicklerarbitrariamente. Voltarei a isso mais abaixo.

Deveria ser:

SELECT DISTINCT ON (1) an.name, au.entwickler
FROM   anwendung an
LEFT   JOIN autor au ON an.name = au.anwendung;

DISTINCT ON (1) é apenas um atalho sintático para DISTINCT ON (an.name). Referências posicionais são permitidas aqui.

Se houver vários desenvolvedores (entwickler) para um aplicativo (anwendung), um desenvolvedor será escolhido arbitrariamente. Você deve adicionar uma cláusula ORDER BY Se desejar o "primeiro" (em ordem alfabética de acordo com o seu código do idioma):

SELECT DISTINCT ON (1) an.name, au.entwickler
FROM   anwendung an
LEFT   JOIN autor au ON an.name = au.anwendung
ORDER  BY 1, 2;

Como o @mdahlman sugeriu, uma maneira mais canônica seria:

SELECT an.name, min(au.entwickler) AS entwickler
FROM   autor au
LEFT   JOIN anwendung an ON an.name = au.anwendung
GROUP  BY an.name;

Ou, melhor ainda, limpe seu modelo de dados, implemente o relacionamento n: m entre anwendung e autor corretamente, adicione chaves primárias substitutas como anwendung e autor dificilmente são únicos, reforçam a integridade relacional com restrições de chave estrangeira e adaptam sua consulta resultante:

A maneira correta

CREATE TABLE autor (
   autor_id serial PRIMARY KEY -- surrogate primary key
 , autor    text NOT NULL);

INSERT INTO autor  VALUES
   (1, 'mike')
 , (2, 'joe')
 , (3, 'jane')   -- worked on two apps
 , (4, 'susi');  -- has no part in any apps (yet)

CREATE TABLE anwendung (
   anwendung_id serial PRIMARY KEY -- surrogate primary key
 , anwendung    text  UNIQUE);     -- disallow duplicate names

INSERT INTO anwendung  VALUES
   (1, 'foo')    -- has 3 authors linked to it
 , (2, 'bar')
 , (3, 'shark')
 , (4, 'bait');  -- has no authors attached to it (yet).

CREATE TABLE autor_anwendung (  -- you might name this table "entwickler"
   autor_id     integer REFERENCES autor     ON UPDATE CASCADE ON DELETE CASCADE
 , anwendung_id integer REFERENCES anwendung ON UPDATE CASCADE ON DELETE CASCADE
 , PRIMARY KEY (autor_id, anwendung_id)
);

INSERT INTO autor_anwendung VALUES
 (1, 1)
,(2, 1)
,(3, 1)
,(2, 2)
,(3, 3);

Esta consulta recupera uma linha por aplicativo com um autor associado (o primeiro em ordem alfabética) ou NULL se não houver:

SELECT DISTINCT ON (1) an.anwendung, au.autor
FROM   anwendung an
LEFT   JOIN autor_anwendung au_au USING (anwendung_id)
LEFT   JOIN autor au USING (autor_id)
ORDER  BY 1, 2;

Resultado:

 name  | entwickler
-------+-----------------
 bait  |
 bar   | joe
 foo   | jane
 shark | jane
12
Erwin Brandstetter