Fonctions de fenêtre SQL Server: ordre et cadre explicites

Les fonctions de fenêtre ajoutent un solde cumulé, une valeur précédente ou un classement à chaque ligne sans réduire le résultat à une ligne par groupe. Leur concision peut toutefois masquer une décision métier. Quelle écriture vient avant une autre? Les opérations du même jour partagent-elles un solde? Les lignes masquées par le rapport participent-elles encore au calcul?

Séparez trois notions. PARTITION BY définit les groupes indépendants. ORDER BY dans OVER établit la séquence du calcul. Pour les fonctions compatibles, le cadre détermine les lignes qui contribuent au résultat courant. Le ORDER BY final commande seulement la présentation et ne remplace aucune de ces décisions.

Rendre les égalités visibles

Ce journal contient volontairement deux écritures à la même date.

DECLARE @Ledger table (
    AccountId int, EntryId int, PostedOn date, Amount decimal(12,2)
);
INSERT @Ledger VALUES
(1, 1, '20250101', 100.00),
(1, 2, '20250101', -20.00),
(1, 3, '20250102', 50.00),
(2, 4, '20250101', 7.00);

SELECT AccountId, EntryId, Amount,
    SUM(Amount) OVER (
        PARTITION BY AccountId ORDER BY PostedOn
    ) AS DatePeerTotal,
    SUM(Amount) OVER (
        PARTITION BY AccountId ORDER BY PostedOn, EntryId
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS RunningBalance,
    LAST_VALUE(Amount) OVER (
        PARTITION BY AccountId ORDER BY PostedOn, EntryId
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS FinalEntryAmount
FROM @Ledger
ORDER BY AccountId, PostedOn, EntryId;

Pour le compte 1, DatePeerTotal retourne 80, 80 puis 130. Un agrégat ordonné sans cadre explicite utilise par défaut un cadre RANGE qui inclut les lignes ayant la même valeur de tri. Les deux écritures du 1er janvier voient donc déjà le total de la journée.

RunningBalance retourne 100, 80 puis 130. Le cadre ROWS accumule les écritures selon la séquence unique PostedOn, EntryId. Ajouter uniquement ROWS sans rendre l'ordre déterministe ne suffirait pas à choisir laquelle des écritures liées vient d'abord. Si l'ordre métier diffère de celui des identifiants, stockez une véritable séquence de comptabilisation.

FinalEntryAmount vaut 50 pour chaque ligne du compte 1. Le cadre atteint explicitement la fin de la partition. LAST_VALUE avec un cadre se terminant à la ligne courante renvoie souvent la valeur courante ou celle du dernier pair, plutôt que la dernière valeur du compte.

Le compte 2 est indépendant et donne 7. Oublier PARTITION BY mélangerait les comptes. Un total général correct ne prouve pas que chaque solde est juste.

Choisir ce que le filtre exclut

La requête suivante sélectionne la dernière écriture de chaque compte. L'identifiant décroissant départage les dates égales.

;WITH Ranked AS (
    SELECT AccountId, EntryId, PostedOn, Amount,
        ROW_NUMBER() OVER (
            PARTITION BY AccountId
            ORDER BY PostedOn DESC, EntryId DESC
        ) AS rn
    FROM @Ledger
)
SELECT AccountId, EntryId, PostedOn, Amount
FROM Ranked
WHERE rn = 1
ORDER BY AccountId;

Le filtre sur rn doit être dans la requête extérieure: le résultat de fenêtre n'est pas disponible dans WHERE au même niveau. Cette organisation sert aussi lorsqu'un rapport affiche une période limitée mais doit calculer un solde incluant l'historique antérieur.

Filtrer le journal sur le 2 janvier avant le cumul donne 50 pour le compte 1, au lieu du solde complet de 130. Pour inclure le solde d'ouverture, calculez d'abord sur l'historique nécessaire puis filtrez l'affichage, ou calculez séparément l'ouverture et ajoutez les mouvements de la période. Cette seconde approche peut réduire le travail, mais les deux calculs doivent partager une vue cohérente des données.

LAG désigne la ligne précédente dans l'ordre défini, pas forcément le jour civil précédent. Les dates absentes ne deviennent pas automatiquement des lignes. Une comparaison quotidienne peut nécessiter une agrégation par jour puis une table calendrier représentant les jours sans activité.

Vérifier le coût réel

Un index commençant par les clés de partition puis les clés de tri peut réduire les opérations de tri pour un accès compatible. N'incluez que les colonnes supplémentaires justifiées par le besoin. Plusieurs fenêtres utilisant des ordres incompatibles peuvent malgré tout exiger plusieurs tris.

Examinez les nombres réels de lignes, les tris débordant sur disque, les allocations mémoire et le volume entrant dans les opérateurs de fenêtre. Une petite sortie visible peut nécessiter un grand historique. Un TOP extérieur ne rend pas automatiquement peu coûteux un calcul portant sur toute une partition.

Testez les dates identiques, plusieurs comptes, les montants négatifs, une partition d'une seule ligne et une période affichée commençant après la première écriture. Si Amount accepte NULL, précisez si un montant inconnu peut être ignoré ou constitue une anomalie. La requête doit rendre lisibles les règles qui déterminent chaque résultat.

Références techniques: Microsoft Learn: OVER clause · Microsoft Learn: ROW_NUMBER · Microsoft Learn: LAST_VALUE.