Filtrowanie w zapytaniach grupujących

W poprzednim poście poruszony został wątek grupowania. Warto też pomyśleć o możliwościach filtrowania takich zapytań w celu prezentacji wyników pod użytkownika. Tak aby zapytanie wróciło tylko te wiersze, o które chodzi użytkownikowi.

Mamy kilka opcji do wyboru w zależności od oczekiwanego efektu. Postaram się najpierw logicznie opisać jak to działa i przedstawię proste przykłady.

Pierwszą możliwością filtrowania jest klauzula where, która jak w przypadku normalnego zapytania pozwoli nam na wycięcie pewnych wierszy z wyniku. Jednak należy być ostrożnym. W zapytaniu grupującym where jest filtrem, który działa przed agregacją danych. Jeśli chcemy filtrować wyniki po zgrupowaniu to w języku SQL istnieje jeszcze klauzula HAVING, jest to filtr dla agregatów.

Operując na tabeli przyjaciele z postu o podstawach grupowania możemy chcieć sprawdzić czy mamy wśród naszych przyjaciół co najmniej 10 osobowe grupy osób o tym samym imieniu:

select imie,count(imie) form przyjaciele
group by imie
having count(imie)>=10

Jeśli chcemy sprawdzić jak wyglądają zarobki najbogatszych naszych kolegów i koleżanek możemy przeprowadzić coś takiego:

select avg(zarobki) from przyjaciele
where zarobki > 10000
group by plec

w wyniku dostaniemy średnią zarobków większych od 10k z uwzględnieniem płci.

Możemy też sprawdzić czy imiennicy mogą zarabiać razem więcej:

select imie,avg(zarobki) from przyjaciele
where zarobki > 1000
group by imie
having count(imie) > 10

zapytanie najpierw odsieje osoby, które zarabiają mniej niż 10k, a później zliczy agregacje i odsieje worki z imionami, które zawierają mniej niż 11 przyjaciół.

Teraz trochę bardziej skomplikowany temat, otóż jak wyciągnąć przyjaciół, którzy zarabiają więcej niż średnia?
Można by spróbować:

select sum(zarobki) from przyjaciele
having zarobki > avg(zarobki)

ale to nam niestety nic nie zwróci (przynajmniej w oracle), pokaże się błąd agregacji.
Gdy zmienimy na:

select sum(zarobki) from przyjaciele
group by zarobki
having zarobki > avg(zarobki)

nie pokaże błędów, ale też nic nie zwróci.

Musimy niestety (lub stety) skorzystać z podzapytania:

select sum(zarobki) from przyjaciele
where zarobki > (select avg(zarobki) from przyjaciele)

ciekawostką jest wynik zapytania:

select sum(zarobki) from przyjaciele
having zarobki = avg(zarobki)

który zwraca wszystkie wiersze

Czasem zachodzi konieczność przefiltrowania wyników już zagregowanych w sposób możliwy do zrealizowania przez where.

select imie,count(imie) imiona from przyjaciele
where imiona > 5

to zapytanie również wyświetli błąd i jest to błąd popełniany dość często przez niedoświadczone osoby. Powinienem napisać o tym w sumie na początku przed opisem HAVING, ale chciałbym uniknąć takiego rozumowania.

W takim zapytaniu możemy wykorzystać wspomniane having lub zrobić z tego podzapytanie i użyć filtru where

select * from (
select imie,count(imie) imiona from przyjaciele
group by imie
) jakis_alias
where imiona>5

Takie zapytanie jest już poprawne. Pozostaje tylko kwestia dyskusji nad optymalnością i kosztem takiego rozwiązania.

Warto zwrócić także uwagę na podzapytania agregujące w selekcie:

select zarobki-srednia from (
select imie, zarobki, (select avg(zarobki) from przyjaciele p1 where p1.plec = p2.plec ) srednia from przyjaciele p2
) x

Nie jest to może zbyt szczęśliwy przykład, ale pokazuje możliwości.

W podzapytaniu grupującym, a właściwie na wynik takiego podzapytania w locie można nakładać funkcje arytmetyczne, np. zaokrąglenie

select zarobki-srednia from (
select imie, zarobki, round((select avg(zarobki) from przyjaciele p1 where p1.plec = p2.plec ),1) srednia from przyjaciele p2
) x

6 Replies to “Filtrowanie w zapytaniach grupujących”

  1. Witam serdecznie,
    nurtuje mnie takie pytanie. Mając tabelę
    CREATE TABLE IF NOT EXISTS `grupa` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `nazwisko` varchar(255) NOT NULL,
    `przedmiot` varchar(55) NOT NULL,
    `wynik` double(2,1) NOT NULL,
    PRIMARY KEY (`id`)
    )
    chciałbym wyciągnąć dla każdego nazwiska najlepszy wynik, czyli
    SELECT nazwisko, MAX(wynik) FROM grupa GROUP BY nazwisko
    I teraz pytanie, czy da się do tego zapytania dołączyć informację o przedmiocie, z którego został uzyskany najlepszy wynik bez stosowania podzapytań czy podzapytania są tutaj niezbędne?
    Pozdrawiam.

    1. Witam,
      proponowałbym Select * from grupa g1 where wynik = (select max(wynik) from grupa g2 where g1.naziwsko=g2.nazwisko group by nazwisko)
      Przy założeniu że nazwiska są unikalne.
      Jeśli tak nie jest to należy użyc podzapytania skorelowanego.

  2. Czyli rozumiem, że gdyby nazwiska nie byłyby unikalne to musiałoby być mniej więcej coś takiego:
    SELECT w.*,
    (SELECT przedmiot FROM `grupa` as gr WHERE gr.nazwisko = w.nazwisko AND gr.wynik = w.max) as przedmiot
    FROM( SELECT a.nazwisko, MAX(a.wynik) as max FROM `grupa` as a GROUP BY a.nazwisko) as w

        1. przepraszam, dosc ciezko z czytelnoscia tych komentarzy… Twoja wersja jest poprawna pod warunkiem że złączenie nie zwróci więcej niż jeden rekord.
          Jednak jesli mamy wiersz ID,NAZWISKO,WYNIK,PRZEDMIOT to dla
          select id from grupa where id=(select max(wynik) from grupa) …
          zostana zwroceone id z najwyzszymi wynikami, wiec moja wersja tez jest poprawna…

          Pozdrawiam
          J.

Leave a Reply

Your email address will not be published. Required fields are marked *

four − 2 =