Logging von analytischen Daten in separate Tabellen

Stell dir vor, du erstellst einen Wochenbericht über Verkäufe. Die Berechnungen sind gemacht, die Kunden sind happy. Aber einen Monat später wirst du gefragt: "Kannst du zeigen, was damals im Bericht stand?" Wenn du die Daten nicht vorher gespeichert hast, musst du sie entweder mühsam rekonstruieren oder sagen: "Geht nicht." Das ist nicht nur unpraktisch, sondern kann auch deinem Ruf schaden.

Das Logging von analytischen Daten löst mehrere wichtige Aufgaben:

• Historie speichern: Du hältst wichtige Metriken (zum Beispiel Umsatz, Anzahl der Bestellungen) für bestimmte Zeiträume fest.
• Audit und Diagnose: Wenn mal was schiefgeht, kannst du immer nachschauen, welche Daten damals geloggt wurden.
• Daten vergleichen: Mit Zeitstempeln kannst du Veränderungen der Kennzahlen über die Zeit analysieren.
• Daten wiederverwenden: Gespeicherte Metriken kannst du für andere analytische Aufgaben nutzen.

Grundidee: Die Tabelle log_analytics

Für das Logging von analytischen Daten legen wir eine spezielle Tabelle an, die alle wichtigen Kennzahlen speichert. Jedes neue Ergebnis ist eine neue Zeile in der Tabelle. Um das besser zu verstehen, starten wir mit einem einfachen Szenario.

Beispiel für den Tabellenaufbau

In der Tabelle log_analytics speichern wir Daten zu Berichten. Hier ist die Struktur (DDL — Data Definition Language):

CREATE TABLE log_analytics (
    log_id SERIAL PRIMARY KEY, -- Einzigartige ID des Eintrags
    report_name TEXT NOT NULL, -- Name des Berichts oder der Metrik
    report_date DATE DEFAULT CURRENT_DATE, -- Datum, auf das sich der Bericht bezieht
    category TEXT, -- Kategorie der Daten (z.B. Region, Produkt)
    metric_value NUMERIC NOT NULL, -- Wert der Metrik
    created_at TIMESTAMP DEFAULT NOW() -- Datum und Uhrzeit des Loggings
);

• log_id: Hauptidentifikator des Eintrags.
• report_name: Name des Berichts oder der Metrik, zum Beispiel "Weekly Sales".
• report_date: Datum, auf das sich die Metrik bezieht. Wenn es z.B. die Verkäufe vom 1. Oktober sind, steht hier 2023-10-01.
• category: Hilft, Daten zu gruppieren, z.B. nach Regionen.
• metric_value: Zahlenwert für die Berichtmetrik.
• created_at: Zeitstempel des Loggings.

Beispiel für das Einfügen von Daten in log_analytics

Angenommen, wir haben den Umsatz für Oktober in der Region "Nord" berechnet. Wie speichern wir diesen Wert?

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

Ergebnis:

Erstellen einer Prozedur fürs Logging

Klar, wir wollen die Daten nicht jede Woche oder jeden Monat von Hand eintragen. Deshalb automatisieren wir das Ganze mit einer Prozedur.

Lass uns eine einfache Prozedur zum Loggen von Umsätzen bauen:

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;

Jetzt nimmt die Prozedur log_monthly_revenue zwei Parameter entgegen:

• category: Kategorie der Daten, z.B. Region.
• revenue: Umsatzwert

So rufst du die Funktion auf, um den Umsatz zu speichern:

SELECT log_monthly_revenue('Nord', 15000.75);

Das Ergebnis ist das gleiche wie beim Einfügen mit INSERT.

Weitere Ideen für die Log-Struktur

Manchmal gibt es nicht nur eine, sondern gleich mehrere wichtige Metriken. Schauen wir uns an, wie man zusätzliche Kennzahlen wie Bestellanzahl oder Durchschnittsbon berücksichtigt.

Wir erweitern die Tabellenstruktur:

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, -- Speichern von Metriken im JSONB-Format
    created_at TIMESTAMP DEFAULT NOW()
);

Das große neue Feature hier: Wir nutzen den Typ JSONB, um mehrere Metriken in einem Feld zu speichern.

Beispiel für einen Eintrag in die erweiterte Tabelle

Angenommen, wir wollen gleichzeitig drei Metriken speichern: Umsatz, Bestellanzahl und Durchschnittsbon. So sieht der Query aus:

INSERT INTO log_analytics_extended (report_name, category, metric_values)
VALUES (
    'Monthly Revenue',
    'Nord',
    '{"umsatz": 15000.75, "bestellungen": 45, "durchschnittsbon": 333.35}'::jsonb
);

Ergebnis:

Beispiele für die Nutzung der Logs: Umsatzanalyse

Angenommen, wir wollen den Gesamtumsatz aller Regionen im Oktober wissen. Hier der Query:

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

Beispiele für die Nutzung der Logs: Trends nach Regionen

Wir analysieren die Umsatzentwicklung nach Regionen:

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

Typische Fehler und wie man sie vermeidet

Beim Logging von analytischen Daten kann man ein paar Fehler machen. Lass uns die anschauen und wie du sie verhinderst.

• Fehler: Kategorie oder Datum vergessen. Es ist sinnvoll, Default-Werte in der Tabelle zu setzen, z.B. DEFAULT CURRENT_DATE.
• Fehler: Doppelte Einträge. Um Duplikate zu vermeiden, kannst du einen eindeutigen Index hinzufügen:

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

• Fehler: Metrikberechnung mit Division durch Null. Prüfe immer den Divisor! Nutze NULLIF:

  SELECT umsatz / NULLIF(bestellanzahl, 0) AS durchschnittsbon FROM bestellungen;
  

Einsatz in echten Projekten

Das Logging von analytischen Daten ist in vielen Bereichen nützlich:

• Retail: Verfolgung von Umsätzen und Verkäufen nach Produktkategorien.
• Services: Analyse der Server- oder App-Auslastung.
• Finanzen: Kontrolle von Transaktionen und Ausgaben.

Diese Daten helfen dir nicht nur zu erklären, was passiert ist, sondern auch, Entscheidungen auf Basis der Logs zu treffen. Jetzt weißt du, wie du die Historie von analytischen Daten in PostgreSQL festhältst. Nice, es kommt noch mehr cooles Wissen!