Home-theater-designers
Duża część mocy relacyjnych baz danych pochodzi z filtrowania danych i łączenia tabel. Dlatego w pierwszej kolejności reprezentujemy te relacje. Jednak nowoczesne systemy baz danych oferują kolejną cenną technikę: grupowanie.
Grupowanie umożliwia uzyskanie podsumowania informacji z bazy danych. Pozwala łączyć wyniki w celu tworzenia przydatnych danych statystycznych. Grupowanie pozwala uniknąć pisania kodu dla typowych przypadków, takich jak uśrednianie list liczb. I może sprawić, że systemy będą wydajniejsze.
Co robi klauzula GROUP BY?
GROUP BY, jak sama nazwa wskazuje, grupuje wyniki w mniejszy zestaw. Wyniki składają się z jednego wiersza dla każdej odrębnej wartości zgrupowanej kolumny. Możemy pokazać jego użycie, patrząc na przykładowe dane z wierszami, które mają wspólne wartości.
laptop nie pozostaje podłączony do systemu Windows 10
Poniżej znajduje się bardzo prosta baza danych z dwiema tabelami reprezentującymi albumy nagrań. Taką bazę danych możesz założyć poprzez: pisanie podstawowego schematu dla wybranego systemu bazodanowego. ten albumy tabela ma dziewięć wierszy z kluczem podstawowym NS kolumna i kolumny z nazwiskiem, wykonawcą, rokiem wydania i sprzedażą:
+----+---------------------------+-----------+--------------+-------+
| id | name | artist_id | release_year | sales |
+----+---------------------------+-----------+--------------+-------+
| 1 | Abbey Road | 1 | 1969 | 14 |
| 2 | The Dark Side of the Moon | 2 | 1973 | 24 |
| 3 | Rumours | 3 | 1977 | 28 |
| 4 | Nevermind | 4 | 1991 | 17 |
| 5 | Animals | 2 | 1977 | 6 |
| 6 | Goodbye Yellow Brick Road | 5 | 1973 | 8 |
| 7 | 21 | 6 | 2011 | 25 |
| 8 | 25 | 6 | 2015 | 22 |
| 9 | Bat Out of Hell | 7 | 1977 | 28 |
+----+---------------------------+-----------+--------------+-------+ten artyści stół jest jeszcze prostszy. Ma siedem wierszy z kolumnami id i nazwy:
+----+---------------+
| id | name |
+----+---------------+
| 1 | The Beatles |
| 2 | Pink Floyd |
| 3 | Fleetwood Mac |
| 4 | Nirvana |
| 5 | Elton John |
| 6 | Adele |
| 7 | Meat Loaf |
+----+---------------+Możesz zrozumieć różne aspekty funkcji GROUP BY za pomocą prostego zestawu danych, takiego jak ten. Oczywiście zestaw rzeczywistych danych miałby wiele, wiele więcej wierszy, ale zasady pozostają takie same.
Grupowanie według jednej kolumny
Powiedzmy, że chcemy dowiedzieć się, ile albumów mamy dla każdego artysty. Zacznij od typowego WYBIERZ zapytanie do pobrania kolumny artist_id:
SELECT artist_id FROM albumsZwraca wszystkie dziewięć wierszy, zgodnie z oczekiwaniami:
+-----------+
| artist_id |
+-----------+
| 1 |
| 2 |
| 3 |
| 4 |
| 2 |
| 5 |
| 6 |
| 6 |
| 7 |
+-----------+Aby pogrupować te wyniki według wykonawcy, dołącz frazę GROUP WG artysty_id :
SELECT artist_id FROM albums GROUP BY artist_idCo daje następujące wyniki:
+-----------+
| artist_id |
+-----------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+-----------+W zestawie wyników jest siedem wierszy, pomniejszonych z łącznej liczby dziewięciu w albumy Tabela. Każdy wyjątkowy identyfikator_artysty ma jeden rząd. Na koniec, aby uzyskać rzeczywiste liczby, dodaj LICZYĆ(*) do wybranych kolumn:
SELECT artist_id, COUNT(*)
FROM albums
GROUP BY artist_id
+-----------+----------+
| artist_id | COUNT(*) |
+-----------+----------+
| 1 | 1 |
| 2 | 2 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
| 6 | 2 |
| 7 | 1 |
+-----------+----------+Wyniki grupują dwie pary wierszy dla artystów z identyfikatorami 2 oraz 6 . Każdy ma w naszej bazie dwa albumy.
Związane z: Niezbędna ściągawka do poleceń SQL dla początkujących
Jak uzyskać dostęp do zgrupowanych danych za pomocą funkcji agregującej
Być może użyłeś LICZYĆ funkcjonować wcześniej, szczególnie w LICZYĆ(*) formularz jak widać powyżej. Pobiera liczbę wyników w zestawie. Możesz go użyć, aby uzyskać całkowitą liczbę rekordów w tabeli:
SELECT COUNT(*) FROM albums
+----------+
| COUNT(*) |
+----------+
| 9 |
+----------+ILE.LICZB to funkcja agregująca. Termin ten odnosi się do funkcji, które tłumaczą wartości z wielu wierszy na pojedynczą wartość. Są często używane w połączeniu z instrukcją GROUP BY.
Zamiast po prostu liczyć liczbę wierszy, możemy zastosować funkcję agregującą do zgrupowanych wartości:
SELECT artist_id, SUM(sales)
FROM albums
GROUP BY artist_id
+-----------+------------+
| artist_id | SUM(sales) |
+-----------+------------+
| 1 | 14 |
| 2 | 30 |
| 3 | 28 |
| 4 | 17 |
| 5 | 8 |
| 6 | 47 |
| 7 | 28 |
+-----------+------------+Łączna sprzedaż pokazana powyżej dla wykonawców 2 i 6 to łączna sprzedaż ich wielu albumów:
SELECT artist_id, sales
FROM albums
WHERE artist_id IN (2, 6)
+-----------+-------+
| artist_id | sales |
+-----------+-------+
| 2 | 24 |
| 2 | 6 |
| 6 | 25 |
| 6 | 22 |
+-----------+-------+Grupowanie według wielu kolumn
Możesz grupować według więcej niż jednej kolumny. Wystarczy dołączyć wiele kolumn lub wyrażeń oddzielonych przecinkami. Wyniki zostaną pogrupowane według kombinacji tych kolumn.
SELECT release_year, sales, count(*)
FROM albums
GROUP BY release_year, salesZwykle daje to więcej wyników niż grupowanie według jednej kolumny:
+--------------+-------+----------+
| release_year | sales | count(*) |
+--------------+-------+----------+
| 1969 | 14 | 1 |
| 1973 | 24 | 1 |
| 1977 | 28 | 2 |
| 1991 | 17 | 1 |
| 1977 | 6 | 1 |
| 1973 | 8 | 1 |
| 2011 | 25 | 1 |
| 2015 | 22 | 1 |
+--------------+-------+----------+Zauważ, że w naszym małym przykładzie tylko dwa albumy mają ten sam rok wydania i liczbę sprzedaży (28 w 1977).
Przydatne funkcje agregujące
Oprócz COUNT, kilka funkcji działa dobrze z GROUP. Każda funkcja zwraca wartość na podstawie rekordów należących do każdej grupy wyników.
- COUNT() zwraca całkowitą liczbę pasujących rekordów.
- SUM() zwraca sumę wszystkich zsumowanych wartości w danej kolumnie.
- MIN() zwraca najmniejszą wartość w danej kolumnie.
- MAX() zwraca największą wartość w danej kolumnie.
- AVG() zwraca średnią średnią. Jest to odpowiednik SUM() / COUNT().
Możesz również użyć tych funkcji bez klauzuli GROUP:
otrzymuję reklamy na moim telefonie
SELECT AVG(sales) FROM albums
+------------+
| AVG(sales) |
+------------+
| 19.1111 |
+------------+Używanie GROUP BY z klauzulą WHERE
Podobnie jak w przypadku normalnego SELECT, nadal możesz użyć WHERE do filtrowania zestawu wyników:
SELECT artist_id, COUNT(*)
FROM albums
WHERE release_year > 1990
GROUP BY artist_id +-----------+----------+
| artist_id | COUNT(*) |
+-----------+----------+
| 4 | 1 |
| 6 | 2 |
+-----------+----------+Teraz masz tylko te albumy wydane po 1990 roku, pogrupowane według wykonawców. Możesz również użyć sprzężenia z klauzulą WHERE, niezależnie od GROUP BY:
SELECT r.name, COUNT(*) AS albums
FROM albums l, artists r
WHERE artist_id=r.id
AND release_year > 1990
GROUP BY artist_id +---------+--------+
| name | albums |
+---------+--------+
| Nirvana | 1 |
| Adele | 2 |
+---------+--------+Pamiętaj jednak, że jeśli spróbujesz filtrować na podstawie zagregowanej kolumny:
SELECT r.name, COUNT(*) AS albums
FROM albums l, artists r
WHERE artist_id=r.id
AND albums > 2
GROUP BY artist_id;Otrzymasz błąd:
ERROR 1054 (42S22): Unknown column 'albums' in 'where clause'Kolumny oparte na danych zagregowanych nie są dostępne dla klauzuli WHERE.
Korzystanie z klauzuli HAVING
Jak więc filtrować zestaw wyników po przeprowadzeniu grupowania? ten MAJĄCY klauzula dotyczy tej potrzeby:
SELECT r.name, COUNT(*) AS albums
FROM albums l, artists r
WHERE artist_id=r.id
GROUP BY artist_id
HAVING albums > 1;Zauważ, że klauzula HAVING występuje po GROUP BY. W przeciwnym razie jest to w zasadzie proste zastąpienie GDZIE na MIEĆ. Wyniki to:
+------------+--------+
| name | albums |
+------------+--------+
| Pink Floyd | 2 |
| Adele | 2 |
+------------+--------+Nadal możesz użyć warunku WHERE, aby filtrować wyniki przed grupowaniem. Będzie działać razem z klauzulą HAVING do filtrowania po grupowaniu:
SELECT r.name, COUNT(*) AS albums
FROM albums l, artists r
WHERE artist_id=r.id
AND release_year > 1990
GROUP BY artist_id
HAVING albums > 1;Tylko jeden artysta w naszej bazie danych wydał więcej niż jeden album po 1990 roku:
+-------+--------+
| name | albums |
+-------+--------+
| Adele | 2 |
+-------+--------+Łączenie wyników z GROUP BY
Instrukcja GROUP BY jest niezwykle użyteczną częścią języka SQL. Może dostarczać informacji podsumowujących dane, na przykład dla strony ze spisem treści. To doskonała alternatywa dla pobierania dużych ilości danych. Baza danych dobrze radzi sobie z tym dodatkowym obciążeniem, ponieważ sama konstrukcja sprawia, że jest optymalna do zadania.
Gdy zrozumiesz grupowanie i sposób łączenia wielu tabel, będziesz w stanie wykorzystać większość możliwości relacyjnej bazy danych.
Udział Udział Ćwierkać E-mail Jak wysyłać zapytania do wielu tabel bazy danych jednocześnie za pomocą złączeń SQL?Dowiedz się, jak używać złączeń SQL, aby usprawnić zapytania, zaoszczędzić czas i sprawić, że poczujesz się jak doświadczony użytkownik SQL.
wylogowywanie się z Netfliksa w telewizjiCzytaj dalej Powiązane tematy
- Programowanie
- SQL
Bobby jest entuzjastą technologii, który przez prawie dwie dekady pracował jako programista. Pasjonuje się grami, pracuje jako redaktor recenzji w magazynie Switch Player i jest zanurzony we wszystkich aspektach publikowania online i tworzenia stron internetowych.
Więcej od Bobby'ego JackaZapisz się do naszego newslettera
Dołącz do naszego newslettera, aby otrzymywać porady techniczne, recenzje, bezpłatne e-booki i ekskluzywne oferty!
Kliknij tutaj, aby zasubskrybować