Pchełki SQL, odcinek 4: alternatywne podejście do grupowania

https://xpil.eu/s7SRt 📋

W mojej codziennej pracy na coraz to brudniejszym zmywaku napotykam czasem całkiem interesujące perełki. Dziś przykład jak w nietypowy sposób można wybrać elementy o najwyższym priorytecie z grup.

Najpierw dane wejściowe:

WITH input AS
 ( SELECT 'wąchock' AS CITY, 1 as priorytet, 'grupa1' AS GRUPA FROM DUAL
 UNION ALL SELECT 'kozia wólka', 2, 'grupa1' FROM DUAL
 UNION ALL SELECT 'kołomyja', 3, 'grupa1' FROM DUAL
 UNION ALL SELECT 'mielec', 4, 'grupa1' FROM DUAL
 UNION ALL SELECT 'cichosze', 5, 'grupa1' FROM DUAL
 UNION ALL SELECT 'wilczyn', 2, 'grupa2' FROM DUAL
 UNION ALL SELECT 'piątek', 3, 'grupa2' FROM DUAL
 UNION ALL SELECT 'karczewo', 4, 'grupa2' FROM DUAL
 UNION ALL SELECT 'śmiechów', 5, 'grupa2' FROM DUAL
 UNION ALL SELECT 'kościeliska', 4, 'grupa3' FROM DUAL
 UNION ALL SELECT 'przybłędów', 5, 'grupa3' FROM DUAL
 )
SELECT * FROM INPUT 
order by grupa, priorytet;

Na wyjściu otrzymujemy:

CITY, PRIORYTET, GRUPA 
wąchock, 1, grupa1 
kozia wólka, 2, grupa1 
kołomyja, 3, grupa1 
mielec, 4, grupa1 
cichosze, 5, grupa1 
wilczyn, 2, grupa2 
piątek, 3, grupa2 
karczewo, 4, grupa2 
śmiechów, 5, grupa2 
kościeliska, 4, grupa3 
przybłędów, 5, grupa3

Jak widać, mamy trzy grupy (grupa1, grupa2 i grupa3), w każdej grupie kilka miast o różnych priorytetach.

Naszym zadaniem będzie wybranie z każdej grupy miasta o najmniejszej wartości priorytetu. A więc wąchock w grupie grupa1, wilczyn w grupie grupa2 oraz kościeliska w grupa3.

Najpierw podejście tradycyjne: wyliczamy najmniejszą wartość priorytetu w każdej grupie po czym wybieramy rekordy odpowiadające tej wartości:

WITH input AS
 ( SELECT 'wąchock' AS CITY, 1 as priorytet, 'grupa1' AS GRUPA FROM DUAL
 UNION ALL SELECT 'kozia wólka', 2, 'grupa1' FROM DUAL
 UNION ALL SELECT 'kołomyja', 3, 'grupa1' FROM DUAL
 UNION ALL SELECT 'mielec', 4, 'grupa1' FROM DUAL
 UNION ALL SELECT 'cichosze', 5, 'grupa1' FROM DUAL
 UNION ALL SELECT 'wilczyn', 2, 'grupa2' FROM DUAL
 UNION ALL SELECT 'piątek', 3, 'grupa2' FROM DUAL
 UNION ALL SELECT 'karczewo', 4, 'grupa2' FROM DUAL
 UNION ALL SELECT 'śmiechów', 5, 'grupa2' FROM DUAL
 UNION ALL SELECT 'kościeliska', 4, 'grupa3' FROM DUAL
 UNION ALL SELECT 'przybłędów', 5, 'grupa3' FROM DUAL
 )
 , T2 AS (
 SELECT 
 MIN(priorytet) AS priorytet,
 GRUPA FROM input GROUP BY GRUPA
 )
 , OUTPUT AS (
 SELECT 
 T2.GRUPA ,
 input.CITY 
 FROM 
 input, T2 
 where 
 INPUT.priorytet=T2.priorytet 
 AND input.GRUPA=T2.GRUPA

 )
 SELECT * FROM OUTPUT order by grupa;

Podzapytanie T2 wyszukuje najmniejszą wartość priorytetu w każdej grupie, a podzapytanie OUTPUT wybiera z oryginalnego zestawu rekordów te, które odpowiadają wartościom priorytetów wyliczonym w T2.

Ktoś bardziej obeznany z SQL-em mógłby popełnić coś takiego:

WITH input AS
 ( SELECT 'wąchock' AS CITY, 1 as priorytet, 'grupa1' AS GRUPA FROM DUAL
 UNION ALL SELECT 'kozia wólka', 2, 'grupa1' FROM DUAL
 UNION ALL SELECT 'kołomyja', 3, 'grupa1' FROM DUAL
 UNION ALL SELECT 'mielec', 4, 'grupa1' FROM DUAL
 UNION ALL SELECT 'cichosze', 5, 'grupa1' FROM DUAL
 UNION ALL SELECT 'wilczyn', 2, 'grupa2' FROM DUAL
 UNION ALL SELECT 'piątek', 3, 'grupa2' FROM DUAL
 UNION ALL SELECT 'karczewo', 4, 'grupa2' FROM DUAL
 UNION ALL SELECT 'śmiechów', 5, 'grupa2' FROM DUAL
 UNION ALL SELECT 'kościeliska', 4, 'grupa3' FROM DUAL
 UNION ALL SELECT 'przybłędów', 5, 'grupa3' FROM DUAL
 )
 , T2 AS (
 select distinct 
 GRUPA, 
 MIN(priorytet) OVER(PARTITION BY GRUPA ORDER BY priorytet) AS priorytet
 FROM INPUT
 )
 , OUTPUT AS (
 SELECT 
 T2.GRUPA ,
 input.CITY 
 FROM 
 input, T2 
 where 
 INPUT.priorytet=T2.priorytet 
 AND input.GRUPA=T2.GRUPA

 )
 SELECT * FROM OUTPUT ORDER BY GRUPA;

Tutaj podzapytanie T2 robi dokładnie to samo co w poprzednim przykładzie, tylko zamiast grupowania stosujemy partycjonowanie danych oraz operator DISTINCT. Efekt jest ten sam: na wyjściu T2 dostajemy listę grup wraz z ich najniższymi priorytetami, a dalej tak samo jak w pierwszym przykładzie.

Na koniec jednak chcę pokazać podejście całkowicie odmienne od powyższego, którego przykład napotkałem niedawno w swojej codziennej pracy.

Spójrzmy:

WITH input AS
 ( SELECT 'wąchock' AS CITY, 1 as priorytet, 'grupa1' AS GRUPA FROM DUAL
 UNION ALL SELECT 'kozia wólka', 2, 'grupa1' FROM DUAL
 UNION ALL SELECT 'kołomyja', 3, 'grupa1' FROM DUAL
 UNION ALL SELECT 'mielec', 4, 'grupa1' FROM DUAL
 UNION ALL SELECT 'cichosze', 5, 'grupa1' FROM DUAL
 UNION ALL SELECT 'wilczyn', 2, 'grupa2' FROM DUAL
 UNION ALL SELECT 'piątek', 3, 'grupa2' FROM DUAL
 UNION ALL SELECT 'karczewo', 4, 'grupa2' FROM DUAL
 UNION ALL SELECT 'śmiechów', 5, 'grupa2' FROM DUAL
 UNION ALL SELECT 'kościeliska', 4, 'grupa3' FROM DUAL
 UNION ALL SELECT 'przybłędów', 5, 'grupa3' FROM DUAL
 )
SELECT 
 GRUPA, 
 COALESCE(
 MAX(CASE priorytet WHEN 1 THEN CITY END)
 , MAX(CASE priorytet WHEN 2 THEN CITY END)
 , MAX(CASE priorytet WHEN 3 THEN CITY END)
 , MAX(CASE priorytet WHEN 4 THEN CITY END)
 , MAX(CASE priorytet WHEN 5 THEN CITY END)
 ) as c1
FROM INPUT
GROUP BY GRUPA
ORDER BY GRUPA;

To ostatnie zapytanie jest na pierwszy rzut oka dość zagmatwane - mi samemu zajęło dobrą chwilę zrozumienie jak to właściwie działa.

Operator COALESCE zwraca pierwszą różną od NULL wartość z listy swoich argumentów. Dla przykładu: COALESCE(1, 2, 3) wynosi 1, a COALESCE(NULL, NULL, 7, 9, NULL) wynosi 7.

Z kolei operator MAX działa w ten sposób, że zwraca największą wartość spośród wszystkich wartości nie będących NULL - jeżeli jednak dostanie na wejście same NULL-e, zwraca NULL.

Wreszcie całkiem wewnętrzny operator CASE ... END działa tak, że jeżeli żaden warunek podany w blokach WHEN ... THEN nie jest spełniony, a także jeżeli nie ma bloku ELSE ... END, wówczas zwraca wartość NULL.

Tym samym całość zapytania zwróci nazwę miasta z priorytetem 1 (w każdej grupie), chyba że takowe nie istnieje, wówczas zwróci nazwę z priorytetem 2, jeżeli dwójki nie ma, zwróci nazwę z priorytetem 3 i tak dalej aż do 5.

Oczywiste ograniczenie tej metody jest takie, że musimy znać a priori wszystkie wartości priorytetów (w przeciwnym wypadku, jeżeli np. któreś z miast będzie miało priorytet 6, COALESCE zwróci NULL bo szóstka nie jest zaimplementowana).

Oprócz tego wydajność tej metody budzi wątpliwości, ponieważ każdy rekord musi być sprawdzony pod kątem kilku różnych priorytetów. Jednak dla stosunkowo niewielkiej liczby rekordów na wejściu metoda sprawdza się doskonale, wymagając przy tym bardzo krótkiego kodu. Ponadto wszystkie wyrażenia CASE ... END można uzyskać przez ^C - ^V, co znacznie przyspiesza pisanie kodu 🙂

Nie polecam powyższej metody, bo jest nieoczywista, nieczytelna i nieoptymalna. Jednak na ciekawostkę nadaje się wprost idealnie.

Aha, kod jest zgodny z Oracle, jednak po usunięciu zeń FROM DUAL powinien również działać w innych środowiskach.

https://xpil.eu/s7SRt 📋