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

• Des exemples simples et la syntaxe sont disponibles sur la doc msdn en ligne
• Un exemple détaillé et didactique avec la fonction RANK

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