PostgreSQLLa base de données la plus sophistiquée au monde.
Documentation PostgreSQL 13.0 » 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.55 alors que les agrégats statistiques sont dans Tableau 9.56. Les fonctions d'agrégat natives à ensemble trié dans des groupes sont listées dans Tableau 9.57 alors que les fonctions à ensemble hypothétique dans des groupes sont dans Tableau 9.58. Les opérations de regroupement, qui sont fortement liées aux fonctions d'agrégat, sont listées dans Tableau 9.59. 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.55. 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

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 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 et les tableaux de chacun de ces types.

Yes

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

Computes the y-intercept of the least-squares-fit linear equation determined by the (X, Y) pairs.

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

Computes the slope of the least-squares-fit linear equation determined by the (X, Y) pairs.

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(X*Y) - 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.57 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 inroduite 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.57. 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.58 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.58. 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.59. 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.59 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).