9.21. Fonctions d'agrégat

» » »

Précédent	Niveau supérieur	Suivant
9.20. Fonctions et opérateurs sur les intervalles simple/multiple	Sommaire	9.22. Fonctions de fenêtrage

9.21. Fonctions d'agrégat

Les fonctions d'agrégat calculent un seul résultat à partir d'un ensemble de valeurs en entrée. Les fonctions d'agrégat natives à but général sont listées dans Tableau 9.58 alors que les agrégats statistiques sont dans Tableau 9.59. Les fonctions d'agrégat natives à ensemble trié dans des groupes sont listées dans Tableau 9.60 alors que les fonctions à ensemble hypothétique dans des groupes sont dans Tableau 9.61. Les opérations de regroupement, qui sont fortement liées aux fonctions d'agrégat, sont listées dans Tableau 9.62. Les considérations spéciales de syntaxe pour les fonctions d'agrégat sont expliquées dans Section 4.2.7. Consultez Section 2.7 pour une introduction supplémentaire.

Les fonctions d'agrégat qui supportent le Mode Partiel sont éligibles à participer à différentes optimisations, telles que l'agrégation parallélisée.

Tableau 9.58. Fonctions d'agrégat à but général

Fonction Description	Mode Partiel
`array_agg` ( `anynonarray` ) → `anyarray` Récupère toutes les valeurs en entrée, y compris les NULL, et les place dans un tableau.	No
`array_agg` ( `anyarray` ) → `anyarray` Concatène tous les tableaux en entrée dans un tableau d'une dimension supérieure. (Les entrées doivent toutes avoir la même dimension, et ne peuvent être ni vides ni NULL.)	No
`avg` ( `smallint` ) → `numeric` `avg` ( `integer` ) → `numeric` `avg` ( `bigint` ) → `numeric` `avg` ( `numeric` ) → `numeric` `avg` ( `real` ) → `double precision` `avg` ( `double precision` ) → `double precision` `avg` ( `interval` ) → `interval` Calcule la moyenne (arithmétique) de toutes les valeurs en entrée, non NULL.	Yes
`bit_and` ( `smallint` ) → `smallint` `bit_and` ( `integer` ) → `integer` `bit_and` ( `bigint` ) → `bigint` `bit_and` ( `bit` ) → `bit` Calcule un AND bit à bit de toutes les valeurs en entrée non NULL.	Yes
`bit_or` ( `smallint` ) → `smallint` `bit_or` ( `integer` ) → `integer` `bit_or` ( `bigint` ) → `bigint` `bit_or` ( `bit` ) → `bit` Calcule un OR bit à bit de toutes les valeurs en entrée non NULL.	Yes
`bit_xor` ( `smallint` ) → `smallint` `bit_xor` ( `integer` ) → `integer` `bit_xor` ( `bigint` ) → `bigint` `bit_xor` ( `bit` ) → `bit` Calcule un OR exclusif bit à bit de toutes les valeurs en entrée non NULL. Peut être utile comme somme de contrôle pour un ensemble de valeurs non ordonnées.	Yes
`bool_and` ( `boolean` ) → `boolean` Renvoie true si toutes les valeurs en entrée non NULL valent true, sinon false.	Yes
`bool_or` ( `boolean` ) → `boolean` Renvoie true si au moins une des valeurs en entrée non NULL vaut true, sinon false.	Yes
`count` ( `*` ) → `bigint` Calcule le nombre de lignes en entrée.	Yes
`count` ( `"any"` ) → `bigint` Calcule le nombre de lignes en entrée pour lesquelles la valeur n'est pas NULL.	Yes
`every` ( `boolean` ) → `boolean` Ceci est l'équivalent de `bool_and` pour le standard SQL.	Yes
`json_agg` ( `anyelement` ) → `json` `jsonb_agg` ( `anyelement` ) → `jsonb` Récupère toutes les valeurs en entrée, y compris les NULL, et les place dans un tableau JSON. Les valeurs sont converties en JSON avec `to_json` ou `to_jsonb`.	No
`json_object_agg` ( `key` `"any"`, `value` `"any"` ) → `json` `jsonb_object_agg` ( `key` `"any"`, `value` `"any"` ) → `jsonb` Récupère toutes les paires clé/valeur et les place dans un objet JSON. Les arguments clé sont convertis en texte ; les arguments valeur sont convertis avec `to_json` ou `to_jsonb`. Les valeurs peuvent être NULL, mais pas les clés.	No
`max` ( `see text` ) → `same as input type` Calcule la valeur maximale de toutes les valeurs en entrée non NULL. Disponible pour les types numeric, string, date/time ou enum type, ainsi que `inet`, `interval`, `money`, `oid`, `pg_lsn`, `tid`, `xid8` et les tableaux de chacun de ces types.	Yes
`min` ( `see text` ) → `same as input type` Calcule la valeur minimale de toutes les valeurs en entrée non NULL. Disponible pour les types numeric, string, date/time ou enum type, ainsi que `inet`, `interval`, `money`, `oid`, `pg_lsn`, `tid`, `xid8` et les tableaux de chacun de ces types.	Yes
`range_agg` ( `value` `anyrange` ) → `anymultirange` `range_agg` ( `value` `anymultirange` ) → `anymultirange` Calcule l'union des valeurs non NULL en entrée.	No
`range_intersect_agg` ( `value` `anyrange` ) → `anyrange` `range_intersect_agg` ( `value` `anymultirange` ) → `anymultirange` Calcule l'intersection des valeurs non NULL en entrée.	No
`string_agg` ( `value` `text`, `delimiter` `text` ) → `text` `string_agg` ( `value` `bytea`, `delimiter` `bytea` ) → `bytea` Concatène les valeurs en entrée non NULL dans une chaîne. Chaque valeur après la première est précédée par le `delimiter` correspondant (s'il n'est pas NULL).	No
`sum` ( `smallint` ) → `bigint` `sum` ( `integer` ) → `bigint` `sum` ( `bigint` ) → `numeric` `sum` ( `numeric` ) → `numeric` `sum` ( `real` ) → `real` `sum` ( `double precision` ) → `double precision` `sum` ( `interval` ) → `interval` `sum` ( `money` ) → `money` Calcule la somme de toutes les valeurs en entrée non NULL.	Yes
`xmlagg` ( `xml` ) → `xml` Concatène toutes les valeurs XML en entrée non NULL (voir Section 9.15.1.7).	No

Il doit être noté que, sauf pour count, ces fonctions renvoient NULL quand aucune ligne n'est sélectionnée. En particulier, le sum d'aucune ligne renvoie NULL, et non pas zéro comme certains s'y attendraient, et array_agg renvoie NULL plutôt qu'un tableau vide s'il n'y a pas de lignes en entrée. La fonction coalesce peut être utilisée pour substituer une valeur NULL à zéro ou un tableau vide quand cela s'avère nécessaire.

Les fonctions d'agrégat array_agg, json_agg, jsonb_agg, json_object_agg, jsonb_object_agg string_agg et xmlagg, ainsi que les fonctions d'agrégat utilisateurs similaires, produisent des valeurs de résultat différentes suivant l'ordre des valeurs en entrée. Cet ordre n'est pas spécifié par défaut, mais peut être contrôlé en écrivant une clause ORDER BY dans l'appel à l'agrégat, comme indiqué dans Section 4.2.7. Autrement, fournir les valeurs en entrée triées à partir d'une sous-requête fonctionne généralement. Par exemple :

SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;

Attention que cette approche peut échouer si le niveau de la requête externe contient un traitement supplémentaire, tel qu'une jointure, parce que cela pourrait remettre en cause la sortie de la sous-requête et notamment un nouveau tri avant le calcul de l'agrégat.

Note

Les agrégats booléens bool_and et bool_or correspondent aux agrégats du standard SQL every et any ou some. PostgreSQL accepte every, mais pas any et some, car il y a une ambiguité dans la syntaxe standard :

SELECT b1 = ANY((SELECT b2 FROM t2 ...)) FROM t1 ...;

Ici, ANY peut être considéré soit comme introduisant une sous-requête, soit comme étant une fonction d'agrégat, si la sous-requête renvoie une ligne avec une valeur booléenne. De ce fait, le nom standard ne peut pas être donné à ces agrégats.

Note

Les utilisateurs habitués à travailler avec d'autres systèmes de gestion de bases de données pourraient être déçus par les performances de l'agrégat count quand il est appliqué à la table entière. Une requête comme :

SELECT count(*) FROM sometable;

nécessite un effort proportionnel à la taille de la table : PostgreSQL aura besoin de parcourir soit toute la table soit tout un index qui inclut toutes les lignes de la table.

Tableau 9.59 montre les fonctions d'agrégat typiquement utilisées dans les analyses statistiques. (Elles sont séparées principalement pour éviter d'encombrer la liste des agrégats plus fréquemment utilisés.) Les fonctions acceptant numeric_type sont disponibles pour les types smallint, integer, bigint, numeric, real et double precision. Quand la description mentionne N, cela signifie le nombre de lignes en entrée pour lesquelles les expressions en entrée ne sont pas NULL. Dans tous les cas, NULL est renvoyé si le calcul n'a pas de sens, par exemple quand N vaut zéro.

Tableau 9.59. Fonctions d'agrégat pour les statistiques

Fonction Description	Mode Partiel
`corr` ( `Y` `double precision`, `X` `double precision` ) → `double precision` Calcule le coefficient de corrélation.	Yes
`covar_pop` ( `Y` `double precision`, `X` `double precision` ) → `double precision` Calcule la covariance de population.	Yes
`covar_samp` ( `Y` `double precision`, `X` `double precision` ) → `double precision` Calcule la covariance d'échantillon.	Yes
`regr_avgx` ( `Y` `double precision`, `X` `double precision` ) → `double precision` Calcule la moyenne de la variable indépendante, `sum(X)/N`.	Yes
`regr_avgy` ( `Y` `double precision`, `X` `double precision` ) → `double precision` Calcule la moyenne de la variable dépendante, `sum(Y)/N`.	Yes
`regr_count` ( `Y` `double precision`, `X` `double precision` ) → `bigint` Calcule le nombre de lignes pour lesquelles les deux entrées sont non NULL.	Yes
`regr_intercept` ( `Y` `double precision`, `X` `double precision` ) → `double precision` Calcule l'ordonnée à l'origine de l'équation d'ajustement linéaire par les moindres carrés déterminée par les paires de valeurs (`X`, `Y`).	Yes
`regr_r2` ( `Y` `double precision`, `X` `double precision` ) → `double precision` Calcule le carré du coefficient de corrélation.	Yes
`regr_slope` ( `Y` `double precision`, `X` `double precision` ) → `double precision` Calcule la pente de la droite de l'équation d'ajustement linéaire par les moindres carrés déterminée par les paires de valeurs (`X`, `Y`).	Yes
`regr_sxx` ( `Y` `double precision`, `X` `double precision` ) → `double precision` Calcule la « somme des carrés » de la variable indépendante, `sum(X^2) - sum(X)^2/N`.	Yes
`regr_sxy` ( `Y` `double precision`, `X` `double precision` ) → `double precision` Calcule la « somme des produits » des variables dépendantes heures indépendantes, `sum(XY) - sum(X) sum(Y)/N`.	Yes
`regr_syy` ( `Y` `double precision`, `X` `double precision` ) → `double precision` Calcule la « somme des carrés » de la variable dépendante, `sum(Y^2) - sum(Y)^2/N`.	Yes
`stddev` ( `numeric_type` ) → `double precision` pour `real` ou `double precision`, sinon `numeric` Alias historique pour `stddev_samp`.	Yes
`stddev_pop` ( `numeric_type` ) → `double precision` pour `real` ou `double precision`, sinon `numeric` Calcule la déviation standard de la population pour les valeurs en entrée.	Yes
`stddev_samp` ( `numeric_type` ) → `double precision` pour `real` ou `double precision`, sinon `numeric` Calcule la déviation standard d'échantillon des valeurs en entrée.	Yes
`variance` ( `numeric_type` ) → `double precision` pour `real` ou `double precision`, sinon `numeric` Alias historique pour `var_samp`.	Yes
`var_pop` ( `numeric_type` ) → `double precision` pour `real` ou `double precision`, sinon `numeric` Calcule la variance de la population pour les valeurs en entrée (carré de la déviation standard de la population).	Yes
`var_samp` ( `numeric_type` ) → `double precision` for `real` or `double precision`, otherwise `numeric` Calcule la variance d'échantillon des valeurs en entrée (carré de la déviation standard d'échantillon).	Yes

Tableau 9.60 montre certaines fonctions d'agrégat utilisant la syntaxe d'agrégat à ensemble ordonné. Ces fonctions sont parfois nommées fonctions à « distribution inverse ». Leur entrée est introduite par ORDER BY, et elles peuvent aussi prendre un argument direct qui n'est pas agrégé mais calculé une seule fois. Toutes ces fonctions ignorent les valeurs NULL dans leur entrée agrégée. Pour celles qui prennent un paramètre fraction, la valeur de la fraction est comprise entre 0 et 1 ; une erreur est renvoyée dans le cas contraire. Néanmoins une valeur de fraction NULL donne simplement un résultat NULL.

Tableau 9.60. Fonctions d'agrégat à ensemble trié

Fonction Description	Mode Partiel
`mode` () `WITHIN GROUP` ( `ORDER BY` `anyelement` ) → `anyelement` Calcule le mode, la valeur la plus fréquente d'un argument agrégé (en choisissant arbitrairement le premier s'il y a plusieurs valeurs de fréquence égale). L'argument agrégé doit être d'un type triable.	No
`percentile_cont` ( `fraction` `double precision` ) `WITHIN GROUP` ( `ORDER BY` `double precision` ) → `double precision` `percentile_cont` ( `fraction` `double precision` ) `WITHIN GROUP` ( `ORDER BY` `interval` ) → `interval` Calcule le pourcentage continu, une valeur correspondant à la `fraction` à l'intérieur d'un ensemble ordonné de valeurs d'argument agrégées. Ceci va interpoler entre les éléments en entrée adjacents si nécessaire.	No
`percentile_cont` ( `fractions` `double precision[]` ) `WITHIN GROUP` ( `ORDER BY` `double precision` ) → `double precision[]` `percentile_cont` ( `fractions` `double precision[]` ) `WITHIN GROUP` ( `ORDER BY` `interval` ) → `interval[]` Calcule plusieurs pourcentages continues. Le résultat est un tableau de mêmes dimensions que le paramètre `fractions`, avec chaque élément non NULL remplacé par la valeur (potentiellement interpolée) correspondance à ce pourcentage.	No
`percentile_disc` ( `fraction` `double precision` ) `WITHIN GROUP` ( `ORDER BY` `anyelement` ) → `anyelement` Calcule le pourcentage discret, la première valeur avec l'ensemble de tri des valeurs d'argument agrégé dont la position dans le tri est égale ou dépasse la `fraction` indiquée. L'argument agrégé doit être d'un type triable.	No
`percentile_disc` ( `fractions` `double precision[]` ) `WITHIN GROUP` ( `ORDER BY` `anyelement` ) → `anyarray` Calcule plusieurs pourcentages discrets. Le résultat est un tableau de mêmes dimensions que le paramètre `fractions`, avec chaque élément non NULL remplacé par la valeur en entrée correspondant à ce pourcentage. L'argument agrégé doit être d'un type triable.	No

Chacun des agrégats d'« ensemble hypothétique » listés dans Tableau 9.61 est associé avec une fonction de fenêtrage du même nom définie dans Section 9.22. Dans chaque cas, le résultat de l'agrégat est la valeur que la fonction de fenêtrage associée aurait renvoyée pour la ligne « hypothétique » construite à partir de args, si une telle ligne a été ajoutée au groupe trié de lignes représenté par sorted_args. Pour chacune de ces fonctions, la liste des arguments directs donnée dans args doit correspondre au nombre et types des arguments agrégés donnés dans sorted_args. Contrairement à la plupart des agrégats natifs, ces agrégats ne sont pas stricts, c'est-à-dire qu'ils ne suppriment pas les lignes en entrée contenant des NULL. Les valeurs NULL se trient suivant la règle indiquée dans la clause ORDER BY.

Tableau 9.61. Fonctions d'agrégat à ensemble hypothétique

Fonction Description	Mode Partiel
`rank` ( `args` ) `WITHIN GROUP` ( `ORDER BY` `sorted_args` ) → `bigint` Calcule le rang de la ligne hypothétique avec des trous ; c'est-à-dire le numéro de la première ligne dans son groupe.	No
`dense_rank` ( `args` ) `WITHIN GROUP` ( `ORDER BY` `sorted_args` ) → `bigint` Calcule le rang de la ligne hypothétique sans trous ; cette fonction compte réellement le nombre de groupes.	No
`percent_rank` ( `args` ) `WITHIN GROUP` ( `ORDER BY` `sorted_args` ) → `double precision` Calcule le rang relatif de la ligne hypothétique, c'est-à-dire (`rank` - 1) / (lignes totales - 1). La valeur est donc comprise entre 0 et 1, les deux étant inclus.	No
`cume_dist` ( `args` ) `WITHIN GROUP` ( `ORDER BY` `sorted_args` ) → `double precision` Calcule la distribution cumulative, c'est-à-dire (nombre de lignes précédentes ou proches de la ligne hypothétique) / (total de lignes). La valeur est donc comprise entre 1/`N` et 1.	No

Tableau 9.62. Opérations de regroupement

Fonction Description
`GROUPING` ( `group_by_expression(s)` ) → `integer` Renvoie un masque de bits indiquant les expressions `GROUP BY` non incluses dans l'ensemble de regroupement actuel. Les bits sont affectés avec l'argument le plus à droit correspondant au bit de plus faible poids ; chaque bit vaut 0 si l'expression correspondante est inclus dans le critère de regroupement de l'ensemble de regroupement générant la ligne résultat actuelle, et 1 dans le cas contraire.

Fonction

Description

GROUPING ( group_by_expression(s) ) → integer

Renvoie un masque de bits indiquant les expressions GROUP BY non incluses dans l'ensemble de regroupement actuel. Les bits sont affectés avec l'argument le plus à droit correspondant au bit de plus faible poids ; chaque bit vaut 0 si l'expression correspondante est inclus dans le critère de regroupement de l'ensemble de regroupement générant la ligne résultat actuelle, et 1 dans le cas contraire.

Les opérations de regroupement affichées dans Tableau 9.62 sont utilisées en conjonction avec les ensembles de regroupement (voir Section 7.2.4) pour distinguer les lignes résultats. Les arguments à la fonction GROUPING ne sont pas réellement évalués car ils doivent correspondre exactement aux expressions données dans la clause GROUP BY du niveau de requête associé. Par exemple :

=> SELECT * FROM items_sold;
 make  | model | sales
-------+-------+-------
 Foo   | GT    |  10
 Foo   | Tour  |  20
 Bar   | City  |  15
 Bar   | Sport |  5
(4 rows)

=> SELECT make, model, GROUPING(make,model), sum(sales) FROM items_sold GROUP BY ROLLUP(make,model);
 make  | model | grouping | sum
-------+-------+----------+-----
 Foo   | GT    |        0 | 10
 Foo   | Tour  |        0 | 20
 Bar   | City  |        0 | 15
 Bar   | Sport |        0 | 5
 Foo   |       |        1 | 30
 Bar   |       |        1 | 20
       |       |        3 | 50
(7 rows)

Ici, la valeur 0 dans la colonne grouping des quatre premières lignes montre qu'elles ont été regroupées normalement par rapport aux colonnes de regroupement. La valeur 1 indique que la colonne model n'a pas été groupé dans les deux lignes suivantes, et la valeur 3 indique que ni la colonne make ni la colonne model n'ont été regroupées dans la dernière ligne (qui, de ce fait, est un agrégat sur tous les lignes en entrée).