Dans PostgreSQL, les fonctions d'agrégat sont exprimées comme des valeurs d'état et des fonctions de transition d'état. C'est-à-dire qu'un agrégat opère en utilisant une valeur d'état qui est mis à jour à chaque ligne traitée. Pour définir une nouvelle fonction d'agrégat, on choisit un type de donnée pour la valeur d'état, une valeur initiale pour l'état et une fonction de transition d'état. La fonction de transition d'état prend la valeur d'état précédente et les valeurs en entrée de l'agrégat pour la ligne courante, et renvoie une nouvelle valeur d'état. Une fonction finale peut également être spécifiée pour le cas où le résultat désiré comme agrégat est différent des données conservées comme valeur d'état courant. La fonction finale prend la dernière valeur de l'état, et renvoie ce qui est voulu comme résultat de l'agrégat. En principe, les fonctions de transition et finale sont des fonctions ordinaires qui pourraient aussi être utilisées en dehors du contexte de l'agrégat. (En pratique, il est souvent utile pour des raisons de performance de créer des fonctions de transition spécialisées qui ne peuvent fonctionner que quand elles sont appelées via l'agrégat.)
Ainsi, en plus des types de données d'argument et de résultat vus par l'utilisateur, il existe un type de données pour la valeur d'état interne qui peut être différent des deux autres.
Un agrégat qui n'utilise pas de fonction finale est un agrégat qui utilise
pour chaque ligne une fonction dépendante des valeurs de colonnes.
sum
en est un exemple.
sum
débute à zéro et ajoute la valeur de la ligne
courante à son total en cours. Par exemple, pour obtenir un agrégat
sum
qui opère sur un type de données nombres
complexes, il suffira décrire la fonction d'addition pour ce type de donnée.
La définition de l'agrégat sera :
CREATE AGGREGATE somme (complex) ( sfunc = ajout_complexe, stype = complexe, initcond = '(0,0)' );
que nous pourrions utiliser ainsi :
SELECT somme(a) FROM test_complexe; somme ----------- (34,53.9)
(Notez que nous nous reposons sur une surcharge de fonction : il existe
plus d'un agrégat nommé sum
mais
PostgreSQL trouve le type de somme s'appliquant à
une colonne de type complex
.)
La définition précédente de sum
retournera zéro (la
condition d'état initial) s'il n'y a que des valeurs d'entrée NULL.
Dans ce cas, on peut souhaiter qu' elle retourne NULL -- le
standard SQL prévoit que la fonction sum
se comporte
ainsi. Cela peut être obtenu par l'omission de l'instruction
initcond
, de sorte que la condition d'état initial soit
NULL.
Dans ce cas, sfunc
vérifie l'entrée d'une condition
d'état NULL mais, pour sum
et quelques
autres agrégats simples comme max
et min
, il
suffit d'insérer la première valeur d'entrée non NULL dans la variable d'état
et d'appliquer la fonction de transition d'état à partir de la
seconde valeur non NULL.
PostgreSQL fait cela automatiquement si la
condition initiale est NULL et si la fonction de transition est marquée
« strict » (elle n'est pas appelée pour les entrées NULL).
Par défaut également, pour les fonctions de transition « strict », la valeur d'état précédente reste inchangée pour une entrée NULL. Les valeurs NULL sont ainsi ignorées. Pour obtenir un autre comportement, il suffit de ne pas déclarer la fonction de transition « strict ». À la place, codez-la de façon à ce qu'elle vérifie et traite les entrées NULL.
avg
(average = moyenne) est un exemple plus complexe d'agrégat.
Il demande deux états courants : la somme des entrées et le
nombre d'entrées. Le résultat final est obtenu en divisant ces quantités. La
moyenne est typiquement implantée en utilisant comme valeur d'état un
tableau. Par exemple, l'implémentation intégrée de
avg(float8)
ressemble à :
CREATE AGGREGATE avg (float8) ( sfunc = float8_accum, stype = float8[], finalfunc = float8_avg, initcond = '{0,0,0}' );
float8_accum
nécessite un tableau à trois éléments,
et non pas seulement deux, car il accumule la somme des carrés, ainsi que
la somme et le nombre des entrées. Cela permet son utilisation pour
d'autres agrégats que avg
.
Les appels de fonctions d'agrégat en SQL autorisent les options
DISTINCT
et ORDER BY
qui contrôlent
les lignes envoyées à la fonction de transition de l'agrégat et leur ordre.
Ces options sont implémentées en arrière plan et ne concernent pas les
fonctions de support de l'agrégat.
Pour plus de détails, voir la commande CREATE AGGREGATE.
Les fonctions d'agrégat peuvent accepter en option un mode
d'agrégat en déplacement, qui autorise une exécution bien plus
rapide des fonctions d'agrégats pour les fenêtre dont le point de démarrage
se déplace.
(Voir Section 3.5 et
Section 4.2.8 pour des informations sur
l'utilisation des fonctions d'agrégats en tant que fonctions de fenêtrage.)
L'idée de base est qu'en plus d'une fonction de transition « en
avant », l'agrégat fournir une fonction de transition
inverse, qui permet aux lignes d'être supprimées de la valeur
d'état de l'agrégat quand elles quittent l'étendue de la fenêtre. Par
exemple, un agrégat sum
qui utilise l'addition comme
fonction de transition en avant pourrait utiliser la soustraction comme
fonction de transition inverse. Sans fonction de transition inverse, le
mécanisme de fonction de fenêtrage doit recalculer l'agrégat à partir du
début à chaque fois que le point de départ de la fenêtre est déplacé, ce
qui a pour effet d'augmenter la durée d'exécution proportionnellement au
nombre de lignes en entrée multiplé à la longueur moyenne de la fenêtre.
Avec une fonction de transition inverse, la durée d'exécution est uniquement
proportionnelle au nombre de lignes en entrée.
La fonction de transition inverse se voit fourni la valeur de l'état courant et les valeurs en entrée de l'agrégat pour la première ligne inclus dans l'état courant. Il doit reconstruire la valeur d'état telle qu'elle aurait été si la ligne en entrée n'avait pas été agrégé, mais seulement les lignes suivantes. Ceci demande parfois que la fonction de transition en avant conserve plus d'informations sur l'état que ce qui était nécessaire auparavant. De ce fait, le mode d'agrégat en déplacement utilise une implémentation complètement séparée du mode standard : il a son propre type de données d'état, sa propre fonction de transition en avant, et sa propre fonction finale si nécessaire. Ils peuvent être les mêmes que le type de données et les fonctions du mode standard si rien de particulier n'est nécessaire.
Comme exemple, nous pouvons étendre l'agrégat sum
donné
ci-dessus pour supporter le mode d'agrégat en déplacement, comme ceci :
CREATE AGGREGATE somme (complex) ( sfunc = ajout_complexe, stype = complexe, initcond = '(0,0)', msfunc = ajout_complexe, minvfunc = retire_complexe, mstype = complexe, minitcond = '(0,0)' );
Les paramètres dont les noms commencent par un m
définissent
l'implémentation des agrégats en mouvement. En dehors de la fonction de
transition inverse minvfunc
, ils correspondent aux
paramètres des agrégats standards sans m
.
La fonction de transition en avant pour le mode d'agrégat en déplacement
n'est pas autorisée à renvoyer NULL comme nouvelle valeur d'état. Si la
fonction de transition inverse renvoie NULL, c'est pris comme indication
que la fonction ne peut pas inverser le calcul de l'état sur ce point
particulier, et donc le calcul d'agrégat sera refait à partir de rien pour
la position de début actuelle. Cette convention permet au mode d'agrégat
par déplacement à être utilisé dans des situations où il existe quelques cas
rares où réaliser l'inverse de la fonction de transition n'est pas
possible. La fonction de transition inverse peut ne pas fonctionner sur ces
cas, et être toujours utilisée pour la plupart des cas où elle est
fonctionnelle. Comme exemple, un agrégat travaillant avec des nombres à
virgules flottantes pourrait choisir de ne pas fonctionner quand une entrée
NaN
doit être supprimée de la valeur d'état en cours.
Lors de l'écriture des fonctions de support d'un agrégat en déplacement, il
est important d'être certain que la fonction de transition inverse peut
reconstruire exactement la valeur d'état correct. Sinon, il pourrait y avoir
des différences visibles pour l'utilisateur dans les résultats, suivant que
le mode d'agrégat en déplacement est utilisé ou pas. Un exemple d'agrégat
pour lequel ajouter une fonction de transition inverse semble facile au premier
coup d'œil, mais où les prérequis ne peuvent pas être assurés, est la fonction
is sum
sur des entrées de type float4
ou
float8
. Une déclaration naïve de
sum(
pourrait être :
float8
)
CREATE AGGREGATE unsafe_sum (float8) ( stype = float8, sfunc = float8pl, mstype = float8, msfunc = float8pl, minvfunc = float8mi );
Cependant, cet agrégat peut renvoyer des résultats très différents qu'il ne l'aurait fait sans fonction de transition inverse. Considérez par exemple :
SELECT unsafe_sum(x) OVER (ORDER BY n ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) FROM (VALUES (1, 1.0e20::float8), (2, 1.0::float8)) AS v (n,x);
Cette requête renvoie 0
en deuxième résultat, plutôt que
la réponse attendue, 1
. La raison vient de la précision
limitée des valeurs en virgule flottante : ajouter 1
à 1e20
renvoie de nouveau 1e20
, alors
qu'y soustraire 1e20
renvoie 0
, et
non pas 1
. Notez que c'est une limitation générale des
opérations de calcul sur des nombres en virgule flottante, pas une limitation
spécifique de PostgreSQL.
Les fonctions d'agrégat peuvent utiliser des fonctions d'état transitionnelles ou des fonctions finales polymorphes. De cette façon, les mêmes fonctions peuvent être utilisées pour de multiples agrégats. Voir la Section 38.2.5 pour une explication des fonctions polymorphes. La fonction d'agrégat elle-même peut être spécifiée avec un type de base et des types d'état polymorphes, ce qui permet ainsi à une unique définition de fonction de servir pour de multiples types de données en entrée. Voici un exemple d'agrégat polymorphe :
CREATE AGGREGATE array_accum (anycompatible) ( sfunc = array_append, stype = anycompatiblearray, initcond = '{}' );
Dans ce cas, le type d'état effectif pour tout appel d'agrégat est le type
tableau avec comme éléments le type effectif d'entrée. Le comportement de
l'agrégat est de concaténer toutes les entrées dans un tableau de ce type.
(Note : l'agrégat array_agg
fournit une
fonctionnalité similaire, avec de meilleures performances que ne pourrait
avoir cette définition.)
Voici le résultat pour deux types de données différents en arguments :
SELECT attrelid::regclass, array_accum(attname) FROM pg_attribute WHERE attnum > 0 AND attrelid = 'pg_tablespace'::regclass GROUP BY attrelid; attrelid | array_accum ---------------+--------------------------------------- pg_tablespace | {spcname,spcowner,spcacl,spcoptions} (1 row) SELECT attrelid::regclass, array_accum(atttypid::regtype) FROM pg_attribute WHERE attnum > 0 AND attrelid = 'pg_tablespace'::regclass GROUP BY attrelid; attrelid | array_accum ---------------+--------------------------- pg_tablespace | {name,oid,aclitem[],text[]} (1 row)
D'habitude, une fonction d'agrégat avec un type de résultat polymorphique
a un type d'état polymorphique, comme dans l'exemple ci-dessus. C'est
nécessaire, sinon la fonction finale ne peut pas être déclarée correctement.
Elle devrait avoir un type de résultat polymorphique mais pas d'argument
polymorphique, ce que CREATE FUNCTION
rejetera sur la base
que le type en résultat ne peut pas être déduit de cet appel. Cependant,
quelque fois, il est inconfortable d'utiliser un type d'état polymorphique.
Le cas le plus fréquent arrive quand les fonctions de support de l'agrégat
sont à écrire en C et que le type d'état doit être déclaré comme
internal
parce qu'il n'existe pas d'équivalent SQL pour lui.
Dans ce cas, il est possible de déclarer la fonction finale comme prenant
des arguments « inutiles » qui correspondent aux arguments en
entrée de l'agrégat. Ce type d'argument est toujours passé avec une valeur
NULL car aucune valeur spécifique n'est disponible quand la fonction finale
est appelée. Leur seule utilisée est de permettre à un type de résultat
d'une fonction finale polymorphique d'être connecté au type de données en
entrée de l'agrégat. Par exemple, la définition de l'agrégat interne
array_agg
est équivalent à :
CREATE FUNCTION array_agg_transfn(internal, anynonarray) RETURNS internal ...; CREATE FUNCTION array_agg_finalfn(internal, anynonarray) RETURNS anyarray ...; CREATE AGGREGATE array_agg (anynonarray) ( sfunc = array_agg_transfn, stype = internal, finalfunc = array_agg_finalfn, finalfunc_extra );
Dans cet exemple, l'option finalfunc_extra
spécifie que
la fonction finale reçoit, en plus de la valeur d'état, tout argument
supplémentaire correspondant aux arguments en entrée de l'agrégat.
L'argument supplémentaire anynonarray
permet que la déclaration
de array_agg_finalfn
soit valide.
Il est possible de créer une fonction d'agrégat qui accepte un nombre variable
d'arguments en déclarant ses derniers arguments dans un tableau
VARIADIC
, un peu de la même façon que les fonctions
standards ; voir Section 38.5.6. La
fonction de transition de l'agrégat doit avoir le même type tableau que leur
dernier argument. Les fonctions de transition seront typiquement marquées
comme VARIADIC
, mais cela n'est pas requis.
Les agrégats variadiques sont facilement mal utilisés avec l'option
ORDER BY
(voir Section 4.2.7), car
l'analyseur ne peut pas dire si le nombre d'arguments réels donnés était
bon ou pas. Gardez à l'esprit que toutes les expressions à droite de
ORDER BY
sont la clé de tri, pas un argument de
l'agrégat. Par exemple, dans :
SELECT mon_agregat(a ORDER BY a, b, c) FROM ...
l'analyseur verra cela comme un seul argument pour la fonction d'agrégat, et trois clés de tri. Alors que l'utilisateur pouvait vouloir dire :
SELECT myaggregate(a, b, c ORDER BY a) FROM ...
Si mon_agregat
est variadique, ces deux appels peuvent
être parfaitement valides.
Pour la même raison, il est conseillé d'y réfléchir à deux fois avant de créer des fonctions d'agrégat avec les mêmes noms et différents nombres d'arguments standards.
Les agrégats que nous avons décrit jusqu'à maintenant sont des agrégats
« normaux ». PostgreSQL accepte aussi
les agrégats d'ensemble trié, qui diffèrent des
agrégats normaux de deux façons. Tout d'abord, en plus des arguments
standards d'agrégats qui sont évalués une fois par ligne en entrée, un
agrégat d'ensemble trié peut avoir des arguments « directs »
qui sont évalués seulement une fois par opération d'agrégation. Ensuite,
la syntaxe pour les arguments standards agrégés indique un ordre de
tri explicitement pour eux. Un agrégat d'ensemble de tri est habituellement
utilisé pour ajouter un calcul dépendant d'un ordre spécifique des lignes,
par exemple le rang ou le centile. Par exemple, la définition interne de
percentile_disc
est équivalent à :
CREATE FUNCTION ordered_set_transition(internal, anyelement) RETURNS internal ...; CREATE FUNCTION percentile_disc_final(internal, float8, anyelement) RETURNS anyelement ...; CREATE AGGREGATE percentile_disc (float8 ORDER BY anyelement) ( sfunc = ordered_set_transition, stype = internal, finalfunc = percentile_disc_final, finalfunc_extra );
Cet agrégat prend un argument direct float8
(la fraction du
percentile) et une entrée agrégée qui peut être de toute type de données
triées. Il pourrait être utilisé pour obtenir le revenu médian des ménages
comme ceci :
SELECT percentile_disc(0.5) WITHIN GROUP (ORDER BY revenu) FROM menages; percentile_disc ----------------- 50489
Ici, 0.5
est un argument direct ; cela n'aurait pas
de sens que la fraction de centile soit une valeur variant suivant les
lignes.
Contrairement aux agrégats normaux, le tri des lignes en entrée pour un
agrégat d'ensemble trié n'est pas fait de façon
caché mais est la responsabilité des fonctions de support de l'agrégat.
L'approche typique de l'implémentation est de conserver une référence
à l'objet « tuplesort » dans la valeur d'état de l'agrégat,
d'alimenter cet objet par les lignes en entrée, et de terminer le tri
et de lire les données dans la fonction finale. Ce design permet à la
fonction finale de réaliser des opérations spéciales comme l'injection
de lignes supplémentaires « hypothétiques » dans les données
à trier. Alors que les agrégats normaux peuvent souvent être implémentés
avec les fonctions de support écrites en
PL/pgSQL ou dans un autre langage PL, les
agrégats d'ensemble trié doivent généralement être écrit en C car leurs
valeurs d'état ne sont pas définissables sous la forme de type de données
SQL. (Dans l'exemple ci-dessus, notez que la valeur d'état est déclarée
en tant que internal
-- c'est typique.)
De plus, comme la fonction finale réalise le tri, il n'est pas possible de
continuer à ajouter des lignes en entrée en exécutant de nouveau la
fonction de transition. Ceci signifie que la fonction finale n'est pas
READ_ONLY
; elle doit être exécutée dans
CREATE AGGREGATE
en READ_WRITE
ou en
SHAREABLE
s'il est possible que des appels
supplémentaires à la fonction finale utilisent l'état déjà triée.
La fonction de transition d'état pour un agrégat d'ensemble trié reçoit la
valeur d'état courante ainsi que les valeurs agrégées en entrée pour chaque
ligne. Elle renvoie la valeur d'état mise à jour. Il s'agit de la même
définition que pour les agrégats normaux mais notez que les arguments
directs (si présents) ne sont pas fournis. La fonction finale reçoit la
valeur du dernier état, les valeurs des arguments directs si présents et
(si finalfunc_extra
est indiqué) des valeurs NULL
correspondant aux entrées agrégées. Comme avec les agrégats normaux,
finalfunc_extra
est seulement réellement utile si
l'agrégat est polymorphique ; alors les arguments inutiles
supplémentaires sont nécessaires pour connecter le type de résultat de
la fonction finale au type de l'entrée de l'agrégat.
Actuellement, les agrégats d'ensemble trié ne peuvent pas être utilisé comme fonctions de fenêtrage, et du coup, il n'est pas nécessaire qu'ils supportent le mode d'agrégat en déplacement.
En option, une fonction d'agrégat peut supporter une agrégation partielle. L'idée d'agrégation partielle est d'exécuter la fonction de transition d'état de l'agrégat sur différents sous-ensembles des données en entrée de façon indépendante, puis de combiner les valeurs d'état provenant de ces sous-ensembles pour produire la même valeur d'état que ce qui aurait résultat du parcours de toutes les entrées en une seule opération. Ce mode peut être utilisé pour l'agrégation parallèle en ayant différents processus parallèles parcourant des portions différentes d'une table. Chaque processus produit une valeur d'état partiel et, à la fin, ces valeurs d'état sont combinées pour produire une valeur d'état finale. (Dans le futur, ce mode pourrait aussi être utilisé dans d'autres cas comme l'agrégation combinée sur des tables locales et externes ; mais ce n'est pas encore implémenté.)
Pour supporter une agrégation partielle, la définition de l'agrégat doit fournir une fonction de combinaison, qui prend deux valeurs du type de l'état d'agrégat (représentant les résultats de l'agrégat sur deux sous-ensembles de lignes en entrée) et produit une nouvelle valeur du type de l'état, représentant l'état qu'on aurait eu en réalisant l'agrégat sur la combinaison de ces deux ensembles de données. L'ordre relatif des lignes entrées n'est pas spécifié pour les deux ensembles de données. Ceci signifie qu'il est habituellement impossible de définir une fonction de combinaison utile pour les agrégats sensibles à l'ordre des lignes en entrée.
Comme exemples simples, les agrégats MAX
et
MIN
peuvent supporter l'agrégation partielle en
indiquant la fonction de combinaison comme étant la même fonction plus-
grand-que ou plus-petit-que que celle utilisée comme fonction de transition
function. L'agrégat SUM
a besoin d'une fonction
supplémentaire comme fonction de combinaison. (Encore une fois, c'est la
même que leur fonction de transition, sauf si la valeur d'état est plus
grand que le type de données en entrée.)
La fonction de combinaison est traitée un peu comme une fonction de
transition qui prend une valeur du type d'état, pas de celle du type
d'entrée sous-jacent, comme deuxième argument. En particulier, les règles
pour gérer les valeurs nulles et les fonctions strictes sont similaires. De
plus, si la définition de l'agrégat indique un initcond
non nul, gardez en tête que ce sera utilisé non seulement comme état
initial pour chaque exécution de l'agrégat partiel, mais aussi comme état
initiale de la fonction de combinaison, qui sera appelée pour combiner
chaque résultat partiel dans cet état.
Si le type d'état de l'agrégat est déclaré comme internal
, il
est de la responsabilité de la fonction de combinaison que son résultat
soit alloué dans le contexte mémoire correct pour les valeurs d'état de
l'agrégat. Ceci signifie en particulier que, quand la première entrée est
NULL
, il est invalide de renvoyer simplement la deuxième
entrée car cette valeur sera dans le mauvais contexte et n'aura pas une
durée de vie suffisante.
Quand le type d'état de l'agrégat est déclaré comme internal
,
il est aussi habituellement approprié que la définition de l'agrégat
fournisse une fonction de sérialisation et une
fonction de désérialisation, qui permet qu'une telle
valeur d'état soit copiée d'un processus à un autre. Sans ces fonctions,
l'agrégation parallèle ne peut pas être réalisée, et les applications
futures telles que l'agrégation locale/distante ne fonctionnera
probablement pas non plus.
Une fonction de sérialisation doit prendre un seul argument de type
internal
et renvoyer un résultat de type bytea
,
qui représente la valeur d'état packagé en un paquet plat d'octets. De la
même façon, une fonction de désérialisation inverse cette conversion. Elle
doit prendre deux arguments de type bytea
et
internal
, et renvoyer un résultat de type
internal
. (Le deuxième argument n'est pas utilisé et vaut
toujours zéro, mais il est requis pour des raisons de sécurité du type.) Le
résultat de la fonction de désérialisation doit simplement être alloué dans
le contexte mémoire courant car, contrairement au résultat de la fonction
de combinaison, il ne vit pas longtemps.
Il est bon de noter aussi que, pour qu'un agrégat soit exécuté en
parallèle, l'agrégat lui-même doit être marqué PARALLEL
SAFE
. Les marques de parallélisation sur les fonctions de support
ne sont pas consultées.
Une fonction écrite en C peut détecter si elle est appelée en tant que
fonction de support d'un agrégat en
appelant AggCheckCallContext
, par exemple :
if (AggCheckCallContext(fcinfo, NULL))
Une raison de surveiller ceci est que, si le retour de cette fonction
vaut true, la première valeur doit être
une valeur de transition temporaire et peut du coup être modifiée en toute
sûreté sans avoir à allouer une nouvelle copie. Voir
int8inc()
pour un exemple.
(Alors que les fonctions de transition des agrégats sont toujours autorisées
à modifier en ligne la valeur de transition, les fonctions finales des
agrégats ne sont généralement pas encouragées à le faire ; si elles le
font, le comportement doit être déclaré lors de la création de l'agrégat. Voir
CREATE AGGREGATE pour plus de détails.)
Le deuxième argument de AggCheckCallContext
peut être
utilisé pour récupérer le contexte mémoire dans lequel les valeurs d'état
de l'agrégat sont conservées. Ceci est utile pour que les fonctions de
transition qui souhaitent utiliser les objets « étendus » (voir
Section 38.13.1) comme leurs valeurs d'état. Au premier
appel, la fonction de transition doit renvoyer un objet étendu dont le
contexte mémoire est un enfant du contexte d'état de l'agrégat. Puis, pour
les appels suivants, il doit renvoyer le même objet étendu. Voir
array_append()
pour un exemple.
(array_append()
n'est pas la fonction de transition
d'un agrégat interne mais il est écrit pour se comporter efficacement
lorsqu'elle est utilisée comme fonction de transition d'un agrégat
personnalisé.)
Une autre routine de support disponible pour les fonctions d'agrégat écrites
en langage C est AggGetAggref
, qui renvoie le nœud
d'analyse Aggref
qui définit l'appel d'agrégat. Ceci est
particulièrement utile pour les agrégats d'ensemble trié, qui peuvent
inspecter la sous-structure du nœud Aggref
pour trouver
l'ordre de tri qu'elles sont supposées implémenter. Des exemples sont
disponibles dans le fichier orderedsetaggs.c
du code
source de PostgreSQL.