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

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.59 alors que les agrégats statistiques sont dans Tableau 9.60. Les fonctions d'agrégat natives à ensemble trié dans des groupes sont listées dans Tableau 9.61 alors que les fonctions à ensemble hypothétique dans des groupes sont dans Tableau 9.62. Les opérations de regroupement, qui sont fortement liées aux fonctions d'agrégat, sont listées dans Tableau 9.63. 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.59. Fonctions d'agrégat à but général

Fonction

Description

Mode Partiel

any_value ( anyelement ) → same as input type

Renvoie une valeur arbitraire à partir des valeurs non NULL en entrée.

Oui

array_agg ( anynonarray ) → anyarray

Récupère toutes les valeurs en entrée, y compris les NULL, et les place dans un tableau.

Oui

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.)

Oui

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.

Oui

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.

Oui

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.

Oui

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.

Oui

bool_and ( boolean ) → boolean

Renvoie true si toutes les valeurs en entrée non NULL valent true, sinon false.

Oui

bool_or ( boolean ) → boolean

Renvoie true si au moins une des valeurs en entrée non NULL vaut true, sinon false.

Oui

count ( * ) → bigint

Calcule le nombre de lignes en entrée.

Oui

count ( "any" ) → bigint

Calcule le nombre de lignes en entrée pour lesquelles la valeur n'est pas NULL.

Oui

every ( boolean ) → boolean

Ceci est l'équivalent de bool_and pour le standard SQL.

Oui

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.

Non

json_objectagg ( [ { key_expression { VALUE | ':' } value_expression } ] [ { NULL | ABSENT } ON NULL ] [ { WITH | WITHOUT } UNIQUE [ KEYS ] ] [ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ])

Se comporte comme json_object, mais sous la forme d'une fonction d'agrégat, donc elle prend uniquement un paramètre key_expression et un paramètre value_expression.

SELECT json_objectagg(k:v) FROM (VALUES ('a'::text,current_date),('b',current_date + 1)) AS t(k,v){ "a" : "2022-05-10", "b" : "2022-05-11" }

Non

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.

Non

json_object_agg_strict ( key "any", value "any" ) → json

jsonb_object_agg_strict ( key "any", value "any" ) → jsonb

Collectionne toutes les paires clé/valeur dans un objet JSON. Les arguments clés sont convertis en texte ; les arguments valeurs sont converties avec to_json ou to_jsonb. La key ne peut pas être NULL. Si la value est NULL, alors la paire complète est ignorée.

Non

json_object_agg_unique ( key "any", value "any" ) → json

jsonb_object_agg_unique ( key "any", value "any" ) → jsonb

Récupère toutes les paires clé/valeur dans un objet JSON. Les arguments des clés sont converties en texte ; les arguments des valeurs sont converties via to_json ou to_jsonb. Les valeurs peuvent être NULL mais les clés ne le peuvent pas. S'il existe une clé dupliquée, une erreur est renvoyée.

Non

json_arrayagg ( [ value_expression ] [ ORDER BY sort_expression ] [ { NULL | ABSENT } ON NULL ] [ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ])

Se comporte de la même façon que json_array mais sous la forme d'une fonction d'agrégat, donc elle ne prend qu'un seul paramètre value_expression. Si ABSENT ON NULL est indiqué, toute valeur NULL est omise. Si ORDER BY est indiqué, les éléments apparaîtront dans le tableau dans cet ordre plutôt que dans l'ordre de saisie.

SELECT json_arrayagg(v) FROM (VALUES(2),(1)) t(v)[2, 1]

Non

json_object_agg_unique_strict ( key "any", value "any" ) → json

jsonb_object_agg_unique_strict ( key "any", value "any" ) → jsonb

Récupère toutes les paires clé/valeur dans un objet JSON. Les arguments des clés sont converties en texte ; les arguments des valeurs sont converties via to_json ou to_jsonb. Une key ne peut pas être NULL. Si value vaut NULL, alors la paire complète est ignorée. S'il ya une clé dupliquée, une erreur est renvoyée.

Non

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.

Oui

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.

Oui

range_agg ( value anyrange ) → anymultirange

range_agg ( value anymultirange ) → anymultirange

Calcule l'union des valeurs non NULL en entrée.

Non

range_intersect_agg ( value anyrange ) → anyrange

range_intersect_agg ( value anymultirange ) → anymultirange

Calcule l'intersection des valeurs non NULL en entrée.

Non

json_agg_strict ( anyelement ) → json

jsonb_agg_strict ( anyelement ) → jsonb

Récupère toutes les valeurs en entrée, en ignorant les valeurs NULL, pour les intégrer dans un tableau JSON. Les valeurs sont converties en JSON avec to_json ou to_jsonb.

Non

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).

Oui

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.

Oui

xmlagg ( xml ) → xml

Concatène toutes les valeurs XML en entrée non NULL (voir Section 9.15.1.7).

Non

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_agg_strict, jsonb_agg_strict, json_object_agg, jsonb_object_agg, json_object_agg_strict, jsonb_object_agg_strict, json_object_agg_unique, jsonb_object_agg_unique, json_object_agg_unique_strict, jsonb_object_agg_unique_strict, 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.60 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.60. 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.

Oui

covar_pop ( Y double precision, X double precision ) → double precision

Calcule la covariance de population.

Oui

covar_samp ( Y double precision, X double precision ) → double precision

Calcule la covariance d'échantillon.

Oui

regr_avgx ( Y double precision, X double precision ) → double precision

Calcule la moyenne de la variable indépendante, sum(X)/N.

Oui

regr_avgy ( Y double precision, X double precision ) → double precision

Calcule la moyenne de la variable dépendante, sum(Y)/N.

Oui

regr_count ( Y double precision, X double precision ) → bigint

Calcule le nombre de lignes pour lesquelles les deux entrées sont non NULL.

Oui

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).

Oui

regr_r2 ( Y double precision, X double precision ) → double precision

Calcule le carré du coefficient de corrélation.

Oui

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).

Oui

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.

Oui

regr_sxy ( Y double precision, X double precision ) → double precision

Calcule la « somme des produits » des variables dépendantes heures indépendantes, sum(X*Y) - sum(X) * sum(Y)/N.

Oui

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.

Oui

stddev ( numeric_type ) → double precision pour real ou double precision, sinon numeric

Alias historique pour stddev_samp.

Oui

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.

Oui

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.

Oui

variance ( numeric_type ) → double precision pour real ou double precision, sinon numeric

Alias historique pour var_samp.

Oui

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).

Oui

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).

Oui

Tableau 9.61 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.61. 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.

Non

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.

Non

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.

Non

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.

Non

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.

Non

Chacun des agrégats d'« ensemble hypothétique » listés dans Tableau 9.62 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.62. 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.

Non

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.

Non

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.

Non

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.

Non

Tableau 9.63. 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.


Les opérations de regroupement affichées dans Tableau 9.63 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).