Erstellen von temporären Tabellen für Analytics

Temporäre Tabellen in PostgreSQL sind Tabellen, die zum temporären Speichern von Daten erstellt werden und nach Beenden der Session oder Transaktion (je nach Einstellung) automatisch gelöscht werden. Sie sind besonders praktisch, um komplexe Operationen zu vereinfachen und Abfragen schneller zu machen.

Stell dir vor, du bist Koch in einem Restaurant mit Drei-Sterne-Gerichten. Du würdest ja auch keinen eigenen Tisch in der Küche nur für die Zutaten eines einzigen Salats reservieren, oder? Genau so funktionieren temporäre Tabellen: Sie sind wie eine temporäre Arbeitsfläche – du bearbeitest, benutzt und räumst sie dann wieder weg.

Vorteile:

• Datenisolation: Die temporäre Tabelle ist nur innerhalb der aktuellen Session oder Transaktion sichtbar. Niemand außer dir kann auf den Inhalt zugreifen.
• Effizienz: Sie ermöglichen es, Zwischenergebnisse zu speichern, damit du komplexe Abfragen nicht mehrfach ausführen musst.
• Sauberkeit: Nach Abschluss der Arbeit werden temporäre Tabellen automatisch gelöscht.

Syntax zum Erstellen temporärer Tabellen

PostgreSQL bietet eine einfache und bequeme Möglichkeit, temporäre Tabellen mit dem Befehl CREATE TEMP TABLE zu erstellen.

Allgemeine Syntax:

CREATE TEMP TABLE table_name (
    column_name1 data_type constraints,
    column_name2 data_type constraints,
    ...
);

• TEMP oder TEMPORARY – Schlüsselwort, das den temporären Charakter der Tabelle angibt.
• table_name – Name der Tabelle. Du kannst einen beliebigen Namen wählen, aber sinnvolle Namen sind besser.
• column_name – Name der Spalte.
• data_type – Datentyp der Spalte (INTEGER, TEXT, DATE usw.).
• constraints – Einschränkungen, zum Beispiel NOT NULL, UNIQUE usw.

Beispiel für das Erstellen einer einfachen temporären Tabelle:

CREATE TEMP TABLE temp_sales (
    id SERIAL PRIMARY KEY,
    product_name TEXT NOT NULL,
    sale_date DATE NOT NULL,
    amount NUMERIC(10, 2) NOT NULL
);

• Hier wird eine temporäre Tabelle zum Speichern von Verkaufsdaten erstellt.
• Das Feld id generiert automatisch eine ID für jede Zeile.
• Das Feld amount speichert einen Wert mit Dezimalstellen (zum Beispiel 1234.56).

Beispiele für die Verwendung temporärer Tabellen

Beispiel 1: Zwischendaten für Analyse speichern

Stell dir vor, wir haben eine Tabelle orders, in der alle Bestellungen gespeichert sind. Wir wollen die Gesamtsumme der Verkäufe nach Produktkategorien bestimmen. Anstatt eine komplexe Abfrage auszuführen, können wir die Daten in einer temporären Tabelle speichern.

Wir erstellen eine temporäre Tabelle:

CREATE TEMP TABLE temp_category_sales (
    category TEXT,
    total_sales NUMERIC(10, 2)
);

Wir füllen die temporäre Tabelle:

INSERT INTO temp_category_sales
SELECT category, SUM(amount)
FROM orders
GROUP BY category;

Jetzt kannst du die temporäre Tabelle nutzen, zum Beispiel um die Top-3-Kategorien zu holen:

SELECT *
FROM temp_category_sales
ORDER BY total_sales DESC
LIMIT 3;

Beispiel 2: Daten aus verschiedenen Tabellen zusammenführen

Angenommen, wir haben zwei Tabellen: students (Infos über Studierende) und enrollments (Einschreibungen). Wir wollen die Einschreibedaten an einem Ort sammeln.

Wir erstellen eine temporäre Tabelle:

CREATE TEMP TABLE temp_student_enrollments (
    student_id INTEGER,
    student_name TEXT,
    course_id INTEGER,
    enrollment_date DATE
);

Daten zusammenführen:

INSERT INTO temp_student_enrollments
SELECT s.id, s.name, e.course_id, e.enrollment_date
FROM students s
JOIN enrollments e ON s.id = e.student_id;

Jetzt kannst du diese Tabelle easy analysieren, zum Beispiel die Anzahl der Einschreibungen pro Student zählen:

SELECT student_name, COUNT(course_id) AS enrollment_count
FROM temp_student_enrollments
GROUP BY student_name
ORDER BY enrollment_count DESC;

Temporäre Tabellen in Transaktionen

Zusätzlich kannst du temporäre Tabellen auf eine Transaktion begrenzen, indem du das Schlüsselwort ON COMMIT angibst.

Optionen für ON COMMIT:

• ON COMMIT DELETE ROWS: Die temporäre Tabelle wird nach Abschluss der Transaktion geleert.
• ON COMMIT PRESERVE ROWS: Die Daten bleiben nach Abschluss erhalten.
• ON COMMIT DROP: Die Tabelle wird nach Abschluss der Transaktion gelöscht.

Beispiel:

CREATE TEMP TABLE temp_analysis (
    category TEXT,
    total_sales NUMERIC(10, 2)
) ON COMMIT DROP;

In diesem Fall verschwindet die temporäre Tabelle, sobald die Transaktion abgeschlossen ist.

Praktische Bedeutung und Use Cases

Temporäre Tabellen sind perfekt für echte Projekte. Hier ein paar Situationen:

Use Cases:

1. Komplexe Reports im Business: Du kannst komplexe, mehrstufige Berechnungen durchführen und Zwischenergebnisse speichern.
2. Datenvorbereitung für ETL: Temporäre Tabellen werden oft beim Extrahieren, Transformieren und Laden von Daten (ETL) genutzt.
3. Entwicklung und Testen von Abfragen: Nutze temporäre Tabellen, um Daten beim Debuggen von Abfragen zu isolieren.

Wenn du zum Beispiel einen Verkaufsreport entwickelst, helfen dir temporäre Tabellen, Zwischenergebnisse wie Gesamteinnahmen oder Trends nach Regionen zu speichern, ohne die Hauptstruktur der Datenbank zu beeinflussen.

Besonderheiten und typische Fehler

Mit temporären Tabellen zu arbeiten ist fast immer ein Fest, aber ein paar Dinge solltest du beachten:

• Zugriffsbeschränkung: Temporäre Tabellen sind nur in der aktuellen Session oder Transaktion sichtbar. Wenn du die Session beendest, sind die Tabellen weg.
• Fehler mit eindeutigen Namen: In einer Session kannst du keine temporäre Tabelle mit einem Namen erstellen, der schon von einer anderen Tabelle belegt ist.
• Datenverlust: Stell sicher, dass du die benötigten Daten aus der temporären Tabelle holst, bevor du die Session beendest.
• Performance: Wenn die temporäre Tabelle viele Zeilen enthält, kann das den RAM-Verbrauch beeinflussen.