Les fonctions d'agrégat calculent une valeur unique à partir d'un ensemble de valeurs en entrée. Les fonctions d'agrégats intégrées sont listées dans Tableau 9.43, « Fonctions d'agrégat générales » et Tableau 9.44, « Fonctions d'agrégats pour les statistiques ». La syntaxe particulière des fonctions d'agrégat est décrite dans la Section 4.2.7, « Expressions d'agrégat ». La Section 2.7, « Fonctions d'agrégat » fournit un supplément d'informations introductives.
Tableau 9.43. Fonctions d'agrégat générales
| Fonction | Type d'argument | Type de retour | Description |
|---|---|---|---|
| array_agg(expression) | any | tableau du type de l'argument | les valeurs en entrée, pouvant inclure des valeurs NULL, concaténées dans un tableau |
| avg(expression) | smallint, int, bigint, real, double precision, numeric, interval ou money | numeric pour tout argument de type entier, sinon identique au type de données de l'argument | la moyenne arithmétique de toutes les valeurs en entrée |
| bit_and(expression) | smallint, int, bigint ou bit | identique au type de données de l'argument | le 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 | le 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 | true si toutes les valeurs en entrée valent true, false sinon |
| bool_or(expression) | bool | bool | true si au moins une valeur en entrée vaut true, false sinon |
| count(*) | bigint | nombre de lignes en entrée | |
| count(expression) | tout type | bigint | nombre de lignes en entrée pour lesquelles l'expression n'est pas NULL |
| every(expression) | bool | bool | équivalent à bool_and |
| max(expression) | tout type array, numeric, string ou date/time | identique au type en argument | valeur maximale de l'expression pour toutes les valeurs en entrée |
| min(expression) | tout type array, numeric, string ou date/time | identique au type en argument | valeur minimale de l'expression pour toutes les valeurs en entrée |
| string_agg(expression , delimiter) | text, text | text | valeurs en entrées concaténées dans une chaîne, séparées par un délimiteur |
| sum(expression) | smallint, int, bigint, real, double precision, numeric ou interval | bigint pour les arguments de type smallint ou int, numeric pour les arguments de type bigint, double precision pour les arguments en virgule flottante, sinon identique au type de données de l'argument | somme de l'expression pour toutes les valeurs en entrée |
| xmlagg(expression) | xml | xml | concaténation de valeurs XML (voir aussi Section 9.14.1.7, « xmlagg ») |
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 renvoit 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.
![[Note]](images/note.png)
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 a 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 renvoit 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 être donné à ces agrégats.
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;
est exécuté par PostgreSQL™ à l'aide d'un parcours séquentiel de la table entière.
Les fonctions d'agrégat array_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, « Expressions d'agrégat ». 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;
Mais cette syntaxe n'est pas autorisée dans le standard SQL et n'est pas portable vers d'autres systèmes de bases de données.
Tableau 9.44, « Fonctions d'agrégats pour les statistiques » 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.44. Fonctions d'agrégats pour les statistiques
| Fonction | Type de l'argument | Type renvoyé | Description |
|---|---|---|---|
| corr(Y, X) | double precision | double precision | coefficient de corrélation |
| covar_pop(Y, X) | double precision | double precision | covariance de population |
| covar_samp(Y, X) | double precision | double precision | covariance exemple |
| regr_avgx(Y, X) | double precision | double precision | moyenne de la variable indépendante (sum(X)/N) |
| regr_avgy(Y, X) | double precision | double precision | moyenne de la variable dépendante (sum(Y) / N) |
| regr_count(Y, X) | double precision | bigint | nombre de lignes dans lesquelles les deux expressions sont non NULL |
| regr_intercept(Y, X) | double precision | double precision | interception de l'axe y pour l'équation linéaire de la méthode des moindres carrés déterminée par les paires (X, Y) |
| regr_r2(Y, X) | double precision | double precision | carré du coefficient de corrélation |
| regr_slope(Y, X) | double precision | double precision | inclinaison pour l'équation linéaire de la méthode des moindres carrés déterminée par les paires (X, Y) |
| regr_sxx(Y, X) | double precision | double precision | sum(X^2) - sum(X)^2 / N (« somme des carrés » de la variable indépendante) |
| regr_sxy(Y, X) | double precision | double precision | sum(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 | sum(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 | alias 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 | déviation standard 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 | exemple de déviation standard pour les valeurs en entrée |
| variance(expression) | smallint, int, bigint, real, double precision ou numeric | double precision pour les arguments en virgule flottante, numeric sinon | alias historique de var_samp |
| var_pop(expression) | smallint, int, bigint, real, double precision ou numeric | double precision pour les arguments en virgule flottante, numeric sinon | variance de la population pour les 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 en virgule flottante, numeric sinon | exemple de la variance des valeurs en entrée (carré de la déviation standard) |
Fonctions et opérateurs
de tableaux