Logowanie danych analitycznych do osobnych tabel

Wyobraź sobie, że robisz raport sprzedaży za tydzień. Obliczenia gotowe, klienci zadowoleni. Ale po miesiącu ktoś pyta: "Możesz pokazać, co było w tamtym raporcie?" Jeśli nie zapisałeś tych danych wcześniej, musisz je odtwarzać ręcznie albo odpowiedzieć "nie mogę". To nie tylko niewygodne, ale może też wpłynąć na twoją reputację.

Logowanie danych analitycznych rozwiązuje kilka ważnych problemów:

• Zachowanie historii: rejestrujesz kluczowe metryki (np. przychód, liczba zamówień) dla określonych okresów.
• Audyt i diagnostyka: jeśli coś pójdzie nie tak, zawsze możesz sprawdzić, jakie dane zostały zapisane.
• Porównywanie danych: dodając znaczniki czasu, możesz analizować zmiany wskaźników w czasie.
• Ponowne użycie danych: zapisane metryki możesz wykorzystać w innych zadaniach analitycznych.

Główna idea: tabela log_analytics

Do logowania danych analitycznych tworzymy specjalną tabelę, która przechowuje wszystkie kluczowe wskaźniki. Każdy nowy wynik to nowy wiersz w tabeli. Żeby lepiej zrozumieć, jak to działa, zaczniemy od podstawowego scenariusza.

Przykład struktury tabeli

W tabeli log_analytics będziemy trzymać dane o raportach. Oto struktura (DDL — Data Definition Language):

CREATE TABLE log_analytics (
    log_id SERIAL PRIMARY KEY, -- Unikalny identyfikator rekordu
    report_name TEXT NOT NULL, -- Nazwa raportu lub metryki
    report_date DATE DEFAULT CURRENT_DATE, -- Data, do której odnosi się raport
    category TEXT, -- Kategoria danych (np. region, produkt)
    metric_value NUMERIC NOT NULL, -- Wartość metryki
    created_at TIMESTAMP DEFAULT NOW() -- Data i czas logowania
);

• log_id: główny identyfikator rekordu.
• report_name: nazwa raportu lub metryki, np. "Weekly Sales".
• report_date: data, do której odnosi się metryka. Na przykład, jeśli to sprzedaż za 1 października, tutaj będzie 2023-10-01.
• category: pomaga grupować dane, np. według regionów.
• metric_value: wartość liczbowa dla metryki raportowej.
• created_at: znacznik czasu logowania.

Przykład zapisu danych do log_analytics

Załóżmy, że policzyliśmy przychód za październik dla regionu "Północ". Jak to zapisać?

INSERT INTO log_analytics (report_name, report_date, category, metric_value)
VALUES ('Monthly Revenue', '2023-10-01', 'North', 15000.75);

Wynik:

Tworzenie procedury do logowania

Oczywiście nie będziemy wpisywać danych ręcznie co tydzień czy miesiąc. Dlatego automatyzujemy to za pomocą procedury.

Stwórzmy prostą procedurę do logowania danych o przychodzie:

CREATE OR REPLACE FUNCTION log_monthly_revenue(category TEXT, revenue NUMERIC)
RETURNS VOID AS $$
BEGIN
    INSERT INTO log_analytics (report_name, report_date, category, metric_value)
    VALUES ('Monthly Revenue', CURRENT_DATE, category, revenue);
END;
$$ LANGUAGE plpgsql;

Teraz procedura log_monthly_revenue przyjmuje dwa parametry:

• category: kategoria danych, np. region.
• revenue: wartość przychodu

Tak wywołasz tę funkcję, żeby zapisać przychód:

SELECT log_monthly_revenue('North', 15000.75);

Efekt będzie taki sam, jak przy wstawianiu przez INSERT.

Dodatkowe pomysły na strukturę logów

Czasem kluczowa metryka to nie jedna liczba, ale kilka naraz. Zobaczmy, jak uwzględnić dodatkowe wskaźniki, takie jak liczba zamówień czy średni koszyk.

Aktualizujemy strukturę tabeli:

CREATE TABLE log_analytics_extended (
    log_id SERIAL PRIMARY KEY,
    report_name TEXT NOT NULL,
    report_date DATE DEFAULT CURRENT_DATE,
    category TEXT,
    metric_values JSONB NOT NULL, -- Przechowywanie metryk w formacie JSONB
    created_at TIMESTAMP DEFAULT NOW()
);

Tu ważna nowość — użycie typu JSONB do przechowywania kilku metryk w jednym polu.

Przykład zapisu do rozszerzonej tabeli

Załóżmy, że chcesz zapisać jednocześnie trzy metryki: przychód, liczbę zamówień i średni koszyk. Oto przykład zapytania:

INSERT INTO log_analytics_extended (report_name, category, metric_values)
VALUES (
    'Monthly Revenue',
    'North',
    '{"przychod": 15000.75, "zamowienia": 45, "sredni_koszyk": 333.35}'::jsonb
);

Wynik:

Przykłady użycia logów: analiza przychodów

Załóżmy, że chcesz poznać łączny przychód ze wszystkich regionów za październik. Oto zapytanie:

SELECT SUM((metric_values->>'przychod')::NUMERIC) AS total_revenue
FROM log_analytics_extended
WHERE report_date BETWEEN '2023-10-01' AND '2023-10-31';

Przykłady użycia logów: trendy według regionów

Analizujemy zmiany przychodu według regionów:

SELECT category, report_date, (metric_values->>'przychod')::NUMERIC AS revenue
FROM log_analytics_extended
ORDER BY category, report_date;

Obsługa typowych błędów

Podczas logowania danych analitycznych można popełnić kilka błędów. Omówmy je i sposoby ich unikania.

• Błąd: zapomniano podać kategorię lub datę. Zaleca się ustawiać wartości domyślne w tabeli, np. DEFAULT CURRENT_DATE.
• Błąd: duplikacja rekordów. Żeby tego uniknąć, możesz dodać unikalny indeks:

  CREATE UNIQUE INDEX unique_log_entry
  ON log_analytics (report_name, report_date, category);
  

• Błąd: obliczanie metryk z dzieleniem przez zero. Zawsze sprawdzaj dzielnik! Użyj NULLIF:

  SELECT przychod / NULLIF(liczba_zamowien, 0) AS sredni_koszyk FROM zamowienia;
  

Zastosowanie w prawdziwych projektach

Logowanie danych analitycznych przydaje się w wielu branżach:

• Retail: śledzenie przychodów i sprzedaży według kategorii produktów.
• Serwisy: analiza obciążenia serwerów lub aplikacji.
• Finanse: kontrola transakcji i wydatków.

Te dane pomogą ci nie tylko wyjaśnić, co się wydarzyło, ale też podejmować decyzje na podstawie tego, co widzisz w logach. Teraz już wiesz, jak rejestrować historię danych analitycznych w PostgreSQL. Super, przed tobą jeszcze więcej przydatnej wiedzy!