Beispiel: Durchschnittlicher Warenkorbwert der Bestellungen der letzten 3 Monate berechnen

In dieser Vorlesung schauen wir uns ein spannendes Praxisbeispiel an.

Der durchschnittliche Warenkorbwert ist eine Metrik, die zeigt, wie viel ein Kunde im Schnitt pro Einkauf ausgibt. Das ist eine der wichtigsten Business-Metriken, mit der du:

• die Veränderung der Kaufkraft analysieren kannst,
• Trends im Verkauf erkennst,
• die Effektivität von Marketingkampagnen bewertest.

Aufgabenstellung

Stell dir vor, wir haben eine Datenbank mit einer Tabelle orders, in der die Bestellungen gespeichert sind. Unser Ziel:

1. Den durchschnittlichen Warenkorbwert für Bestellungen der letzten drei Monate berechnen.
2. Diesen Vorgang mit einer Prozedur automatisieren.
3. Das Ergebnis in einer separaten Tabelle für spätere Analysen speichern.

Wir erweitern unsere Datenbank: Struktur der Tabelle orders

Erstmal checken wir, ob wir eine Tabelle mit den nötigen Daten haben. So könnte die Struktur der Tabelle orders aussehen:

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 – eindeutige Bestell-ID.
• customer_id – Kunde, der die Bestellung gemacht hat.
• order_date – Datum, an dem die Bestellung aufgegeben wurde.
• total_amount – Gesamtsumme der Bestellung.

Zum Üben fügen wir ein paar Einträge in die Tabelle ein, damit wir was zum Arbeiten haben:

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

Durchschnittlichen Warenkorbwert manuell berechnen

Bevor wir das Ganze automatisieren, schreiben wir erstmal ein Basic-Query, das den durchschnittlichen Warenkorbwert der letzten 3 Monate berechnet. Wir nutzen das aktuelle Datum (CURRENT_DATE) und die Funktion AVG() für den Durchschnitt.

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

Was passiert hier:

• AVG(total_amount) – Aggregatfunktion, die den Durchschnitt von total_amount berechnet.
• CURRENT_DATE - INTERVAL '3 months' – filtert Bestellungen der letzten drei Monate.
• ROUND(..., 2) – rundet das Ergebnis auf zwei Nachkommastellen.

Das Ergebnis des Queries sieht ungefähr so aus:

Automatisierung mit einer Prozedur

Jetzt ist unser Ziel, eine Prozedur zu bauen, die diese Berechnung automatisch macht und das Ergebnis in einer separaten Tabelle loggt. Erstmal erstellen wir eine Tabelle für die Analytics-Logs.

Tabelle log_analytics erstellen

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 – Datum und Uhrzeit des Eintrags.
• metric_name – Name der Metrik (bei uns "averagecheck_3_months").
• metric_value – berechneter Wert der Metrik.

Prozedur erstellen

Jetzt schreiben wir eine Prozedur, die:

1. Den durchschnittlichen Warenkorbwert der letzten drei Monate berechnet.
2. Das Ergebnis in die Tabelle log_analytics speichert.

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

    -- Schritt 2: Ergebnis loggen
    INSERT INTO log_analytics (metric_name, metric_value)
    VALUES ('average_check_3_months', avg_check);

    -- Debug-Info ausgeben (optional)
    RAISE NOTICE 'Durchschnittlicher Warenkorbwert: %', avg_check;
END;
$$ LANGUAGE plpgsql;

Jetzt kannst du diese Funktion aufrufen und sie schreibt das Ergebnis automatisch in die Tabelle log_analytics:

SELECT calculate_average_check();

Automatisierung mit einem Task-Scheduler

In der letzten Vorlesung haben wir schon einen Task-Scheduler eingerichtet. Wenn du auf Linux arbeitest, war das das pg_cron-Extension; wenn du Windows oder macOS nutzt, hast du wahrscheinlich einen System-Scheduler (cron oder Task Scheduler) eingerichtet. Jetzt, wo alles bereit ist, binden wir unsere Prozedur in den Zeitplan ein.

Wenn du auf Linux bist und pg_cron nutzt, check, dass das Extension in der richtigen Datenbank aktiviert ist:

CREATE EXTENSION IF NOT EXISTS pg_cron;

(Zur Erinnerung: Die Installation von pg_cron und das Setzen von shared_preload_libraries haben wir schon in der letzten Session gemacht.)

Jetzt kannst du das Ausführen unserer Funktion calculate_average_check() planen – zum Beispiel jeden Tag um Mitternacht:

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

Erklärung:

• 'daily_avg_check' – Name des Tasks;
• '0 0 * * *' – cron-Expression für 00:00 Uhr täglich;
• der Befehl in $$ – SQL, das ausgeführt wird.

Wenn du auf Windows oder macOS bist: pg_cron läuft auf diesen Systemen nicht (auf Windows gar nicht, auf macOS nur mit Handarbeit). Aber du hast schon einen System-Scheduler eingerichtet – jetzt musst du nur noch das SQL-File einbinden.

1. Erstelle eine Datei mit dem Query:

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

2. Nutze psql, um die Datei nach Zeitplan auszuführen:

   • Auf Linux/macOS:

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

     (wird mit crontab -e hinzugefügt)
   • Im Windows Task Scheduler:
     • Gib den Pfad zu psql.exe an.
     • In den Argumenten:
       -U postgres -d your_database -f "C:\path\to\script.sql"

So wird – egal auf welchem System – die Prozedur automatisch und regelmäßig ausgeführt und der durchschnittliche Warenkorbwert in der Tabelle log_analytics gespeichert. Wenn du nicht sicher bist, welche Methode du nutzt, schau nochmal in die letzte Vorlesung – da findest du Setup und Konfiguration für verschiedene Plattformen.

Ergebnisse prüfen und analysieren

Schauen wir uns an, was rausgekommen ist. Wir holen uns die Daten aus der Tabelle log_analytics:

SELECT * FROM log_analytics ORDER BY log_date DESC;

Beispiel-Ergebnis:

Jetzt haben wir ein Log aller Berechnungen des durchschnittlichen Warenkorbwerts! Diese Daten kannst du für Reports oder die Analyse von Metrik-Änderungen über die Zeit nutzen.

Typische Fehler und wie du sie vermeidest

Die Arbeit mit Analytics-Prozeduren zur Berechnung des durchschnittlichen Warenkorbwerts kann mit ein paar typischen Fehlern verbunden sein.

Einer davon ist, leere Ergebnisse zu vergessen. Wenn es in den letzten drei Monaten keine Bestellungen gab, gibt die Funktion AVG() NULL zurück – das kann beim Logging zu Problemen führen. Um das zu vermeiden, kannst du COALESCE() nutzen:

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

Ein weiterer Fehler sind fehlerhafte Daten in der Tabelle orders. Zum Beispiel negative Bestellsummen oder ungültige Daten. Es ist sinnvoll, die Daten regelmäßig zu prüfen oder Constraints auf Datenbankebene zu setzen (z.B. CHECK (total_amount > 0)).

Glückwunsch, jetzt hast du eine komplette Prozedur, die automatisch den durchschnittlichen Warenkorbwert der letzten drei Monate berechnet und das Ergebnis für spätere Analysen speichert. Das ist nur eines von vielen Beispielen, wie PostgreSQL und PL/pgSQL dir helfen können, Analytics-Aufgaben zu automatisieren. In der nächsten Vorlesung schauen wir uns noch komplexere Analytics-Szenarien an. Bis dann!