Grupowanie Danych

Grupowanie danych to jedna z kluczowych operacji, które pozwalają na analizę zestawów danych w oparciu o określone wartości w kolumnach. W SQL, grupowanie realizowane jest za pomocą klauzuli GROUP BY. Pozwala ona zgrupować wiersze, które mają te same wartości w jednej lub kilku kolumnach, i zastosować funkcje agregujące (np. COUNT(), SUM(), AVG(), MIN(), MAX()) do grup danych.

Struktura zapytania z grupowaniem:

SELECT kolumna1, funkcja_agregująca(kolumna2)
FROM tabela
WHERE warunek
GROUP BY kolumna1
HAVING warunek_na_grupie;
  • GROUP BY: Grupuje dane według kolumny lub zestawu kolumn.
  • Funkcje agregujące: Zliczają, sumują, uśredniają, zwracają minimalną lub maksymalną wartość dla każdej grupy.
  • HAVING: Używana do filtrowania wyników na poziomie grupy (podobna do WHERE, ale działa po zgrupowaniu danych).

Przykład:

Grupowanie danych z funkcją COUNT()

SELECT RokStudiow, COUNT(*) AS LiczbaStudentow
FROM Studenci
GROUP BY RokStudiow;

To zapytanie zwraca liczbę studentów na każdym roku studiów. Dane są zgrupowane według kolumny RokStudiow.

Grupowanie danych z funkcją AVG()

SELECT KodKursu, AVG(Ocena) AS SredniaOcena
FROM Oceny
GROUP BY KodKursu;

W tym zapytaniu, średnia ocena dla każdego kursu jest obliczana i grupowana według kolumny KodKursu.

Ważne informacje:

  • Klauzula GROUP BY: Powinna zawierać kolumny, które nie są używane w funkcjach agregujących.
  • Klauzula HAVING: Używana do filtrowania wyników na poziomie grupy (filtrowanie po funkcjach agregujących).
  • Kolumny w SELECT, które nie są funkcjami agregującymi: Muszą być częścią klauzuli GROUP BY.

Funkcje agregujące, które mogą być używane z GROUP BY:

  1. COUNT(): Zlicza liczbę wierszy.
  2. SUM(): Sumuje wartości.
  3. AVG(): Oblicza średnią.
  4. MIN(): Znajduje najmniejszą wartość.
  5. MAX(): Znajduje największą wartość.
CREATE TABLE Pracownicy (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    Imie VARCHAR(50),
    Nazwisko VARCHAR(50),
    Funkcja VARCHAR(50),
    Dzial VARCHAR(50),
    Wynagrodzenie DECIMAL(10,2),
    DataZatrudnienia DATE
);

INSERT INTO Pracownicy (Imie, Nazwisko, Funkcja, Dzial, Wynagrodzenie, DataZatrudnienia)
VALUES 
('Jan', 'Kowalski', 'Specjalista', 'IT', 6000.00, '2018-05-01'),
('Anna', 'Nowak', 'Starszy Specjalista', 'HR', 7000.00, '2019-07-12'),
('Tomasz', 'Wisniewski', 'Specjalista', 'Finanse', 5500.00, '2021-02-20'),
('Marta', 'Zielinska', 'Kierownik', 'IT', 9000.00, '2017-03-14'),
('Piotr', 'Wojcik', 'Asystent', 'HR', 4000.00, '2020-11-30'),
('Kasia', 'Kowalczyk', 'Specjalista', 'Marketing', 6200.00, '2019-01-09'),
('Magda', 'Lewandowska', 'Starszy Specjalista', 'Finanse', 7500.00, '2016-04-05'),
('Karol', 'Dabrowski', 'Kierownik', 'Marketing', 8500.00, '2015-08-23'),
('Ewa', 'Pawlak', 'Asystent', 'IT', 4200.00, '2022-06-15'),
('Rafal', 'Sikorski', 'Specjalista', 'Finanse', 5800.00, '2023-01-02');

CREATE TABLE Studenci (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    Imie VARCHAR(50),
    Nazwisko VARCHAR(50),
    RokStudiow INT
);

INSERT INTO Studenci (Imie, Nazwisko, RokStudiow)
VALUES 
('Jan', 'Kowalski', 1),
('Anna', 'Nowak', 2),
('Tomasz', 'Wisniewski', 3),
('Marta', 'Zielinska', 1),
('Piotr', 'Wojcik', 4),
('Kasia', 'Kowalczyk', 2),
('Magda', 'Lewandowska', 3),
('Karol', 'Dabrowski', 5),
('Ewa', 'Pawlak', 2),
('Rafal', 'Sikorski', 4);

CREATE TABLE Kursy (
    KodKursu VARCHAR(10) PRIMARY KEY,
    NazwaKursu VARCHAR(100),
    LiczbaGodzin INT,
    Wykladowca VARCHAR(50),
    Dzial VARCHAR(50)
);

INSERT INTO Kursy (KodKursu, NazwaKursu, LiczbaGodzin, Wykladowca, Dzial)
VALUES 
('CS101', 'Wprowadzenie do programowania', 30, 'Jan Kowalski', 'IT'),
('CS102', 'Algorytmy i struktury danych', 40, 'Anna Nowak', 'IT'),
('CS103', 'Bazy danych', 45, 'Tomasz Wisniewski', 'IT'),
('CS104', 'Sieci komputerowe', 35, 'Jan Kowalski', 'IT'),
('CS105', 'Programowanie w Java', 50, 'Piotr Wojcik', 'IT'),
('CS106', 'Zaawansowane techniki programowania', 60, 'Jan Kowalski', 'IT'),
('CS107', 'Podstawy systemow operacyjnych', 25, 'Magda Lewandowska', 'E'),
('CS108', 'Zarzadzanie projektami IT', 30, 'Karol Dabrowski', 'E'),
('CS109', 'Programowanie w Python', 40, 'Ewa Pawlak', 'IT'),
('CS110', 'Cyberbezpieczenstwo', 35, 'Piotr Wojcik', 'IT');

CREATE TABLE Oceny (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    StudentID INT,
    KodKursu VARCHAR(10),
    Ocena DECIMAL(3,2),
    FOREIGN KEY (StudentID) REFERENCES Studenci(ID),
    FOREIGN KEY (KodKursu) REFERENCES Kursy(KodKursu)
);

INSERT INTO Oceny (StudentID, KodKursu, Ocena)
VALUES 
(1, 'CS101', 4.5),
(2, 'CS102', 3.0),
(3, 'CS103', 5.0),
(3, 'CS104', 4.0),
(3, 'CS105', 3.5),
(2, 'CS106', 4.2),
(1, 'CS107', 5.0),
(1, 'CS108', 3.8),
(9, 'CS109', 4.9),
(10, 'CS110', 4.0);

CREATE TABLE Zakupy (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    Produkt VARCHAR(100),
    Kategoria VARCHAR(50),
    Cena DECIMAL(10,2),
    DataZakupu DATE
);

INSERT INTO Zakupy (Produkt, Kategoria, Cena, DataZakupu)
VALUES 
('Laptop', 'Elektronika', 4500.00, '2023-01-05'),
('Monitor', 'Elektronika', 1200.00, '2023-02-10'),
('Drukarka', 'Elektronika', 800.00, '2022-12-15'),
('Biurko', 'Meble', 600.00, '2022-10-01'),
('Krzeslo', 'Meble', 400.00, '2022-09-12'),
('Telewizor', 'Elektronika', 3000.00, '2023-03-08'),
('Smartfon', 'Elektronika', 2000.00, '2023-05-20'),
('Klawiatura', 'Akcesoria', 150.00, '2023-04-12'),
('Myszka', 'Akcesoria', 100.00, '2023-06-25'),
('Tablet', 'Elektronika', 2500.00, '2023-07-01');

Ćwiczenia:

Ćwiczenie 1:

  1. Policz liczbę pracowników na każdym stanowisku w tabeli Pracownicy.

Ćwiczenie 2:

  1. Oblicz średnią liczbę godzin kursów dla każdego wykładowcy w tabeli Kursy, ale tylko dla tych wykładowców, którzy mają więcej niż 1 kurs.

  2. Policz liczbę pracowników zatrudnionych po 2019 roku na każdym stanowisku.

Ćwiczenie 3:

  1. Oblicz sumę wynagrodzeń dla każdego działu w tabeli Pracownicy, ale wyświetl tylko działy, w których suma wynagrodzeń przekracza 15 000 zł.

  2. Oblicz liczbę kursów, które mają więcej niż 30 godzin, grupując wyniki według wykładowcy.

Ćwiczenie 4:

  1. Oblicz minimalną liczbę godzin kursów dla każdego działu w tabeli Kursy, ale wyświetl tylko te działy, gdzie minimalna liczba godzin jest większa niż 25.

  2. Policz liczbę studentów w każdym roku studiów, ale pokaż tylko te lata, gdzie liczba studentów jest większa niż 1.

Ćwiczenie 5 (bardziej zaawansowane):

  1. Oblicz maksymalną cenę produktów kupionych po 2023-01-01, grupując wyniki według kategorii produktów.

  2. Oblicz średnią ocenę dla każdego studenta, ale wyświetl tylko tych studentów, którzy mają więcej niż 1 ocenę.

Ćwiczenie 6 (bardziej zaawansowane):

  1. Oblicz sumę liczby godzin dla kursów, które mają więcej niż 30 godzin, grupując wyniki według działu i wykładowcy.