Moduł 6 – Praktyka: Funkcje agregujące
Czym są funkcje agregujące?
Funkcje agregujące obliczają jedną wartość na podstawie wielu wierszy. Stosuje się je do podsumowań, statystyk i raportów.
| Funkcja | Opis |
|---|---|
COUNT() | Liczy wiersze |
SUM() | Sumuje wartości |
AVG() | Oblicza średnią |
MIN() | Zwraca wartość minimalną |
MAX() | Zwraca wartość maksymalną |
1. COUNT – zliczanie rekordów
-- Ile jest samochodów w bazie?
SELECT COUNT(*) FROM SAMOCHODY;
-- Ile jest pracowników? (z aliasem kolumny)
SELECT COUNT(*) LICZBA FROM PRACOWNICY;
-- Ile jest klientów posiadających kartę kredytową?
SELECT COUNT(*) FROM KLIENCI WHERE NR_KARTY_KREDYT IS NOT NULL;
2. AVG – średnia
-- Jaka jest średnia pensja pracowników?
SELECT AVG(PENSJA) FROM PRACOWNICY;
-- Średnia pensja sprzedawców
SELECT AVG(PENSJA) FROM PRACOWNICY WHERE STANOWISKO = 'sprzedawca';
-- Średni przebieg samochodów
SELECT AVG(PRZEBIEG) FROM SAMOCHODY;
3. MAX i MIN – ekstremalne wartości
-- Który samochód ma największy przebieg?
SELECT MAX(PRZEBIEG) FROM SAMOCHODY;
-- Jaki jest minimalny przebieg?
SELECT MIN(PRZEBIEG) FROM SAMOCHODY;
-- Najwyższa i najniższa pensja
SELECT MAX(PENSJA), MIN(PENSJA) FROM PRACOWNICY;
Wyświetlenie pełnych danych rekordu z min/max
-- Marka, typ i przebieg samochodu z NAJMNIEJSZYM przebiegiem
SELECT MARKA, TYP, PRZEBIEG FROM SAMOCHODY
WHERE PRZEBIEG = (SELECT MIN(PRZEBIEG) FROM SAMOCHODY);
-- Samochód z NAJWIĘKSZYM przebiegiem
SELECT MARKA, TYP, PRZEBIEG FROM SAMOCHODY
WHERE PRZEBIEG = (SELECT MAX(PRZEBIEG) FROM SAMOCHODY);
Zagnieżdżone zapytanie
(SELECT MIN(...) FROM ...)zwraca wartość, która jest użyta jako warunek WHERE.
4. SUM – suma
-- Ile miesięcznie trzeba przeznaczyć na pensje?
SELECT SUM(PENSJA) FROM PRACOWNICY;
-- Suma pensji sprzedawców
SELECT SUM(PENSJA) FROM PRACOWNICY WHERE STANOWISKO = 'sprzedawca';
-- Łączna wartość kaucji
SELECT SUM(KAUCJA) FROM WYPOZYCZENIA;
5. GROUP BY – grupowanie wyników
GROUP BY dzieli wiersze na grupy i stosuje funkcję agregującą do każdej grupy.
-- Suma pensji wg stanowisk
SELECT STANOWISKO, SUM(PENSJA)
FROM PRACOWNICY
GROUP BY STANOWISKO;
-- Suma pensji wg działów (z nazwą działu)
SELECT DZIAL, SUM(PENSJA)
FROM PRACOWNICY
GROUP BY DZIAL;
-- Liczba pracowników w każdym dziale
SELECT DZIAL, COUNT(*) AS LICZBA_PRACOWNIKOW
FROM PRACOWNICY
GROUP BY DZIAL;
-- Średnia pensja wg stanowisk
SELECT STANOWISKO, AVG(PENSJA) AS SREDNIA_PENSJA, COUNT(*) AS LICZBA
FROM PRACOWNICY
GROUP BY STANOWISKO
ORDER BY SREDNIA_PENSJA DESC;
6. HAVING – filtrowanie po agregacji
HAVING działa jak WHERE, ale po wykonaniu grupowania.
-- Działy, w których suma pensji przekracza 3000
SELECT DZIAL, SUM(PENSJA) AS suma
FROM PRACOWNICY
GROUP BY DZIAL
HAVING SUM(PENSJA) > 3000;
-- Stanowiska z więcej niż 2 pracownikami
SELECT STANOWISKO, COUNT(*) AS LICZBA
FROM PRACOWNICY
GROUP BY STANOWISKO
HAVING COUNT(*) > 2;
Różnica:
WHERE– filtruje przed grupowaniem (działa na wierszach)HAVING– filtruje po grupowaniu (działa na grupach)
7. Przykłady złożone z bazy wypożyczalni
-- Ile razy każdy samochód był wypożyczony?
SELECT s.MARKA, s.TYP, COUNT(*) AS LICZBA_WYPOZYCZEN
FROM SAMOCHODY s
JOIN WYPOZYCZENIA w ON s.NR_SAMOCHODU = w.NR_SAMOCHODU
GROUP BY s.NR_SAMOCHODU, s.MARKA, s.TYP
ORDER BY LICZBA_WYPOZYCZEN DESC;
-- Ilu klientów wypożyczało z każdego miasta?
SELECT m.MIASTO, COUNT(DISTINCT w.NR_KLIENTA) AS LICZBA_KLIENTOW
FROM MIEJSCA m
JOIN WYPOZYCZENIA w ON m.NR_MIEJSCA = w.NR_MIEJSCA_WYP
GROUP BY m.MIASTO;
-- Pracownicy i liczba obsłużonych wypożyczeń
SELECT p.IMIE, p.NAZWISKO, COUNT(*) AS OBSLUZONYCH
FROM PRACOWNICY p
JOIN WYPOZYCZENIA w ON p.NR_PRACOWNIKA = w.NR_PRACOW_WYP
GROUP BY p.NR_PRACOWNIKA, p.IMIE, p.NAZWISKO
ORDER BY OBSLUZONYCH DESC;
Kolejność klauzul w SELECT
SELECT -- co wybrać
FROM -- skąd
JOIN -- złączenia
WHERE -- filtr wierszy (przed grupowaniem)
GROUP BY -- grupowanie
HAVING -- filtr grup (po grupowaniu)
ORDER BY -- sortowanie
LIMIT -- ograniczenie liczby wyników
Ćwiczenia
- Wyświetl łączną liczbę wypożyczeń.
- Wyświetl średnią pojemność silnika samochodów wg marki.
- Wyświetl marka i liczbę samochodów danej marki w bazie.
- Wyświetl działy i średnią pensję w każdym dziale, posortowane malejąco po średniej.
- Wyświetl tylko te marki, których średni przebieg przekracza 30000.
Rozwiązania
-- 1
SELECT COUNT(*) AS LICZBA_WYPOZYCZEN FROM WYPOZYCZENIA;
-- 2
SELECT MARKA, AVG(POJ_SILNIKA) AS SREDNIA_POJEMNOSC
FROM SAMOCHODY
GROUP BY MARKA;
-- 3
SELECT MARKA, COUNT(*) AS LICZBA
FROM SAMOCHODY
GROUP BY MARKA
ORDER BY LICZBA DESC;
-- 4
SELECT DZIAL, AVG(PENSJA) AS SREDNIA
FROM PRACOWNICY
GROUP BY DZIAL
ORDER BY SREDNIA DESC;
-- 5
SELECT MARKA, AVG(PRZEBIEG) AS SREDNI_PRZEBIEG
FROM SAMOCHODY
GROUP BY MARKA
HAVING AVG(PRZEBIEG) > 30000;
Poprzedni moduł: Złączenia tabel Następny moduł: Projekt – struktura bazy