Comment puis-je (ou puis-je) effectuer un SELECT DISTINCT sur plusieurs colonnes ?

J'ai besoin de récupérer toutes les lignes d'un tableau dont les deux colonnes combinées sont toutes différentes. Je veux donc 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 qui sont uniques en fonction du jour et du prix seront mises à jour et auront un statut actif.

Je pense donc :

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

Mais mon cerveau a du mal à aller plus loin que ça.

Erwin Brandstetter
Question modifiée 22 août 2014 в 12:07
Programmation
sql postgresql sql-update duplicates distinct
10 septembre 2008 в 3:33
25 vues
Solution
SELECT DISTINCT a,b,c FROM t

est très équivalent à : 

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

Il est bon de se familiariser avec la syntaxe GROUP BY, car elle est plus puissante.

Pour votre requête, je procéderais comme suit :

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
 )
Joel Coehoorn
Réponse modifiée 10 septembre 2008 в 3:55
Commentaires (3)

Si vous rassemblez les réponses obtenues jusqu'à présent, que vous les nettoyez et les améliorez, vous obtiendrez cette question supérieure :

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

Qui est beaucoup plus rapide que l'une ou l'autre. Les performances de la réponse actuellement acceptée sont multipliées par 10 à 15 (dans mes tests sur PostgreSQL 8.4 et 9.1).

Mais c'est encore loin d'être optimal. Utilisez une [NOT EXISTS][1] (anti-)semi-jonction pour des performances encore meilleures. EXISTS` est du SQL standard, existe depuis toujours (au moins depuis PostgreSQL 7.2, bien avant que cette question ne soit posée) et répond parfaitement aux exigences présentées :

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

dbfiddle here [Old SQL Fiddle][2]

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 la remplacer par la colonne système ctid pour les besoins de cette requête (mais pas pour d'autres besoins) :

   AND    s1.ctid  s.ctid

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

Relié :

Comment est-ce plus rapide ?

La sous-requête dans l'anti-semi-joint EXISTS peut arrêter de s'évaluer dès que le premier doublon est trouvé (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 bien plus efficace.

Exclure les mises à jour vides

Pour les lignes qui ont déjà status = 'ACTIVE' cette mise à jour ne changerait rien, mais insérerait quand même une nouvelle version de ligne au coût total (des exceptions mineures s'appliquent). Normalement, ce n'est pas ce que vous voulez. Ajoutez une autre condition WHERE comme démontré ci-dessus pour éviter cela et rendre le processus encore plus rapide :

Si status est défini NOT NULL, vous pouvez simplifier à :

AND status  'ACTIVE';

Subtile différence dans la gestion de NULL

Cette requête (contrairement à la réponse actuellement acceptée par Joel) ne traite pas les valeurs NULL comme égales. Les deux lignes suivantes pour (saleprice, saledate) seraient qualifiées de "distinctes&quot ; (bien qu'elles semblent identiques à l'oeil humain) :

(123, NULL)
(123, NULL)

Passe aussi dans un index unique et presque partout ailleurs, puisque les valeurs NULL ne se comparent pas égales selon la norme SQL. Voir :

En revanche, GROUP BY, DISTINCT ou DISTINCT ON () traitent les valeurs NULL comme égales. Utilisez un style de requête approprié en fonction de ce que vous voulez obtenir. Vous pouvez toujours utiliser cette requête plus rapide avec [IS NOT DISTINCT FROM][4] au lieu de = pour une ou toutes les comparaisons afin de rendre égales les valeurs NULL. Plus encore :

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

[1] : https://www.postgresql.org/docs/current/functions-subquery.html#FUNCTIONS-SUBQUERY-EXISTS [2] : http://sqlfiddle.com/#!17/6b5ef/1

[4] : https://www.postgresql.org/docs/current/functions-comparison.html#FUNCTIONS-COMPARISON-PRED-TABLE

Erwin Brandstetter
Réponse modifiée 7 mai 2019 в 10:31
Commentaires (12)

Le problème de votre requête est que lorsque vous utilisez une clause GROUP BY (ce que vous faites essentiellement en utilisant distinct), vous ne pouvez utiliser que les colonnes que vous regroupez par ou les fonctions d'agrégation. Vous ne pouvez pas utiliser la colonne id car il y a potentiellement des valeurs différentes. Dans votre cas, il n'y a toujours qu'une seule valeur en raison de la clause HAVING, mais la plupart des SGBDR ne sont pas assez intelligents pour le reconnaître.

Cela devrait toutefois 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 pourriez é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.

Commentaires (0)