Analytische Berichte mit PL/pgSQL erstellen

Analytische Berichte sind systematisierte Datenansichten, die bei Entscheidungen helfen. Zum Beispiel:

• Manager wollen sehen, wie hoch der Umsatz im letzten Monat war.
• Analysten suchen nach Trends auf dem Markt.
• Entwickler überwachen die Performance der Anwendung.

Stell dir vor, du bist ein Chefkoch, der ein riesiges Restaurant leitet. Um zu verstehen, welche Gerichte am häufigsten bestellt werden, brauchst du einen Bericht. PostgreSQL ist in diesem Fall deine Datenbank für Rezepte und Bestellungen, und PL/pgSQL (Prozeduren) ist dein Küchenhelfer, der den Analyseprozess der Bestellungen automatisiert.

Grundlagen der Erstellung analytischer Berichte

Ein analytischer Bericht ist ein Tool zum Aggregieren, Filtern, Sortieren und Ordnen von Daten, um nützliche Infos zu bekommen. Normalerweise besteht der Aufbau eines Berichts aus folgenden Schritten:

1. Datenvorbereitung: Auswahl von Infos aus Tabellen, Filtern und Vorverarbeitung.
2. Datenaggregation: Berechnung von Metriken (durchschnittlicher Warenkorb, Gesamtsumme der Verkäufe usw.).
3. Formatierung: Daten in eine gut lesbare Form bringen.
4. Ausgabe der Ergebnisse: Bericht den Usern zeigen oder in einer Tabelle speichern.

Jeder dieser Schritte kann mit PL/pgSQL-Prozeduren umgesetzt werden.

Prozedur für einen analytischen Bericht erstellen

Schauen wir uns ein einfaches Beispiel für einen analytischen Bericht an. Angenommen, wir haben eine Tabelle orders, in der Bestelldaten gespeichert sind:

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

Unsere Aufgabe: Einen Bericht über die Verkaufssumme für einen bestimmten Monat erstellen. Also wollen wir sehen:

• Monat.
• Gesamtsumme der Verkäufe für diesen Monat.

Aufbau der Prozedur

Hier ist unser Plan für die Prozedur (keine Angst, PL/pgSQL-Programmierung ist harmlos):

1. Einen Eingabeparameter entgegennehmen – den Monat.
2. Daten für diesen Monat aus der Tabelle orders auswählen.
3. Gesamtsumme der Verkäufe berechnen.
4. Das Ergebnis zurückgeben.

Implementierung der Prozedur

Beispielcode:

CREATE OR REPLACE FUNCTION monthly_sales_report(p_month DATE)
RETURNS TABLE (
    month DATE,
    total_sales NUMERIC(10, 2)
) AS $$
BEGIN
    -- Daten für den angegebenen Monat auswählen und aggregieren
    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. Eingabeparameter: p_month – ein Datum. Damit filtern wir die Daten nach Monat.
2. RETURN QUERY: Das ist die magische Sache, mit der du Daten direkt aus der Prozedur zurückgeben kannst.
3. DATE_TRUNC: Wird genutzt, um order_date auf den Monatsanfang zu runden.
4. SUM: Aggregatfunktion, um die Summe aller Bestellungen zu berechnen.
5. GROUP BY: Gruppiert die Daten nach Monat, weil die Berichte monatlich sind.

Jetzt können wir unsere Funktion aufrufen:

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

Und wir bekommen sowas wie:

Diese Funktion ist die Basis. Lass uns das Ganze schwieriger machen!

Einen komplexeren Bericht erstellen

Jetzt stell dir vor, wir wollen die Verkäufe nach Kunden aufschlüsseln. Unser Bericht soll also zeigen:

• Kunde
• Monat
• Summe der Bestellungen dieses Kunden im Monat

Wir ändern die Prozedur

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;

Jetzt rufen wir die Prozedur so auf:

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

Und das Ergebnis könnte so aussehen:

Temporäre Tabellen verwenden

Manchmal ist es beim Erstellen komplexer Berichte praktisch, temporäre Tabellen zu nutzen. Zum Beispiel, wenn du Zwischenergebnisse verarbeiten willst.

CREATE OR REPLACE FUNCTION temp_table_example(p_month DATE)
RETURNS VOID AS $$
BEGIN
    -- Temporäre Tabelle erstellen
    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);

    -- Zusätzliche Berechnungen oder Manipulationen mit dieser Tabelle machen
    -- Zum Beispiel die Top-3-Kunden nach Bestellsumme anzeigen
    RAISE NOTICE 'Top-3 Kunden für Monat %:', p_month;
    FOR record IN
        SELECT customer_id, total_sales
        FROM temp_sales
        ORDER BY total_sales DESC
        LIMIT 3
    LOOP
        RAISE NOTICE 'Kunde: %, Summe: %', record.customer_id, record.total_sales;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

In diesem Fall wird die temporäre Tabelle temp_sales genutzt, um Zwischenergebnisse zu speichern.

Nützliche Tipps

1. Optimierung: Nutze Indizes, um Datenabfragen zu beschleunigen.
2. Division-durch-Null-Fehler: Prüfe immer den Divisor, damit dein Bericht nicht "abstürzt".
3. Datumsformatierung: Nutze Funktionen wie TO_CHAR für eine schöne Ausgabe.

Ich hoffe, du bist nicht zu sehr eingeschlafen! Es kommen noch schwierigere und spannendere Aufgaben, also bleib dran!

1

Umfrage/Quiz

Prozeduren für Analytics, Level 59, Lektion 4

Nicht verfügbar

Prozeduren für Analytics

Prozeduren für Analytics

Öffnen