Creazione di tabelle temporanee per l'analisi

Le tabelle temporanee in PostgreSQL sono tabelle create per conservare dati temporanei e vengono eliminate automaticamente al termine della sessione o della transazione (dipende dalle impostazioni). Sono super utili per semplificare operazioni complesse e velocizzare l'esecuzione delle query.

Pensa di essere uno chef in un ristorante con piatti da tre stelle. Non ti metteresti certo a occupare un tavolo solo per gli ingredienti di un'insalata, giusto? Ecco, le tabelle temporanee fanno proprio da piano di lavoro temporaneo: elabori, usi, poi spariscono.

Vantaggi:

• Isolamento dei dati: la tabella temporanea è accessibile solo nella sessione o transazione corrente. Nessuno tranne te può toccare il suo contenuto.
• Efficienza: ti permettono di salvare risultati intermedi, evitando di rifare query complesse ogni volta.
• Pulizia: quando hai finito, le tabelle temporanee vengono eliminate in automatico.

Sintassi per creare tabelle temporanee

PostgreSQL offre un modo semplice e comodo per creare tabelle temporanee usando il comando CREATE TEMP TABLE.

Sintassi generale:

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

• TEMP o TEMPORARY — parola chiave che indica che la tabella è temporanea.
• table_name — nome della tabella. Scegli quello che vuoi, ma meglio se ha un senso.
• column_name — nome della colonna.
• data_type — tipo di dato della colonna (INTEGER, TEXT, DATE ecc.).
• constraints — vincoli, tipo NOT NULL, UNIQUE ecc.

Esempio di creazione di una semplice tabella temporanea:

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

• Qui si crea una tabella temporanea per salvare i dati delle vendite.
• Il campo id genera automaticamente un identificatore per ogni riga.
• Il campo amount tiene un valore decimale (tipo 1234.56).

Esempi di utilizzo delle tabelle temporanee

Esempio 1: salvataggio di dati intermedi per l'analisi

Immagina di avere una tabella orders dove sono salvati tutti gli ordini. Devi calcolare il totale delle vendite per categoria di prodotto. Invece di fare una query super complicata, puoi salvare i dati in una tabella temporanea.

Creiamo la tabella temporanea:

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

Riempiamo la tabella temporanea:

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

Ora puoi usare la tabella temporanea, ad esempio per estrarre le top 3 categorie:

SELECT *
FROM temp_category_sales
ORDER BY total_sales DESC
LIMIT 3;

Esempio 2: unione di dati da tabelle diverse

Supponiamo di avere due tabelle: students (info sugli studenti) e enrollments (iscrizioni ai corsi). Devi raccogliere i dati delle iscrizioni in un unico posto.

Creiamo la tabella temporanea:

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

Unione dei dati:

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;

Ora puoi analizzare facilmente questa tabella, ad esempio contare quante iscrizioni ha ogni studente:

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

Tabelle temporanee nelle transazioni

In più, puoi limitare le tabelle temporanee alla durata di una transazione usando la parola chiave ON COMMIT.

Opzioni di ON COMMIT:

• ON COMMIT DELETE ROWS: svuota la tabella temporanea dopo la transazione.
• ON COMMIT PRESERVE ROWS: mantiene i dati dopo la fine della transazione.
• ON COMMIT DROP: elimina la tabella dopo la transazione.

Esempio:

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

In questo caso la tabella temporanea sparisce appena la transazione finisce.

Significato pratico e casi d'uso

Le tabelle temporanee sono perfette nei progetti reali. Vediamo un paio di situazioni:

Casi:

1. Report complessi in azienda: puoi fare calcoli multipli e complicati, salvando i risultati intermedi.
2. Preparazione dati per ETL: le tabelle temporanee sono spesso usate nei processi di estrazione, trasformazione e caricamento dati (ETL).
3. Sviluppo e test delle query: usa le tabelle temporanee per isolare i dati mentre fai il debug delle query.

Per esempio, se stai sviluppando un report sulle vendite, le tabelle temporanee ti aiutano a salvare risultati intermedi, come il totale dei ricavi o i trend per regione, senza toccare la struttura principale del database.

Particolarità ed errori tipici

Lavorare con le tabelle temporanee è quasi sempre una festa, ma ci sono alcune cose da tenere a mente:

• Limitazione dell'accesso: le tabelle temporanee sono visibili solo nella sessione o transazione corrente. Se chiudi la sessione, spariscono.
• Errori con nomi unici: nella stessa sessione non puoi creare una tabella temporanea con un nome già usato da un'altra tabella.
• Perdita di dati: assicurati di aver estratto i dati che ti servono dalla tabella temporanea prima di chiudere la sessione.
• Performance: se la tabella temporanea ha tante righe, può impattare sulla RAM.