Funkcje agregujące w MySQL

Funkcje agregujące to wszystko

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

Norma ISO definiuje pięć (5) funkcji zbiorczych, a mianowicie;

1) LICZ
2) SUMA
3) AVG
4)MIN
5) MAKS

Dlaczego warto używać funkcji agregujących

Z perspektywy biznesowej, różne poziomy organizacji mają różne wymagania informacyjne. Menadżerowie najwyższego szczebla są zazwyczaj zainteresowani poznaniem całych liczb, a niekoniecznie poszczególnych szczegółów.

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 ciągu miesiąca.

Powyższe raporty z łatwością tworzymy korzystając z funkcji agregujących.

Przyjrzyjmy się szczegółowo funkcjom agregującym.

funkcja LICZENIE

Funkcja COUNT zwraca całkowitą liczbę wartości w określonym polu. Działa zarówno na numerycznych, jak i nienumerycznych typach danych. Wszystkie funkcje agregujące domyślnie wykluczają wartości null przed rozpoczęciem pracy na danych.

COUNT (*) to specjalna implementacja funkcji COUNT, która zwraca liczbę wszystkich wierszy w określonej tabeli. COUNT (*) również bierze pod uwagę Zero i duplikaty.

Poniższa tabela przedstawia dane w tabeli wypożyczeń filmów

numer referencyjny	Data dokonania transakcji	Data powrotu	numer członkostwa	identyfikator_filmu	film_ powrócił
11	20-06-2012	NULL	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	NULL	3	3	0

Załóżmy, że chcemy uzyskać liczbę wypożyczeń filmu o identyfikatorze 2

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

Wykonanie powyższego zapytania w MySQL Workbench wobec myflixdb daje nam następujące wyniki.

COUNT('movie_id')



3

WYRÓŻNIONE słowo kluczowe

Słowo kluczowe DISTINCT, które pozwala nam pominąć duplikaty w naszych wynikach. Osiąga się to poprzez grupowanie 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ębny pomija w wynikach zduplikowane rekordy.

movie_id



1


2


3

Funkcja MIN

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

Załóżmy na przykład, że chcemy poznać rok, w którym ukazał się najstarszy film w naszej bibliotece, możemy użyć MySQLfunkcję MIN, aby uzyskać żądaną informację.

Poniższe zapytanie pomoże nam to osiągnąć

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

Wykonanie powyższego zapytania w MySQL workbench i myflixdb dają 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 określonego pola tabeli.

Załóżmy, że chcemy uzyskać rok premiery najnowszego filmu w naszej bazie danych. Możemy to łatwo osiągnąć za pomocą funkcji MAX.

Poniższy przykład zwraca rok wydania najnowszego filmu.

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

Wykonanie powyższego zapytania w MySQL Workbench używający myflixdb daje nam następujące wyniki.

MAX('year_released')



2012

Funkcja SUM

Załóżmy, że chcemy raportu zawierającego całkowitą kwotę dotychczas dokonanych płatności. Możemy skorzystać z MySQL SUMA funkcja, która zwraca sumę wszystkich wartości w określonej kolumnie. Funkcja SUM działa tylko na polach numerycznych. Wartości null są wykluczane ze zwracanego wyniku.

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

identyfikator płatności	numer członkostwa	termin płatności	opis	opłata zapłacona	zewnętrzny_numer referencyjny
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	NULL

Zapytanie pokazane poniżej 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 MySQL workbench z myflixdb daje następujące wyniki.

SUM('amount_paid')



10500

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ę zapłaconą. Możemy użyć następującego zapytania –

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

Wykonanie powyższego zapytania w MySQL workbench, daje nam następujące wyniki.

AVG('amount_paid')



3500

Ł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 łączną liczbę płatności, średnią kwotę płatności oraz całkowitą sumę 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 MySQL workbench daje nam następujące wyniki.

Podsumowanie

• MySQL obsługuje wszystkie pięć (5) standardowych funkcji agregujących ISO COUNT, SUM, AVG, MIN i MAKS.
• SUMA i AVG funkcje działają tylko na danych numerycznych.
• Jeśli chcesz wykluczyć zduplikowane wartości z wyników funkcji agregującej, użyj słowa kluczowego DISTINCT. Słowo kluczowe ALL uwzględnia nawet duplikaty. Jeśli nic nie jest określone, domyślnie przyjmuje się WSZYSTKO.
• Funkcje agregujące mogą być używane w połączeniu z innymi klauzulami SQL, takimi jak GRUPUJ WEDŁUG