Przykład: obliczanie średniego paragonu z zamówień za ostatnie 3 miesiące

W tym wykładzie zobaczymy ciekawy praktyczny przykład.

Średni paragon — to metryka, która pokazuje, ile średnio wydaje klient na jedno zakupy. To jedna z kluczowych metryk biznesowych, która pozwala:

• analizować zmiany siły nabywczej klientów,
• wyłapywać trendy w sprzedaży,
• oceniać skuteczność kampanii marketingowych.

Opis zadania

Wyobraź sobie, że mamy bazę danych z tabelą orders, gdzie trzymamy zamówienia. Nasz cel:

1. Obliczyć średni paragon dla zamówień z ostatnich trzech miesięcy.
2. Zautomatyzować to obliczenie za pomocą procedury.
3. Zapisać wynik w osobnej tabeli do dalszej analizy.

Rozszerzamy naszą bazę danych: struktura tabeli orders

Na początek upewnijmy się, że mamy tabelę z potrzebnymi danymi. Tak może wyglądać struktura tabeli orders:

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
);

• order_id — unikalny identyfikator zamówienia.
• customer_id — klient, który złożył zamówienie.
• order_date — data, kiedy zamówienie zostało złożone.
• total_amount — całkowita kwota zamówienia.

Dla przykładu dodajmy kilka rekordów do tabeli, żeby było na czym pracować:

INSERT INTO orders (customer_id, order_date, total_amount)
VALUES
    (1, '2023-07-15', 100.00),
    (2, '2023-08-10', 200.50),
    (3, '2023-09-01', 150.75),
    (1, '2023-09-20', 300.00),
    (4, '2023-09-25', 250.00),
    (5, '2023-10-05', 450.00);

Obliczanie średniego paragonu ręcznie

Zanim zautomatyzujemy proces, napiszmy podstawowe zapytanie, które policzy średni paragon za ostatnie 3 miesiące. Użyjemy aktualnej daty (CURRENT_DATE) i funkcji AVG() do obliczenia średniej.

SELECT ROUND(AVG(total_amount), 2) AS avg_check
FROM orders
WHERE order_date >= (CURRENT_DATE - INTERVAL '3 months');

Co tu się dzieje:

• AVG(total_amount) — funkcja agregująca, która liczy średnią z total_amount.
• CURRENT_DATE - INTERVAL '3 months' — wybiera zamówienia z ostatnich trzech miesięcy.
• ROUND(..., 2) — zaokrągla wynik do dwóch miejsc po przecinku.

Wynik zapytania będzie wyglądał mniej więcej tak:

Automatyzacja za pomocą procedury

Teraz naszym zadaniem jest stworzyć procedurę, która będzie robić to automatycznie, a wynik logować do osobnej tabeli. Najpierw utworzymy tabelę do przechowywania logów analitycznych.

Tworzenie tabeli log_analytics

CREATE TABLE log_analytics (
    log_id SERIAL PRIMARY KEY,
    log_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    metric_name VARCHAR(50),
    metric_value NUMERIC(10, 2)
);

• log_date — data i godzina wpisu.
• metric_name — nazwa metryki (u nas "averagecheck_3_months").
• metric_value — wyliczona wartość metryki.

Tworzenie procedury

Teraz napiszemy procedurę, która:

1. Liczy średni paragon za ostatnie trzy miesiące.
2. Zapisuje wynik do tabeli log_analytics.

CREATE OR REPLACE FUNCTION calculate_average_check()
RETURNS VOID AS $$
DECLARE
    avg_check NUMERIC(10, 2);
BEGIN
    -- Krok 1: Obliczanie średniego paragonu
    SELECT ROUND(AVG(total_amount), 2)
    INTO avg_check
    FROM orders
    WHERE order_date >= (CURRENT_DATE - INTERVAL '3 months');

    -- Krok 2: Logowanie wyniku
    INSERT INTO log_analytics (metric_name, metric_value)
    VALUES ('average_check_3_months', avg_check);

    -- Wyświetlenie informacji do debugowania (opcjonalnie)
    RAISE NOTICE 'Średni paragon: %', avg_check;
END;
$$ LANGUAGE plpgsql;

Teraz możesz wywołać tę funkcję i ona automatycznie zapisze wynik do tabeli log_analytics:

SELECT calculate_average_check();

Automatyzacja przez scheduler zadań

W poprzednim wykładzie już instalowaliśmy scheduler zadań. Jeśli pracujesz na Linuxie — to było rozszerzenie pg_cron; jeśli używasz Windowsa lub macOS — pewnie ustawiłeś uruchamianie przez systemowy scheduler (cron lub Task Scheduler). Teraz, gdy wszystko gotowe, podłączmy naszą procedurę do harmonogramu.

Jeśli jesteś na Linuxie i używasz pg_cron upewnij się, że rozszerzenie jest aktywne w odpowiedniej bazie danych:

CREATE EXTENSION IF NOT EXISTS pg_cron;

(Przypominamy: instalacja samego pg_cron i ustawienie parametru shared_preload_libraries były już omówione na poprzednich zajęciach.)

Teraz możesz zaplanować wywołanie naszej funkcji calculate_average_check() — np. codziennie o północy:

SELECT cron.schedule(
    'daily_avg_check',
    '0 0 * * *',
    $$ SELECT calculate_average_check(); $$
);

Wyjaśnienie:

• 'daily_avg_check' — nazwa zadania;
• '0 0 * * *' — wyrażenie cron do uruchamiania o 00:00 codziennie;
• komenda wewnątrz $$ — SQL, który zostanie wykonany.

Jeśli jesteś na Windowsie lub macOS, pg_cron na tych systemach nie działa (na Windowsie — w ogóle, na macOS — wymaga ręcznej kompilacji). Ale już ustawiłeś systemowy scheduler — wystarczy podpiąć plik SQL.

1. Stwórz plik z zapytaniem:

   echo "SELECT calculate_average_check();" > /path/to/script.sql
   

2. Użyj psql do wykonania pliku według harmonogramu:

   • Na Linux/macOS:

       0 0 * * * psql -h localhost -U postgres -d your_database -f /path/to/script.sql
     

     (dodajesz przez crontab -e)
   • W Windows Task Scheduler:
     • Podaj ścieżkę do psql.exe.
     • W argumentach:
       -U postgres -d your_database -f "C:\path\to\script.sql"

Dzięki temu, niezależnie od systemu, procedura będzie wykonywana automatycznie i regularnie zapisywać średni paragon do tabeli log_analytics. Jeśli nie jesteś pewien, którego sposobu używasz, wróć do poprzedniego wykładu — tam opisaliśmy instalację i konfigurację schedulerów dla różnych platform.

Sprawdzanie i analiza wyników

Zobaczmy, co nam wyszło. Pobierzmy dane z tabeli log_analytics:

SELECT * FROM log_analytics ORDER BY log_date DESC;

Przykładowy wynik:

Teraz mamy log wszystkich obliczeń średniego paragonu! Te dane możesz wykorzystać do generowania raportów albo analizy zmian metryki w czasie.

Typowe błędy i jak ich unikać

Praca z procedurami analitycznymi do obliczania średniego paragonu może wiązać się z kilkoma typowymi błędami.

Jeden z nich — zapomnieć o pustych wynikach. Jeśli przez ostatnie trzy miesiące nie było zamówień, funkcja AVG() zwróci NULL, co może prowadzić do problemów przy logowaniu. Żeby tego uniknąć, można użyć COALESCE():

SELECT ROUND(COALESCE(AVG(total_amount), 0), 2) AS avg_check

Kolejny błąd — niepoprawne dane w tabeli orders. Na przykład, ujemne kwoty zamówień albo nieprawidłowe daty. Zaleca się regularnie sprawdzać dane lub dodać ograniczenia na poziomie bazy (np. CHECK (total_amount > 0)).

Gratulacje, masz już pełną procedurę, która automatycznie liczy średni paragon za ostatnie trzy miesiące i zapisuje wynik do dalszej analizy. To tylko jeden z wielu przykładów, jak PostgreSQL i PL/pgSQL mogą pomóc zautomatyzować zadania analityczne. W następnym wykładzie przejdziemy do bardziej zaawansowanych scenariuszy analitycznych. Do zobaczenia!