Jak klauzulą HAVING filtrować grupy w wyniku zapytania SQL? – lekcja 16

Wstęp

Czy zawsze potrzebujesz oglądać wszystkie grupy i agregaty?

Czasem grupując dane i wykonując na nich funkcję agregującą interesujące będą jedynie wartości spełniające określone kryteria. Na przykład tylko podsumy lub tylko suma całkowita. O tym jak to uzyskać w SQL w lekcji niniejszej.

Po co kolejna klauzula?

Do ograniczania wyniku po grupowaniu służy klauzula HAVING. Po co osobna klauzula? Dlaczego nie można ograniczać w klauzuli WHERE?

Wynika to z kolejności przetwarzania komendy SELECT. Motor bazy danych analizuje komendę począwszy od FROM. Później WHERE, GROUP BY, SELECT i ORDER BY na końcu.

Jak widzisz klauzula GROUP BY jest analizowana po WHERE. W związku z tym WHERE nie pracuje na danych pogrupowanych. Nie może być wykorzystane do ograniczania. Dlatego wprowadzono osobną klauzulę HAVING, która jest wykonywana po GROUP BY. Czyli dotyczy danych pogrupowanych.

Gdy w zapytaniu klauzula GROUP BY nie występuje HAVING dotyczy całego wyniku. Wtedy cały wynik traktowany jest jako jedna grupa.

SELECT
  count(*)
FROM
  all_tables
HAVING
  count(*)>9;

Najprościej

Twoje zapytanie z poprzedniej lekcji może wyglądać tak:

SELECT
  table_name "nazwa tabeli"
  ,max(column_id) "maks. id kolumny"
FROM
  all_tab_columns
GROUP BY
  table_name
ORDER BY
  "maks. id kolumny" DESC;

Zakładając, że interesują Cię jedynie tabele mające więcej niż 5 kolumn wynik ograniczysz w taki sposób:

SELECT
  table_name "nazwa tabeli"
  ,max(column_id) "maks. id kolumny"
FROM
  all_tab_columns
GROUP BY
  table_name
HAVING
  max(column_id)>5
ORDER BY
  "maks. id kolumny" DESC;

Klauzula HAVING wykonywana jest po klauzuli GROUP BY. Natomiast w treści zapytania może być umieszczona po lub przed. Spróbuj.

W klauzuli HAVING możesz użyć kolumn wskazanych w klauzuli GROUP BY oraz funkcji agregujących. Możesz także używać operatorów logicznych. Mówiłem o nich we wcześniejszych lekcjach. Na przykład w taki sposób:

SELECT
  table_name "nazwa tabeli"
  ,max(column_id) "maks. id kolumny"
FROM
  all_tab_columns
GROUP BY
  table_name
HAVING
  max(column_id)>5
  AND min(data_length)>15
ORDER BY
  "maks. id kolumny" DESC;

Wybierze nazwy tabel dla których maksymalna wartość column_id jest większa niż 5, a minimalna wielkość kolumny w bajtach większa niż 15.

Jak widzisz w klauzuli HAVING możesz użyć dowolnej funkcji agregującej wykonanej na dowolnej kolumnie tabeli. Możesz także użyć kolumny table_name wskazanej w klauzuli GROUP BY, ale nie możesz użyć innych kolumn widoku all_tab_columns. Spróbuj samodzielnie i daj znać jaki przyniosło to efekt.

Podsumy

Pamiętasz klauzulę GROUP BY z rozszerzeniem rollup z poprzedniej lekcji? Prezentowałem tam takie zapytanie:

SELECT
  owner "schemat"
  ,initial_extent "wielkość inicjalnego extentu w bajtach"
  ,count(*) "liczba tabel"
  ,grouping(initial_extent) "podsuma initial_extent"
  ,grouping(owner) "podsuma owner"
FROM
  all_tables
GROUP BY ROLLUP(
  owner
  ,initial_extent)
ORDER BY
  "schemat";

Pamiętasz do czego służyła funkcja grouping użyta w klauzuli SELECT?

Tak, oznaczała podsumy jedynką. 'zwykłe’ grupy były oznaczone zerem. Chcąc otrzymać w wyniku jedynie podsumy możesz użyć klauzuli HAVING w taki sposób:

SELECT
  owner "schemat"
  ,initial_extent "bajtów inic. extentu"
  ,count(*) "liczba tabel"
  ,grouping(initial_extent) "podsuma initial_extent"
  ,grouping(owner) "podsuma owner"
FROM
  all_tables
GROUP BY ROLLUP(
  owner
  ,initial_extent)
HAVING
  grouping(initial_extent)=1
ORDER BY
  "schemat";

Takie zapytanie zwróci jedynie podsumy dla każdego schematu. Oraz sumę całkowitą.

Jeśli potrzebujesz jedynie sumę całkowitą zmień funkcję użytą w HAVING na tę:

SELECT
  owner "schemat"
  ,initial_extent "wielkość inicjalnego extentu w bajtach"
  ,count(*) "liczba tabel"
  ,grouping(initial_extent) "podsuma initial_extent"
  ,grouping(owner) "podsuma owner"
FROM
  all_tables
GROUP BY ROLLUP(
  owner
  ,initial_extent)
HAVING
  grouping(owner)=1
ORDER BY
  "schemat";

Warto pamiętać, że w zależności od tego jak klauzulą GROUP BY pogrupujesz dane możesz otrzymać różne perspektywy.

Oracle dostarcza wielu funkcjonalności wspierających analizę danych. Ich znajomość i wykorzystanie oszczędza koszty transferu sieciowego oraz czas użytkownika.

Podsumowanie

Gratuluję Ci ukończenia szesnastej lekcji darmowej edycji mojego kursu 'Podstawy SQL dla użytkowników Oracle’. Wiesz z niej, jak filtrować zwracane grupy i agregaty oraz, że klauzula WHERE służy do filtrowania przed grupowaniem, a HAVING po. Jedną z możliwych optymalizacji grupowania jest jawne wskazanie interesujących Cię grup wyniku.

Zadanie dla Ciebie: napisz zapytanie do widoku all_tab_columns zwracające liczbę kolumn poszczególnych typów danych. Ogranicz wynik do typów występujących więcej niż jedenaście razy. Wynik posortuj malejąco według liczby kolumn. Nadaj kontekstowe nazwy kolumnom wyniku przy pomocy aliasów.

Jeśli potrzebujesz podpowiedzi wbijaj na discord. Tam też chwal się swoim rozwiązaniem prezentując zrzut ekranu swojego klienta. Dedykowany serwer: Marcin Badtke – bazy danych i SQL.

Wiesz już jak używać klauzuli HAVING do filtrowania grup w wyniku zapytania. Klauzula HAVING kończy cykl dotyczący klauzul zapytania SQL.

Tymczasem dziękuję za ukończenie niniejszej.

Wiesz co trzeba zrobić aby nie przegapić kolejnych.

Wróć do poprzedniej lekcji