Comment puis-je (ou puis-je) sélectionner DISTINCT sur plusieurs colonnes?

Je dois récupérer toutes les lignes d’une table où 2 colonnes combinées sont toutes différentes. Donc, je veux toutes les ventes qui n’ont pas d’autres ventes qui ont eu lieu le même jour pour le même prix. Les ventes uniques basées sur le jour et le prix seront mises à jour pour devenir un statut actif.

Donc je pense:

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

Mais mon cerveau fait plus mal que ça.

 SELECT DISTINCT a,b,c FROM t 

est à peu près équivalent à:

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

C’est une bonne idée de s’habituer à la syntaxe GROUP BY, car elle est plus puissante.

Pour votre requête, je le ferais comme ceci:

 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 ) 

Si vous rassemblez les réponses jusqu’ici, nettoyez et améliorez, vous arriverez à cette requête supérieure:

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

Ce qui est beaucoup plus rapide que l’un d’eux. Indique la performance de la réponse actuellement acceptée par un facteur de 10 à 15 (dans mes tests sur PostgreSQL 8.4 et 9.1).

Mais cela rest loin d’être optimal. Utilisez un (anti-) semi-jointure NOT EXISTS pour des performances encore meilleures. EXISTS est un SQL standard, existe depuis toujours (du moins depuis PostgreSQL 7.2, bien avant que cette question ne soit posée) et correspond parfaitement aux exigences présentées:

 UPDATE sales s SET status = 'ACTIVE' WHERE NOT EXISTS ( SELECT 1 FROM sales s1 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 

Violon SQL

Clé unique pour identifier la ligne

Si vous n’avez pas de clé primaire ou unique pour la table ( id dans l’exemple), vous pouvez ctid colonne système ctid pour cette requête (mais pas à d’autres fins):

  AND s1.ctid <> s.ctid 

Chaque table doit avoir une clé primaire. Ajoutez-en un si vous n’en aviez pas encore. Je suggère une colonne serial ou IDENTITY dans Postgres 10+.

En relation:

  • Génération de séquence dans l’ordre
  • Incrémentation automatique de la colonne de table

Comment est-ce plus rapide?

La sous-requête dans la semi-jointure EXISTS peut cesser d’évaluer dès que la première dupe est trouvée (inutile de chercher plus loin). Pour une table de base avec peu de doublons, cela n’est que légèrement plus efficace. Avec beaucoup de doublons, cela devient beaucoup plus efficace.

Exclure les mises à jour vides

Si certaines ou plusieurs lignes ont déjà status = 'ACTIVE' , votre mise à jour ne changera rien, mais insérez toujours une nouvelle version de ligne à plein coût (des exceptions mineures s’appliquent). Normalement, vous ne le voulez pas. Ajoutez une autre condition WHERE comme démontré ci-dessus pour rendre cela encore plus rapide:

Si le status est défini sur NOT NULL , vous pouvez simplifier pour:

 AND status <> 'ACTIVE'; 

Différence subtile dans la manipulation NULL

Cette requête (contrairement à la réponse actuellement acceptée par Joel ) ne traite pas les valeurs NULL comme égales. Ces deux lignes pour (saleprice, saledate) seraient qualifiées de “distinctes” (bien que semblant identiques à l’œil humain):

 (123, NULL) (123, NULL) 

Passe également dans un index unique et presque n’importe où, puisque les valeurs NULL ne sont pas égales selon le standard SQL. Voir:

  • Créer une contrainte unique avec des colonnes nulles

OTOH, GROUP BY ou DISTINCT ou DISTINCT ON () traitent les valeurs NULL comme égales. Utilisez un style de requête approprié en fonction de vos objectives. Vous pouvez toujours utiliser ce style de requête plus rapide en utilisant IS NOT DISTINCT FROM au lieu de = pour toute ou toutes les comparaisons pour rendre NULL compare égal. Plus:

  • Comment supprimer des lignes en double sans identifiant unique

Si toutes les colonnes comparées sont définies NOT NULL , il n’y a pas de place pour le désaccord.

Le problème avec votre requête est que lorsque vous utilisez une clause GROUP BY (que vous utilisez essentiellement en utilisant distinct), vous ne pouvez utiliser que des colonnes regroupées ou agrégées. Vous ne pouvez pas utiliser l’ID de colonne car il existe des valeurs potentiellement différentes. Dans votre cas, il n’y a toujours qu’une seule valeur à cause de la clause HAVING, mais la plupart des SGBDR ne sont pas assez intelligents pour le reconnaître.

Cela devrait fonctionner (et ne nécessite pas de jointure):

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

Vous pouvez également utiliser MAX ou AVG au lieu de MIN, il est seulement important d’utiliser une fonction qui renvoie la valeur de la colonne s’il n’y a qu’une seule ligne correspondante.

Je souhaite sélectionner les valeurs distinctes d’une colonne «GrondOfLucht» mais elles doivent être sortingées dans l’ordre indiqué dans la colonne «sorting». Je ne peux pas obtenir les valeurs distinctes d’une seule colonne en utilisant

 Select distinct GrondOfLucht,sortering from CorWijzeVanAanleg order by sortering 

Cela donnera aussi la colonne ‘sortingage’ et parce que ‘GrondOfLucht’ ET ‘sortinge’ n’est pas unique, le résultat sera TOUTES les lignes.

utiliser le GROUP pour sélectionner les enregistrements de ‘GrondOfLucht’ dans l’ordre indiqué par ‘sortinger

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