Comparer des périodes quand une période manque

Tu comptes les avis notés 2 par mois. Le résultat passe d'octobre 2016 à janvier 2017. Pourtant, LAG appelle octobre « ligne précédente » de janvier.

Cette fiche travaille l'interprétation de tendances en BC02. Elle utilise Olist pour apprendre le contrôle, puis te demande de le transférer sur un autre jeu.

Observer le trou

WITH par_mois AS (
    SELECT date_trunc('month', o.order_purchase_timestamp)::date AS mois,
           COUNT(*) AS avis_note_2
    FROM order_reviews r
    JOIN orders o ON o.order_id = r.order_id
    WHERE r.review_score = 2
    GROUP BY 1
),
avec_precedent AS (
    SELECT mois, avis_note_2,
           LAG(mois) OVER (ORDER BY mois) AS precedent
    FROM par_mois
)
SELECT mois, avis_note_2, precedent
FROM avec_precedent
WHERE precedent IS NULL
   OR mois <> (precedent + interval '1 month')::date
ORDER BY mois;

Avant d'exécuter, prédis ce que la seconde ligne signalera. Le résultat est :

Novembre et décembre n'ont aucune ligne dans le résultat agrégé. LAG est une fonction de fenêtrage : elle lit la ligne précédente dans l'ordre fourni, pas le mois précédent du calendrier.

Fabriquer les mois attendus

generate_series produit le calendrier. LEFT JOIN garde ses mois même sans observation et COALESCE transforme l'absence de compte en zéro.

WITH par_mois AS (
    SELECT date_trunc('month', o.order_purchase_timestamp)::date AS mois,
           COUNT(*) AS avis_note_2
    FROM order_reviews r
    JOIN orders o ON o.order_id = r.order_id
    WHERE r.review_score = 2
    GROUP BY 1
),
calendrier AS (
    SELECT generate_series(MIN(mois), MAX(mois), interval '1 month')::date AS mois
    FROM par_mois
),
complet AS (
    SELECT c.mois, COALESCE(p.avis_note_2, 0) AS avis_note_2
    FROM calendrier c
    LEFT JOIN par_mois p USING (mois)
)
SELECT mois, avis_note_2,
       LAG(avis_note_2) OVER (ORDER BY mois) AS mois_precedent
FROM complet
ORDER BY mois
LIMIT 6;

Les quatre premières lignes deviennent :

Prédis maintenant ce qui arriverait à un pourcentage d'évolution en janvier : le dénominateur serait zéro. Une comparaison exacte dans le calendrier n'est pas forcément calculable ni interprétable. Garde NULL quand la base de comparaison vaut zéro et explique cette absence.

Modifier puis transférer

Remplace la note 2 par une autre note. Avant d'exécuter, prédis si les mêmes mois manqueront. Vérifie avec la première requête, puis complète le calendrier seulement si la question demande tous les mois.

Sur un autre jeu de données, produis ensuite une série quotidienne ou hebdomadaire. Rends les lignes qui prouvent le trou, puis les mêmes lignes après complétion. Le schémaVocabulaire · schémaLa description des tables d'une base : leurs noms, leurs colonnes, et le type de chaque colonne. de transfert doit être nouveau pour toi : ne recopie pas les noms Olist de cette fiche.