Jak (lub mogę) WYBRAĆ ODRÓŻNIENIE w wielu kolumnach?


415

Muszę pobrać wszystkie wiersze z tabeli, w której 2 kolumny łącznie są różne. Chcę więc wszystkich sprzedaży, które nie mają żadnej innej sprzedaży, które miały miejsce tego samego dnia za tę samą cenę. Sprzedaż, która jest unikalna na podstawie dnia i ceny, zostanie zaktualizowana do stanu aktywnego.

Więc myślę:

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

Ale mój mózg boli dalej.

Odpowiedzi:


436
SELECT DISTINCT a,b,c FROM t

jest w przybliżeniu równoważny z:

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

Warto przyzwyczaić się do składni GROUP BY, ponieważ jest ona bardziej wydajna.

Dla twojego zapytania zrobiłbym to w ten sposób:

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
 )

117
To zapytanie, choć poprawne i akceptowane od roku, jest wyjątkowo nieefektywne i niepotrzebnie. Nie używaj tego. Podałem alternatywę i wyjaśnienie w innej odpowiedzi.
Erwin Brandstetter

1
nie WYBIERZ ODRÓŻNIENIE a, b, c OD t dokładnie to samo, co WYBIERZ a, b, c OD t GRUPA WEDŁUG a, b, c?
famargar

8
@famargar dla prostego przypadku, ale mają one różne znaczenia semantyczne i różnią się pod względem tego, co możesz zrobić dla kroku przy tworzeniu większego zapytania. Ponadto ludzie na forach technicznych często są wyjątkowo pedantyczni, często przydaje mi się dodawanie słów łasicy do moich postów w tym kontekście.
Joel Coehoorn

344

Jeśli połączysz dotychczasowe odpowiedzi, posprzątasz i ulepszysz, dojdziesz do tego wyższego zapytania:

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

Co jest znacznie szybsze niż którekolwiek z nich. Nukuje wydajność obecnie akceptowanej odpowiedzi przez współczynnik 10-15 (w moich testach na PostgreSQL 8.4 i 9.1).

Ale wciąż nie jest to optymalne. Użyj NOT EXISTS(anty) półsprzężenia, aby uzyskać jeszcze lepszą wydajność. EXISTSjest standardowym SQL, istnieje już od zawsze (przynajmniej od PostgreSQL 7.2, na długo przed tym pytaniem) i doskonale spełnia przedstawione wymagania:

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 <> skrzypce tutaj
Old SQL Fiddle

Unikalny klucz do identyfikacji wiersza

Jeśli nie masz klucza podstawowego lub unikalnego dla tabeli ( idw tym przykładzie), możesz zastąpić kolumnę systemową ctidna potrzeby tego zapytania (ale nie do innych celów):

   AND    s1.ctid <> s.ctid

Każda tabela powinna mieć klucz podstawowy. Dodaj jeden, jeśli jeszcze go nie masz. Sugeruję kolumnę seriallub IDENTITYPostgres 10+.

Związane z:

Jak to jest szybsze?

Podkwerenda w łączeniu EXISTSczęściowym może przestać oceniać, gdy tylko zostanie znaleziony pierwszy dupe (nie ma sensu szukać dalej). W przypadku tabeli podstawowej z kilkoma duplikatami jest to tylko nieco bardziej wydajne. Z dużą ilością powtórzeń staje sposób bardziej efektywny.

Wyklucz puste aktualizacje

W przypadku wierszy, które już mają status = 'ACTIVE'tę aktualizację, nic nie zmieni, ale nadal wstaw nową wersję wiersza po pełnym koszcie (obowiązują niewielkie wyjątki). Zwykle tego nie chcesz. Dodaj kolejny WHEREwarunek, jak pokazano powyżej, aby tego uniknąć i uczynić go jeszcze szybszym:

Jeśli statusjest zdefiniowane NOT NULL, możesz uprościć:

AND status <> 'ACTIVE';

Typ danych kolumny musi obsługiwać <>operatora. Niektóre typy jak jsonnie. Widzieć:

Subtelna różnica w obsłudze NULL

To zapytanie (w przeciwieństwie do obecnie akceptowanej odpowiedzi Joela ) nie traktuje wartości NULL jako równych. Poniższe dwa wiersze (saleprice, saledate)kwalifikują się jako „odrębne” (choć wyglądają identycznie jak ludzkie oko):

(123, NULL)
(123, NULL)

Przechodzi również w unikalny indeks i prawie wszędzie indziej, ponieważ wartości NULL nie są równe zgodnie ze standardem SQL. Widzieć:

OTOH, GROUP BY, DISTINCTlub DISTINCT ON ()wartości NULL traktować jako równe. Użyj odpowiedniego stylu zapytania w zależności od tego, co chcesz osiągnąć. Nadal możesz użyć tego szybszego zapytania IS NOT DISTINCT FROMzamiast z =jakimkolwiek lub wszystkimi porównaniami, aby wyrównać NULL. Więcej:

Jeśli wszystkie porównywane kolumny są zdefiniowane NOT NULL, nie ma miejsca na spory.


16
Dobra odpowiedź. Jestem facetem od serwera SQL, więc nie przyszła mi do głowy pierwsza sugestia użycia krotki z sprawdzeniem IN (). Nieistniejąca sugestia zwykle kończy się na tym samym planie wykonania na serwerze SQL, co połączenie wewnętrzne.
Joel Coehoorn

2
Miły. Wyjaśnienie znacznie zwiększa wartość odpowiedzi. Prawie kusi mnie, aby przeprowadzić testy z Oracle, aby zobaczyć porównanie planów z Postgres i SQLServer.
Peter,

2
@alairock: Skąd to masz? W przypadku Postgres jest odwrotnie . Podczas liczenia wszystkich wierszy count(*)jest bardziej wydajny niż count(<expression>). Po prostu spróbuj. Postgres ma szybszą implementację dla tego wariantu funkcji agregującej. Może mylisz Postgres z innym RDBMS?
Erwin Brandstetter

6
@alairock: Tak się składa, że ​​jestem współautorem tej strony i nie mówi nic takiego.
Erwin Brandstetter,

2
@ErwinBrandstetter, zawsze masz rację, udzielając odpowiedzi na stos. Pomagałeś przez lata na niewyobrażalnie wiele sposobów. W tym przykładzie znałem kilka różnych sposobów rozwiązania mojego problemu, ale chciałem zobaczyć, że ktoś przetestował skuteczność między możliwościami. Dziękuję Ci.
WebWanderer

24

Problem z zapytaniem polega na tym, że korzystając z klauzuli GROUP BY (co zasadniczo robisz, używając odrębnych), możesz używać tylko kolumn, które grupujesz lub agregujesz. Nie można użyć identyfikatora kolumny, ponieważ istnieją potencjalnie różne wartości. W twoim przypadku zawsze jest tylko jedna wartość z powodu klauzuli HAVING, ale większość RDBMS nie jest wystarczająco inteligentna, aby to rozpoznać.

Powinno to jednak działać (i nie wymaga łączenia):

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

Możesz także użyć MAX lub AVG zamiast MIN, ważne jest tylko użycie funkcji, która zwraca wartość kolumny, jeśli jest tylko jeden pasujący wiersz.


1

Chcę wybrać odrębne wartości z jednej kolumny „GrondOfLucht”, ale należy je posortować w kolejności podanej w kolumnie „sortowanie”. Nie mogę uzyskać odrębnych wartości tylko jednej kolumny

Select distinct GrondOfLucht,sortering
from CorWijzeVanAanleg
order by sortering

Daje również kolumnę „sortowanie”, a ponieważ „GrondOfLucht” ORAZ „sortowanie” nie jest unikalne, wynikiem będą WSZYSTKIE wiersze.

użyj GRUPY, aby wybrać rekordy „GrondOfLucht” w kolejności podanej przez „sortowanie

SELECT        GrondOfLucht
FROM            dbo.CorWijzeVanAanleg
GROUP BY GrondOfLucht, sortering
ORDER BY MIN(sortering)

To w zasadzie wyjaśnia, co robi zaakceptowana odpowiedź, ale odradzam używanie takich nazw na przykład (przynajmniej je tłumaczę). PS: Polecam zawsze nazywać wszystko po angielsku we wszystkich projektach, nawet jeśli jesteś Holendrem.
Kerwin Sneijders

0

Jeśli Twój DBMS nie obsługuje odrębnych z wieloma takimi kolumnami:

select distinct(col1, col2) from table

Ogólnie wybór wielokrotny można bezpiecznie wykonać w następujący sposób:

select distinct * from (select col1, col2 from table ) as x

Ponieważ może to działać na większości DBMS i oczekuje się, że będzie to szybsze niż grupowanie według rozwiązania, ponieważ unikasz funkcji grupowania.

Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.