Guide pratique : Analyse de l'évolution d'un paramètre avec SQL Window Functions

<fonction>() OVER (
  [PARTITION BY colonne1, colonne2, ...]
  [ORDER BY colonne]
  [ROWS BETWEEN ... AND ...]
)

SELECT
    ville,
    date,
    montant,
    SUM(montant) OVER (
        PARTITION BY ville 
        ORDER BY date
    ) AS cumul_ventes
FROM ventes
ORDER BY ville, date;

SELECT
    ville,
    date,
    montant,
    LAG(montant) OVER (
        PARTITION BY ville 
        ORDER BY date
    ) AS montant_precedent,
    montant - LAG(montant) OVER (
        PARTITION BY ville 
        ORDER BY date
    ) AS variation
FROM ventes
ORDER BY ville, date;

SELECT
    ville,
    date,
    montant,
    LAG(montant) OVER (PARTITION BY ville ORDER BY date) AS montant_precedent,
    ROUND(
        (montant - LAG(montant) OVER (PARTITION BY ville ORDER BY date)) 
        / NULLIF(LAG(montant) OVER (PARTITION BY ville ORDER BY date), 0) * 100, 
        2
    ) AS variation_pct
FROM ventes;

SELECT
    ville,
    date,
    montant,
    AVG(montant) OVER (PARTITION BY ville) AS moyenne_ville
FROM ventes;

SELECT
    ville,
    date,
    montant,
    MIN(montant) OVER (PARTITION BY ville) AS ventes_min_ville,
    MAX(montant) OVER (PARTITION BY ville) AS ventes_max_ville
FROM ventes;

SELECT
    ville,
    date,
    montant,
    AVG(montant) OVER (
        PARTITION BY ville 
        ORDER BY date 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moyenne_mobile_3j
FROM ventes
ORDER BY ville, date;

SELECT
    ville,
    date,
    montant,
    ROW_NUMBER() OVER (PARTITION BY ville ORDER BY date) AS rang_chrono
FROM ventes;

SELECT
    ville,
    date,
    montant,
    RANK() OVER (PARTITION BY ville ORDER BY montant DESC) AS classement_ventes
FROM ventes;