Wie muss (oder kann) SELECT DISTINCT auf mehrere Spalten angewendet werden?

Question

Ich muss alle Zeilen aus einer Tabelle abrufen, in der 2 Spalten kombiniert werden, die alle unterschiedlich sind. Ich möchte also alle Verkäufe, die keine anderen Verkäufe am selben Tag zum selben Preis haben. Die Verkäufe, die aufgrund des Tages und des Preises eindeutig sind, werden auf einen aktiven Status aktualisiert.

Ich denke also nach:

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

Aber mein Gehirn tut weh, wenn ich noch weiter gehe.

Answer 1

SELECT DISTINCT a,b,c FROM t

es etwa äquivalent zu:

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

Es ist eine gute Idee, sich mit der GROUP BY-Syntax vertraut zu machen, da sie leistungsfähiger ist.

Für Ihre Anfrage würde ich das so machen:

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

Wenn Sie die bisherigen Antworten zusammenfassen, bereinigen und verbessern, kommen Sie zu dieser übergeordneten Frage:

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

Das ist viel schneller als jeder von ihnen. Nukes die Leistung der derzeit akzeptierten Antwort um Faktor 10 - 15 (in meinen Tests auf PostgreSQL 8.4 und 9.1).

Aber das ist noch lange nicht optimal. Verwenden Sie eine NOT EXISTS (Anti-)Semi-Join für eine noch bessere Leistung. EXISTS ist Standard-SQL, gibt es schon ewig (mindestens seit PostgreSQL 7.2, lange bevor diese Frage gestellt wurde) und passt perfekt zu den vorgestellten Anforderungen:

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

_db<>Gefiedel ici_
Alte SQL-Fiedel

Eindeutiger Schlüssel zur Identifizierung der Zeile

Wenn Sie keinen Primär- oder eindeutigen Schlüssel für die Tabelle haben ( id im Beispiel), können Sie durch die Systemspalte ersetzen ctid für die Zwecke dieser Abfrage (aber nicht für andere Zwecke):

   AND    s1.ctid <> s.ctid

_{Jede Tabelle sollte einen Primärschlüssel haben. Fügen Sie einen hinzu, wenn Sie noch keinen haben. Ich empfehle einen <code>serial</code> oder ein <code>IDENTITY</code> Spalte in Postgres 10+.}

Verwandt:

• Erzeugung von Sequenzen in Reihenfolge
• Automatische Inkrementierung der Tabellenspalte

Wieso ist das schneller?

Die Unterabfrage in der EXISTS anti-semi-join kann mit der Auswertung aufhören, sobald das erste Duplikat gefunden wurde (es macht keinen Sinn, weiter zu suchen). Bei einer Basistabelle mit wenigen Duplikaten ist dies nur geringfügig effizienter. Bei vielen Duplikaten wird dies zu Weg effizienter.

Leere Aktualisierungen ausschließen

Für Zeilen, die bereits über status = 'ACTIVE' diese Aktualisierung würde nichts ändern, aber dennoch eine neue Zeilenversion zum vollen Preis einfügen (es gelten kleinere Ausnahmen). Normalerweise wollen Sie das nicht. Hinzufügen einer weiteren WHERE Bedingung wie oben gezeigt, um dies zu vermeiden und es noch schneller zu machen:

Si status ist definiert NOT NULL können Sie auf vereinfachen:

AND status <> 'ACTIVE';

Der Datentyp der Spalte muss die <> Betreiber. Einige Typen wie json nicht. Siehe:

• Wie kann man eine Json-Spalte nach leeren Objekten abfragen?

Geringfügiger Unterschied in der NULL-Behandlung

Diese Abfrage (im Gegensatz zu der derzeit akzeptierte Antwort von Joel ) behandelt NULL-Werte nicht als gleich. Die folgenden zwei Zeilen für (saleprice, saledate) als "unterschiedlich" eingestuft werden (obwohl sie für das menschliche Auge identisch aussehen):

(123, NULL)
(123, NULL)

Geht auch in einem eindeutigen Index und fast überall sonst, da NULL-Werte nach dem SQL-Standard nicht gleichwertig sind. Siehe:

• Erstellen einer eindeutigen Einschränkung mit Nullspalten

OTOH, GROUP BY , DISTINCT o DISTINCT ON () NULL-Werte als gleich behandeln. Verwenden Sie einen geeigneten Abfragestil, je nachdem, was Sie erreichen wollen. Sie können diese schnellere Abfrage immer noch verwenden mit IS NOT DISTINCT FROM anstelle von = für beliebige oder alle Vergleiche, um NULL-Vergleiche gleich zu machen. Mehr:

• Wie löscht man doppelte Zeilen ohne eindeutigen Bezeichner?

Wenn alle zu vergleichenden Spalten definiert sind NOT NULL gibt es keinen Raum für Meinungsverschiedenheiten.

Answer 3

Das Problem bei Ihrer Abfrage ist, dass Sie bei der Verwendung einer GROUP BY-Klausel (die Sie im Wesentlichen durch die Verwendung von distinct durchführen) nur Spalten verwenden können, nach denen Sie gruppieren oder Funktionen aggregieren. Sie können die Spalte id nicht verwenden, da es potenziell verschiedene Werte gibt. In Ihrem Fall gibt es aufgrund der HAVING-Klausel immer nur einen Wert, aber die meisten RDBMS sind nicht intelligent genug, um das zu erkennen.

Dies sollte jedoch funktionieren (und benötigt keine Verknüpfung):

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

Sie könnten auch MAX oder AVG anstelle von MIN verwenden. Wichtig ist nur, dass Sie eine Funktion verwenden, die den Wert der Spalte zurückgibt, wenn es nur eine passende Zeile gibt.

Answer 4

Wenn Ihr DBMS nicht distinct mit mehreren Spalten wie dieser unterstützt:

select distinct(col1, col2) from table

Multi Select kann im Allgemeinen wie folgt sicher ausgeführt werden:

select distinct * from (select col1, col2 from table ) as x

Dies kann auf den meisten DBMS funktionieren und ist voraussichtlich schneller als die Gruppierung nach Lösung, da Sie die Gruppierungsfunktionalität vermeiden.

Answer 5

Ich möchte die unterschiedlichen Werte aus einer Spalte "GrondOfLucht" auswählen, aber sie sollen in der Reihenfolge sortiert werden, die in der Spalte "Sortierung" angegeben ist. Ich kann die eindeutigen Werte nur einer Spalte nicht erhalten, indem ich

Select distinct GrondOfLucht,sortering
from CorWijzeVanAanleg
order by sortering

Es wird auch die Spalte "sorting" angegeben, und da "GrondOfLucht" UND "sorting" nicht eindeutig sind, wird das Ergebnis ALLE Zeilen sein.

Verwenden Sie die GROUP, um die Datensätze von 'GrondOfLucht' in der durch 'sorting' vorgegebenen Reihenfolge auszuwählen.

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