MySQL Funkcje agregujące: SUMA, LICZNIK, AVG & MAX

Czym są funkcje agregujące w MySQL?

An funkcja zagregowana Odczytuje wiele wierszy z jednej kolumny i łączy je w jedną wartość. Funkcje agregujące polegają na:

• Wykonywanie obliczeń na wielu wierszach
• Pojedynczej kolumny tabeli
• I zwrócenie pojedynczej wartości.

Norma ISO definiuje pięć (5) funkcji agregacyjnych, a mianowicie:

1. COUNT
2. SUMA
3. AVG
4. MIN
5. MAX

Do wszystkich pięciu stosuje się jedną zasadę: funkcje agregujące ignorują wartości NULL. COUNT(*) jest jedynym wyjątkiem, którego przyczyny przedstawiamy poniżej.

Dlaczego warto używać funkcji agregujących

Różne poziomy organizacji mają różne wymagania informacyjne. Menedżerowie najwyższego szczebla zazwyczaj interesują się liczbami całkowitymi, a nie pojedynczymi szczegółami.

Funkcje agregujące pozwalają nam łatwo generować podsumowania danych z naszej bazy danych.

Na przykład z naszej bazy danych myflix kierownictwo może wymagać następujących raportów:

• Najmniej wypożyczane filmy.
• Najczęściej wypożyczane filmy.
• Średnia liczba wypożyczeń każdego filmu w miesiącu.

Wszystkie powyższe raporty pochodzą z funkcji agregujących. Przyjrzyjmy się każdemu z nich szczegółowo.

funkcja LICZENIE

Funkcja COUNT zwraca całkowitą liczbę wartości w określonym polu, zarówno w przypadku danych liczbowych, jak i nieliczbowych. Jak każda funkcja agregująca, COUNT(kolumna) wyklucza wartości NULL.

COUNT(*) to specjalny formularz, który zwraca liczbę wszystkich wierszy w tabeli. Zlicza również NULL i duplikaty, ponieważ zlicza wiersze, a nie wartości.

Tabela movierentals zawiera następujące dane:

Załóżmy, że chcemy dowiedzieć się, ile razy film o identyfikatorze 2 został wypożyczony.

SELECT COUNT(`movie_id`) FROM `movierentals` WHERE `movie_id` = 2;

Wykonanie tego w MySQL Workbench w przypadku myflixdb zwracana jest wartość 3, ponieważ trzy wiersze zawierają movie_id o wartości 2.

WYRÓŻNIONE słowo kluczowe

COUNT odpowiada „ile”. Następne pytanie zazwyczaj brzmi „ile różne „jedynki”, i do tego właśnie służy DISTINCT.

Słowo kluczowe DISTINCT pomija duplikaty w naszych wynikach według grupping identyczne wartości razem, dokładnie tak jak sugeruje powyższa ilustracja.

Najpierw wykonajmy proste zapytanie.

SELECT `movie_id` FROM `movierentals`;

Teraz to samo zapytanie ze słowem kluczowym DISTINCT:

SELECT DISTINCT `movie_id` FROM `movierentals`;

DISTINCT pomija duplikaty rekordów:

COUNT vs COUNT(*) vs COUNT(DISTINCT): Której opcji powinieneś użyć?

Można również umieścić DISTINCT wewnątrz funkcja agregacyjna i to właśnie tutaj większość początkujących przegrywa track wierszy, z których wiersze są faktycznie liczone. Wszystkie cztery poniższe formularze działają na tej samej pięciowierszowej tabeli movierentals, pokazanej wcześniej, ale nie wszystkie zwracają tę samą liczbę. Różnica sprowadza się do dwóch pytań: czy formularz zlicza wiersze, czy wartości, i czy zachowuje duplikaty?

SELECT COUNT(*) AS `all_rows`,
       COUNT(`return_date`) AS `returned_rows`,
       COUNT(DISTINCT `movie_id`) AS `unique_movies`
FROM `movierentals`;

💡 Wskazówka: Użyj COUNT(*) do zliczania wierszy, COUNT(kolumna), gdy NULL oznacza „nie dotyczy”, oraz COUNT(DISTINCT kolumna) dla wartości unikatowych. Przeciwieństwem DISTINCT jest ALL — wartość domyślna, dlatego rzadko zapisywana.

Funkcja MIN

Funkcja MIN zwraca najmniejszą wartość w określonym polu tabeli.

Załóżmy, że chcemy poznać rok premiery najstarszego filmu w naszej bibliotece. MySQLFunkcja MIN daje nam to.

SELECT MIN(`year_released`) FROM `movies`;

Wynik:

Funkcja MAX

Jak sama nazwa wskazuje, funkcja MAX jest przeciwieństwem funkcji MIN. To zwraca największą wartość z określonego pola tabeli.

Załóżmy, że chcemy poznać rok premiery najnowszego filmu w naszej bazie danych. Poniższy przykład zwraca ten rok.

SELECT MAX(`year_released`) FROM `movies`;

Wynik:

Funkcja SUM

MIN i MAX pobierają istniejącą wartość z kolumny. SUMA i AVG obliczyć nową liczbę z całej kolumny.

Załóżmy, że chcemy poznać całkowitą kwotę dotychczas dokonanych płatności. MySQL SUMA funkcjonować zwraca sumę wszystkich wartości w określonej kolumnie. Funkcja SUM działa tylko na polach numerycznych, Wartości NULL są wykluczane z wyniku.

Poniższa tabela przedstawia dane zawarte w tabeli płatności.

Poniższe zapytanie pobiera wszystkie dokonane płatności i sumuje je w pojedynczy wynik: 2500 + 2000 + 6000 = 10500.

SELECT SUM(`amount_paid`) FROM `payments`;

Wynik:

AVG funkcjonować

MySQL AVG funkcjonować zwraca średnią wartości w określonej kolumnie. Podobnie jak funkcja SUMA działa tylko na numerycznych typach danych.

Załóżmy, że chcemy znaleźć średnią kwotę płatności. Możemy użyć następującego zapytania, które dzieli sumę 10500 przez trzy wiersze płatności różne od NULL.

SELECT AVG(`amount_paid`) FROM `payments`;

Wynik:

⚠️ Ostrzeżenie: AVG Dzieli przez liczbę wierszy innych niż NULL, a nie przez liczbę wierszy w tabeli. Wartość NULL jest pomijana, a nie liczona jako zero, co dyskretnie podnosi średnią. Użyj AVG(IFNULL(`amount_paid`, 0)), gdy brakująca wartość oznacza zero.

Przykład praktyczny: łączenie funkcji agregujących z GROUP BY

Każda z powyższych funkcji zwróciła jedną wartość dla całej tabeli. Dodanie GRUPUJ WEDŁUG klauzula zwraca jedną liczbę na grupę zamiast tego — i tak właśnie powstają prawdziwe raporty.

Poniższy przykład grupuje członków według nazwy, a następnie zlicza łączną liczbę płatności, średnią kwotę płatności i całkowitą sumę kwot płatności dla każdego członka.

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 MySQL Workbench daje nam następujące wyniki.

Zapytanie łączy dwie tabele w klauzuli WHERE — w starszym stylu łączenia przecinkami. Współczesny kod zapisuje tę samą logikę, co jawny kod. WEWNĘTRZNE POŁĄCZENIE … WŁĄCZONE. Należy również pamiętać, że każda niezagregowana kolumna na liście SELECT musi znajdować się w GROUP BY lub MySQL Wersje 5.7 i nowsze odrzucają zapytanie w ramach ONLY_FULL_GROUP_BY. Zobacz urzędnik MySQL odwołanie do funkcji agregującej.

FAQ