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

Question

J'ai besoin de récupérer toutes les lignes d'une table où les 2 colonnes sont toutes différentes. Donc, je veux que toutes les ventes qui n'ont pas d'autres ventes qui s'est passé le même jour pour le même prix. Les ventes qui sont uniques en fonction du jour et le prix sera mis à jour à un état actif.

Donc, je suis en train de penser:

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

Mais mon cerveau fait mal d'aller encore plus loin.

Answer 1

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 d'obtenir utilisé pour le GROUPE PAR la syntaxe, car il est plus puissant.

Pour votre requête, je 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
 )

Answer 2

^{Cette question est un couple d'années, mais il a beaucoup de liens et je sens la meilleure réponse n'a pas été donnée, cependant.}

Si vous mettez ensemble les réponses jusqu'à présent, les nettoyer et de les améliorer, vous arrivez à ce supérieur de la requête:

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. Les armes nucléaires de la performance de la accepté de répondre (dès à présent) par un facteur de 10 - 15 (dans mes tests sur PostgreSQL 8.4 9.1).

Mais cela est encore loin d'être optimale. Utiliser un NOT EXISTS (anti-)semi-jointure pour des performances encore meilleures. EXISTS est la norme SQL, a été autour pour toujours (au moins depuis PostgreSQL 7.2, longtemps avant que cette question a été posée) et l'adapte les exigences parfaitement:

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

-> SQLfiddle démo.

Comment est-ce plus rapide?

La sous-requête dans l' EXISTS (anti-)semi-jointure peut arrêter l'évaluation dès la première dupe est trouvé (pas de point en regardant de plus). Pour une table de base avec quelques doublons, ce n'est que légèrement plus efficace. Avec beaucoup de doublons cela devient de façon plus efficace.
Aussi, IN est généralement lente pour les grands ensembles de PostgreSQL.

Exclure vide mises à jour

Si certains ou beaucoup de lignes ont déjà status = 'ACTIVE', la mise à jour ne change rien, mais encore l'insertion d'une nouvelle version de ligne à coût complet (quelques exceptions s'appliquent). Normalement, vous ne voulez pas cela. Ajouter un autre WHERE condition comme démontré ci-dessus pour que ce soit encore plus rapide:

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

AND status <> 'ACTIVE';

Answer 3

Le problème avec votre requête, c'est que lors de l'utilisation d'une clause GROUP BY (qui vous font essentiellement en utilisant différentes), vous pouvez uniquement utiliser les colonnes que vous groupe ou par des fonctions d'agrégation. Vous ne pouvez pas utiliser l'id de colonne, 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 intelligent pour reconnaître que.

Cela devrait fonctionner, en revanche (et n'a pas besoin d'une 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 AVG ou au lieu de MIN, il est seulement important d'utiliser une fonction qui retourne la valeur de la colonne si il y a une seule ligne correspondante.