Exemple : calcul du ticket moyen des commandes sur les 3 derniers mois

Dans cette leçon, on va mater un exemple pratique sympa.

Le ticket moyen, c’est une métrique qui montre combien en moyenne un client dépense pour un achat. C’est une des métriques business clés qui permet de :

• analyser l’évolution du pouvoir d’achat des clients,
• repérer les tendances dans les ventes,
• évaluer l’efficacité des campagnes marketing.

Énoncé du problème

Imagine qu’on a une base de données avec une table orders où sont stockées les commandes. Notre objectif :

1. Calculer le ticket moyen pour les commandes passées sur les trois derniers mois.
2. Automatiser ce calcul avec une procédure.
3. Sauvegarder le résultat dans une table séparée pour l’analyse future.

On étend notre base : structure de la table orders

Pour commencer, assurons-nous qu’on a une table avec les données nécessaires. Voilà à quoi peut ressembler la structure de la table orders :

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

• order_id — identifiant unique de la commande.
• customer_id — client qui a passé la commande.
• order_date — date à laquelle la commande a été passée.
• total_amount — montant total de la commande.

Pour l’exemple, ajoutons quelques lignes dans la table, histoire d’avoir de la matière :

INSERT INTO orders (customer_id, order_date, total_amount)
VALUES
    (1, '2023-07-15', 100.00),
    (2, '2023-08-10', 200.50),
    (3, '2023-09-01', 150.75),
    (1, '2023-09-20', 300.00),
    (4, '2023-09-25', 250.00),
    (5, '2023-10-05', 450.00);

Calcul du ticket moyen à la main

Avant d’automatiser le truc, écrivons une requête de base qui va calculer le ticket moyen sur les 3 derniers mois. On va utiliser la date du jour (CURRENT_DATE) et la fonction AVG() pour le calcul.

SELECT ROUND(AVG(total_amount), 2) AS avg_check
FROM orders
WHERE order_date >= (CURRENT_DATE - INTERVAL '3 months');

Ce qui se passe ici :

• AVG(total_amount) — fonction d’agrégation qui calcule la moyenne de total_amount.
• CURRENT_DATE - INTERVAL '3 months' — sélectionne les commandes passées sur les trois derniers mois.
• ROUND(..., 2) — arrondit le résultat à deux décimales.

Le résultat de la requête ressemblera à ça :

Automatisation avec une procédure

Maintenant, notre mission — créer une procédure qui va faire ce calcul automatiquement et logger le résultat dans une table séparée. D’abord, créons la table pour stocker les logs analytics.

Création de la table log_analytics

CREATE TABLE log_analytics (
    log_id SERIAL PRIMARY KEY,
    log_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    metric_name VARCHAR(50),
    metric_value NUMERIC(10, 2)
);

• log_date — date et heure de l’enregistrement.
• metric_name — nom de la métrique (dans notre cas "averagecheck_3_months").
• metric_value — valeur calculée de la métrique.

Création de la procédure

Maintenant, écrivons une procédure qui :

1. Calcule le ticket moyen sur les trois derniers mois.
2. Sauvegarde le résultat dans la table log_analytics.

CREATE OR REPLACE FUNCTION calculate_average_check()
RETURNS VOID AS $$
DECLARE
    avg_check NUMERIC(10, 2);
BEGIN
    -- Étape 1 : Calcul du ticket moyen
    SELECT ROUND(AVG(total_amount), 2)
    INTO avg_check
    FROM orders
    WHERE order_date >= (CURRENT_DATE - INTERVAL '3 months');

    -- Étape 2 : Logging du résultat
    INSERT INTO log_analytics (metric_name, metric_value)
    VALUES ('average_check_3_months', avg_check);

    -- Affichage d’info pour debug (optionnel)
    RAISE NOTICE 'Ticket moyen : %', avg_check;
END;
$$ LANGUAGE plpgsql;

Maintenant tu peux appeler cette fonction, et elle va automatiquement écrire le résultat dans la table log_analytics :

SELECT calculate_average_check();

Automatisation avec un scheduler

Dans la leçon précédente, on a déjà installé un scheduler. Si tu bosses sous Linux — c’était l’extension pg_cron ; si tu utilises Windows ou macOS — t’as sûrement configuré le lancement via le scheduler système (cron ou Task Scheduler). Maintenant que tout est prêt, branchons notre procédure sur le planning.

Si tu es sous Linux et que tu utilises pg_cron, assure-toi que l’extension est activée dans la bonne base :

CREATE EXTENSION IF NOT EXISTS pg_cron;

(Petit rappel : l’installation de pg_cron et la config du paramètre shared_preload_libraries ont déjà été vues dans la leçon précédente.)

Maintenant, tu peux planifier l’exécution de notre fonction calculate_average_check() — par exemple, tous les jours à minuit :

SELECT cron.schedule(
    'daily_avg_check',
    '0 0 * * *',
    $$ SELECT calculate_average_check(); $$
);

Explications :

• 'daily_avg_check' — nom de la tâche ;
• '0 0 * * *' — expression cron pour lancer à 00:00 chaque jour ;
• la commande dans $$ — c’est le SQL qui sera exécuté.

Si tu es sous Windows ou macOS, pg_cron ne marche pas (sous Windows — pas du tout, sous macOS — faut le compiler à la main). Mais t’as déjà configuré le scheduler système — il reste juste à brancher le fichier SQL.

1. Crée un fichier avec la requête :

   echo "SELECT calculate_average_check();" > /path/to/script.sql
   

2. Utilise psql pour exécuter le fichier selon le planning :

   • Sur Linux/macOS :

       0 0 * * * psql -h localhost -U postgres -d your_database -f /path/to/script.sql
     

     (à ajouter via crontab -e)
   • Dans Windows Task Scheduler :
     • Indique le chemin vers psql.exe.
     • Dans les arguments :
       -U postgres -d your_database -f "C:\path\to\script.sql"

Comme ça, peu importe ton OS, la procédure va tourner automatiquement et enregistrer régulièrement le ticket moyen dans la table log_analytics. Si tu sais pas trop quelle méthode tu utilises, retourne à la leçon précédente — tout y est expliqué pour chaque plateforme.

Vérification et analyse des résultats

Voyons ce qu’on a obtenu. On va interroger la table log_analytics :

SELECT * FROM log_analytics ORDER BY log_date DESC;

Exemple de résultat :

Maintenant, on a un log de tous les calculs du ticket moyen ! Ces données peuvent servir à générer des rapports ou à analyser l’évolution de la métrique dans le temps.

Erreurs fréquentes et comment les éviter

Travailler avec des procédures analytiques pour calculer le ticket moyen peut amener quelques erreurs classiques.

L’une d’elles — oublier de gérer les résultats vides. Si aucune commande n’a été passée sur les trois derniers mois, la fonction AVG() renverra NULL, ce qui peut poser problème au moment du logging. Pour éviter ça, tu peux utiliser COALESCE() :

SELECT ROUND(COALESCE(AVG(total_amount), 0), 2) AS avg_check

Autre erreur — des données incorrectes dans la table orders. Par exemple, des montants négatifs ou des dates invalides. Il est conseillé de vérifier régulièrement les données ou d’ajouter des contraintes au niveau de la base (genre CHECK (total_amount > 0)).

Bravo, maintenant t’as une vraie procédure qui calcule automatiquement le ticket moyen sur les trois derniers mois et sauvegarde le résultat pour l’analyse future. Ce n’est qu’un exemple parmi plein d’autres de ce que PostgreSQL et PL/pgSQL peuvent faire pour automatiser l’analytics. Dans la prochaine leçon, on va continuer avec des scénarios analytiques plus costauds. À bientôt !