On ne pose pas de cookies de suivi nous-mêmes. Les vidéos de cours sont hébergées sur YouTube, qui dépose ses propres cookies dès qu'une vidéo se charge — elles restent disponibles en un clic si vous préférez ne pas les accepter maintenant.
Classer les lignes sans perdre le détail (ROW_NUMBER, RANK) · SQL — Sup2Tech
Classer les lignes sans perdre le détail (ROW_NUMBER, RANK)
0/5
Vidéo
Étape 1 sur 4, Vidéo
Introduction aux window functions en SQL : OVER, ORDER BY et PARTITION BY
Points clés de cette vidéo
Une window function calcule une valeur par ligne (un rang, une somme cumulée…) SANS regrouper les lignes comme le fait GROUP BY — chaque ligne individuelle reste visible
RANK() attribue un rang selon un tri, avec des ex-æquo qui sautent le rang suivant ; ROW_NUMBER() attribue toujours un numéro unique, même en cas d'égalité
Ces fonctions s'utilisent avec OVER (...), vu en détail à la prochaine leçon
Transcription de la vidéo
Salut et bienvenue. Aujourd'hui, on va s'attaquer à un outil SQL qui est franchement un peu magique pour l'analyse de données, les fonctions fenêtres. Si on cherche à vraiment faire passer ses analyses au niveau supérieur, et bien on est au bon endroit. Allez, pour bien comprendre, on va partir d'un défi très concret. Imaginez qu'on a une base de données sur des résultats sportifs. L'objectif, c'est pas juste de trouver les champions, non, c'est de repérer celles et ceux qui défendent leur titre. Donc la question est simple. En apparence, dans une liste de champions, année après année, comment on fait pour savoir qui était le champion en titre ? Autrement dit, qui a réussi l'exploit de gagner la compétition d'avant est celle dont on est en train de parler. Voilà exactement ce qu'on veut obtenir. Regardez, pour savoir que la Lituanie, LTU, était championne en titre en 2004, il faut pouvoir jeter un œil à qui a gagné en 2000. Le nœud du problème, il est là. Il faut que sur la ligne de 2004, on ait accès à l'info de la ligne de 2000, la ligne précédente. Et ça, avec du SQL classique, bon courage, c'est pas si simple. Mais heureusement, il y a une solution et elle est plutôt élégante. Il s'agit d'une catégorie de fonction SQL bien particulière qui ouvre vraiment la porte à des analyses beaucoup plus poussé. Et la voilà la star du jour, la fonction fenêtre. La définition peut paraître un peu abstraite mais elle est super puissante. En gros, c'est une fonction qui fait un calcul en regardant un groupe de lignes autour de la ligne actuelle. L'image à avoir en tête, c'est celle d'une sorte de fenêtre qui se déplace sur notre donnée. Alors attention, point très important, il ne faut surtout pas confondre ça avec un groupe bail. Un groupe bail, ça écrase, ça compresse plusieurs lignes pour n'en faire qu'une seule. La fonction fenêtre, elle est bien plus subtile. Elle fait son calcul mais elle garde toutes les lignes intactes dans le résultat final et ça change absolument tout. Et pour la syntaxe, c'est assez simple à repérer. On a le nom de la fonction et juste après le mot magique over. C'est cette clause over qui dit au moteur SQL ou là attention ici on fait un truc spécial, c'est une fonction fenêtre, pas une simple agrégation. Bon pour commencer en douceur et bien piger le truc, on va voir la fonction fenêtre la plus basique qui soit ronumbering. Son nom est assez transparent hein, elle va simplement numéroter les lignes. Avant de voir R number en action, rappelons-nous de ce qu'on obtient avec une requête classique. On sélectionne nos colonnes et voilà, on a une liste de données un peu en vrac, sans ordre particulier, sans numéotation. C'est brut. Et maintenant, regardez la différence. On ajoute juste row number over entre entre parenthèses et hop, on a cette nouvelle colonne renote chaque ligne. C'est simple comme bonjour. Par contre, petit bémol, pour l'instant l'ordre dans lequel il numérote et bien il est un peu au pif, il n'est pas garanti. OK, donc pour que cette numérotation ait un vrai sens, il faut qu'on lui dise comment numéroter. Il faut prendre les rennes. Et pour ça, on va ajouter quelque chose à l'intérieur de notre clause overing. Un order buy. C'est grâce à ça qu'on va pouvoir dire "OK, numérot-moi les lignes" mais dans cet ordre précis. Et voilà le résultat. On ajoute order desc et tout change. Le numéro 1 est maintenant attribué à la médaille la plus récente de 2012, le numéro 2 à la suivante et ainsi de suite. D'un coup, notre numérotation a un vrai sens, c'est un classement du plus récent au plus ancien. OK, on a compris le principe de base. Maintenant, revenons à notre problème de champion en titre. Pour ça, il nous faut une fonction un peu plus maligne que row number. une fonction qui sait regarder en arrière. Et cet outil, il a un nom, c'est la fonction lag. Et laager comme le décalage en anglais. Sa mission, elle est ultra spécifique aller chercher une valeur dans une ligne précédente. Par exemple, lag champion 1, ça veut dire va me chercher la valeur de la colonne champion sur la ligne juste avant. Et là, magie, on applique ça à notre problème. On fait un lag champion 1 en ordonnant bien par année et regarder la colonne Last Champion. Pour l'an 2000, on récupère bien G, le champion de 1996. Pour 2004, on récupère LTU, le champion de 2000. C'est parfait. Notre problème est résolu. Enfin, presque. Parce que oui, il y a un piège. Qu'est-ce qui se passe si notre table ne contient pas qu'une seule discipline, mais plusieurs ? Le lancer du disque, le triple saut, le 100 m. Là, notre logique actuelle, elle risque de tout mélanger. Il nous manque une dernière pièce au puzzle. Et voilà le problème en action. Regardez bien. Pour la première ligne du triple saut en 2004, la requête nous dit que le champion précédent était l'Allemagne. Mais l'Allemagne, c'était le champion du lancer du disque. Ça n'a aucun sens. La fonction lag a été un peu bête. Elle a juste regardé la ligne physique juste au-dessus sans se rendre compte qu'on avait changé de port. La solution pour éviter ce genre de Melimelo, c'est partition bail. C'est la clause qui va tout régler. Ce qu'elle fait, c'est qu'elle va découper nos données en petit groupe, en partition. Ici, une partition pour le lancer du disque, une autre pour le triple saut et cetera. Et ensuite, la fonction fenêtre va travailler indépendamment à l'intérieur de chaque groupe sans jamais regarder ce qui se passe chez le voisin. Et on arrive donc à la structure complète, la plus puissante d'une fonction fenêtre. On a la fonction elle-même. Puis dans le overing, on a d'abord le partition buy pour créer nos mini tables et ensuite le order buy pour dire dans quel ordre on travaille à l'intérieur de chaque mini table. Et maintenant le résultat final corriger. En ajoutant partition by event, on a forcé SQL à respecter les disciplines. Quand le calcul arrive sur le triple saut, il ne regarde plus ce qui s'est passé avant pour le lancer du disque. Il repart de zéro. Du coup, la première ligne du triple saut a bien un nul comme champion précédent. C'est exactement ce qu'on voulait. Cette fois, c'est la bonne. Problème résolu. Alors, si on devait résumer en quelques mots, overcone, c'est ce qui dit attention fonction fenêtre. Partition buy, ça crée des groupes, ça met des cloisons. Order buy, ça tri les données à l'intérieur de chaque groupe. Et une fonction comme lag, ça permet de naviguer entre les lignes de ce groupe bien définie. Voilà pour ce tour d'horizon des fonctions fenêtres. Finalement, la vraie question, c'est de se demander maintenant qu'on a cet outil pour regarder au-delà d'une simple ligne et comprendre les relations entre les données, quelles sont les tendances cachées, les pépites d'informations qu'on pourrait dénicher dans nos propres jeux de données ?
