Najważniejsze są funkcje agregujące
- Wykonywanie obliczeń na wielu wierszach
- Z jednej kolumny tabeli
- I zwraca pojedynczą wartość.
Norma ISO definiuje pięć (5) funkcji zagregowanych, a mianowicie;
1) LICZBA
3) ŚREDNIA
4) MIN
5) MAKS
Dlaczego warto korzystać z funkcji agregujących.
Z perspektywy biznesowej różne poziomy organizacji mają różne wymagania dotyczące informacji. Menedżerowie najwyższego szczebla są zwykle zainteresowani znajomością całych liczb i nie są potrzebne szczegółowe informacje.
> Funkcje agregujące pozwalają nam łatwo tworzyć podsumowane dane z naszej bazy danych.
Na przykład z naszej bazy danych myflix zarządzanie może wymagać następujących raportów
- Najmniej wypożyczonych filmów.
- Najczęściej wypożyczane filmy.
- Średnia liczba wypożyczeń każdego filmu w ciągu miesiąca.
Powyższe raporty z łatwością tworzymy za pomocą funkcji agregujących.
Przyjrzyjmy się szczegółowo funkcjom agregującym.
Funkcja COUNT
Funkcja LICZBA zwraca całkowitą liczbę wartości w określonym polu. Działa zarówno na liczbowych, jak i nieliczbowych typach danych. Wszystkie funkcje agregujące domyślnie wykluczają wartości null przed rozpoczęciem pracy z danymi.
COUNT (*) to specjalna implementacja funkcji COUNT, która zwraca liczbę wszystkich wierszy w określonej tabeli. COUNT (*) uwzględnia również wartości Null i duplikaty.
Poniższa tabela przedstawia dane w tabeli movierentals
| numer referencyjny | Data dokonania transakcji | Data powrotu | numer_ członkostwa | movie_id | film_ zwrócił |
|---|---|---|---|---|---|
| 11 | 20-06-2012 | ZERO | 1 | 1 | 0 |
| 12 | 22-06-2012 | 25-06-2012 | 1 | 2 | 0 |
| 13 | 22-06-2012 | 25-06-2012 | 3 | 2 | 0 |
| 14 | 21-06-2012 | 24-06-2012 | 2 | 2 | 0 |
| 15 | 23-06-2012 | ZERO | 3 | 3 | 0 |
Załóżmy, że chcemy uzyskać, ile razy film o identyfikatorze 2 został wypożyczony
SELECT COUNT(`movie_id`) FROM `movierentals` WHERE `movie_id` = 2;
Wykonanie powyższego zapytania w środowisku MySQL na myflixdb daje nam następujące wyniki.
| COUNT('movie_id') |
|---|
| 3 |
Słowo kluczowe DISTINCT
Słowo kluczowe DISTINCT, które pozwala nam pominąć duplikaty w naszych wynikach. Osiąga się to poprzez grupowanie razem podobnych wartości.
Aby docenić koncepcję Distinct, wykonajmy proste zapytanie
SELECT `movie_id` FROM `movierentals`;
| movie_id |
|---|
| 1 |
| 2 |
| 2 |
| 2 |
| 3 |
Teraz wykonajmy to samo zapytanie z odrębnym słowem kluczowym -
SELECT DISTINCT `movie_id` FROM `movierentals`;
Jak pokazano poniżej, funkcja odrębna pomija zduplikowane rekordy z wyników.
| movie_id |
|---|
| 1 |
| 2 |
| 3 |
Funkcja MIN
Funkcja MIN zwraca najmniejszą wartość w określonym polu tabeli .
Jako przykład, załóżmy, że chcemy poznać rok, w którym wydano najstarszy film w naszej bibliotece, możemy użyć funkcji MIN MySQL, aby uzyskać żądane informacje.
Poniższe zapytanie pomoże nam to osiągnąć
SELECT MIN(`year_released`) FROM `movies`;
Wykonanie powyższego zapytania w środowisku MySQL na myflixdb daje nam następujące wyniki.
| MIN('year_released') |
|---|
| 2005 |
Funkcja MAX
Jak sama nazwa wskazuje, funkcja MAX jest przeciwieństwem funkcji MIN. To zwraca największą wartość z zakresu określonej tabeli .
Załóżmy, że chcemy uzyskać rok, w którym opublikowano najnowszy film w naszej bazie danych. W tym celu możemy łatwo skorzystać z funkcji MAX.
Poniższy przykład zwraca ostatni wydany rok filmowy.
SELECT MAX(`year_released`) FROM `movies`;
Wykonanie powyższego zapytania w środowisku MySQL przy użyciu myflixdb daje następujące wyniki.
| MAX('year_released') |
|---|
| 2012 |
SUMA, funkcja
Załóżmy, że chcemy raportu, który podaje całkowitą kwotę płatności dokonanych do tej pory. Możemy użyć funkcji SUMA MySQL, która zwraca sumę wszystkich wartości w określonej kolumnie . SUMA działa tylko na polach numerycznych . Wartości null są wykluczane z zwracanego wyniku.
Poniższa tabela przedstawia dane w tabeli płatności -
| identyfikator_płaty | numer_ członkostwa | termin płatności | opis | opłata zapłacona | numer_odniesienia_zewnętrznego |
|---|---|---|---|---|---|
| 1 | 1 | 23-07-2012 | Opłata za wypożyczenie filmu | 2500 | 11 |
| 2 | 1 | 25-07-2012 | Opłata za wypożyczenie filmu | 2000 | 12 |
| 3 | 3 | 30-07-2012 | Opłata za wypożyczenie filmu | 6000 | ZERO |
Poniższe zapytanie pobiera wszystkie dokonane płatności i sumuje je, aby zwrócić jeden wynik.
SELECT SUM(`amount_paid`) FROM `payments`;
Wykonanie powyższego zapytania w środowisku roboczym MySQL na myflixdb daje następujące wyniki.
| SUM('amount_paid') |
|---|
| 10500 |
Funkcja AVG
Funkcja MySQL AVG zwraca średnią wartości w określonej kolumnie . Podobnie jak funkcja SUMA, działa tylko na liczbowych typach danych .
Załóżmy, że chcemy znaleźć średnią zapłaconą kwotę. Możemy użyć następującego zapytania -
SELECT AVG(`amount_paid`) FROM `payments`;
Wykonanie powyższego zapytania w środowisku MySQL daje nam następujące wyniki.
| AVG('amount_paid') |
|---|
| 3500 |
Podsumowanie
- MySQL obsługuje wszystkie pięć (5) standardowych funkcji agregujących ISO LICZBA, SUMA, ŚREDNIA, MIN i MAKS.
- Funkcje SUMA i ŚREDNIA działają tylko na danych liczbowych.
- Jeśli chcesz wykluczyć zduplikowane wartości z wyników funkcji agregujących, użyj słowa kluczowego DISTINCT. Słowo kluczowe ALL obejmuje nawet duplikaty. Jeśli nic nie zostanie określone, jako domyślne przyjmuje się WSZYSTKIE.
- Funkcje agregujące mogą być używane w połączeniu z innymi klauzulami SQL, takimi jak GROUP BY
Łamigłówka
Myślisz, że funkcje agregujące są łatwe. Spróbuj tego!
Poniższy przykład grupuje członków według nazwy, zlicza całkowitą liczbę płatności, średnią kwotę płatności i sumę całkowitą kwot płatności.
SELECT m.`full_names`,COUNT(p.`payment_id`) AS `paymentscount`,AVG(p.`amount_paid`) AS `averagepaymentamount`,SUM(p.`amount_paid`) AS `totalpayments` FROM members m, payments p WHERE m.`membership_number` = p.`membership_number` GROUP BY m.`full_names`;
Wykonanie powyższego przykładu w środowisku MySQL daje nam następujące wyniki.
