PostgreSQLLa base de données la plus sophistiquée au monde.
Documentation PostgreSQL 12.18 » Langage SQL » Fonctions et opérateurs » Fonctions d'agrégat

9.20. Fonctions d'agrégat

Les fonctions d'agrégat calculent une valeur unique à partir d'un ensemble de valeurs en entrée. Les fonctions d'agrégat généralistes fournies par défaut sont listées dans Tableau 9.55 et les agrégats statistiques dans Tableau 9.56. Les fonctions d'agrégat par défaut pour les ensembles ordonnés au sein d'un groupe sont listées dans Tableau 9.57 alors que celles fournies par défaut pour les ensembles hypothétiques au sein d'un groupe sont dans Tableau 9.58. Les opérations de regroupement, qui sont proches des fonctions d'agrégats, sont listées dans le Tableau 9.59. La syntaxe particulière des fonctions d'agrégat est décrite dans la Section 4.2.7. La Section 2.7 fournit un supplément d'informations introductives.

Tableau 9.55. Fonctions d'agrégat générales

FonctionType d'argumentType de retourMode partielDescription
array_agg(expression) tout type non tableau tableau du type de l'argument Nonles valeurs en entrée, pouvant inclure des valeurs NULL, concaténées dans un tableau
array_agg(expression) tout type tableau identique au type de données de l'argument Nonles tableaux en entrée sont concaténés dans un tableau englobant (les tableaux en entrée doivent tous être de même dimension et ne peuvent pas être vides ou NULL)
avg(expression) smallint, int, bigint, real, double precision, numeric ou interval numeric pour tout argument de type entier, double precision pour tout argument en virgule flottante, sinon identique au type de données de l'argument Ouila moyenne arithmétique de toutes les valeurs en entrée non NULL
bit_and(expression) smallint, int, bigint ou bit identique au type de données de l'argument Ouile AND bit à bit de toutes les valeurs non NULL en entrée ou NULL s'il n'y en a pas
bit_or(expression) smallint, int, bigint ou bit identique au type de données de l'argument Ouile OR bit à bit de toutes les valeurs non NULL en entrée ou NULL s'il n'y en a pas
bool_and(expression) bool bool Ouitrue si toutes les valeurs en entrée valent true, false sinon
bool_or(expression) bool bool Ouitrue si au moins une valeur en entrée vaut true, false sinon
count(*)  bigintOuinombre de lignes en entrée
count (expression)tout typebigintOui nombre de lignes en entrée pour lesquelles l'expression n'est pas NULL
every(expression) boolboolOuiéquivalent à bool_and
json_agg(expression) any json Nonagrège les valeurs, avec les NULL, sous la forme d'un tableau JSON
jsonb_agg(expression) any jsonb Nonagrège les valeurs, avec les NULL, sous la forme d'un tableau JSONB
json_object_agg(name, value) (any, any) json Nonagrège les paires nom/valeur en tant qu'objet JSON ; les valeurs peuvent être NULL, mais pas les noms
jsonb_object_agg(name, value) (any, any) jsonb Nonagrège les paires nom/valeur en tant qu'objet JSONB ; les valeurs peuvent être NULL, mais pas les noms
max(expression) tout type numeric, string, date/time, network, or enum ou tableau de ces typesidentique au type en argumentOui valeur maximale de l'expression pour toutes les valeurs en entrée non NULL
min(expression) tout type numeric, string, date/time, network ou enum, ou tableaux de ces typesidentique au type en argumentOui valeur minimale de l'expression pour toutes les valeurs en entrée non NULL
string_agg(expression , delimiter) (text, text) ou (bytea, bytea) identique aux arguments Nonvaleurs en entrées non NULL concaténées dans une chaîne, séparées par un délimiteur
sum(expression) smallint, int, bigint, real, double precision, numeric, interval ou money bigint pour les arguments de type smallint ou int, numeric pour les arguments de type bigint, sinon identique au type de données de l'argument Ouisomme de l'expression pour toutes les valeurs en entrée non NULL
xmlagg(expression) xml xml Nonconcaténation de valeurs XML non NULL (voir aussi Section 9.14.1.7)

En dehors de count, ces fonctions renvoient une valeur NULL si aucune ligne n'est sélectionnée. En particulier, une somme (sum) sur aucune ligne renvoie NULL et non zéro, et array_agg renvoie NULL plutôt qu'un tableau vide quand il n'y a pas de lignes en entrée. La fonction coalesce peut être utilisée pour substituer des zéros ou un tableau vide aux valeurs NULL quand cela est nécessaire.

Les fonctions d'agrégat qui supportent le mode partiel sont éligibles à participer à différentes optimisations, telles que les agrégats parallèles.

Note

Les agrégats booléens bool_and et bool_or correspondent aux agrégats standard du SQL every et any ou some. Pour any et some, il semble qu'il y ait une ambiguïté 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 si l'expression de sélection ne renvoie qu'une ligne. Du coup, 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 SQL peuvent être surpris par les performances de l'agrégat count lorsqu'il est appliqué à la table entière. En particulier, une requête identique à

SELECT count(*) FROM ma_table;

nécessitera un travail proportionnel à la taille de la table : PostgreSQL devra parcourir complètement la table ou un de ses index (comprenant toutes les lignes de la table).

Les fonctions d'agrégat array_agg, json_agg, jsonb_agg, json_object_agg, jsonb_object_agg, string_agg et xmlagg, ainsi que d'autres fonctions similaires d'agrégats définies par l'utilisateur, produisent des valeurs de résultats qui ont un sens différents, dépendant de l'ordre des valeurs en entrée. Cet ordre n'est pas précisé par défaut, mais peut être contrôlé en ajoutant une clause ORDER BY comme indiquée dans Section 4.2.7. Une alternative revient à fournir les valeurs à partir d'une sous-requête triée fonctionnera généralement. Par exemple :

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

Attention : cette approche peut échouer si la requête externe contient un traitement supplémentaire, telle qu'une jointure, car cela pourrait causer le tri de la sortie de la sous-requête avant le calcul de l'agrégat.

Tableau 9.56 présente les fonctions d'agrégat typiquement utilisées dans l'analyse statistique. (Elles sont séparées pour éviter de grossir la liste des agrégats les plus utilisés.) Là où la description mentionne N, cela représente le nombre de lignes en entrée pour lesquelles toutes les expressions en entrée sont non NULL. Dans tous les cas, NULL est renvoyé si le calcul n'a pas de signification, par exemple si N vaut zéro.

Tableau 9.56. Fonctions d'agrégats pour les statistiques

FonctionType de l'argumentType renvoyéMode partielDescription
corr(Y, X) double precision double precision Ouicoefficient de corrélation
covar_pop(Y, X) double precision double precision Ouicovariance de population
covar_samp(Y, X) double precision double precision Ouicovariance de l'échantillon
regr_avgx(Y, X) double precision double precision Ouimoyenne de la variable indépendante (sum(X)/N)
regr_avgy(Y, X) double precision double precision Ouimoyenne de la variable dépendante (sum(Y) / N)
regr_count(Y, X) double precision bigint Ouinombre de lignes dans lesquelles les deux expressions sont non NULL
regr_intercept(Y, X) double precision double precision OuiOrdonnée à l'origine de l'axe y pour la régression linéaire par la méthode des moindres carrés déterminée par les paires (X, Y)
regr_r2(Y, X) double precision double precision Ouicarré du coefficient de corrélation
regr_slope(Y, X) double precision double precision OuiPente pour la régression linéaire par la méthode des moindres carrés déterminée par les paires (X, Y)
regr_sxx(Y, X) double precision double precision Ouisum(X^2) - sum(X)^2 / N (« somme des carrés » de la variable indépendante)
regr_sxy(Y, X) double precision double precision Ouisum(X*Y) - sum(X) * sum(Y) / N (« somme des produits » de la variable indépendante multipliée par la variable dépendante)
regr_syy(Y, X) double precision double precision Ouisum(Y^2) - sum(Y)^2 / N (« somme des carrés » de la variable dépendante)
stddev(expression) smallint, int, bigint, real, double precision ou numeric double precision pour les arguments en virgule flottante, numeric sinon Ouialias historique pour stddev_samp
stddev_pop(expression) smallint, int, bigint, real, double precision ou numeric double precision pour les arguments en virgule flottante, numeric sinon Ouiécart type de la population pour les valeurs en entrée
stddev_samp(expression) smallint, int, bigint, real, double precision ou numeric double precision pour les arguments en virgule flottante, numeric sinon Ouiécart type d'échantillon pour les valeurs en entrée
variance(expression) smallint, int, bigint, real, double precision ou numeric double precision pour les arguments en virgule flottante, sinon numeric Ouialias historique de var_samp
var_pop(expression) smallint, int, bigint, real, double precision ou numeric double precision pour les arguments en virgule flottante, sinon numeric Ouivariance de la population des valeurs en entrée (carré de la déviation standard de la population)
var_samp(expression) smallint, int, bigint, real, double precision ou numeric double precision pour les arguments à virgule flottante, numeric sinon Ouivariance d'échantillon des valeurs en entrée (carré de la déviation standard)

Tableau 9.57 montre certaines fonctions d'agrégat qui utilisent la syntaxe des agrégats d'ensemble trié. Ces fonctions sont parfois référencées en tant que fonctions de « distribution inverse ».

Tableau 9.57. Fonctions d'agrégat par ensemble trié

FonctionType(s) d'argument direct(s)Type(s) d'argument agrégé(s)Type renvoyéMode partielDescription
mode() WITHIN GROUP (ORDER BY sort_expression) tout type triable identique à l'expression de tri Non renvoie la valeur en entrée la plus fréquente (choisie arbitrairement dans le cas où plusieurs valeurs ont la même fréquence)
percentile_cont(fraction) WITHIN GROUP (ORDER BY sort_expression) double precision double precision ou interval identique à l'expression de tri Non centile continue : renvoie une valeur correspondant à la fraction spécifiée dans l'ordre, avec une interpolation entre les éléménts adjacents en entrée si nécessaire
percentile_cont(fractions) WITHIN GROUP (ORDER BY sort_expression) double precision[] double precision ou interval tableau du type de l'expression de tri Non multiple centile continue : renvoie un tableau de résultats correspondant au format du paramètre fractions, chaque élément étant un élément non NULL remplacé par la valeur correspondant à ce centile
percentile_disc(fraction) WITHIN GROUP (ORDER BY sort_expression) double precision tout type triable identique à l'expression de tri Non centile discret : renvoie la première valeur en entrée dont la position dans le tri est identique ou égale à la fraction indiquée
percentile_disc(fractions) WITHIN GROUP (ORDER BY sort_expression) double precision[] tout type triable tableau du type de l'expression de tri Non plusieurs centiles discrets : renvoie un tableau de résultats correspondant au format du paramètre fractions, avec chaque élément non NULL remplacé par la valeur en entrée correspondant à ce centile

Tous les agrégats listés dans Tableau 9.57 ignorent les valeurs NULL dans leur entrée triée. Pour ceux qui prennent un paramètre fraction, la valeur de la fraction doit valoir entre 0 et 1 ; une erreur est renvoyée dans le cas contraire. Néanmoins, une valeur nulle de fraction produit simplement un résultat nul.

Chaque agrégat listé dans Tableau 9.58 est associé avec une fonction de fenêtrage de même définie dans Section 9.21. 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 calculé à partir de sorted_args.

Tableau 9.58. Fonctions d'agrégat par ensemble hypothétique

FonctionType(s) d'argument direct(s)Type(s) d'argument agrégé(s)Type renvoyéMode partielDescription
rank(args) WITHIN GROUP (ORDER BY sorted_args) VARIADIC "any" VARIADIC "any" bigint Non rang de la ligne hypothétique, avec des trous pour les lignes dupliquées
dense_rank(args) WITHIN GROUP (ORDER BY sorted_args) VARIADIC "any" VARIADIC "any" bigint Non rang de la ligne hypothétique, sans trous
percent_rank(args) WITHIN GROUP (ORDER BY sorted_args) VARIADIC "any" VARIADIC "any" double precision Non rang relatif de la ligne hypothétique, de 0 à 1
cume_dist(args) WITHIN GROUP (ORDER BY sorted_args) VARIADIC "any" VARIADIC "any" double precision Non rang relatif de la ligne hypothétique, de 1/N à 1

Pour chacun de ces agrégats par ensemble trié, la liste des arguments directs donnés dans args doit correspondre au nombre et aux types des arguments d'agrégat donnés dans sorted_args. Contrairement aux agrégats internes, 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 sont triées suivant la règle spécifiée dans la clause ORDER BY.

Tableau 9.59. Opérations de regroupement

FonctionType renvoyéDescription
GROUPING(args...) integer Masque entier de bits indiquant les arguments non inclus dans l'ensemble de regroupement courant

Les opérations de regroupement sont utilisées en conjonction avec les ensembles de regroupement (voir Section 7.2.4) pour distinguer les lignes résultantes. Les arguments de GROUPING ne sont pas évalués, mais ils doivent correspondre exactement aux expressions indiquées dans la clause GROUP BY de la requête associée. Les bits sont assignés avec l'argument le plus à droite comme le bit le moins significatif ; chaque bit est à 0 si l'expression correspondante est incluse dans le critère de regroupement générant la ligne résultat, et à 1 si elle ne l'est pas. 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)