Catégories :: Fonctions d’agrégation (général)

ACCUMULATE¶

Renvoie une valeur d’agrégation personnalisée calculée par 4 fonctions SQL lambda définies par l’utilisateur : initialiser, accumuler, combiner et terminer. ACCUMULATE suit le modèle d’agrégation « map-reduce » et s’intègre à GROUP BY,HAVING et les sous-requêtes de la même manière que les agrégats intégrés.

ACCUMULATE est particulièrement utile pour le prototypage et les agrégations ponctuelles qui ne couvrent pas les agrégats intégrés. Pour les charges de travail sensibles aux performances, préférez les agrégats intégrés lorsque cela est possible, ou demandez-vous si une combinaison d’agrégats intégrés et de jointures peut atteindre le même résultat. L’utilisation d’ACCUMULATE avec les types de statut OBJECT, ARRAY ou VARIANT entraîne une surcharge de performance supplémentaire.

Syntaxe¶

ACCUMULATE(
    <input_expr>,
    <initialize_lambda>,
    <accumulate_lambda>,
    <combine_lambda>,
    <terminate_lambda>
)

Arguments¶

input_expr: Expression évaluée une fois par ligne d’entrée non NULL. La valeur résultante est transmise à chaque lambda en tant que valeur d’entrée.
initialize_lambda: Lambda avec signature (value) -> <state_expr>. Appelé une fois par ligne d’entrée nonNULL pour produire l’état partiel initial à partir de la valeur de cette ligne.
accumulate_lambda: Lambda avec signature (state, value) -> <state_expr>. Combine une nouvelle valeur d’entrée dans un état partiel existant et renvoie l’état mis à jour.
combine_lambda: Lambda avec signature (state1, state2) -> <state_expr>. Fusionne deux états partiels produits par des travailleurs parallèles. Doit être associatif.
terminate_lambda: Lambda avec signature (state) -> <output_expr>. Convertit l’état fusionné final en valeur de résultat renvoyée à la requête.

Les noms des arguments lambda sont arbitraires. Les annotations de type sont facultatives ; voir Inférence de type pour plus de détails.

Renvoie¶

Renvoie la valeur produite par terminate_lambda. Le type de données correspond au type de renvoi de terminate_lambda. Renvoie NULL si toutes les lignes d’entrée sont NULL ou le jeu d’entrée est vide.

Notes sur l’utilisation¶

Les lignes d’entrée NULL sont ignorées silencieusement avant l’appel de toute lambda, conformément au comportement standard des agrégations SQL. Si toutes les lignes d’entrée sont NULL ou si le jeu d’entrée est vide, le résultat estNULL.
ACCUMULATE n’a pas de forme persistante. Il n’existe pas de CREATE AGGREGATE FUNCTION ou de DDL équivalent. Pour réutiliser une agrégation, encapsulez-la dans une vue, une CTE ou une procédure stockée.
Les lambdas ne peuvent faire référence qu’à leurs paramètres déclarés. Les références aux colonnes de la requête externe ne sont pas autorisées. Pour inclure une valeur de requête externe, projetez-la dans l’expression d’entrée ou précalculez-la dans une CTE.

L’exemple suivant provoque une erreur de compilation, car column2 n’est pas un paramètre lambda :
```
-- ERROR: column2 is a column reference, not a lambda parameter.
SELECT ACCUMULATE(
    column1,
    (v INT) -> v + column2,
    ...
) FROM t;
```
Les classes de fonctions suivantes ne sont pas autorisées dans les Lambdas :

Classe de fonction

Exemples

Fonctions d’agrégation

SUM, AVG, COUNT

Fonctions de fenêtre

ROW_NUMBER() OVER (…)

Fonctions non déterministes

RANDOM(), UUID_STRING()

Classe de fonction	Exemples
Fonctions d’agrégation	SUM, AVG, COUNT
Fonctions de fenêtre	ROW_NUMBER() OVER (…)
Fonctions non déterministes	RANDOM(), UUID_STRING()

Inférence de type¶

Les types d’argument Lambda sont facultatifs. S’ils sont omis, les types sont déduits de l’expression d’entrée et propagés à travers la chaîne lambda.

Les types explicites ne subissent pas de conversion forcé entre les lambdas. Si vous annotez un type sur un argument lambda, cette annotation fait autorité pour le type d’état à cette position. Si les annotations des quatre lambdas sont incohérentes et ne peuvent pas être réconciliées (par exemple, si initialize renvoie ARRAY mais qu’accumulate déclare l’état comme étant INT), la compilation échoue.
Vous pouvez omettre toutes les annotations de type ou n’annoter que certains arguments ; le compilateur déduit et élargit les types automatiquement. Le mélange d’arguments annotés et non annotés est autorisé à condition que les annotations explicites soient cohérentes.
Le type d’état (le type qui circule entre initialize, accumulate et combine) et le type de sortie (le type de retour de terminate) sont suivis indépendamment et peuvent différer.
L’expression d’entrée est implicitement convertie dans le type attendu par l’argument value de la lambda initialize. Par exemple, si la colonne d’entrée est INT et que value est déclaré comme STRING, la conversion est appliquée automatiquement.

Exemples¶

Calculer la somme d’une colonne, en simulant le comportement équivalent de la fonction d’agrégation SUM(c1) existante :

SELECT
    ACCUMULATE(
        c1,
        (v INT)                      -> v,
        (state INT, v INT)           -> state + v,
        (state1 INT, state2 INT)     -> state1 + state2,
        (state INT)                  -> state
    ) AS total
FROM t;

Calculer le produit de toutes les valeurs d’un groupe.

SELECT
    ACCUMULATE(
        c1,
        (v INT)                      -> v,
        (state INT, v INT)           -> state * v,
        (state1 INT, state2 INT)     -> state1 * state2,
        (state INT)                  -> state
    ) AS total
FROM t;

Calculer la moyenne à l’aide d’un ARRAY pour suivre la somme cumulée et le décompte :

SELECT ACCUMULATE(
    c1,
    (v INT)                        -> [v, 1],
    (state ARRAY, v INT)           -> [state[0] + v, state[1] + 1],
    (state1 ARRAY, state2 ARRAY)   -> [state1[0] + state2[0], state1[1] + state2[1]],
    (state ARRAY)                  -> state[0] / state[1]
) AS mean
FROM t;

Calculer la moyenne à l’aide d’un OBJECT structuré en tant qu’état :

SELECT ACCUMULATE(
    c1,
    (v INT) -> {'sum': v, 'count': 1}::OBJECT(sum INT, count INT),
    (state OBJECT(sum INT, count INT), v) ->
        {'sum': state:sum + v, 'count': state:count + 1}::OBJECT(sum INT, count INT),
    (state1, state2) ->
        {'sum': state1:sum + state2:sum, 'count': state1:count + state2:count}::OBJECT(sum INT, count INT),
    (state) -> state:sum / state:count
) AS mean
FROM t;

Rechercher la chaîne la plus courte dans une colonne :

SELECT ACCUMULATE(
    c1,
    (v STRING) -> v,
    (state STRING, v STRING) ->
        CASE WHEN LENGTH(v) < LENGTH(state) THEN v ELSE state END,
    (state1 STRING, state2 STRING) ->
        CASE WHEN LENGTH(state1) <= LENGTH(state2) THEN state1 ELSE state2 END,
    (state STRING) -> state
) AS shortest
FROM t;

Utiliser ACCUMULATE avecGROUP BY :

SELECT
    category,
    ACCUMULATE(
        amount,
        (v INT)                      -> v,
        (state INT, v INT)           -> state + v,
        (state1 INT, state2 INT)     -> state1 + state2,
        (state INT)                  -> state
    ) AS category_total
FROM orders
GROUP BY category;

Appeler une UDF à l’intérieur d’une lambda :

SELECT ACCUMULATE(
    c1,
    (v NUMBER)       -> v,
    (state, v)       -> my_sum_udf(state, v),
    (state1, state2) -> my_sum_udf(state1, state2),
    (state)          -> state
) AS total
FROM t;