9.22. Fonctions de fenêtrage

» » »

Précédent	Niveau supérieur	Suivant
9.21. Fonctions d'agrégat	Sommaire	9.23. Expressions de sous-requêtes

9.22. Fonctions de fenêtrage #

Les fonctions de fenêtrage fournissent des moyens pour réaliser des calculs sur des ensembles de lignes relatives à la ligne actuelle de la requête. Voir Section 3.5 pour une introduction à cette fonctionnalité, et Section 4.2.8 pour les détails sur la syntaxe.

Les fonctions de fenêtrage natives sont montrées dans Tableau 9.64. Notez que ces fonctions doivent être appelées en utilisant la syntaxe des fonctions de fénêtrage, c'est-à-dire en utilisant une clause OVER.

En plus de ces fonctions, toute fonction d'agrégat standard native ou définie par un utilisateur (donc pas les agrégats à ensemble ordonné ou à ensemble hypothétique) peut être utilisée comme une fonction de fenêtrage ; voir Section 9.21 pour une liste des agrégats natifs. Les fonctions d'agrégat agissent comme des fonctions de fenêtrage quand une clause OVER est utilisée pour l'appel ; sinon elles agissent comme des agrégats standards et renvoient une seule ligne pour un ensemble complet.

Tableau 9.64. Fonctions de fenêtrage à usage général

Fonction Description
`row_number` () → `bigint` Renvoie le nombre de la ligne courante dans sa partition, en commençant à 1.
`rank` () → `bigint` Renvoie le range de la ligne courante, avec des trous ; c'est-à-dire le `row_number` de la première ligne dans son groupe.
`dense_rank` () → `bigint` Renvoie le range de la ligne courante, sans trous ; cette fonction compte réellement les groupes.
`percent_rank` () → `double precision` Renvoie le rang relatif de la ligne courante, c'est-à-dire (`rank` - 1) / (total de lignes dans la partition - 1). La valeur est donc comprise entre 0 et 1, les deux inclus.
`cume_dist` () → `double precision` Renvoie la distribution cumulative, c'est-à-dire (nombre de lignes dans la partition précédente ou nombre de groupes à partir de la ligne courante) / (total de lignes dans la partition). La valeur est donc comprise entre 1/`N` et 1.
`ntile` ( `num_buckets` `integer` ) → `integer` Renvoie un entier entre 1 et la valeur argument, divisant la partition aussi également que possible.
`lag` ( `value` `anycompatible` [, `offset` `integer` [, `default` `anycompatible` ]] ) → `anycompatible` Renvoie `value` évaluée à la ligne qui se trouve à `offset` lignes avant la ligne actuelle dans la partition ; si une telle ligne n'existe pas, renvoie `default` à la place (qui doit être d'un type compatible avec `value`). `offset` et `default` sont évalués suivant la ligne actuelle. Par défaut, `offset` vaut 1 et `default` vaut `NULL`.
`lead` ( `value` `anycompatible` [, `offset` `integer` [, `default` `anycompatible` ]] ) → `anycompatible` Renvoie `value` évaluée à la ligne qui se trouve à `offset` lignes après la ligne actuelle dans la partition ; si une telle ligne n'existe pas, renvoie `default` à la place (qui doit être d'un type compatible que `value`). `offset` et `default` sont évalués suivant la ligne actuelle. Par défaut, `offset` vaut 1 et `default` vaut `NULL`.
`first_value` ( `value` `anyelement` ) → `anyelement` Renvoie `value` évaluée à la première ligne de la fenêtre.
`last_value` ( `value` `anyelement` ) → `anyelement` Renvoie `value` évaluée à la dernière ligne de la fenêtre.
`nth_value` ( `value` `anyelement`, `n` `integer` ) → `anyelement` Renvoie `value` évaluée à la ligne qui se trouve à la `n`-ième ligne de la fenêtre (en comptant à partir de 1) ; renvoie `NULL` si cette ligne n'existe pas.

Fonction

Description

row_number () → bigint

Renvoie le nombre de la ligne courante dans sa partition, en commençant à 1.

rank () → bigint

Renvoie le range de la ligne courante, avec des trous ; c'est-à-dire le row_number de la première ligne dans son groupe.

dense_rank () → bigint

Renvoie le range de la ligne courante, sans trous ; cette fonction compte réellement les groupes.

percent_rank () → double precision

Renvoie le rang relatif de la ligne courante, c'est-à-dire (rank - 1) / (total de lignes dans la partition - 1). La valeur est donc comprise entre 0 et 1, les deux inclus.

cume_dist () → double precision

Renvoie la distribution cumulative, c'est-à-dire (nombre de lignes dans la partition précédente ou nombre de groupes à partir de la ligne courante) / (total de lignes dans la partition). La valeur est donc comprise entre 1/N et 1.

ntile ( num_buckets integer ) → integer

Renvoie un entier entre 1 et la valeur argument, divisant la partition aussi également que possible.

lag ( value anycompatible [, offset integer [, default anycompatible ]] ) → anycompatible

Renvoie value évaluée à la ligne qui se trouve à offset lignes avant la ligne actuelle dans la partition ; si une telle ligne n'existe pas, renvoie default à la place (qui doit être d'un type compatible avec value). offset et default sont évalués suivant la ligne actuelle. Par défaut, offset vaut 1 et default vaut NULL.

lead ( value anycompatible [, offset integer [, default anycompatible ]] ) → anycompatible

Renvoie value évaluée à la ligne qui se trouve à offset lignes après la ligne actuelle dans la partition ; si une telle ligne n'existe pas, renvoie default à la place (qui doit être d'un type compatible que value). offset et default sont évalués suivant la ligne actuelle. Par défaut, offset vaut 1 et default vaut NULL.

first_value ( value anyelement ) → anyelement

Renvoie value évaluée à la première ligne de la fenêtre.

last_value ( value anyelement ) → anyelement

Renvoie value évaluée à la dernière ligne de la fenêtre.

nth_value ( value anyelement, n integer ) → anyelement

Renvoie value évaluée à la ligne qui se trouve à la n-ième ligne de la fenêtre (en comptant à partir de 1) ; renvoie NULL si cette ligne n'existe pas.

Toutes les fonctions listées dans Tableau 9.64 dépendent de l'ordre de tri indiqué par la clause ORDER BY sur la définition de la fenêtre associée. Les lignes qui ne sont pas distinctes lors de la seule considération des colonnes ORDER BY sont des peers. Les quatre fonctions de rang (y compris cume_dist) sont définies pour qu'elles donnent la même réponse pour toutes les lignes d'un groupe peer.

Notez que les fonctions first_value, last_value et nth_value ne prennent en compte que les lignes à l'intérieur d'une « fenêtre », qui, par défaut, contient les lignes du début de la partition jusqu'au dernier peer de la ligne actuelle. Ceci risque de donner des résultats sans intérêt pour last_value et quelques fois aussi pour nth_value. Vous pouvez redéfinir la fenêtre en ajoutant une spécification convenable (RANGE, ROWS ou GROUPS) à la clause OVER. Voir Section 4.2.8 pour plus d'informations.

Quand une fonction d'agrégat est utilisée comme fonction de fenêtrage, elle agrège les lignes dans la fenêtre de la ligne courante. Un agrégat utilisé avec ORDER BY et la définition par défaut de la fenêtre produit un type de comportement du style « somme mouvante », qui pourrait être ou pas ce qui est souhaité. Pour obtenir l'agrégat sur la partition complète, n'utilisez pas ORDER BY ou utilisez ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Les autres spécifications de fenêtre peuvent être utilisées pour obtenir d'autres effets.

Note

Le standard SQL définit une option RESPECT NULLS ou IGNORE NULLS pour les fonctions lead, lag, first_value, last_value et nth_value. Ceci n'est pas couvert dans PostgreSQL : le comportement est toujours identique au comportement par défaut du standard, autrement dit RESPECT NULLS. De même, les options du standard FROM FIRST et FROM LAST pour nth_value ne sont pas supportées : seul le comportement par défaut, FROM FIRST, l'est. (Vous pouvez obtenir le résultat de FROM LAST en inversant le tri ORDER BY.)