9.21. Fonctions Window

» » »

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

9.21. Fonctions Window

Les fonctions Window fournissent la possibilité de réaliser des calculs au travers d'ensembles de lignes relatifs à la ligne de la requête en cours. Voir Section 3.5 pour une introduction à cette fonctionnalité, et Section 4.2.8 pour les détails sur la syntaxe.

Les fonctions window internes sont listées dans Tableau 9.60. Notez que ces fonctions doivent être appelées en utilisant la syntaxe des fonctions window ; autrement dit, une clause OVER est requise.

En plus de ces fonctions, toute fonction normale d'agrégat, interne ou définie par l'utilisateur (mais pas les agrégats d'ensemble trié ou d'ensemble hypothétique) peut être utilisée comme une fonction window (voir Section 9.20 pour une liste des agrégats internes). Les fonctions d'agrégat agissent comme des fonctions window seulement quand une clause OVER suit l'appel ; sinon elles agissent comme des agrégats standards et renvoient une seule ligne pour l'ensemble entier.

Tableau 9.60. Fonctions Window généralistes

Fonction	Type renvoyé	Description
`row_number()`	`bigint`	numéro de la ligne en cours de traitement dans sa partition, en comptant à partir de 1
`rank()`	`bigint`	rang de la ligne en cours de traitement, avec des trous ; identique `row_number` pour le premier pair
`dense_rank()`	`bigint`	rang de la ligne en cours de traitement, sans trous ; cette fonction compte les groupes de pairs
`percent_rank()`	`double precision`	rang relatif de la ligne en cours de traitement ;: (`rank` - 1) / (nombre total de lignes dans la partition - 1)
`cume_dist()`	`double precision`	distribution cumulative : (nombre de lignes dans la partition précédant ou de pair avec la ligne courante) / nombre total de lignes dans la partition
`ntile(num_buckets integer)`	`integer`	entier allant de 1 à la valeur de l'argument, divisant la partition aussi équitablement que possible
`lag(value anyelement [, offset integer [, default anyelement ]])`	`même type que value`	renvoie `value` évalué à la ligne qui est `offset` lignes avant la ligne actuelle à l'intérieur de la partition ; s'il n'y a pas de ligne, renvoie à la place `default` (qui doit être du même type que `value`). `offset` et `default` sont évalués par rapport à la ligne en cours. Si omis, `offset` a comme valeur par défaut 1 et `default` est NULL
`lead(value anyelement [, offset integer [, default anyelement ]])`	`same type as value`	renvoie `value` évalué à la ligne qui est `offset` lignes après la ligne actuelle à l'intérieur de la partition ; s'il n'y a pas de ligne, renvoie à la place `default` (qui doit être du même type que `value`). `offset` et `default` sont évalués par rapport à la ligne en cours. Si omis, `offset` a comme valeur par défaut 1 et `default` est NULL
`first_value(value any)`	`même type que value`	renvoie `value` évaluée à la ligne qui est la première ligne du frame window
`last_value(value any)`	`même type que value`	renvoie `value` évaluée à la ligne qui est la dernière ligne du frame window
`nth_value(value any, nth integer)`	`même type que value`	renvoie `value` évaluée à la ligne qui est la`nth`-ième ligne de la frame window (en comptant à partir de 1) ; NULL si aucune ligne

Toutes les fonctions listées dans Tableau 9.60 dépendent du tri indiqué par la clause ORDER BY de la définition window associée. Les lignes qui ne sont pas distinctes en considérant uniquement les colonnes ORDER BY sont des pairs ; les quatre fonctions de rang (incluant cume_dist) sont définies de façon à ce qu'elles donnent la même réponse pour toutes les lignes pairs.

Notez que first_value, last_value et nth_value considèrent seulement les lignes à l'intérieur du « frame window » qui contient par défaut les lignes du début de la partition jusqu'au dernier pair de la ligne en cours. Cela risque de donner des résultats peu intéressants pour last_value et quelques fois aussi pour nth_value. Vous pouvez redéfinir la frame en ajoutant une spécification convenable de frame (avec RANGE ou ROWS) dans la clause OVER. Voir Section 4.2.8 pour plus d'informations sur les spécifications de la frame.

Quand une fonction d'agrégat est utilisée comme fonction window, elle aggrège les lignes sur la frame window de la ligne en cours de traitement. Pour obtenir un agrégat sur la partition complète, omettez ORDER BY ou utilisez ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Un agrégat utilisé avec ORDER BY et la définition de la frame window par défaut produit un comportement de type « somme en cours d'exécution », qui pourrait être souhaité, ou pas.

Note

Le standard SQL définit une option RESPECT NULLS ou IGNORE NULLS pour lead, lag, first_value, last_value et nth_value. Ceci n'est pas implanté dans PostgreSQL : le comportement est toujours le même que le comportement par défaut du standard, nommément RESPECT NULLS. De la même façon, les options FROM FIRST ou FROM LAST pour nth_value ne sont pas implantées : seul le comportement FROM FIRST est supporté par défaut. (Vous pouvez obtenir le résultat d'un FROM LAST en inversant l'ordre du ORDER BY.)

cume_dist calcule la fraction des lignes de la partition qui sont inférieures ou égales à la ligne courante et ses pairs, alors que percent_rank calcule la fraction des lignes de la partition qui sont inférieures ou égales à la ligne courante, en supposant que la ligne courante n'existe pas dans la partition.