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, für die es keine anderen Verkäufe am selben Tag zum selben Preis gibt. 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.
SELECT DISTINCT a,b,c FROM t
ist völlig ä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 viel leistungsfähiger ist.
Für Ihre Abfrage 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
)
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 jede der beiden Antworten. Verbessert die Leistung der aktuell akzeptierten Antwort um den Faktor 10 - 15 (in meinen Tests auf PostgreSQL 8.4 und 9.1).
Aber das ist immer noch weit von optimal entfernt. Verwenden Sie einen NOT EXISTS
(Anti-)Semi-Join für 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<>fiddle here Old SQL Fiddle
Wenn Sie keinen primären oder eindeutigen Schlüssel für die Tabelle haben (id
im Beispiel), können Sie für den Zweck dieser Abfrage die Systemspalte ctid
verwenden (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 schlage eine serial
oder eine IDENTITY
Spalte in Postgres 10+ vor.
Verwandt:
Die Unterabfrage in der "EXISTS"-Anti-Semi-Verknüpfung 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 viel effizienter.
Bei Zeilen, die bereits status = 'ACTIVE'
haben, würde diese Aktualisierung nichts ändern, aber trotzdem eine neue Zeilenversion zu vollen Kosten einfügen (es gibt kleinere Ausnahmen). Normalerweise wollen Sie das nicht. Fügen Sie eine weitere "WHERE"-Bedingung wie oben gezeigt hinzu, um dies zu vermeiden und die Aktualisierung noch schneller zu machen:
Wenn status
als NOT NULL
definiert ist, können Sie es vereinfachen:
AND status <> 'ACTIVE';
Diese Abfrage (anders als die derzeit akzeptierte Antwort von Joel) behandelt NULL-Werte nicht als gleich. Die folgenden zwei Zeilen für (saleprice, saledate)
würden sich als "unterschiedlich" qualifizieren (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:
OTOH, GROUP BY
, DISTINCT
oder DISTINCT ON ()
behandeln NULL-Werte als gleich. Verwenden Sie einen geeigneten Abfragestil, je nachdem, was Sie erreichen wollen. Sie können diese schnellere Abfrage immer noch mit IS NOT DISTINCT FROM
anstelle von =
für beliebige oder alle Vergleiche verwenden, um NULL-Vergleiche gleich zu machen. Mehr:
Wenn alle zu vergleichenden Spalten NOT NULL
definiert sind, gibt es keinen Raum für Unstimmigkeiten.
Das Problem bei Ihrer Abfrage ist, dass Sie bei der Verwendung einer GROUP BY-Klausel (die Sie im Wesentlichen durch die Verwendung von distinct erreichen) 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.