Potrebujem získať všetky riadky z tabuľky, v ktorej sú skombinované 2 stĺpce, ktoré sa líšia. Chcem teda všetky predaje, ktoré nemajú žiadne iné predaje, ktoré sa uskutočnili v ten istý deň za tú istú cenu. Predaje, ktoré sú jedinečné na základe dňa a ceny, sa aktualizujú na aktívny stav.
Takže rozmýšľam:
UPDATE sales
SET status = 'ACTIVE'
WHERE id IN (SELECT DISTINCT (saleprice, saledate), id, count(id)
FROM sales
HAVING count = 1)
Ale môj mozog bolí ísť ďalej.
SELECT DISTINCT a,b,c FROM t
je úplne ekvivalentný:
SELECT a,b,c FROM t GROUP BY a,b,c
Je dobré zvyknúť si na syntax GROUP BY, pretože je výkonnejšia.
V prípade vášho dotazu by som to urobil takto:
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
)
Ak dáte dohromady doterajšie odpovede, vyčistíte ich a zlepšíte, dostanete sa k tejto vynikajúcej otázke:
UPDATE sales
SET status = 'ACTIVE'
WHERE (saleprice, saledate) IN (
SELECT saleprice, saledate
FROM sales
GROUP BY saleprice, saledate
HAVING count(*) = 1
);
Ktorý je veľmi rýchlejší ako ktorýkoľvek z nich. Výkon aktuálne akceptovanej odpovede je 10 až 15-krát vyšší (v mojich testoch na PostgreSQL 8.4 a 9.1).
Stále to však zďaleka nie je optimálne. Pre ešte lepší výkon použite NOT EXISTS
(anti-)semi-join. EXISTS
je štandardné SQL, existuje odjakživa (prinajmenšom od PostgreSQL 7.2, dávno pred položením tejto otázky) a dokonale vyhovuje prezentovaným požiadavkám:
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
Ak nemáte primárny alebo jedinečný kľúč pre tabuľku (id
v príklade), môžete ho pre účely tohto dotazu nahradiť systémovým stĺpcom ctid
(ale nie pre niektoré iné účely):
AND s1.ctid <> s.ctid
Každá tabuľka by mala mať primárny kľúč. Ak ste ho ešte nemali, pridajte ho. V Postgres 10+ odporúčam stĺpec serial
alebo IDENTITY
.
Súvisiace: V prípade, že je v systéme Poststyl, je potrebné, aby sa v ňom nachádzali údaje, ktoré sú v ňom uvedené:
Poddotaz v anti-semi-join EXISTS
sa môže prestať vyhodnocovať hneď, ako sa nájde prvý duplikát (nemá zmysel hľadať ďalej). Pre základnú tabuľku s niekoľkými duplikátmi je to len mierne efektívnejšie. Pri veľkom počte duplikátov je to oveľa efektívnejšie.
Pre riadky, ktoré už majú status = 'ACTIVE'
, by táto aktualizácia nič nezmenila, ale stále by vložila novú verziu riadku za plnú cenu (platia menšie výnimky). Za normálnych okolností to nechcete. Pridajte ďalšiu podmienku WHERE
, ako je demonštrované vyššie, aby ste sa tomu vyhli a aby to bolo ešte rýchlejšie:
Ak je status
definovaný ako NOT NULL
, môžete to zjednodušiť na:
AND status <> 'ACTIVE';
Tento dotaz (na rozdiel od v súčasnosti akceptovanej odpovede Joela) nezaobchádza s hodnotami NULL ako s rovnými. Nasledujúce dva riadky pre (saleprice, saledate)
by sa kvalifikovali ako "rozdielne" (hoci pre ľudské oko vyzerajú rovnako):
(123, NULL)
(123, NULL)
Prechádza aj v jedinečnom indexe a takmer kdekoľvek inde, pretože hodnoty NULL sa podľa normy SQL neporovnávajú ako rovnaké. Pozri:
Naopak, GROUP BY
, DISTINCT
alebo DISTINCT ON ()
považujú hodnoty NULL za rovnaké. Použite vhodný štýl dotazu v závislosti od toho, čo chcete dosiahnuť. Tento rýchlejší dotaz môžete ešte použiť s IS NOT DISTINCT FROM
namiesto =
pre akékoľvek alebo všetky porovnania, aby sa NULL porovnávali rovnako. Viac:
Ak sú všetky porovnávané stĺpce definované ako NOT NULL
, nie je priestor na nezhodu.
Problém s vaším dotazom spočíva v tom, že pri použití klauzuly GROUP BY (ktorú v podstate používate pomocou distinct) môžete použiť len stĺpce, ktoré zoskupujete podľa alebo agregačných funkcií. Nemôžete použiť stĺpec id, pretože potenciálne existujú rôzne hodnoty. Vo vašom prípade je vždy len jedna hodnota kvôli klauzule HAVING, ale väčšina RDBMS nie je dostatočne inteligentná na to, aby to rozpoznala.
Toto by však malo fungovať (a nepotrebuje join):
UPDATE sales
SET status='ACTIVE'
WHERE id IN (
SELECT MIN(id) FROM sales
GROUP BY saleprice, saledate
HAVING COUNT(id) = 1
)
Namiesto MIN by ste mohli použiť aj MAX alebo AVG, dôležité je len použiť funkciu, ktorá vráti hodnotu stĺpca, ak existuje len jeden zodpovedajúci riadok.