Tworzenie tymczasowych tabel do analityki

Tymczasowe tabele w PostgreSQL to tabele, które są tworzone do tymczasowego przechowywania danych i automatycznie usuwane po zakończeniu sesji lub transakcji (w zależności od ustawień). Są mega przydatne do upraszczania skomplikowanych operacji i przyspieszania zapytań.

Wyobraź sobie, że jesteś kucharzem w restauracji z trzygwiazdkowymi daniami. Przecież nie będziesz trzymać na kuchni osobnego stołu tylko pod składniki jednej sałatki, nie? No właśnie tymczasowe tabele robią za tymczasowy blat roboczy: obrobiłeś, użyłeś, sprzątasz.

Zalety:

• Izolacja danych: tymczasowa tabela jest dostępna tylko w bieżącej sesji lub transakcji. Nikt poza tobą nie namiesza ci w jej zawartości.
• Efektywność: pozwalają zachować wyniki pośrednie, żeby nie odpalać ciągle tych samych ciężkich zapytań.
• Czystość: po zakończeniu pracy tymczasowe tabele znikają automatycznie.

Składnia tworzenia tymczasowych tabel

PostgreSQL daje prosty i wygodny sposób na tworzenie tymczasowych tabel za pomocą komendy CREATE TEMP TABLE.

Ogólna składnia:

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

• TEMP albo TEMPORARY — słowo kluczowe, które mówi, że tabela jest tymczasowa.
• table_name — nazwa tabeli. Możesz wybrać dowolną, ale lepiej, żeby miała sens.
• column_name — nazwa kolumny.
• data_type — typ danych kolumny (INTEGER, TEXT, DATE itd.).
• constraints — ograniczenia, np. NOT NULL, UNIQUE itd.

Przykład tworzenia prostej tymczasowej tabeli:

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

• Tu tworzymy tymczasową tabelę do przechowywania danych o sprzedaży.
• Pole id automatycznie generuje identyfikator dla każdego wiersza.
• Pole amount trzyma wartość z miejscami po przecinku (np. 1234.56).

Przykłady użycia tymczasowych tabel

Przykład 1: zapisanie danych pośrednich do analizy

Wyobraź sobie, że mamy tabelę orders, gdzie są wszystkie zamówienia. Musimy określić sumę sprzedaży według kategorii produktów. Zamiast odpalać ciężkie zapytanie, można wrzucić dane do tymczasowej tabeli.

Tworzymy tymczasową tabelę:

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

Wypełniamy tymczasową tabelę:

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

Teraz można użyć tymczasowej tabeli, np. żeby wyciągnąć top 3 kategorie:

SELECT *
FROM temp_category_sales
ORDER BY total_sales DESC
LIMIT 3;

Przykład 2: łączenie danych z różnych tabel

Załóżmy, że mamy dwie tabele: students (info o studentach) i enrollments (zapisy na kursy). Trzeba zebrać dane o ich zapisach w jednym miejscu.

Tworzymy tymczasową tabelę:

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

Łączenie danych:

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;

Teraz łatwo analizować tę tabelę, np. policzyć liczbę zapisów dla każdego studenta:

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

Tymczasowe tabele w transakcjach

Dodatkowo tymczasowe tabele można ograniczyć do transakcji, używając słowa kluczowego ON COMMIT.

Opcje ON COMMIT:

• ON COMMIT DELETE ROWS: czyści tymczasową tabelę po zakończeniu transakcji.
• ON COMMIT PRESERVE ROWS: zostawia dane po zakończeniu.
• ON COMMIT DROP: usuwa tabelę po zakończeniu transakcji.

Przykład:

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

W tym przypadku tymczasowa tabela zniknie, jak tylko transakcja się skończy.

Praktyczne znaczenie i case'y użycia

Tymczasowe tabele są idealne do prawdziwych projektów. Zobaczmy parę sytuacji:

Case'y:

1. Złożone raporty w biznesie: możesz robić wielokrotne obliczenia, zapisując wyniki pośrednie.
2. Przygotowanie danych do ETL: tymczasowe tabele są często używane podczas ekstrakcji, transformacji i ładowania danych (ETL).
3. Tworzenie i testowanie zapytań: używaj tymczasowych tabel do izolowania danych podczas debugowania zapytań.

Na przykład, jeśli robisz raport sprzedaży, tymczasowe tabele pomogą ci zapisać wyniki pośrednie, takie jak całkowity przychód albo trendy według regionów, bez mieszania w głównej strukturze bazy danych.

Specyfika i typowe błędy

Praca z tymczasowymi tabelami to prawie zawsze przyjemność, ale warto pamiętać o kilku rzeczach:

• Ograniczenie dostępu: tymczasowe tabele są widoczne tylko w bieżącej sesji lub transakcji. Jak skończysz sesję, tabele znikną.
• Błędy z unikalnymi nazwami: w jednej sesji nie możesz stworzyć tymczasowej tabeli o nazwie, która już jest zajęta przez inną tabelę.
• Utrata danych: upewnij się, że wyciągnąłeś potrzebne dane z tymczasowej tabeli zanim skończysz sesję.
• Wydajność: jeśli tymczasowa tabela ma dużo wierszy, może to wpłynąć na zużycie RAM-u.