Hoe (of kan ik) SELECT DISTINCT op meerdere kolommen?

Ik moet alle rijen ophalen uit een tabel waar 2 kolommen gecombineerd allemaal verschillend zijn. Dus ik wil alle verkopen die geen andere verkopen hebben die op dezelfde dag hebben plaatsgevonden voor dezelfde prijs. De verkopen die uniek zijn op basis van dag en prijs zullen worden bijgewerkt naar een actieve status.

Dus ik'ben aan het denken:

UPDATE sales
SET status = 'ACTIVE'
WHERE id IN (SELECT DISTINCT (saleprice, saledate), id, count(id)
             FROM sales
             HAVING count = 1)

Maar mijn hersenen doen pijn om verder te gaan dan dat.

Erwin Brandstetter
Bewerkte vraag 22ste4 augustus 2014 в 12:07
Programmering
sql postgresql sql-update duplicates distinct
10de2 september 2008 в 3:33
21 views
Oplossing
SELECT DISTINCT a,b,c FROM t

is eigenlijk gelijk aan: 

SELECT a,b,c FROM t GROUP BY a,b,c

Het is een goed idee om te wennen aan de GROUP BY syntaxis, omdat die krachtiger is.

Voor jouw query, zou ik het als volgt doen:

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
Bewerkt antwoord 10de2 september 2008 в 3:55
Commentaren (3)

Als je de antwoorden tot nu toe samenvoegt, opruimt en verbetert, kom je tot deze superieure vraag:

UPDATE sales
SET    status = 'ACTIVE'
WHERE  (saleprice, saledate) IN (
    SELECT saleprice, saledate
    FROM   sales
    GROUP  BY saleprice, saledate
    HAVING count(*) = 1 
    );

Die veel sneller is dan elk van hen. Verneukt de prestatie van het huidige geaccepteerde antwoord met factor 10 - 15 (in mijn tests op PostgreSQL 8.4 en 9.1).

Maar dit is nog steeds verre van optimaal. Gebruik een NOT EXISTS (anti-)semi-join voor nog betere prestaties. EXISTS is standaard SQL, bestaat al sinds mensenheugenis (in ieder geval sinds PostgreSQL 7.2, lang voordat deze vraag werd gesteld) en voldoet perfect aan de gestelde eisen:

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

dbfiddle here Old SQL Fiddle

Unieke sleutel om rij te identificeren

Als je geen primaire of unieke sleutel hebt voor de tabel (id in het voorbeeld), kun je dit vervangen door de systeem kolom ctid voor het doel van deze query (maar niet voor sommige andere doeleinden):

   AND    s1.ctid  s.ctid

Elke tabel zou een primaire sleutel moeten hebben. Voeg er een toe als je er nog geen hebt. Ik stel een serial of een IDENTITY kolom voor in Postgres 10+.

Gerelateerd:

Hoe is dit sneller?

De subquery in de EXISTS anti-semi-join kan stoppen met evalueren zodra de eerste dupe is gevonden (geen zin om verder te zoeken). Voor een basistabel met weinig duplicaten is dit maar een klein beetje efficiënter. Met veel doublures wordt dit weg efficiënter.

Lege updates uitsluiten

Voor rijen die al status = 'ACTIVE' hebben zou deze update niets veranderen, maar toch een nieuwe rijversie invoegen tegen volledige kostprijs (kleine uitzonderingen zijn van toepassing). Normaal gesproken, wil je dit niet. Voeg een andere WAAR voorwaarde toe zoals hierboven gedemonstreerd om dit te vermijden en het nog sneller te maken:

Als status NOT NULL is gedefinieerd, kun je vereenvoudigen naar:

AND status  'ACTIVE';

Subtiel verschil in NULL afhandeling

Deze query (in tegenstelling tot het momenteel geaccepteerde antwoord van Joel) behandelt NULL waarden niet als gelijk. De volgende twee rijen voor (saleprice, saledate) zouden kwalificeren als "distinct" (hoewel ze er voor het menselijk oog identiek uitzien):

(123, NULL)
(123, NULL)

Komt ook voor in een unieke index en bijna overal elders, omdat NULL waarden niet gelijk vergelijken volgens de SQL standaard. Zie:

OTOH, GROUP BY, DISTINCT of DISTINCT ON () behandelen NULL waarden als gelijk. Gebruik een geschikte query stijl afhankelijk van wat u wilt bereiken. U kunt deze snellere query nog steeds gebruiken met IS NOT DISTINCT FROM in plaats van = voor elke of alle vergelijkingen om NULL gelijk te maken. Meer:

Als alle kolommen die vergeleken worden NOT NULL gedefinieerd zijn, is er geen ruimte voor onenigheid.

Erwin Brandstetter
Bewerkt antwoord 7de1 mei 2019 в 10:31
Commentaren (12)

Het probleem met uw query is dat wanneer u een GROUP BY-clausule gebruikt (wat u in wezen doet door distinct te gebruiken) u alleen kolommen kunt gebruiken waarop u groepeert of aggregatiefuncties gebruikt. Je kunt de kolom id niet gebruiken omdat er potentieel verschillende waarden zijn. In uw geval is er altijd maar één waarde vanwege de HAVING-clausule, maar de meeste RDBMS zijn niet slim genoeg om dat te herkennen.

Dit zou echter moeten werken (en heeft geen join nodig):

UPDATE sales
SET status='ACTIVE'
WHERE id IN (
  SELECT MIN(id) FROM sales
  GROUP BY saleprice, saledate
  HAVING COUNT(id) = 1
)

Je zou ook MAX of AVG kunnen gebruiken in plaats van MIN, het is alleen belangrijk om een functie te gebruiken die de waarde van de kolom teruggeeft als er maar één overeenkomende rij is.

Commentaren (0)