Création de rapports analytiques avec PL/pgSQL

Les rapports analytiques, c'est des présentations systématisées de données qui aident à prendre des décisions. Par exemple :

• Les managers veulent voir quel a été le chiffre d'affaires du mois dernier.
• Les analystes cherchent des tendances sur le marché.
• Les devs surveillent la perf de l'appli.

Imagine que t'es un chef cuistot qui gère un resto géant. Pour piger quels plats sont les plus commandés, t'as besoin d'un rapport. PostgreSQL ici, c'est ta base de données de recettes et de commandes, et PL/pgSQL (les procédures), c'est ton assistant en cuisine qui automatise l'analyse des commandes.

Bases de la création de rapports analytiques

Un rapport analytique, c'est un outil pour agréger, filtrer, trier et organiser les données pour en tirer des infos utiles. En général, la structure d'un rapport comprend ces étapes :

1. Préparation des données : sélection des infos depuis les tables, filtrage et prétraitement.
2. Agrégation des données : calcul des métriques (ticket moyen, total des ventes, etc.).
3. Formatage : organisation des données dans un format facile à lire.
4. Affichage des résultats : présentation du rapport aux utilisateurs ou écriture dans une table pour stockage.

Chacune de ces étapes peut être réalisée avec des procédures PL/pgSQL.

Créer une procédure pour un rapport analytique

On va voir un exemple basique de création de rapport analytique. Disons qu'on a une table orders où sont stockées les infos sur les commandes :

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

Notre mission : créer un rapport sur le total des ventes pour un mois donné. Donc, on veut voir :

• Le mois.
• Le total des ventes pour ce mois.

Structure de la procédure

Voilà le plan de notre petite procédure (t'inquiète, le code PL/pgSQL ne mord pas) :

1. On prend un paramètre d'entrée — le mois.
2. On sélectionne les données de ce mois dans la table orders.
3. On calcule le total des ventes.
4. On retourne le résultat.

Implémentation de la procédure

Exemple de code :

CREATE OR REPLACE FUNCTION monthly_sales_report(p_month DATE)
RETURNS TABLE (
    month DATE,
    total_sales NUMERIC(10, 2)
) AS $$
BEGIN
    -- On sélectionne les données pour le mois donné et on les agrège
    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. Paramètre d'entrée : p_month — une date. On va l'utiliser pour filtrer les données par mois.
2. RETURN QUERY : c'est le truc magique qui permet de renvoyer les données direct depuis la procédure.
3. DATE_TRUNC : sert à arrondir order_date au début du mois.
4. SUM : fonction d'agrégation pour calculer le total des commandes.
5. GROUP BY : on groupe les données par mois, vu que les rapports sont mensuels.

Maintenant, on peut appeler notre fonction :

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

Et on aura un truc du genre :

Cette fonction, c'est la base. On va corser un peu !

Créer un rapport plus complexe

Maintenant, imagine qu'on veut ventiler les ventes par client. Donc, notre rapport doit afficher :

• Le client
• Le mois
• Le total des commandes de ce client pour le mois

On modifie la procédure

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;

Maintenant, appelons la procédure :

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

Et le résultat peut ressembler à ça :

Utiliser des tables temporaires

Parfois, pour des rapports complexes, c'est utile d'utiliser des tables temporaires. Par exemple, si tu dois manipuler des données intermédiaires.

CREATE OR REPLACE FUNCTION temp_table_example(p_month DATE)
RETURNS VOID AS $$
BEGIN
    -- On crée une table temporaire
    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);

    -- On fait des calculs ou manips supplémentaires sur cette table
    -- Par exemple, afficher le top 3 des clients par total des commandes
    RAISE NOTICE 'Top-3 des clients pour le mois %:', p_month;
    FOR record IN
        SELECT customer_id, total_sales
        FROM temp_sales
        ORDER BY total_sales DESC
        LIMIT 3
    LOOP
        RAISE NOTICE 'Client : %, Total : %', record.customer_id, record.total_sales;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

Ici, la table temporaire temp_sales sert à stocker les résultats intermédiaires.

Astuces utiles

1. Optimisation : utilise des index pour accélérer les sélections de données.
2. Erreurs de division par zéro : vérifie toujours le diviseur pour ne pas "casser" le rapport.
3. Formatage de date : utilise des fonctions comme TO_CHAR pour un affichage plus sympa.

J'espère que tu t'es pas trop ennuyé ! Des trucs plus corsés et fun arrivent, alors reste concentré !

1

Étude/Quiz

Procédures pour l'analytics, niveau 59, leçon 4

Indisponible

Procédures pour l'analytics

Procédures pour l'analytics

Ouvrir