with - sql select distinct count example




Como posso(ou posso) SELECT DISTINCT em várias colunas? (4)

Eu quero selecionar os valores distintos de uma coluna 'GrondOfLucht', mas eles devem ser classificados na ordem como indicado na coluna 'sortering'. Não consigo obter os valores distintos de apenas uma coluna usando

Select distinct GrondOfLucht,sortering
from CorWijzeVanAanleg
order by sortering

Ele também dará a coluna 'classificador' e porque 'GrondOfLucht' AND 'sortering' não é exclusivo, o resultado será TODAS as linhas.

use o GROUP para selecionar os registros de 'GrondOfLucht' na ordem dada por 'sortering

SELECT        GrondOfLucht
FROM            dbo.CorWijzeVanAanleg
GROUP BY GrondOfLucht, sortering
ORDER BY MIN(sortering)

Eu preciso recuperar todas as linhas de uma tabela onde 2 colunas combinadas são todas diferentes. Então, eu quero todas as vendas que não tenham outras vendas que aconteceram no mesmo dia pelo mesmo preço. As vendas exclusivas com base no dia e no preço serão atualizadas para um status ativo.

Então estou pensando:

UPDATE sales
SET status = 'ACTIVE'
WHERE id IN (SELECT DISTINCT (saleprice, saledate), id, count(id)
             FROM sales
             HAVING count = 1)

Mas meu cérebro dói ir mais longe que isso.


O problema com a sua consulta é que, ao usar uma cláusula GROUP BY (que você faz basicamente usando distintos), você só pode usar colunas agrupadas por você ou funções agregadas. Você não pode usar o ID da coluna porque existem valores potencialmente diferentes. No seu caso, há sempre apenas um valor por causa da cláusula HAVING, mas a maioria dos RDBMS não é inteligente o suficiente para reconhecer isso.

Isso deve funcionar no entanto (e não precisa de uma junção):

UPDATE sales
SET status='ACTIVE'
WHERE id IN (
  SELECT MIN(id) FROM sales
  GROUP BY saleprice, saledate
  HAVING COUNT(id) = 1
)

Você também pode usar MAX ou AVG em vez de MIN, só é importante usar uma função que retorna o valor da coluna se houver apenas uma linha correspondente.


Se você juntasse as respostas até agora, limpe e melhore, você chegaria a esta consulta superior:

UPDATE sales
SET    status = 'ACTIVE'
WHERE  (saleprice, saledate) IN (
    SELECT saleprice, saledate
    FROM   sales
    GROUP  BY saleprice, saledate
    HAVING count(*) = 1 
    );

Que é muito mais rápido que qualquer um deles. Explica o desempenho da resposta atualmente aceita por fator 10 - 15 (em meus testes no PostgreSQL 8.4 e 9.1).

Mas isso ainda está longe de ser ideal. Use uma semi-join NOT EXISTS (anti-) para um desempenho ainda melhor. EXISTS é um SQL padrão, existe desde sempre (pelo menos desde o PostgreSQL 7.2, muito antes desta pergunta ser feita) e se encaixa perfeitamente nos requisitos apresentados:

UPDATE sales s
SET    status = 'ACTIVE'
WHERE  NOT EXISTS (
   SELECT FROM sales s1                     -- SELECT list can be empty for EXISTS
   WHERE  s.saleprice = s1.saleprice
   AND    s.saledate  = s1.saledate
   AND    s.id <> s1.id                     -- except for row itself
   )
AND    s.status IS DISTINCT FROM 'ACTIVE';  -- avoid empty updates. see below

SQL Fiddle.

Chave única para identificar linha

Se você não tiver uma chave primária ou exclusiva para a tabela ( id no exemplo), você pode substituir com a coluna do sistema ctid para o propósito desta consulta (mas não para outras finalidades):

   AND    s1.ctid <> s.ctid

Cada tabela deve ter uma chave primária. Adicione um se você não tiver um, ainda. Eu sugiro uma coluna serial ou IDENTITY no Postgres 10+.

Relacionado:

Como isso é mais rápido?

A subconsulta no anti-semi-join do EXISTS pode parar de ser avaliada assim que o primeiro dupe for encontrado (não adianta procurar mais). Para uma tabela base com poucas duplicatas, isso é apenas ligeiramente mais eficiente. Com muitos duplicados, isso se torna muito mais eficiente.

Excluir atualizações vazias

Se algumas ou muitas linhas já tiverem status = 'ACTIVE' , sua atualização não alterará nada, mas ainda inserirá uma nova versão de linha com custo total (exceções menores se aplicam). Normalmente, você não quer isso. Adicione outra condição WHERE , como demonstrado acima, para tornar isso ainda mais rápido:

Se o status for definido como NOT NULL , você poderá simplificar para:

AND status <> 'ACTIVE';

Diferença sutil no manuseio do NULL

Esta consulta (ao contrário da resposta aceita atualmente por Joel ) não trata valores NULL como iguais. Essas duas linhas para (saleprice, saledate) se qualificariam como "distintas" (embora (saleprice, saledate) idênticas ao olho humano):

(123, NULL)
(123, NULL)

Também passa em um índice único e quase em qualquer outro lugar, já que os valores NULL não se comparam de acordo com o padrão SQL. Vejo:

OTOH, GROUP BY ou DISTINCT ou DISTINCT ON () tratam os valores NULL como iguais. Use um estilo de consulta apropriado, dependendo do que você deseja alcançar. Você ainda pode usar esse estilo de consulta mais rápido usando IS NOT DISTINCT FROM vez de = para qualquer ou todas as comparações para tornar NULL compare equal. Mais:

Se todas as colunas comparadas forem definidas como NOT NULL , não haverá espaço para discordância.


SELECT DISTINCT a,b,c FROM t

é aproximadamente equivalente a:

SELECT a,b,c FROM t GROUP BY a,b,c

É uma boa ideia habituar-se à sintaxe GROUP BY, pois é mais poderosa.

Para sua consulta, eu faria assim:

UPDATE sales
SET status='ACTIVE'
WHERE id IN
(
    SELECT id
    FROM sales S
    INNER JOIN
    (
        SELECT saleprice, saledate
        FROM sales
        GROUP BY saleprice, saledate
        HAVING COUNT(*) = 1 
    ) T
    ON S.saleprice=T.saleprice AND s.saledate=T.saledate
 )




distinct