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.

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