Jak i po co klauzulą GROUP BY grupować dane? – lekcja 15

Wstęp

Grupowanie to zbieranie w zestawy danych o takich samych wartościach. W niniejszej lekcji pokażę jak i po co robić to przy pomocy klauzuli GROUP BY.

Po co grupować?

Zwróć uwagę ile powtarzających się danych zwracały zapytania w lekcji o sortowaniu. Czy to było przydatne?

Czasem jest. Czasem potrzebujesz wykonać jakąś operację na wszystkich wierszach poukładanych w określonej kolejności. Część danych może się powtarzać. Możesz nie chcieć przedzierać się przez mnogość wierszy o takich samych wartościach. Możesz potrzebować jedynie informacji o tym jakie grupy wartości w bazie danych istnieją. Wtedy nakazujesz motorowi bazy danych ich grupowanie.

Najprościej

Najprościej pogrupujesz w taki sposób:

SELECT
  owner
FROM
  all_tables
WHERE
  num_rows>0
GROUP BY
  owner
ORDER BY
  owner;

Klauzulę GROUP BY umieszczasz po klauzuli WHERE. O ile WHERE występuje. GROUP BY wykonywana jest po klauzulach FROM i WHERE, ale przed SELECT i ORDER BY. Czyli motor bazy danych wie już z jakiej tabeli i jakie wiersze wybrać. W klauzuli GROUP BY nakazujesz mu jak ma je pogrupować. Następnie w klauzulach SELECT i ORDER BY mówisz co i w jakiej kolejności ma zwrócić. Możesz zażądać jedynie kolumn użytych w GROUP BY. Jak sądzisz dlaczego?

Zapytanie zwróci unikalne wartości kolumny owner widoku all_tables. Czyli grupy wartości kolumny owner. Przypomina działanie klauzuli DISTINCT zaprezentowanej we wcześniejszej lekcji, prawda?

Można spotkać się z opiniami, że takie użycie GROUP BY jest bardziej efektywne niż DISTINCT. Ja nie testowałem. Przetestuj Ty i pochwal się wynikiem. Albo, w komentarzach, namów mnie to zrobienia testów.

Z funkcjami agregującymi

Prawdziwą moc klauzuli GROUP BY możesz poczuć używając jej w połączeniu z funkcjami agregującymi. Czyli funkcjami wyliczającymi jeden wynik na podstawie wielu wartości. Używając funkcji agregującej w klauzuli SELECT oraz grupując rekordy przy pomocy GROUP BY instruujesz motor bazy danych aby wykonał funkcję agregującą na każdej grupie wartości. Pamiętasz o jakiej funkcji agregującej mówiłem w lekcji o liczeniu wierszy?

Masz rację mówiłem o count.

SELECT
  owner "właściciel"
  ,count(*) "liczba tabel"
FROM
  all_tables
GROUP BY
  owner;

W klauzuli SELECT wymieniasz kolumnę wg której grupujesz. Tę samą co w klauzuli GROUP BY. Następnie wskazujesz funkcję agregującą jaka ma być wykonana na każdej grupie rekordów.

Teraz samodzielnie napisz zapytanie zwracające liczbę tabel w każdym z tablespace, ale bez tabel nieprzypisanych do żadnego tablespace. Czyli bez rekordów gdzie tablespace_name jest NULL. Masz?

Ja zrobiłbym tak:

SELECT
  tablespace_name "nazwa tablespace"
  ,count(*) "liczba tabel w tablespace"
FROM
  all_tables
WHERE
  tablespace_name IS NOT NULL
GROUP BY
  tablespace_name

Swoją drogą jak to możliwe? Mieć w Oracle tabele nieprzypisane do żadnego tablespace? Jak sądzisz czym te tabele się wyróżniają?

W jednym zapytaniu możesz użyć wielu funkcji agregujących. Na przykład można policzyć ile tabel ma każdy schemat. Oraz zsumować bloki bazodanowe zajmowane przez należące do niego tabele.

SELECT
  owner "schemat"
  ,count(table_name) "liczba tabel"
  ,sum(blocks) "liczba bloków"
FROM
  all_tables
GROUP BY
  owner;

Zwróć uwagę, że parametrami funkcji agregujących są kolumny, które nie zostały wymienione w klauzuli GROUP BY. Jest to zrozumiałe. Bez funkcji agregujących motor bazy danych nie wiedziałby, którą wartość z grupy rekordów zwrócić. Funkcja agregująca zwraca pojedynczy wynik wyliczony z grupy wartości.

Warto wiedzieć, że w Oracle do wersji 23ai, w GROUP BY, nie możesz użyć do wskazania kolumny jej aliasu ani pozycji z klauzuli SELECT. Za to możesz tak zrobić w PostgreSQL, MySQL i Oracle od wersji 23ai.

Klauzula GROUP BY grupuje rekordy, ale nie gwarantuje kolejności grup w wyniku. Tę zapewniasz sobie klauzulą ORDER BY o której mówiłem w lekcji o sortowaniu. Pogrupowany wynik możesz sortować po wynikach zwracanych przez funkcję agregującą. Na przykład możesz posortować według liczby wartości w kolumnie table_name:

SELECT
  owner "schemat"
  ,count(table_name) "liczba tabel"
  ,sum(blocks) "zaalokowanych bloków"
FROM
  all_tables
GROUP BY
  owner
ORDER BY
  "liczba tabel";

W klauzuli ORDER BY użyłem aliasu. O aliasach mówiłem we wcześniejszej lekcji.

Możesz także grupować po kilku kolumnach:

SELECT
  owner "schemat"
  ,partitioned "spartycjonowane"
  ,count(*) "liczba tabel"
FROM
  all_tables
GROUP BY
  owner
  ,partitioned
ORDER BY
  "schemat";

To zapytanie pokaże ile, w każdym ze schematów, jest tabel spartycjonowanych, a ile nie. Partycjonowanie to logiczne dzielenie danych w tabeli na mniejsze obiekty.

Oracle udostępnia znaczną liczbę funkcji agregujących. Oto kilka z najpopularniejszych:

SELECT
  owner "schemat"
  ,min(num_rows) "minimalna liczba wierszy w tabeli"
  ,avg(num_rows) "średnia liczba wierszy w tabeli"
  ,median(num_rows) "mediana liczby wierszy w tabeli"
  ,max(num_rows) "maksymalna liczba wierszy w tabeli"
FROM
  all_tables
GROUP BY
  owner;

Pełną listę funkcji agregujących znajdziesz w dokumentacji.

Pamiętaj, że funkcje agregujące ignorują NULL, o którym mówiłem we wcześniejszych lekcjach. Możesz myśleć o nich jako o funkcjach zwracających jedną wartość z grupy według zadanych przez Ciebie kryteriów.

Podsumy

Oprócz grupowania rekordów i wykonywania funkcji agregujących na grupach klauzula GROUP BY została wzbogacona o rozszerzenie pozwalające na liczenie podsum. Czyli sumy dla każdej grupy oraz łącznej sumy na końcu. Jest to optymalizowanie dostępu do danych. Skoro motor bazy danych już dane odczytał i pogrupował to niech policzy podsumę niejako przy okazji. Jest to bardziej efektywne niż liczenie podsum w aplikacji czy kolejnymi zapytaniami. Oczywiście o ile podsumy potrzebujesz.

A może potrzebujesz jedynie podsumy? Możesz spowodować, że Oracle zwróci jedynie je. Zaoszczędzisz na transferze sieciowym i obliczeniach. Dzięki czemu Twoja aplikacja będzie działać szybciej.

Do policzenia podsum w każdej grupie służy rozszerzenie rollup:

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

Jak widzisz inaczej niż funkcje agregujące używane jest w klauzuli GROUP BY. Dzięki użyciu rozszerzenia rollup w wyniku pojawiły się podsumy dla każdego właściciela. Czyli dla każdej wartości kolumny owner.

W wierszu z podsumą druga kolumna, initial_extent, została zastąpiona NULL. Jest to poszlaka wskazująca, który wiersz zawiera podsumy. Niemniej trzeba zwrócić uwagę czy NULL nie zostało wybrane z bazy danych i nie występuje w wyniku. W moim przypadku zostało.

O ile wizualnie jesteś w stanie domyślić się co jest podsumą, NULL w kolumnie wskazanej w rollup, o tyle program komputerowy niekoniecznie. Bezpieczniej będzie jawnie mu to wskazać. Szczególnie jeśli NULL występuje także jako grupowana wartość.

Do wskazywania podsum służy funkcja grouping.

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";

Jako parametr podajesz kolumnę według, której następuje sumowanie.

Wtedy w wyniku sumy oznaczone są cyfrą jeden.

Przydatnych do analizy danych funkcji i rozszerzeń operujących na grupach jest znacznie więcej. Możesz użyć komentarzy pod tym materiałem aby zachęcić mnie do ich omówienia.

Podsumowanie

Gratuluję Ci ukończenia piętnastej lekcji darmowej edycji mojego kursu 'Podstawy SQL dla użytkowników Oracle’. Wiesz z niej, jak używać klauzuli GROUP BY do grupowania rekordów w zbiory oraz jak wykonywać na nich funkcje agregujące.

Zadanie dla Ciebie: napisz i uruchom zapytanie wybierające table_name i wartość maksymalną kolumny column_id z all_tab_columns. Wynik posortuj malejąco wg wartości maksymalnej. Nadaj kontekstowe nazwy kolumnom 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 GROUP BY do grupowania rekordów w zbiory o tych samych wartościach oraz jak wykonywać na nich funkcje agregujące. A co zrobić gdy chcesz zobaczyć tylko grupy spełniające określone warunki? Na przykład interesują Cię tylko tabele mające więcej niż 5 kolumn? O tym będzie następna lekcja.

Dziękuję za ukończenie niniejszej. Wiesz co trzeba zrobić aby nie przegapić kolejnych.

Wróć do poprzedniej lekcji