Przykłady złożonych zapytań z wieloma CTE

W tej lekcji będziemy robić prawdziwą magię z zapytaniami! Stworzymy kilka przykładów z użyciem wielu CTE, żeby pokazać, jak można je łączyć ze sobą do budowania skomplikowanych i wieloetapowych zapytań. Te przykłady przydadzą się w realnych sytuacjach, szczególnie przy trudnych zadaniach analitycznych.

Przykład 1: Analiza pracy studentów

Wyobraź sobie, że mamy bazę danych uniwersytetu z trzema tabelami:

Tabela students:

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    student_name TEXT NOT NULL
);

Tabela grades:

CREATE TABLE grades (
    grade_id SERIAL PRIMARY KEY,
    student_id INT REFERENCES students(student_id),
    course_id INT NOT NULL,
    grade NUMERIC(3, 1) NOT NULL
);

Tabela courses:

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    course_name TEXT NOT NULL
);

Zadanie: pobrać listę studentów, którzy mają średnią ocenę powyżej 85, z podaniem ich średniej oraz nazw kursów, na które uczęszczają.

Zapytanie:

WITH high_achievers AS (
    -- Szukamy studentów z wysoką średnią oceną
    SELECT 
        student_id, 
        AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 85
),
student_courses AS (
    -- Szukamy kursów, na które zapisany jest każdy student
    SELECT 
        s.student_id, 
        c.course_name
    FROM grades g
    JOIN courses c ON g.course_id = c.course_id
    JOIN students s ON g.student_id = s.student_id
)
-- Łączymy wyniki
SELECT 
    s.student_name, 
    ha.avg_grade, 
    sc.course_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id
JOIN students s ON s.student_id = ha.student_id;

Wyjaśnienie:

1. W pierwszym CTE (high_achievers) liczymy średnią ocenę dla każdego studenta i wybieramy tych, którzy mają powyżej 85.
2. W drugim CTE (student_courses) łączymy studentów z ich kursami.
3. W głównym zapytaniu łączymy dane z obu CTE, żeby dostać listę studentów, ich średnią ocenę i kursy, na które chodzą.

Przykład 2: Raport sprzedaży dla sklepu internetowego

Załóżmy, że pracujemy ze sklepem internetowym i mamy takie tabele:

Tabela orders (zamówienia):

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount NUMERIC(10, 2) NOT NULL
);

Tabela customers (klienci):

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name TEXT NOT NULL
);

Tabela order_items (produkty w zamówieniach):

CREATE TABLE order_items (
    order_item_id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(order_id),
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price NUMERIC(10, 2) NOT NULL
);

Zadanie: zbudować raport pokazujący dla każdego klienta:

• Łączną liczbę zamówień.
• Łączną sumę wszystkich zamówień z ostatniego miesiąca.
• Listę wszystkich unikalnych produktów, które kupił.

Zapytanie:

WITH recent_orders AS (
    -- Wybieramy zamówienia z ostatniego miesiąca
    SELECT 
        order_id, 
        customer_id, 
        total_amount 
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '1 month'
),
customer_summary AS (
    -- Liczymy łączną liczbę zamówień i sumę dla każdego klienta
    SELECT 
        ro.customer_id,
        COUNT(ro.order_id) AS total_orders,
        SUM(ro.total_amount) AS total_spent
    FROM recent_orders ro
    GROUP BY ro.customer_id
),
customer_products AS (
    -- Wybieramy unikalne produkty, które kupił każdy klient
    SELECT DISTINCT
        ro.customer_id,
        oi.product_id
    FROM recent_orders ro
    JOIN order_items oi ON ro.order_id = oi.order_id
)
-- Łączymy wyniki
SELECT 
    c.customer_name,
    cs.total_orders,
    cs.total_spent,
    ARRAY_AGG(cp.product_id) AS purchased_products
FROM customer_summary cs
JOIN customers c ON cs.customer_id = c.customer_id
JOIN customer_products cp ON cp.customer_id = c.customer_id
GROUP BY c.customer_name, cs.total_orders, cs.total_spent;

Wyjaśnienie:

1. W pierwszym CTE (recent_orders) wybieramy zamówienia z ostatniego miesiąca.
2. W drugim CTE (customer_summary) liczymy łączną liczbę zamówień i sumę dla każdego klienta.
3. W trzecim CTE (customer_products) pobieramy unikalne produkty, które kupił każdy klient.
4. W końcowym zapytaniu łączymy dane i używamy ARRAY_AGG(), żeby zrobić listę unikalnych produktów.

Przykład 3: Analiza hierarchii pracowników

Mamy tabelę pracowników:

Tabela employees:

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    employee_name TEXT NOT NULL,
    manager_id INT NULL
);

Zadanie: zbudować hierarchię pracowników, zaczynając od dyrektora generalnego. Pokazać poziom każdego pracownika w hierarchii.

Zapytanie:

WITH RECURSIVE employee_hierarchy AS (
    -- Zaczynamy od pracowników bez managera (dyrektor generalny)
    SELECT 
        employee_id, 
        employee_name, 
        manager_id, 
        1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    -- Dodajemy wszystkich podwładnych aktualnego poziomu
    SELECT 
        e.employee_id,
        e.employee_name,
        e.manager_id,
        eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
-- Wyświetlamy hierarchię
SELECT 
    employee_id, 
    employee_name, 
    manager_id, 
    level
FROM employee_hierarchy
ORDER BY level, employee_id;

1. W zapytaniu rekurencyjnym zaczynamy od dyrektora generalnego (pracownicy bez managera).
2. Na każdym kroku dodajemy podwładnych aktualnego poziomu, zwiększając level o jeden.
3. W głównym zapytaniu wybieramy całą hierarchię, posortowaną po poziomie i ID pracownika.

Przydatne wskazówki i typowe błędy

• Nadmierna ilość CTE: nie używaj ich tam, gdzie wystarczy podzapytanie. CTE czasem mogą spowolnić zapytanie przez materializację danych.
• Nazwy CTE: dawaj sensowne i krótkie nazwy swoim CTE, żeby zapytania były czytelne.
• Kolejność wykonywania: pamiętaj, że CTE wykonują się dokładnie w kolejności, w jakiej są zadeklarowane.
• Grupowanie danych: używaj GROUP BY tylko tam, gdzie to naprawdę potrzebne, żeby nie robić niepotrzebnych operacji.

Wszystkie te przykłady pokazują, jak CTE można używać do podziału złożonych zadań na etapy, poprawiając czytelność i łatwość utrzymania zapytań. Teraz masz narzędzia, żeby rozwiązywać trudne zadania analityczne z PostgreSQL!