PostgreSQL et Window Functions: différences entre row_number(), rank(), dense_rank()

postgresql, sql
Publié le 2017-02-26 | Jérémy Grèze

Les windows functions apportent en SQL la possibilité de calculer des valeurs sur des fenêtres (ou partitions, ou groupes de lignes). Exemples: valeur de la ligne précédente, somme cumulée, moyenne ou médiane sur la fenêtre, etc.

Une option pratique est de pouvoir numéroter les lignes. Il existe 3 fonctions avec des comportements légèrement différents.

Les définitions de la documentation:

Essayons de comprendre avec un exemple. Prenons une table avec des films.

select
    "movie_title",
    "country",
    "title_year",
    "imdb_score"
from "movies";
movie_title country title_year imdb_score
Avatar USA 2009 7.9
Pirates of the Caribbean: At World's End USA 2007 7.1
Spectre UK 2015 6.8
The Dark Knight Rises USA 2012 8.5
Star Wars: Episode VII - The Force Awakens NULL NULL 7.1
John Carter USA 2012 6.6
Spider-Man 3 USA 2007 6.2
Tangled USA 2010 7.8
Avengers: Age of Ultron NULL NULL 7.5
Harry Potter and the Half-Blood Prince UK 2009 7.5
Batman v Superman: Dawn of Justice USA 2016 6.8

Essayons les trois fonctions en calculant la position de chaque film par rapport à la note IMDB (OVER(ORDER BY "imdb_score" DESC)).

select
    row_number() OVER(ORDER BY "imdb_score" DESC) AS "row_number",
    rank() OVER(ORDER BY "imdb_score" DESC) AS "rank",
    dense_rank() OVER(ORDER BY "imdb_score" DESC) AS "dense_rank",
    "movie_title",
    "country",
    "title_year",
    "imdb_score"
from "movies";
row_number rank dense_rank movie_title country title_year imdb_score
1 1 1 The Dark Knight Rises USA 2012 8.5
2 2 2 Avatar USA 2009 7.9
3 3 3 Tangled USA 2010 7.8
4 4 4 Avengers: Age of Ultron NULL NULL 7.5
5 4 4 Harry Potter and the Half-Blood Prince UK 2009 7.5
6 6 5 Pirates of the Caribbean: At World's End USA 2007 7.1
7 6 5 Star Wars: Episode VII - The Force Awakens NULL NULL 7.1
8 8 6 Batman v Superman: Dawn of Justice USA 2016 6.8
9 8 6 Spectre UK 2015 6.8
10 10 7 John Carter USA 2012 6.6
11 11 8 Spider-Man 3 USA 2007 6.2

On constate que des différences apparaissent quand les films ont des notes identiques. Les trois fonctions différent de la façon suivante:

A noter que les valeurs NULL n’influencent pas. Comme pour un SORT BY normal,

De la même façon le partitionnement (PARTITION BY) ne modifient pas le comportement de ces fonctions.

Vous pouvez tester de votre côté sur SQL Fiddle.

Enfin, pour rappel, les window functions sont disponibles sur PostgreSQL, SQL Server, Redshift, Oracle. Mais pas sur MySQL.