Tworzenie raportów analitycznych z PL/pgSQL

Raporty analityczne — to uporządkowane przedstawienia danych, które pomagają podejmować decyzje. Na przykład:

• Managerowie chcą zobaczyć, jaki był przychód w ostatnim miesiącu.
• Analitycy szukają trendów na rynku.
• Developerzy monitorują wydajność aplikacji.

Wyobraź sobie, że jesteś szefem kuchni, który zarządza ogromną restauracją. Żeby ogarnąć, które dania są najczęściej zamawiane, potrzebujesz raportu. PostgreSQL w tym przypadku — to Twoja baza danych przepisów i zamówień, a PL/pgSQL (procedury) — Twój pomocnik na kuchni, który automatyzuje analizę zamówień.

Podstawy tworzenia raportów analitycznych

Raport analityczny — to narzędzie do agregowania, filtrowania, sortowania i układania danych w celu uzyskania przydatnych informacji. Zazwyczaj struktura raportu obejmuje takie etapy:

1. Przygotowanie danych: pobranie info z tabel, filtrowanie i wstępna obróbka.
2. Agregacja danych: liczenie metryk (średni rachunek, suma sprzedaży itd.).
3. Formatowanie: układanie danych w wygodnej do ogarnięcia formie.
4. Wyświetlenie wyników: pokazanie raportu użytkownikom albo zapis do tabeli na później.

Każdy z tych etapów można ogarnąć przy użyciu procedur PL/pgSQL.

Tworzenie procedury do raportu analitycznego

Rozkminimy prosty przykład tworzenia raportu analitycznego. Załóżmy, że mamy tabelę orders, gdzie trzymamy dane o zamówieniach:

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

Nasze zadanie: zrobić raport o sumie sprzedaży za podany miesiąc. Czyli chcemy zobaczyć:

• Miesiąc.
• Całkowitą sumę sprzedaży za ten miesiąc.

Struktura procedury

Oto plan naszej procedurki (spokojnie, programowanie w PL/pgSQL nie gryzie):

1. Przyjmujemy parametr wejściowy — miesiąc.
2. Wybieramy dane za ten miesiąc z tabeli orders.
3. Liczymy sumę sprzedaży.
4. Zwracamy wynik.

Implementacja procedury

Przykład kodu:

CREATE OR REPLACE FUNCTION monthly_sales_report(p_month DATE)
RETURNS TABLE (
    month DATE,
    total_sales NUMERIC(10, 2)
) AS $$
BEGIN
    -- Wybieramy dane za podany miesiąc i je agregujemy
    RETURN QUERY
    SELECT 
        DATE_TRUNC('month', o.order_date) AS month,
        SUM(o.total_amount) AS total_sales
    FROM orders o
    WHERE DATE_TRUNC('month', o.order_date) = DATE_TRUNC('month', p_month)
    GROUP BY 1;
END;
$$ LANGUAGE plpgsql;

1. Parametr wejściowy: p_month — data. Użyjemy go do filtrowania danych po miesiącu.
2. RETURN QUERY: to taka magiczna rzecz, która pozwala zwrócić dane prosto z procedury.
3. DATE_TRUNC: służy do zaokrąglania order_date do początku miesiąca.
4. SUM: funkcja agregująca do liczenia sumy wszystkich zamówień.
5. GROUP BY: grupujemy dane po miesiącu, bo raporty są miesięczne.

Teraz możemy wywołać naszą funkcję:

SELECT * FROM monthly_sales_report('2023-08-01');

I dostaniemy coś takiego:

Ta funkcja to podstawa. To co, robimy trudniej!

Tworzenie bardziej złożonego raportu

Teraz wyobraź sobie, że chcemy rozbić sprzedaż na klientów. Czyli nasz raport powinien pokazywać:

• Klienta
• Miesiąc
• Sumę zamówień tego klienta za miesiąc

Zmieniamy procedurę

CREATE OR REPLACE FUNCTION customer_monthly_report(p_month DATE)
RETURNS TABLE (
    customer_id INT,
    month DATE,
    total_sales NUMERIC(10, 2)
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        o.customer_id,
        DATE_TRUNC('month', o.order_date) AS month,
        SUM(o.total_amount) AS total_sales
    FROM orders o
    WHERE DATE_TRUNC('month', o.order_date) = DATE_TRUNC('month', p_month)
    GROUP BY o.customer_id, DATE_TRUNC('month', o.order_date);
END;
$$ LANGUAGE plpgsql;

Teraz wywołanie procedury:

SELECT * FROM customer_monthly_report('2023-08-01');

I wynik może wyglądać tak:

Używanie tymczasowych tabel

Czasem przy tworzeniu złożonych raportów warto użyć tymczasowych tabel. Na przykład, jeśli trzeba ogarnąć dane pośrednie.

CREATE OR REPLACE FUNCTION temp_table_example(p_month DATE)
RETURNS VOID AS $$
BEGIN
    -- Tworzymy tymczasową tabelę
    CREATE TEMP TABLE temp_sales AS
    SELECT
        customer_id,
        DATE_TRUNC('month', order_date) AS month,
        SUM(total_amount) AS total_sales
    FROM orders
    WHERE DATE_TRUNC('month', order_date) = DATE_TRUNC('month', p_month)
    GROUP BY customer_id, DATE_TRUNC('month', order_date);

    -- Robimy dodatkowe obliczenia lub manipulacje na tej tabeli
    -- Na przykład, pokazujemy top-3 klientów po sumie zamówień
    RAISE NOTICE 'Top-3 klientów za miesiąc %:', p_month;
    FOR record IN
        SELECT customer_id, total_sales
        FROM temp_sales
        ORDER BY total_sales DESC
        LIMIT 3
    LOOP
        RAISE NOTICE 'Klient: %, Suma: %', record.customer_id, record.total_sales;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

W tym przypadku tymczasowa tabela temp_sales służy do trzymania wyników pośrednich.

Przydatne tipy

1. Optymalizacja: używaj indeksów, żeby przyspieszyć pobieranie danych.
2. Błędy z dzieleniem przez zero: zawsze sprawdzaj dzielnik, żeby nie "uwalić" raportu.
3. Formatowanie daty: używaj funkcji typu TO_CHAR dla wygodnego wyświetlania.

Mam nadzieję, że nie musiałeś za dużo ziewać! Przed Tobą jeszcze trudniejsze i ciekawsze zadania, więc nie odpuszczaj!

2

Zadanie

SQL SELF, poziom 59, lekcja 4

Niedostępne

Wyszukiwanie top-3 najlepiej sprzedających się produktów w miesiącu

Wyszukiwanie top-3 najlepiej sprzedających się produktów w miesiącu

Otwórz

1

Ankieta/quiz

Procedury do analityki, poziom 59, lekcja 4

Niedostępny

Procedury do analityki

Procedury do analityki

Otwórz