Les fonctions de fenêtrage SQL (over partition by)

Il y a peu de temps, je me suis retrouvé bloqué devant un problème SQL que je n’arrivais pas à résoudre. Je devais calculer un maximum, calcul agrégé donc, et à la fois renvoyer des données non agrégées qui dépendaient de ce max. Dans cet article je vais essayer d’expliquer comment résoudre ce problème à l’aide d’opérateurs SQL oubliés : les fonctions de fenêtrage. Basiquement, la fonction de fenetrâge sert à partitionner un ensemble, à effectuer des calculs juste sur un sous-groupe, et non sur un groupe entier comme le fait la clause GROUP BY.

Le problème (simplifié)

Imaginons une table qui regroupe tous les matchs de foot joués depuis la nuit des temps (enfin depuis la création de la FIFA). Dans cette table sont stockées quatre informations : MatchId, EventId, TeamId et SeasonId (qui est l’année de l’évènement). Appelons cette table Event_Team_Match.

On veut récupérer, pour une équipe donnée, tous les matchs de la dernière saison de tous les événements auxquels elle a participée. La dernière saison dépend donc de l’évènement. Par exemple, bien que la Hongrie (TeamId = 42) n’a pas participé à la dernière saison de la coupe du Monde de Football, on veut les matchs de la dernière fois où elle y a joué (à savoir 1986). On veut donc filtrer par Team et regrouper par Event, calculer le max de l’année et renvoyer les matchs correspondant.

Premier essai

La première idée qui vient à l’esprit est de faire la requête en deux fois : on calcule le max pour chaque event puis on récupère les matchs. Pour ce faire, on utilise une table temporaire :

SELECT DISTINCT    etm.MatchId, etm.SeasonId, etm.EventId
FROM EVENT_TEAM_MACTH etm
        INNER JOIN (
                         SELECT EventId, MAX(SeasonId) as MaxSeasonId
                         FROM EVENT_TEAM_MATCH
                         WHERE TeamId = 42
                         GROUP BY EventId
                       ) t
             ON (etm.EventId = t.EventId AND etm.SeasonId = t.MaxSeasonId)
WHERE TeamId = 42

Ca marche mais c’est quand même pas si lisible que ça, on parcourt 2 fois la table et surtout il y a un opérateur pour faire ce que l’on veut : OVER PARTITION BY

Les fonctions de fenêtrage

Les opérateurs de fenêtrage sont méconnus mais font partie de la norme SQL depuis 2003. Ils permettent de définir la fenêtre des données à utiliser pour faire des calculs d’aggrégat (SUM, AVG, MAX, MIN…) ou de ranking (RANK, ROW NUMBER…). Ca tombe bien car c’est exactement ce que l’on veut faire ici : calculer la saison max en fonction de l’event et de la team sans laisser en route les matchs.

En pratique il faut que la fonction d’agrégation soit suivie du mot clef OVER puis des précisions nécessaires pour déterminer la partition (mot clef PARTITION BY). Cela permet de s’affranchir du GROUP BY de la proc précédente qui est trop restrictif (il nous empêche de garder les MatchId).

Dans notre cas, ça donne :

SELECT DISTINCT MatchId, SeasonId, EventId, MAX(SeasonId) OVER(PARTITION BY EventId) AS 'MaxSeasonId'
FROM EVENT_TEAM_MATCH
WHERE TeamId = 42

Cette requête renvoie tous les matchs auxquels l’équipe d’Id 42 a participé, accompagnés de leur saison et événement ainsi que de la saison max jouée par l’équipe dans cet événement. Il ne reste plus qu’à prendre les bonnes lignes pour avoir notre requête finale :

SELECT MatchId, SeasonId, EventId
FROM
(
      SELECT DISTINCT
              MatchId, SeasonId, EventId,
              MAX(SeasonId) OVER(PARTITION BY EventId) AS 'MaxSeasonId'
      FROM EVENT_TEAM_MATCH
      WHERE TeamId = 42
) t
WHERE SeasonId = MaxSeasonId

Aller plus loin

© SOAT
Toute reproduction interdite sans autorisation de la société SOAT

Nombre de vue : 4019

COMMENTAIRES 3 commentaires

  1. A. dit :

    Bien que je n’aime pas le foot, merci pour cet exemple on ne peut plus clair de ce mot clef monstrueusement puissant mais si peu connu !

  2. Eric dit :

    En effet, ces fonctions de fenêtrages sont extrêmement utiles et personnellement j’utilise très souvent le ROW_NUMBER() (un compteur tout bête qui compte suivant une partition (ou pas) et un ordre donné). Par exemple, on pourrait réécrire la dernière requête de l’exemple de cette façon :

    SELECT MatchId, SeasonId, EventId
    FROM
    (
    SELECT DISTINCT
    MatchId, SeasonId, EventId,
    ROW_NUMBER() OVER(PARTITION BY EventId ORDER BY SeasonId desc) AS RN
    FROM EVENT_TEAM_MATCH
    WHERE TeamId = 42
    ) t
    WHERE RN = 1

    Et nouveauté, cette fonction qui permet également de faire de la pagination (ex : on veut les enregistrements du 10ème au 20ème en fonction d’un tri donné)

    SELECT *
    FROM
    (
    SELECT ROW_NUMBER() OVER(ORDER BY SeasonId, EventId, MatchId) AS RN, *
    FROM EVENT_TEAM_MATCH
    ) t
    WHERE RN between 10 and 20

    Va être simplifiée dans SQL SERVER 2012 et va pouvoir s’écrire de la façon suivante :

    SELECT *
    FROM EVENT_TEAM_MATCH
    ORDER BY SeasonId, EventId, MatchId
    OFFSET 10 ROWS
    FETCH NEXT 10 ROWS ONLY

    Qui peut être lue : on écarte les 10 premières lignes (OFFSET 10 ROWS) et on prend les 10 suivantes (FETCH NEXT 10 ROWS ONLY)

  3. Guy Talom dit :

    Merci pour le rappel sur cette pépite de SQL server qui en effet reste peu populaire auprès des développeurs….
    L’explication par l’exemple de ton article est très efficace.

AJOUTER UN COMMENTAIRE