kzen.dev
Viac na
Question

Ako môžem (alebo môžem) vykonať SELECT DISTINCT na viac stĺpcov?

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.

Erwin Brandstetter
Erwin Brandstetter
Edited question 22 august 2014 в 12:07
Programovanie
sql
postgresql
sql-update
duplicates
distinct
This question has 1 odpoveď in English, to read them log in to your account.
Solution / Answer
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
 )
Joel Coehoorn
Joel Coehoorn
Edited answer 10 september 2008 в 3:55
414

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

Jedinečný kľúč na identifikáciu riadku

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é:

Ako je to rýchlejšie?

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.

Vylúčenie prázdnych aktualizácií

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';

Subtílny rozdiel v spracovaní NULL

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.

Erwin Brandstetter
Erwin Brandstetter
Edited answer 7 máj 2019 в 10:31
a free online environment to experiment with SQL and other code
dbfiddle.uk
330

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.

23