# Window functions

Les window functions (ou "fonctions de fenêtrage") permettent d'effectuer un calcul sur plusieurs lignes liées à la ligne courante dans un jeu de résultats. C'est comparable aux opérations effectuées avec les fonctions d'agrégation (COUNT(), AVG(), etc).
A la différence des fonctions d'agrégation, les lignes ne seront pas automatiquement regroupées, le calcul se fait "en coulisse" !

Une window function est déclarée par la clause OVER dans laquelle on utilise généralement une clause PARTITION BY pour indiquer la valeur par laquelle regrouper les lignes. Dans certains cas on peut également ajouter une clause ORDER BY si l'ordre des lignes est important dans votre calcul.

Partons d'une table qui liste des emprunts dans une médiathèque:

CREATE TABLE emprunt_mediatheque (
    id INT NOT NULL,
    media VARCHAR(100) NOT NULL,
    utilisateur VARCHAR(20) NOT NULL,
    date_emprunt DATE NOT NULL,
    PRIMARY KEY (id)
);

Pour simplifier l'exemple on ne va pas normaliser nos données et n'avoir aucune clé étrangère, les noms des médias et des utilisateur seront directement inscrits dans la table:

INSERT INTO emprunt_mediatheque (id, media, utilisateur, date_emprunt) VALUES
(1, 'Victor Hugo - Les Misérables', 'Margot', '2019-12-17'),
(2, 'Jurassic Park', 'Julie', '2019-12-18'),
(3, 'Victor Hugo - Les Misérables', 'Antoine', '2019-12-19'),
(4, 'Antoine de Saint-Exupéry - Le Petit Prince', 'Aymeric', '2019-12-19'),
(5, 'Walter One - Face To Face', 'Aymeric', '2019-12-19'),
(6, 'The Beatles - Abbey Road', 'Julie', '2020-03-20'),
(7, 'Star Wars: Episode IV', 'Julie', '2020-03-20'),
(8, 'The Beatles - Abbey Road', 'Antoine', '2020-06-13'),
(9, 'Victor Hugo - Les Misérables', 'Julie', '2020-06-15');

On va afficher le contenu de la table qui fait office d'historique des emprunts dans la médiathèque, en rajoutant 2 colonnes:

• numero_emprunt_media: est-ce qu'il s'agit de la 1e, 2e, 3e... fois que ce média est emprunté
• numero_emprunt_utilisateur: est-ce qu'il s'agit de la 1e, 2e, 3e... fois que cet utilisateur effectue un emprunt

Pour cela, nous allons utiliser la fonction ROW_NUMBER() qui numérotera chaque ligne de manière croissante. En utilisant OVER avec PARTITION BY, on pourra indiquer de regrouper les lignes par media ou utilisateur, et ainsi numéroter à l'intérieur de chaque groupe le 1er, 2e, 3e... emprunt, par media ou utilisateur. L'utilisation de la clause ORDER BY dans la window function sera utile pour s'assurer que la numérotation se fera chronologiquement selon la date d'emprunt:

SELECT *
    , ROW_NUMBER() OVER (PARTITION BY media ORDER BY date_emprunt ASC) AS numero_emprunt_media
    , ROW_NUMBER() OVER (PARTITION BY utilisateur ORDER BY date_emprunt ASC) AS numero_emprunt_utilisateur
FROM emprunt_mediatheque
ORDER BY date_emprunt ASC, id ASC;

Si on regarde la dernière ligne (identifiant 9), on peut observer que le numéro d'emprunt de média est à 3. PARTITION BY se base ici sur la colonne media, elle a donc fait une agrégation par cette colonne, créant un groupe contitué des emprunts 1, 3 et 9. ORDER BY a permis de faire un tri chronologique sur date_emprunt, tri qui a été respecté par ROW_NUMBER() pour que l'emprunt 1 soit le 1er emprunt de ce livre, 3 le 2e et 9 le 3e.

Un tel calcul sans window function aurait pu demander des jointures plus complexes ou des solutions peu performantes. À noter que d'autres fonctions autres que ROW_NUMBER() peuvent être utilisées, mais des fonctions similaires telles que RANK() ou DENSE_RANK() n'auraient pas été appropriés dans cet exemple.