Formation Apprendre et maitriser SQL : Fonction de fenêtrage

Dans ce chapitre je vous propose de découvrir le "fenêtrage" qui permet d'utiliser les fonctions d'aggrégations sur plusieurs lignes.

Pour la suite de cet article nous allons utiliser une base de données répertoriant les ventes d'une entreprise dans différents pays.

CREATE TABLE IF NOT EXISTS sales (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    year INTEGER,
    country VARCHAR(255),
    product VARCHAR(255),
    profit INTEGER
);

INSERT INTO sales (year, country, product, profit)
VALUES
  (2000,'Finland','Computer'  ,  1500),
  (2000,'Finland','Phone'     ,   100),
  (2001,'Finland','Phone'     ,    10),
  (2000,'India'  ,'Calculator',    75),
  (2000,'India'  ,'Calculator',    75),
  (2000,'India'  ,'Computer'  ,  1200),
  (2000,'USA'    ,'Calculator',    75),
  (2000,'USA'    ,'Computer'  ,  1500),
  (2001,'USA'    ,'Calculator',    50),
  (2001,'USA'    ,'Computer'  ,  1500),
  (2001,'USA'    ,'Computer'  ,  1200),
  (2001,'USA'    ,'TV'        ,   150),
  (2001,'USA'    ,'TV'        ,   100);

Par défaut, les fonctions comme SUM() aggrègent les données en ne générant qu'une ligne correspondant au résultat de l'opération.

SELECT *, SUM(profit) as total FROM sales

Avec l'utilisation du fenêtrage nous allons pouvoir récupérer toutes les lignes avec le résultat de l'aggrégation en plus. Pour cela on utilisera la syntaxe <fonction> OVER (PARTITION BY <champs>)

SELECT 
  *,
  SUM(profit) OVER () as total,
  SUM(profit) OVER (PARTITION BY country) as total_country
FROM sales

On peut aussi utiliser des fonctions de fenêtrage pour récupérer des informations sur la partition.

SELECT 
  *,
  SUM(profit) OVER () as total,
  SUM(profit) OVER w as total_country,
  RANK() OVER w as rank
  ROW_NUMBER() OVER w as idx
FROM sales
WINDOW w (PARTITION BY country ORDER BY profit DESC)

On peut aussi utiliser le mot clef WINDOW pour éviter de répéter une partition.

Cas concret

On souhaite récupérer les 5 derniers commentaires des 5 derniers articles.

SELECT * FROM(
    SELECT 
        post_id,
        ROW_NUMBER() OVER (PARTITION BY post_id) as row_number,
        *
    FROM comments
    WHERE post_id IN (
        SELECT id FROM posts ORDER BY id DESC LIMIT 5
    )
) as t
WHERE t.row_number < 6;