Jeg trenger å hente alle rader fra en tabell der to kolonner til sammen er forskjellige. Så jeg vil ha alle salgene som ikke har noen andre salg som skjedde på samme dag til samme pris. Salgene som er unike basert på dag og pris vil bli oppdatert til en aktiv status.
Så jeg tenker:
UPDATE sales
SET status = 'ACTIVE'
WHERE id IN (SELECT DISTINCT (saleprice, saledate), id, count(id)
FROM sales
HAVING count = 1)
Men jeg får vondt i hjernen av å gå lenger enn det.
SELECT DISTINCT a,b,c FROM t
er omtrent ekvivalent med:
SELECT a,b,c FROM t GROUP BY a,b,c
Det er en god idé å bli vant til GROUP BY-syntaksen, siden den er kraftigere.
For spørringen din ville jeg gjort det slik:
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 setter sammen svarene så langt, rydder opp og forbedrer, vil du komme frem til denne overordnede spørringen:
UPDATE sales
SET status = 'ACTIVE'
WHERE (saleprice, saledate) IN (
SELECT saleprice, saledate
FROM sales
GROUP BY saleprice, saledate
HAVING count(*) = 1
);
Som er mye raskere enn noen av dem. Nukes ytelsen til det nåværende aksepterte svaret med faktor 10 - 15 (i testene mine på PostgreSQL 8.4 og 9.1).
Men dette er fortsatt langt fra optimalt. Bruk en NOT EXISTS
(anti-) semi-join for enda bedre ytelse. EXISTS
er standard SQL, har eksistert for alltid (i det minste siden PostgreSQL 7.2, lenge før dette spørsmålet ble stilt) og passer perfekt til de presenterte kravene:
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 unik nøkkel for tabellen (id
i eksemplet), kan du erstatte den med systemkolonnen ctid
for formålet med denne spørringen (men ikke for andre formål):
AND s1.ctid <> s.ctid
Hver tabell skal ha en primærnøkkel. Legg til en hvis du ikke har en ennå. Jeg foreslår en serial
eller en IDENTITY
kolonne i Postgres 10+.
Beslektet:
Underspørringen i EXISTS
anti-semi-join kan slutte å evaluere så snart den første dubletten er funnet (ingen vits i å lete videre). For en basistabell med få duplikater er dette bare litt mer effektivt. Med mange duplikater blir dette mye mer effektivt.
For rader som allerede har status = 'ACTIVE' vil denne oppdateringen ikke endre noe, men likevel sette inn en ny radversjon til full kostnad (mindre unntak gjelder). Normalt ønsker du ikke dette. Legg til en annen
WHERE`-tilstand som demonstrert ovenfor for å unngå dette og gjøre det enda raskere:
Hvis status
er definert NOT NULL
, kan du forenkle til:
AND status <> 'ACTIVE';
Denne spørringen (i motsetning til det nåværende aksepterte svaret fra Joel) behandler ikke NULL-verdier som like. Følgende to rader for (saleprice, saledate)
vil kvalifisere som "forskjellige" (selv om de ser identiske ut for det menneskelige øyet):
(123, NULL)
(123, NULL)
Passerer også i en unik indeks og nesten hvor som helst ellers, siden NULL-verdier ikke sammenlignes likt i henhold til SQL-standarden. Se:
OTOH, GROUP BY
, DISTINCT
eller DISTINCT ON ()
behandler NULL-verdier som like. Bruk en passende spørrestil avhengig av hva du ønsker å oppnå. Du kan fortsatt bruke denne raskere spørringen med IS NOT DISTINCT FROM
i stedet for =
for noen eller alle sammenligninger for å gjøre NULL-sammenligninger like. Mer:
Hvis alle kolonnene som sammenlignes er definert som NOT NULL
, er det ikke rom for uenighet.
Problemet med spørringen din er at når du bruker en GROUP BY-klausul (som du i hovedsak gjør ved å bruke distinct), kan du bare bruke kolonner som du grupperer etter eller aggregerer funksjoner. Du kan ikke bruke kolonne-ID fordi det er potensielt forskjellige verdier. I ditt tilfelle er det alltid bare en verdi på grunn av HAVING-klausulen, men de fleste RDBMS er ikke smarte nok til å gjenkjenne det.
Dette skal imidlertid fungere (og trenger ikke en sammenføyning):
UPDATE sales
SET status='ACTIVE'
WHERE id IN (
SELECT MIN(id) FROM sales
GROUP BY saleprice, saledate
HAVING COUNT(id) = 1
)
Du kan også bruke MAX eller AVG i stedet for MIN, det er bare viktig å bruke en funksjon som returnerer verdien av kolonnen hvis det bare er én matchende rad.