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.60 alors que les agrégats statistiques sont dans Tableau 9.61. Les fonctions d'agrégat natives à ensemble trié dans des groupes sont listées dans Tableau 9.62 alors que les fonctions à ensemble hypothétique dans des groupes sont dans Tableau 9.63. Les opérations de regroupement, qui sont fortement liées aux fonctions d'agrégat, sont listées dans Tableau 9.64. 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.
Bien que tous les agrégats ci-dessous acceptent une clause optionnelle
ORDER BY (comme décrit dans Section 4.2.7),
la clause n'a été ajoutée qu'aux agrégats dont la sortie est affectée par l'ordre.
Tableau 9.60. Fonctions d'agrégat à but général
Fonction Description | Mode Partiel |
|---|---|
Renvoie une valeur arbitraire à partir des valeurs non NULL en entrée. | Oui |
Récupère toutes les valeurs en entrée, y compris les NULL, et les place dans un tableau. | Oui |
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 |
|
Calcule la moyenne (arithmétique) de toutes les valeurs en entrée, non NULL. | Oui |
Calcule un AND bit à bit de toutes les valeurs en entrée non NULL. | Oui |
Calcule un OR bit à bit de toutes les valeurs en entrée non NULL. | Oui |
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 |
Renvoie true si toutes les valeurs en entrée non NULL valent true, sinon false. | Oui |
|
Renvoie true si au moins une des valeurs en entrée non NULL vaut true, sinon false. | Oui |
|
Calcule le nombre de lignes en entrée. | Oui |
Calcule le nombre de lignes en entrée pour lesquelles la valeur n'est pas NULL. | Oui |
|
Ceci est l'équivalent de | Oui |
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
| Non |
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 | Non |
Se comporte de la même façon que
| Non |
Se comporte comme
| Non |
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 | Non |
Collectionne toutes les paires clé/valeur dans un objet JSON.
Les arguments clés sont convertis en texte ; les arguments
valeurs sont converties avec
| Non |
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 | Non |
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 | Non |
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 | Oui |
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 | Oui |
Calcule l'union des valeurs non NULL en entrée. | Non |
Calcule l'intersection des valeurs non NULL en entrée. | Non |
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 | Oui |
|
Calcule la somme de toutes les valeurs en entrée non NULL. | Oui |
Concatène toutes les valeurs XML en entrée non NULL (voir Section 9.15.1.8). | 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.
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.
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.61 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.61. Fonctions d'agrégat pour les statistiques
Tableau 9.62 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.62. Fonctions d'agrégat à ensemble trié
Chacun des agrégats d'« ensemble hypothétique » listés dans
Tableau 9.63 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.63. Fonctions d'agrégat à ensemble hypothétique
Tableau 9.64. Opérations de regroupement
Les opérations de regroupement affichées dans Tableau 9.64 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).