Grupowanie danych polega na tworzeniu grup rekordów w oparciu o definicję grupowania (klauzulę GROUP BY). Krok ten, wykonywany jest jako kolejny po filtrowaniu rekordów, zgodnie z warunkami określonymi w WHERE (o ile w ogóle cokolwiek filtrujemy), lub bezpośrednio po FROM jeśli nie korzystamy z selekcji wierszy.
(5) SELECT
(1) FROM
(2) WHERE
(3) GROUP BY
(4) HAVING
(6) ORDER BY
Aby dobrze zrozumieć możliwości jakie daje nam grupowanie rekordów, trzeba poznać sposób, w jaki jest ono realizowane przez silnik relacyjny.
W przypadku stosowania GROUP BY, w trakcie przetwarzania zapytania, tworzone są dwie sekcje danych.
Sekcja grupująca składa się z atrybutów tworzących definicję grupowania (są to kolumny wyszczególnione w GROUP BY). Grupy tworzone są przez wiersze, które mają takie same wartości w ramach tej sekcji. Grupa może być tworzona przez jeden (jeśli jest unikalny w ramach definicji grupy) lub wiele wierszy, w przypadku gdy posiadają te same wartości w kolumnach po których grupujemy.
Sekcja danych surowych (raw data section) zawierają wszystkie pozostałe dane, które mogą być już unikalne dla każdego wiersza w ramach sekcji grupującej. Sekcja danych surowych jest zawsze rozpatrywana w kontekście grupy.
Przykład grupowania rekordów
Najłatwiej zaprezentować ten podział na konkretnym przypadku. Spójrz na fragment danych w tabeli dbo.Employees, przez pryzmat przyszłej operacji grupowania. Będziemy chcieli, pogrupować pracowników po ich stanowisku pracy (title) oraz miejscu zatrudnienia (country). Firma posiada dwie placówki – jedna w USA, druga w UK.
USE Northwind
GO
select Title, Country, LastName, FirstName,
cast(BirthDate as date) BirthDate, cast(HireDate as date) HireDate
from dbo.Employees
ORDER BY Country,Title;
Grupując dane po kolumnach Title i Country, zgodnie z definicją mechanizmów grupujących, utworzone zostaną dwie sekcje.
Sekcja grupująca – będzie składała się z elementów unikalnych (tworzących faktyczne grupy), opisana za pomocą dwóch atrybutów grupujących Title i Country. Takich grup będziemy mieli 5. Dwie, które zawierają po trzy elementy (ponieważ są ciekawsze, zaznaczyłem je ramkami czerwonymi) oraz 3 jednoelementowe (np. Sales Manager z UK).
W skład sekcji danych surowych, wchodzą wszystkie pozostałe kolumny. Każda grupa sekcji grupującej jest bezpośrednio powiązana z właściwą sobie – grupą danych surowych (zaznaczyłem dwie ciekawsze na niebiesko – widać że zawierają unikalne w ramach grupy, wartości).
Grupowanie rekordów w praktyce
Wiemy już na jakich danych będziemy działać i jak w teorii będą wyznaczone sekcje. Napiszmy więc zapytanie, które pogrupuje rekordy ze względu na stanowisko i miejsce zatrudnienia.
select Title, Country, COUNT(*) as EmQty
from dbo.Employees
GROUP BY Country,Title
Korzystając z możliwości grupowania, musimy być świadomi pewnej logicznej konsekwencji. We wszystkich kolejnych krokach przetwarzania zapytania następujących po klauzuli GROUP BY (czyli w HAVING, SELECT, ORDER BY), będziemy mogli bezpośrednio wybierać tylko dane z kolumn sekcji grupującej. Wszystkie pozostałe kolumny, zawarte w sekcji surowej, mogą być wyciągane i przetwarzane tylko za pośrednictwem funkcji agregujących.
W naszym przykładzie, w SELECT możemy odwołać się bezpośrednio tylko do kolumn Title i Country.
Zwróć uwagę na to, że w wyniku grupowania, otrzymaliśmy zbiór 5 elementów. Zasada dostępu bezpośredniego tylko do danych z atrybutów grupujących jest moim zdaniem intuicyjna. W kontekście np. elementu Sales Representative z UK – w skład tej grupy wchodzą 3 wiersze. Pokazałem to za pomocą funkcji COUNT(), użytej w tym zapytaniu. Jest to także widoczne w poprzednim przykładzie, gdzie prezentowałem wszystkie dane na których będę działał (ramka niebieska – właściwa dla tej grupy rekordów).
Próbując wyciągnąć w SELECT bezpośrednio informację z kolumny HireDate, skąd silnik relacyjny miałby wiedzieć, o którą wartość nam chodzi. W ramach grupy są trzy rekordy i każdy z nich posiada inną wartość ‘1993-10-17’, ‘1994-01-02’ oraz ‘1994-11-15’.
Język SQL jest oparty na matematycznej teorii zbiorów w której (prawie) nie ma przypadków. Jest ściśle określony. Dlatego w każdym kroku po GROUP BY, bezpośrednio mamy dostęp tylko do kolumn tworzących grupę (wyszczególnionych w GROUP BY), a do pozostałych kolumn, możemy odwoływać się tylko poprzez funkcje agregujące.
Pomimo tego „ograniczenia”, mamy możliwość wykonywania dowolnych przekształceń i działań na danych surowych, przed wykonaniem agregacji.
W kolejnym przykładzie, obliczam średni wiek (AVG) pracownika w ramach grupy, a także pokazuje informację o najstarszym (MIN) i najmłodszym z nich (MAX). Korzystam tu także z dwóch skalarnych funkcji wbudowanych – YEAR(), która wyciąga rok z daty oraz GETDATE() – zwracająca aktualny czas systemowy.
SELECT Title, Country, COUNT(*) as EmQty,
AVG( YEAR(Getdate()) - YEAR(BirthDate) ) as AvgAge,
MAX( YEAR(Getdate()) - YEAR(BirthDate) ) as MaxAge,
MIN( YEAR(Getdate()) - YEAR(BirthDate) ) as MinAge
from dbo.Employees
GROUP BY Country,Title
ORDER BY AvgAge
Zauważ, że w ORDER BY, odwołuje się do aliasu wyniku działania funkcji AVG(). Jest to niejako potwierdzenie, że krok ORDER BY wykonywany jest jako ostatni – po SELECT.
Podsumowanie
Zaprezentowane powyżej sposób działania GROUP BY jest esencją grupowania rekordów. Pamiętać należy, że tworzone są sekcje atrybutów grupujących i danych surowych. Nie ma znaczenia czy grupujemy najpierw po kolumnie 'x’ a potem po 'y’. W teorii zbiorów, kolejność elementów oraz atrybutów z definicji nie ma znaczenia.
W każdym kolejnym kroku przetwarzania zapytania po GROUP BY, bezpośredni dostęp mamy do kolumn grupujących (wymienionych w klauzuli GROUP BY). Na pozostałych działamy za pomocą funkcji agregujących, które szerzej opisuję w artykule im poświęconym.
SQL Server 2008 R2 oferuje też inne sposoby grupowania rekordów. Bazują one wszystkie na zasadzie przedstawionej powyżej i są tylko rozszerzeniem mechanizmu grupowania. Ułatwiają i optymalizują zadania wielokrotnego grupowania (GROUPING SETS, CUBE, ROLLUP).
Mam taki case:
1. Tabela zawiera wiele danych, m.in. nazwę produktu (name), jego id (product_id)oraz grupę produktów (prodgroup).
2. Potrzebuję wyświetlić najmniejsze id w każdej z grup oraz odpowiednia nazwę produktu o najmniejszym id.
select min(product_id), prodgroup
from products
group by prodgroup
order by prodgroup desc.
Powyższe zapytanie działa prawidłowo, ale dodanie do selecta kolumny name zawierającej nazwę produktu wykrzacza wszystko.
Jak to zrobić w łatwy sposób?
A jak ograniczyć wyświetlanie ilości rekordów dla wszystkich grup?
Np jedna grupa ma wystąpień 20, inna 15 a kolejne po np 5.
W sekcji grupującej będą to po prostu dane powtarzające się.
A jak zawęzić wyświetlenia np do 3 najnowszych, najdroższych, najcięższych itp. wystąpień (Sortując po danych w sekcji RAW Data).
Jest to możliwe w obrębie podstawowych poleceń? Czy w tym przypadku wkraczają już funkcje?
Próbowałem DISTINCT, LIMIT, MAX, MIN, ale bezskutecznie. Nie zależy mi na zliczaniu, ale na wyświetlaniu.
Byłbym wdzięczny za jakieś wskazówki.
Pozdrawiam
Cześć,
Czy jest jakaś różnica wydajności w przypadku stosowania funkcji count(*) i count (1)? Różnie opinie krążą na ten temat, jedni twierdzą, że count(1) jest bardziej wydajne ponieważ zlicza rekordy po 1 kolumnie, która często jest primary key z indeksem. Z drugiej strony inni piszą że, MSSQL zamienia funkcję count(1) na count(*) i nie ma żadnej różnicy?
Nie ma znaczenia, count(1) jest przepisywane na count(*).
Cześć, wiecie może jak zrobić order by, po nowopowstałej kolumnie.
Do zapytania poniżej chciałabym dołożyć sortowanie po kolumnie count(1) over (partition by t.channelname).
Dodaj alias do tej nowo powstałej kolumny.
Dzieki kolego. Robota pierwsza klasa. Zaczynam zabawe w pracy z TSQL i ten kurs duzo mi pomogl.
Kolejny plus za „graficzne” wytłumaczenie grupowania danych. W typowych podręcznikach do nauki SQL-a oczywiście omawia się grupowanie, ale zaletą podejścia Autora SQLpedii jest to, że pokazał ten proces wizualnie. A jak się coś ogląda na obrazku (np. za pomocą tabeli z kolorami), to łatwiej to zrozumieć. Dobrze że położono nacisk (wraz z wytłumaczeniem) na utworzenie sekcji grupującej i sekcji danych surowych, i na to że nie mamy bezpośredniego dostępu do danych w tej ostatniej.
Witam
Dziękuję za kurs. Mam pytanie do pierwszego przykładu. Dlaczego użyta została klauzula ORDER BY „ORDER BY Country,Title;”, kiedy opis dotyczy GROUP BY. Kiedy próbowałem zmienić na GROUP BY otrzymałem błąd:
„Msg 8120, Level 16, State 1, Line 2
Column 'dbo.Employees.LastName’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.”
Pozdrawiam
Przykład ma obrazować dane które będą później grupowane 😉 dlatego pozwoliłem sobie je najpierw posortować.
Witam. Mam 2 pytania:
1. Dlaczego na początku artykułu została użyta funkcja CAST?
2. W przykładzie z obliczaniem średniego wieku pracownika w ramach grupy po użyciu funkcji AVG poprzez alias niejako w sposób „automatyczny” zostaje stworzona nowa kolumna (AvgAge), a dzieje się to przecież cały czas w ramach funkcji SELECT, czyli wybierania danych. Szczerze mówiąc nie do końca jest to dla mnie intuicyjne. Spodziewałbym się, że najpierw należałoby stworzyć nową kolumnę, a później ją dopiero wybierać. Dlaczego działa to w ten sposób?
Ad.1 – kolumna BirthDate jest kolumną typu DateTime czyli może przechowywać informacje o dacie wraz z godziną (całkiem dokładnie bo do tysięcznych/3 części sekundy). W przykładzie interesowała mnie tylko DATA. Stąd funkcja konwersji.
Ad.2 – funkcja działająca w SELECT (tu AVG) działa na sekcji danych surowych (RAW data – zobacz na schemacie na początku artykułu). Tworzy ona „w locie” nową kolumnę, podobnie jak dowolne inne możliwe przekształcenia w SELECT. Polecam uwadze artykuł na temat SELECT
Dobrze się czyta, nawet dla początkujących:)
Taki był cel 😉
„Jest to niejako potwierdzenie, że krok ORDER BY wykonywany jest jako ostatni – przed SELECT.”
chyba powinno być
„Jest to niejako potwierdzenie, że krok ORDER BY wykonywany jest jako ostatni – po SELECT.”
ostatnie zdanie przed podsumowaniem 🙂
no chyba, że czegoś nie zrozumiałem
ogólnie kurs dobry 🙂
Bardzo dobrze zrozumiałeś 🙂 Z tego masła maślanego nasunęłą mi się analogia :
– Do you understand ?
– Yes, I don’t 🙂
To tak mniej więcej wyglądało przed korektą. Dzięki za zwrócenie uwagi.