Création de tables temporaires pour l’analytics

Les tables temporaires dans PostgreSQL, c’est des tables créées pour stocker des données temporairement et qui sont supprimées automatiquement à la fin de la session ou de la transaction (selon la config). Elles sont super utiles pour simplifier les opérations complexes et accélérer l’exécution des requêtes.

Imagine que tu bosses comme chef dans un resto trois étoiles. Tu ne vas pas garder une table entière juste pour les ingrédients d’une seule salade, non ? Les tables temporaires, c’est comme un plan de travail temporaire : tu prépares, tu utilises, tu ranges.

Avantages :

• Isolation des données : la table temporaire est accessible uniquement dans la session ou la transaction en cours. Personne d’autre ne viendra mettre le bazar dedans.
• Efficacité : elles permettent de sauvegarder des résultats intermédiaires pour éviter de refaire des requêtes complexes.
• Propreté : à la fin du taf, les tables temporaires sont supprimées automatiquement.

Syntaxe pour créer des tables temporaires

PostgreSQL propose un moyen simple et pratique de créer des tables temporaires avec la commande CREATE TEMP TABLE.

Syntaxe générale :

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

• TEMP ou TEMPORARY — mot-clé qui indique que la table est temporaire.
• table_name — nom de la table. Prends ce que tu veux, mais choisis des noms qui ont du sens.
• column_name — nom de la colonne.
• data_type — type de données de la colonne (INTEGER, TEXT, DATE, etc.).
• constraints — contraintes, genre NOT NULL, UNIQUE, etc.

Exemple de création d’une table temporaire simple :

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

• Ici, on crée une table temporaire pour stocker les données de ventes.
• La colonne id génère automatiquement un identifiant pour chaque ligne.
• La colonne amount stocke une valeur décimale (genre 1234.56).

Exemples d’utilisation des tables temporaires

Exemple 1 : sauvegarder des données intermédiaires pour l’analyse

Imagine qu’on a une table orders où sont stockées toutes les commandes. On veut calculer le total des ventes par catégorie de produits. Au lieu de faire une requête compliquée, on peut sauvegarder les données dans une table temporaire.

Créons une table temporaire :

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

On remplit la table temporaire :

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

Maintenant, on peut utiliser la table temporaire, par exemple pour sortir le top 3 des catégories :

SELECT *
FROM temp_category_sales
ORDER BY total_sales DESC
LIMIT 3;

Exemple 2 : fusionner des données de différentes tables

Disons qu’on a deux tables : students (infos sur les étudiants) et enrollments (inscriptions). On veut rassembler les infos d’inscription au même endroit.

Créons une table temporaire :

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

Fusion des données :

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;

Maintenant, on peut facilement analyser cette table, par exemple compter le nombre d’inscriptions pour chaque étudiant :

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

Tables temporaires dans les transactions

En plus, tu peux limiter la durée de vie des tables temporaires à une transaction en utilisant le mot-clé ON COMMIT.

Options pour ON COMMIT :

• ON COMMIT DELETE ROWS : vide la table temporaire à la fin de la transaction.
• ON COMMIT PRESERVE ROWS : garde les données après la fin.
• ON COMMIT DROP : supprime la table à la fin de la transaction.

Exemple :

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

Dans ce cas, la table temporaire disparaît dès que la transaction est terminée.

Utilité pratique et cas d’utilisation

Les tables temporaires sont parfaites pour les projets réels. Voici quelques situations :

Cas :

1. Rapports business complexes : tu peux faire des calculs multiples et compliqués en sauvegardant les résultats intermédiaires.
2. Préparation des données pour ETL : les tables temporaires sont souvent utilisées dans les process d’extraction, transformation et chargement de données (ETL).
3. Développement et test de requêtes : utilise les tables temporaires pour isoler les données quand tu débogues tes requêtes.

Par exemple, si tu développes un rapport de ventes, les tables temporaires t’aident à sauvegarder des résultats intermédiaires, comme le revenu total ou les tendances par région, sans toucher à la structure principale de la base de données.

Particularités et erreurs courantes

Travailler avec les tables temporaires, c’est presque toujours la fête, mais fais gaffe à quelques points :

• Restriction d’accès : les tables temporaires sont visibles seulement dans la session ou la transaction en cours. Si tu fermes la session, les tables disparaissent.
• Problèmes avec les noms uniques : tu ne peux pas créer une table temporaire avec un nom déjà pris par une autre table dans la même session.
• Perte de données : assure-toi d’avoir récupéré les données dont tu as besoin avant de finir la session.
• Performance : si ta table temporaire contient beaucoup de lignes, ça peut bouffer pas mal de RAM.