Avancé

LAG() — accéder à la ligne précédente

La fonction LAG() est une window function qui accède à la valeur d'une ligne précédente dans un ensemble trié. Elle sert aux comparaisons temporelles : évolution mois sur mois, année sur année, ou tout calcul nécessitant une référence à une valeur passée.

Syntaxe de LAG()

sql

LAG(colonne, n, valeur_par_defaut) OVER (
    PARTITION BY colonne_groupe   -- optionnel
    ORDER BY colonne_tri          -- obligatoire
)

Trois paramètres :

Paramètre	Rôle	Par défaut
`colonne`	La colonne dont on veut la valeur précédente	—
`n`	Nombre de lignes en arrière	1
`valeur_par_defaut`	Valeur retournée quand il n'y a pas de ligne précédente	NULL

ORDER BY est obligatoire

LAG() n'a de sens que sur un ensemble trié. Sans ORDER BY dans la clause OVER, le résultat est indéterminé — la « ligne précédente » n'a pas de sens sans ordre défini.

Exemple simple — le mois précédent :

sql

SELECT
    mois,
    chiffre_affaires,
    LAG(chiffre_affaires, 1, 0) OVER (ORDER BY mois) AS ca_mois_precedent
FROM ca_mensuel

Pour la première ligne (pas de mois précédent), LAG() retourne la valeur par défaut : 0.

Que retourne LAG(montant, 1, 0) quand il n'y a pas de ligne précédente ?

Comparaison mois sur mois

Calculer l'évolution du chiffre d'affaires par rapport au mois précédent :

sql

WITH ca_mensuel AS (
    SELECT
        strftime('%Y-%m', date_vente) AS mois,
        SUM(montant) AS chiffre_affaires
    FROM ventes
    GROUP BY strftime('%Y-%m', date_vente)
),
avec_precedent AS (
    SELECT
        mois,
        chiffre_affaires,
        LAG(chiffre_affaires, 1, 0) OVER (ORDER BY mois) AS ca_mois_precedent
    FROM ca_mensuel
)
SELECT
    mois,
    chiffre_affaires,
    ca_mois_precedent,
    chiffre_affaires - ca_mois_precedent AS evolution_absolue,
    CASE
        WHEN ca_mois_precedent = 0 THEN NULL
        ELSE ROUND(
            (chiffre_affaires - ca_mois_precedent) * 100.0 / ca_mois_precedent,
            2
        )
    END AS evolution_pct
FROM avec_precedent
ORDER BY mois

Pourquoi CASE WHEN pour le pourcentage ?

La division par zéro est une erreur SQL. Le premier mois a un ca_mois_precedent de 0 (valeur par défaut). Le CASE WHEN retourne NULL dans ce cas au lieu de provoquer une erreur.

Décomposons le flux :

Chargement du diagramme…

Comparaison année sur année (N vs N-1)

Pour comparer le même mois d'une année à l'autre, on utilise PARTITION BY sur le numéro de mois et ORDER BY sur l'année :

sql

WITH ca_mensuel AS (
    SELECT
        CAST(strftime('%Y', date_vente) AS INTEGER) AS annee,
        CAST(strftime('%m', date_vente) AS INTEGER) AS mois_num,
        strftime('%Y-%m', date_vente) AS mois_label,
        SUM(montant) AS chiffre_affaires
    FROM ventes
    GROUP BY strftime('%Y', date_vente), strftime('%m', date_vente)
),
avec_annee_precedente AS (
    SELECT
        annee,
        mois_num,
        mois_label,
        chiffre_affaires,
        LAG(chiffre_affaires, 1, 0) OVER (
            PARTITION BY mois_num
            ORDER BY annee
        ) AS ca_annee_precedente
    FROM ca_mensuel
)
SELECT
    mois_label,
    chiffre_affaires AS ca_actuel,
    ca_annee_precedente AS ca_n_moins_1,
    chiffre_affaires - ca_annee_precedente AS evolution_absolue,
    CASE
        WHEN ca_annee_precedente = 0 THEN NULL
        ELSE ROUND(
            (chiffre_affaires - ca_annee_precedente) * 100.0 / ca_annee_precedente,
            2
        )
    END AS evolution_pct
FROM avec_annee_precedente
WHERE annee = 2024
ORDER BY mois_num

PARTITION BY mois_num

PARTITION BY mois_num crée une fenêtre par mois calendaire (janvier, février, etc.). Dans chaque fenêtre, ORDER BY annee trie par année. LAG() récupère donc le CA du même mois de l'année précédente — exactement ce qu'il faut pour une comparaison N vs N-1.

Dans une comparaison N vs N-1, pourquoi utilise-t-on PARTITION BY mois_num et ORDER BY annee ?

Intégration repository

python

def get_evolution_mensuelle_avec_comparaison(self, annee=2024):
    """Retourne le CA mensuel avec évolution M-1 et N-1."""
    query = f"""
    WITH ca_mensuel AS (
        SELECT
            strftime('%Y-%m', date_vente) AS mois,
            SUM(montant) AS chiffre_affaires
        FROM ventes
        WHERE strftime('%Y', date_vente) = '{annee}'
        GROUP BY strftime('%Y-%m', date_vente)
    ),
    avec_precedent AS (
        SELECT
            mois,
            chiffre_affaires,
            LAG(chiffre_affaires, 1, 0) OVER (ORDER BY mois) AS ca_mois_precedent
        FROM ca_mensuel
    )
    SELECT
        mois,
        chiffre_affaires,
        ca_mois_precedent,
        chiffre_affaires - ca_mois_precedent AS evolution_absolue,
        CASE
            WHEN ca_mois_precedent = 0 THEN NULL
            ELSE ROUND(
                (chiffre_affaires - ca_mois_precedent) * 100.0
                / ca_mois_precedent, 2
            )
        END AS evolution_pct
    FROM avec_precedent
    ORDER BY mois
    """

    rows = self._execute(query)
    return [
        {
            "mois": row[0],
            "chiffre_affaires": row[1],
            "ca_mois_precedent": row[2],
            "evolution_absolue": row[3],
            "evolution_pct": row[4]
        }
        for row in rows
    ]

Visualisation Streamlit : double graphique

Un layout en deux colonnes : un line chart pour le CA mensuel et un bar chart pour l'évolution en pourcentage.

python

import plotly.express as px
import streamlit as st

data = repo.get_evolution_mensuelle_avec_comparaison(annee=2024)
df = pd.DataFrame(data)

col1, col2 = st.columns(2)

with col1:
    st.subheader("CA mensuel")
    fig_ca = px.line(
        df, x="mois", y="chiffre_affaires",
        markers=True,
        labels={"chiffre_affaires": "CA (€)", "mois": "Mois"}
    )
    st.plotly_chart(fig_ca, use_container_width=True)

with col2:
    st.subheader("Évolution M-1 (%)")
    # Colorer en vert (positif) ou rouge (négatif)
    df["couleur"] = df["evolution_pct"].apply(
        lambda x: "Hausse" if x and x >= 0 else "Baisse"
    )
    fig_evo = px.bar(
        df.dropna(subset=["evolution_pct"]),
        x="mois", y="evolution_pct",
        color="couleur",
        color_discrete_map={"Hausse": "#2ca02c", "Baisse": "#d62728"},
        labels={"evolution_pct": "Évolution (%)", "mois": "Mois"}
    )
    fig_evo.add_hline(y=0, line_dash="solid", line_color="gray")
    st.plotly_chart(fig_evo, use_container_width=True)

Ligne de référence à zéro

Sur le graphique d'évolution, fig.add_hline(y=0) ajoute une ligne horizontale à zéro. Les barres au-dessus sont les mois en hausse, celles en dessous les mois en baisse. Le dropna() exclut le premier mois (pas de mois précédent → NULL).

LEAD() — la fonction symétrique

LEAD() est l'inverse de LAG() : elle accède à la ligne suivante au lieu de la précédente. La syntaxe est identique :

sql

LEAD(colonne, n, valeur_par_defaut) OVER (ORDER BY colonne_tri)

LEAD() sert aux prévisions ou pour comparer une valeur avec ce qui vient après. En BI, LAG() est plus fréquent (on compare au passé, pas au futur).

À retenir

Points clés

LAG(col, n, default) accède à la n-ième ligne précédente dans la fenêtre
ORDER BY est obligatoire dans la clause OVER de LAG()
Comparaison M-1 : LAG() OVER (ORDER BY mois)
Comparaison N-1 : LAG() OVER (PARTITION BY mois_num ORDER BY annee)
Toujours gérer la division par zéro avec CASE WHEN pour les pourcentages d'évolution
Double graphique Streamlit : line chart (valeurs) + bar chart (évolution %) avec st.columns(2)

SQLite Built-in Window Functions

Documentation de LAG(), LEAD() et autres fonctions de fenêtrage SQLite

Documentation