Jeg har brug for at hente alle rækker fra en tabel, hvor 2 kolonner kombineret er alle forskellige. Så jeg vil have alle de salg, der ikke har andre salg, der er sket på samme dag til samme pris. De salg, der er unikke baseret på dag og pris, vil blive opdateret til en aktiv status.
Så jeg tænker:
UPDATE sales
SET status = 'ACTIVE'
WHERE id IN (SELECT DISTINCT (saleprice, saledate), id, count(id)
FROM sales
HAVING count = 1)
Men min hjerne gør ondt at gå længere end det.
SELECT DISTINCT a,b,c FROM t
svarer gennemgående til:
SELECT a,b,c FROM t GROUP BY a,b,c
Det er en god idé at vænne sig til GROUP BY-syntaksen, da den er mere kraftfuld.
For din forespørgsel, ville jeg gøre det sådan her:
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
)
Hvis du samler de hidtidige svar, rydder op og forbedrer dem, vil du komme frem til denne overlegne forespørgsel:
UPDATE sales
SET status = 'ACTIVE'
WHERE (saleprice, saledate) IN (
SELECT saleprice, saledate
FROM sales
GROUP BY saleprice, saledate
HAVING count(*) = 1
);
Hvilket er meget hurtigere end nogen af dem. Nukes ydelsen af det nuværende accepterede svar med faktor 10 - 15 (i mine tests på PostgreSQL 8.4 og 9.1).
Men det er stadig langt fra optimalt. Brug en NOT EXISTS
(anti-)semi-join for at opnå endnu bedre ydelse. EXISTS
er standard SQL, har eksisteret i al evighed (i hvert fald siden PostgreSQL 7.2, længe før dette spørgsmål blev stillet) og passer perfekt til de præsenterede krav:
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
Hvis du ikke har en primær eller entydig nøgle til tabellen (id
i eksemplet), kan du erstatte den med systemkolonnen ctid
i forbindelse med denne forespørgsel (men ikke til andre formål):
AND s1.ctid <> s.ctid
Alle tabeller bør have en primær nøgle. Tilføj en, hvis du ikke har en endnu. Jeg foreslår en serial
eller en IDENTITY
kolonne i Postgres 10+.
Relateret:
Underafspørgslen i EXISTS
anti-semi-join kan stoppe evalueringen, så snart den første dupe er fundet (der er ingen grund til at lede videre). For en grundtabel med få dubletter er dette kun en smule mere effektivt. Med mange dubletter bliver det meget mere effektivt.
For rækker, der allerede har status = 'ACTIVE'
, vil denne opdatering ikke ændre noget, men stadig indsætte en ny rækkeversion til fulde omkostninger (mindre undtagelser gælder). Normalt ønsker du ikke dette. Tilføj en anden WHERE
betingelse som demonstreret ovenfor for at undgå dette og gøre det endnu hurtigere:
Hvis status
er defineret som NOT NULL
, kan du forenkle til:
AND status <> 'ACTIVE';
Denne forespørgsel (i modsætning til [det i øjeblikket accepterede svar fra Joel) behandler ikke NULL-værdier som ligeværdige. De følgende to rækker for (saleprice, saledate)
ville kunne betegnes som "forskellige" (selv om de ser identiske ud for det menneskelige øje):
(123, NULL)
(123, NULL)
Passer også i et unikt indeks og næsten alle andre steder, da NULL-værdier ikke sammenlignes ens i henhold til SQL-standarden. Se:
Derimod behandler GROUP BY
, DISTINCT
eller DISTINCT ON ()
NULL-værdier som ligeværdige. Brug en passende forespørgselsstil afhængigt af, hvad du ønsker at opnå. Du kan stadig bruge denne hurtigere forespørgsel med IS NOT DISTINCT FROM
i stedet for =
til en eller alle sammenligninger for at gøre NULL sammenligne lige. Mere:
Hvis alle kolonner, der sammenlignes, er defineret NOT NULL
, er der ikke plads til uenighed.
Problemet med din forespørgsel er, at når du bruger en GROUP BY-klausul (som du i det væsentlige gør ved at bruge distinct), kan du kun bruge kolonner, som du grupperer efter eller aggregerer funktioner. Du kan ikke bruge kolonnen id, fordi der er potentielt forskellige værdier. I dit tilfælde er der altid kun én værdi på grund af HAVING-klausulen, men de fleste RDBMS er ikke smarte nok til at erkende det.
Dette burde dog virke (og behøver ikke et join):
UPDATE sales
SET status='ACTIVE'
WHERE id IN (
SELECT MIN(id) FROM sales
GROUP BY saleprice, saledate
HAVING COUNT(id) = 1
)
Du kunne også bruge MAX eller AVG i stedet for MIN, det er kun vigtigt at bruge en funktion der returnerer værdien af kolonnen hvis der kun er én matchende række.