Log des données analytiques dans des tables séparées

Imagine, tu fais un rapport sur les ventes de la semaine. Les calculs sont faits, les clients sont contents. Mais un mois plus tard, on te demande : "Tu peux montrer ce qu’il y avait dans ce rapport ?" Si t’as pas gardé les données à l’avance, tu devras soit tout reconstituer à la main, soit répondre "je peux pas". C’est pas juste chiant, ça peut aussi flinguer ta réputation.

Le logging des données analytiques règle plusieurs problèmes importants :

• Sauvegarde de l’historique : tu fixes les métriques clés (genre revenu, nombre de commandes) pour des périodes précises.
• Audit et diagnostic : si un truc cloche, tu peux toujours vérifier quelles données ont été enregistrées.
• Comparaison des données : en ajoutant des timestamps, tu peux analyser l’évolution des indicateurs dans le temps.
• Réutilisation des données : les métriques sauvegardées peuvent servir dans d’autres analyses.

L’idée principale : la table log_analytics

Pour logger les données analytiques, on crée une table spéciale qui va stocker tous les indicateurs clés. Chaque nouveau résultat = une nouvelle ligne dans la table. Pour piger comment ça marche, on commence par un scénario de base.

Exemple de structure de table

Dans la table log_analytics, on va stocker les infos sur les rapports. Voilà la structure (DDL — Data Definition Language) :

CREATE TABLE log_analytics (
    log_id SERIAL PRIMARY KEY, -- Identifiant unique de l’enregistrement
    report_name TEXT NOT NULL, -- Nom du rapport ou de la métrique
    report_date DATE DEFAULT CURRENT_DATE, -- Date à laquelle le rapport se rapporte
    category TEXT, -- Catégorie des données (par exemple, région, produit)
    metric_value NUMERIC NOT NULL, -- Valeur de la métrique
    created_at TIMESTAMP DEFAULT NOW() -- Date et heure du logging
);

• log_id : identifiant principal de la ligne.
• report_name : nom du rapport ou de la métrique, genre "Ventes Hebdo".
• report_date : date à laquelle la métrique se rapporte. Par exemple, si c’est les ventes du 1er octobre, ici ce sera 2023-10-01.
• category : aide à grouper les données, genre par région.
• metric_value : valeur numérique de la métrique du rapport.
• created_at : timestamp du logging.

Exemple d’insertion de données dans log_analytics

Imaginons qu’on a calculé le revenu d’octobre pour la région "Nord". Comment on sauvegarde cette valeur ?

INSERT INTO log_analytics (report_name, report_date, category, metric_value)
VALUES ('Revenu Mensuel', '2023-10-01', 'Nord', 15000.75);

Résultat :

Création d’une procédure pour logger

Évidemment, on va pas insérer les données à la main chaque semaine ou chaque mois. Donc on va automatiser ça avec une procédure.

On va créer une petite fonction pour logger les revenus :

CREATE OR REPLACE FUNCTION log_monthly_revenue(category TEXT, revenue NUMERIC)
RETURNS VOID AS $$
BEGIN
    INSERT INTO log_analytics (report_name, report_date, category, metric_value)
    VALUES ('Revenu Mensuel', CURRENT_DATE, category, revenue);
END;
$$ LANGUAGE plpgsql;

Maintenant la fonction log_monthly_revenue prend deux paramètres :

• category : catégorie des données, genre la région.
• revenue : la valeur du revenu

Voilà comment appeler cette fonction pour enregistrer un revenu :

SELECT log_monthly_revenue('Nord', 15000.75);

Le résultat sera le même que si tu faisais un INSERT.

Idées en plus pour la structure des logs

Parfois, la métrique clé n’est pas unique, mais il y en a plusieurs. Voyons comment prendre en compte des indicateurs en plus, genre nombre de commandes ou panier moyen.

On met à jour la structure de la table :

CREATE TABLE log_analytics_extended (
    log_id SERIAL PRIMARY KEY,
    report_name TEXT NOT NULL,
    report_date DATE DEFAULT CURRENT_DATE,
    category TEXT,
    metric_values JSONB NOT NULL, -- Stockage des métriques au format JSONB
    created_at TIMESTAMP DEFAULT NOW()
);

Ici, la nouveauté c’est l’utilisation du type JSONB pour stocker plusieurs métriques dans un seul champ.

Exemple d’insertion dans la table étendue

Imaginons qu’on veut sauvegarder trois métriques d’un coup : revenu, nombre de commandes et panier moyen. Voilà un exemple de requête :

INSERT INTO log_analytics_extended (report_name, category, metric_values)
VALUES (
    'Revenu Mensuel',
    'Nord',
    '{"revenu": 15000.75, "commandes": 45, "panier_moyen": 333.35}'::jsonb
);

Résultat :

Exemples d’utilisation des logs : analyse des revenus

Supposons qu’on veut connaître le revenu total de toutes les régions pour octobre. Voilà la requête :

SELECT SUM((metric_values->>'revenu')::NUMERIC) AS revenu_total
FROM log_analytics_extended
WHERE report_date BETWEEN '2023-10-01' AND '2023-10-31';

Exemples d’utilisation des logs : tendances par région

On analyse l’évolution du revenu par région :

SELECT category, report_date, (metric_values->>'revenu')::NUMERIC AS revenu
FROM log_analytics_extended
ORDER BY category, report_date;

Gestion des erreurs classiques

Quand tu logges des données analytiques, tu peux faire quelques boulettes. Parlons-en et voyons comment les éviter.

• Erreur : oubli de la catégorie ou de la date. Mets des valeurs par défaut dans la table, genre DEFAULT CURRENT_DATE.
• Erreur : doublons dans les enregistrements. Pour éviter ça, ajoute un index unique :

  CREATE UNIQUE INDEX unique_log_entry
  ON log_analytics (report_name, report_date, category);
  

• Erreur : calcul de métriques avec division par zéro. Vérifie toujours le diviseur ! Utilise NULLIF :

  SELECT revenu / NULLIF(nombre_commandes, 0) AS panier_moyen FROM commandes;
  

Utilisation dans des projets réels

Le logging des données analytiques est utile dans plein de domaines :

• Retail : suivi des revenus et ventes par catégorie de produits.
• Services : analyse de la charge des serveurs ou applis.
• Finance : contrôle des transactions et des dépenses.

Ces données t’aident non seulement à expliquer ce qui s’est passé, mais aussi à prendre des décisions basées sur ce que tu vois dans les logs. Maintenant tu sais comment enregistrer l’historique des données analytiques dans PostgreSQL. Nickel, encore plein de trucs utiles à venir !