Fonctions d'agrégation définies par l'utilisateur

Pour obtenir de l'aide pendant la version preview, envoyez un e-mail à bigquery-sql-preview-support@google.com.

Ce document explique comment créer, appeler et supprimer des fonctions d'agrégation définies par l'utilisateur (UDAF) dans BigQuery.

Une fonction UDAF vous permet de créer une fonction d'agrégation à l'aide d'une expression contenant du code. Une fonction UDAF accepte des colonnes d'entrée, effectue un calcul sur un groupe de lignes à la fois, puis renvoie le résultat de ce calcul sous forme de valeur unique.

Créer une fonction UDAF SQL

Cette section décrit les différentes manières de créer une fonction UDAF SQL persistante ou temporaire dans BigQuery.

Créer une fonction UDAF SQL persistante

Vous pouvez créer une fonction UDAF SQL persistante, ce qui signifie que vous pouvez la réutiliser dans plusieurs requêtes. Les fonctions UDAF persistantes peuvent être appelées en toute sécurité lorsqu'elles sont partagées entre propriétaires. Les fonctions UDAF ne peuvent pas muter des données, communiquer avec des systèmes externes ni envoyer de journaux à Google Cloud Observability ou à des applications similaires.

Pour créer une fonction UDAF persistante, utilisez l'instruction CREATE AGGREGATE FUNCTION sans le mot clé TEMP ou TEMPORARY. Vous devez inclure l'ensemble de données dans le chemin de la fonction.

Par exemple, la requête suivante crée une fonction UDAF persistante appelée ScaledAverage :

CREATE AGGREGATE FUNCTION myproject.mydataset.ScaledAverage(
  dividend FLOAT64,
  divisor FLOAT64)
RETURNS FLOAT64
AS (
  AVG(dividend / divisor)
);

Créer une fonction UDAF SQL temporaire

Vous pouvez créer une fonction UDAF SQL temporaire, ce qui signifie que la fonction UDAF n'existe que dans le champ d'application d'une requête, d'un script, d'une session ou d'une procédure.

Pour créer une fonction UDAF temporaire, utilisez l'instruction CREATE AGGREGATE FUNCTION avec le mot clé TEMP ou TEMPORARY.

Par exemple, la requête suivante crée une fonction UDAF temporaire appelée ScaledAverage :

CREATE TEMP AGGREGATE FUNCTION ScaledAverage(
  dividend FLOAT64,
  divisor FLOAT64)
RETURNS FLOAT64
AS (
  AVG(dividend / divisor)
);

Utiliser des paramètres agrégés et non agrégés

Vous pouvez créer une fonction UDAF SQL comportant des paramètres agrégés et non agrégés.

Les fonctions UDAF agrègent normalement les paramètres de fonction sur toutes les lignes d'un groupe. Toutefois, vous pouvez spécifier un paramètre de fonction en tant que non agrégé avec le mot clé NOT AGGREGATE.

Un paramètre de fonction non agrégé est un paramètre de fonction scalaire avec une valeur constante pour toutes les lignes d'un groupe. Un paramètre de fonction non agrégé valide doit être un littéral. Dans la définition UDAF, les paramètres de fonction d'agrégation ne peuvent apparaître que comme arguments de fonction pour agréger les appels de fonction. Les références à des paramètres de fonction non agrégés peuvent apparaître n'importe où dans la définition UDAF.

Par exemple, la fonction suivante contient un paramètre agrégé appelé dividend et un paramètre non agrégé appelé divisor :

-- Create the function.
CREATE TEMP AGGREGATE FUNCTION ScaledSum(
  dividend FLOAT64,
  divisor FLOAT64 NOT AGGREGATE)
RETURNS FLOAT64
AS (
  SUM(dividend) / divisor
);

Utiliser le projet par défaut dans le corps de la fonction

Dans le corps d'une UDAF SQL, les références aux entités BigQuery, telles que les tables ou les vues, doivent inclure l'ID du projet, sauf si l'entité réside dans le projet qui exécute la fonction UDAF.

Prenons l'exemple de l'instruction suivante :

CREATE AGGREGATE FUNCTION project1.dataset_a.ScaledAverage(
  dividend FLOAT64,
  divisor FLOAT64)
RETURNS FLOAT64
AS (
  ( SELECT AVG(dividend / divisor) FROM dataset_a.my_table )
);

Si vous exécutez l'instruction précédente dans le projet project1, l'instruction aboutit, car my_table existe dans project1. Toutefois, si vous exécutez l'instruction précédente à partir d'un autre projet, elle échoue. Pour corriger l'erreur, incluez l'ID du projet dans la référence de table :

CREATE AGGREGATE FUNCTION project1.dataset_a.ScaledAverage(
  dividend FLOAT64,
  divisor FLOAT64)
RETURNS FLOAT64
AS (
  ( SELECT AVG(dividend / divisor) FROM project1.dataset_a.my_table )
);

Vous pouvez également référencer une entité dans un projet ou un ensemble de données différent de celui dans lequel vous créez la fonction :

CREATE AGGREGATE FUNCTION project1.dataset_a.ScaledAverage(
  dividend FLOAT64,
  divisor FLOAT64)
RETURNS FLOAT64
AS (
  ( SELECT AVG(dividend / divisor) FROM project2.dataset_c.my_table )
);

Créer un UDAF JavaScript

Cette section décrit les différentes manières de créer un UDAF JavaScript dans BigQuery. Il existe quelques règles à observer lors de la création d'un UDAF JavaScript :

• Le corps de l'UDAF JavaScript doit être un littéral de chaîne entre guillemets, qui représente le code JavaScript. Pour en savoir plus sur les différents types de littéraux de chaîne entre guillemets que vous pouvez utiliser, consultez la section Formats des littéraux entre guillemets.

• Seuls certains encodages de types sont autorisés. Pour en savoir plus, consultez la section Encodages de types SQL autorisés dans un fichier UDAF JavaScript.

• Le corps de la fonction JavaScript doit inclure quatre fonctions JavaScript qui initialisent, agrégent, fusionnent et finalisent les résultats pour l'UDAF JavaScript (initialState, aggregate, merge et finalize). Pour en savoir plus, consultez les pages suivantes : Encodages de types SQL autorisés dans une fonction UDAF JavaScript.

• Toute valeur renvoyée par la fonction initialState ou laissée dans l'argument state après l'appel de la fonction aggregate ou merge doit être sérialisable. Si vous souhaitez utiliser des données d'agrégation non sérialisables, telles que des fonctions ou des champs de symboles, vous devez utiliser les fonctions serialize et deserialize incluses. Pour en savoir plus, consultez la section Sérialiser et désérialiser des données dans une fonction UDAF JavaScript.

Créer un UDAF JavaScript persistant

Vous pouvez créer une fonction UDAF JavaScript persistante, ce qui signifie que vous pouvez la réutiliser dans plusieurs requêtes. Les fonctions UDAF persistantes peuvent être appelées en toute sécurité lorsqu'elles sont partagées entre propriétaires. Les fonctions UDAF ne peuvent pas muter des données, communiquer avec des systèmes externes ni envoyer de journaux à Google Cloud Observability ou à des applications similaires.

Pour créer une fonction UDAF persistante, utilisez l'instruction CREATE AGGREGATE FUNCTION sans le mot clé TEMP ou TEMPORARY. Vous devez inclure l'ensemble de données dans le chemin de la fonction.

La requête suivante crée un UDAF JavaScript persistant appelé SumPositive :

CREATE OR REPLACE AGGREGATE FUNCTION my_project.my_dataset.SumPositive(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS r'''

  export function initialState() {
    return {sum: 0}
  }
  export function aggregate(state, x) {
    if (x > 0) {
      state.sum += x;
    }
  }
  export function merge(state, partialState) {
    state.sum += partialState.sum;
  }
  export function finalize(state) {
    return state.sum;
  }

''';

-- Call the JavaScript UDAF.
WITH numbers AS (
  SELECT * FROM UNNEST([1.0, -1.0, 3.0, -3.0, 5.0, -5.0]) AS x)
SELECT my_project.my_dataset.SumPositive(x) AS sum FROM numbers;

/*-----*
 | sum |
 +-----+
 | 9.0 |
 *-----*/

Créer un UDAF JavaScript temporaire

Vous pouvez créer une fonction UDAF JavaScript temporaire, ce qui signifie que la fonction UDAF n'existe que dans le champ d'application d'une requête, d'un script, d'une session ou d'une procédure.

Pour créer une fonction UDAF temporaire, utilisez l'instruction CREATE AGGREGATE FUNCTION avec le mot clé TEMP ou TEMPORARY.

La requête suivante crée un UDAF JavaScript temporaire appelé SumPositive :

CREATE TEMP AGGREGATE FUNCTION SumPositive(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS r'''

  export function initialState() {
    return {sum: 0}
  }
  export function aggregate(state, x) {
    if (x > 0) {
      state.sum += x;
    }
  }
  export function merge(state, partialState) {
    state.sum += partialState.sum;
  }
  export function finalize(state) {
    return state.sum;
  }

''';

-- Call the JavaScript UDAF.
WITH numbers AS (
  SELECT * FROM UNNEST([1.0, -1.0, 3.0, -3.0, 5.0, -5.0]) AS x)
SELECT SumPositive(x) AS sum FROM numbers;

/*-----*
 | sum |
 +-----+
 | 9.0 |
 *-----*/

Inclure des paramètres non agrégés dans un UDAF JavaScript

Vous pouvez créer une fonction UDAF JavaScript comportant des paramètres agrégés et non agrégés.

Les fonctions UDAF agrègent normalement les paramètres de fonction sur toutes les lignes d'un groupe. Toutefois, vous pouvez spécifier un paramètre de fonction en tant que non agrégé avec le mot clé NOT AGGREGATE.

Un paramètre de fonction non agrégé est un paramètre de fonction scalaire avec une valeur constante pour toutes les lignes d'un groupe. Un paramètre de fonction non agrégé valide doit être un littéral. Dans la définition UDAF, les paramètres de fonction d'agrégation ne peuvent apparaître que comme arguments de fonction pour agréger les appels de fonction. Les références à des paramètres de fonction non agrégés peuvent apparaître n'importe où dans la définition UDAF.

Dans l'exemple suivant, l'UDAF JavaScript contient un paramètre d'agrégation appelé s et un paramètre non agrégé appelé delimiter :

CREATE TEMP AGGREGATE FUNCTION JsStringAgg(
  s STRING,
  delimiter STRING NOT AGGREGATE)
RETURNS STRING
LANGUAGE js
AS r'''

  export function initialState() {
    return {strings: []}
  }
  export function aggregate(state, s) {
    state.strings.push(s);
  }
  export function merge(state, partialState) {
    state.strings = state.strings.concat(partialState.strings);
  }
  export function finalize(state, delimiter) {
    return state.strings.join(delimiter);
  }

''';

-- Call the JavaScript UDAF.
WITH strings AS (
  SELECT * FROM UNNEST(["aaa", "bbb", "ccc", "ddd"]) AS values)
SELECT JsStringAgg(values, '.') AS result FROM strings;

/*-----------------*
 | result          |
 +-----------------+
 | aaa.bbb.ccc.ddd |
 *-----------------*/

Sérialiser et désérialiser des données dans une fonction UDAF JavaScript

BigQuery doit sérialiser tout objet renvoyé par la fonction initialState ou figurant dans l'argument state après l'appel de la fonction aggregate ou merge. BigQuery accepte la sérialisation d'un objet si tous les champs sont l'un des éléments suivants :

• Une valeur primitive JavaScript (par exemple : 2, "abc", null, undefined).
• Objet JavaScript pour lequel BigQuery accepte la sérialisation de toutes les valeurs de champ.
• Tableau JavaScript pour lequel BigQuery accepte la sérialisation de tous les éléments.

Les valeurs renvoyées suivantes sont sérialisables :

export function initialState() {
  return {a: "", b: 3, c: null, d: {x: 23} }
}

export function initialState() {
  return {value: 2.3};
}

Les valeurs renvoyées suivantes ne sont pas sérialisables :

export function initialState() {
  return {
    value: function() {return 6;}
  }
}

export function initialState() {
  return 2.3;
}

Si vous souhaitez utiliser des états d'agrégation non sérialisables, l'UDAF JavaScript doit inclure les fonctions serialize et deserialize. La fonction serialize convertit l'état d'agrégation en objet sérialisable. La fonction deserialize reconvertit l'objet sérialisable en état d'agrégation.

Dans l'exemple suivant, une bibliothèque externe calcule les sommes à l'aide d'une interface :

export class SumAggregator {
 constructor() {
   this.sum = 0;
 }
 update(value) {
   this.sum += value;
 }
 getSum() {
   return this.sum;
 }
}

La requête suivante ne s'exécute pas, car l'objet de classe SumAggregator n'est pas sérialisable par BigQuery en raison de la présence de fonctions dans la classe.

CREATE TEMP AGGREGATE FUNCTION F(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS r'''

  class SumAggregator {
   constructor() {
     this.sum = 0;
   }

   update(value) {
     this.sum += value;
   }

   getSum() {
     return this.sum;
   }
  }

  export function initialState() {
   return new SumAggregator();
  }

  export function aggregate(agg, value) {
   agg.update(value);
  }

  export function merge(agg1, agg2) {
   agg1.update(agg2.getSum());
  }

  export function finalize(agg) {
   return agg.getSum();
  }

''';

--Error: getSum is not a function
SELECT F(x) AS results FROM UNNEST([1,2,3,4]) AS x;

Si vous ajoutez les fonctions serialize et deserialize à la requête précédente, celle-ci s'exécute, car l'objet de classe SumAggregator est converti en objet sérialisable par BigQuery, puis redevient un objet de classe SumAggregator.

CREATE TEMP AGGREGATE FUNCTION F(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS r'''

  class SumAggregator {
   constructor() {
     this.sum = 0;
   }

   update(value) {
     this.sum += value;
   }

   getSum() {
     return this.sum;
   }
  }

  export function initialState() {
   return new SumAggregator();
  }

  export function aggregate(agg, value) {
   agg.update(value);
  }

  export function merge(agg1, agg2) {
   agg1.update(agg2.getSum());
  }

  export function finalize(agg) {
   return agg.getSum();
  }

  export function serialize(agg) {
   return {sum: agg.getSum()};
  }

  export function deserialize(serialized) {
   var agg = new SumAggregator();
   agg.update(serialized.sum);
   return agg;
  }

''';

SELECT F(x) AS results FROM UNNEST([1,2,3,4]) AS x;

/*-----------------*
 | results         |
 +-----------------+
 | 10.0            |
 *-----------------*/

Pour en savoir plus sur les fonctions de sérialisation, consultez la page Fonctions de sérialisation JavaScript facultatives.

Inclure des variables globales et des fonctions personnalisées dans un fichier UDAF JavaScript

Le corps de la fonction JavaScript peut inclure du code JavaScript personnalisé, tel que des variables globales JavaScript et des fonctions personnalisées.

Les variables globales sont exécutées lorsque le code JavaScript est chargé dans BigQuery et avant l'exécution de la fonction initialState. Les variables globales peuvent être utiles si vous devez effectuer un travail d'initialisation ponctuel qui ne doit pas être répété pour chaque groupe d'agrégation, comme serait le cas avec les fonctions initialState, aggregate, merge et finalize.

N'utilisez pas de variables globales pour stocker l'état d'agrégation. À la place, limitez l'état d'agrégation aux objets transmis aux fonctions exportées. N'utilisez des variables globales que pour mettre en cache des opérations coûteuses qui ne sont pas spécifiques à une opération d'agrégation particulière.

Dans la requête suivante, la fonction SumOfPrimes calcule une somme, mais seuls les nombres primitifs sont inclus dans le calcul. Dans le corps de la fonction JavaScript, deux variables globales, primes et maxTested, sont initialisées en premier. En outre, il existe une fonction personnalisée appelée isPrime qui vérifie si un nombre est premier.

CREATE TEMP AGGREGATE FUNCTION SumOfPrimes(x INT64)
RETURNS INT64
LANGUAGE js
AS r'''

  var primes = new Set([2]);
  var maxTested = 2;

  function isPrime(n) {
    if (primes.has(n)) {
      return true;
    }
    if (n <= maxTested) {
      return false;
    }
    for (var k = 2; k < n; ++k) {
      if (!isPrime(k)) {
        continue;
      }
      if ((n % k) == 0) {
        maxTested = n;
        return false;
      }
    }
    maxTested = n;
    primes.add(n);
    return true;
  }

  export function initialState() {
    return {sum: 0};
  }

  export function aggregate(state, x) {
    x = Number(x);
    if (isPrime(x)) {
      state.sum += x;
    }
  }

  export function merge(state, partialState) {
    state.sum += partialState.sum;
  }

  export function finalize(state) {
    return state.sum;
  }

''';

-- Call the JavaScript UDAF.
WITH numbers AS (
  SELECT * FROM UNNEST([10, 11, 13, 17, 19, 20]) AS x)
SELECT SumOfPrimes(x) AS sum FROM numbers;

/*-----*
 | sum |
 +-----+
 | 60  |
 *-----*/

Inclure les bibliothèques JavaScript

Vous pouvez étendre vos UDAF JavaScript à l'aide de l'option library dans la clause OPTIONS. Cette option vous permet de spécifier des bibliothèques de code externes pour l'UDAF JavaScript, puis de les importer avec la déclaration import.

Dans l'exemple suivant, le code de bar.js est disponible pour tout code figurant dans le corps de la fonction de l'UDAF JavaScript :

CREATE TEMP AGGREGATE FUNCTION JsAggFn(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js
OPTIONS (library = ['gs://foo/bar.js'])
AS r'''

  import doInterestingStuff from 'bar.js';

  export function initialState() {
    return ...
  }
  export function aggregate(state, x) {
    var result = doInterestingStuff(x);
    ...
  }
  export function merge(state, partial_state) {
    ...
  }
  export function finalize(state) {
    return ...;
  }

''';

Structure JavaScript requise

Contrairement à une fonction JavaScript définie par l'utilisateur, où le corps de la fonction est un code JavaScript libre qui s'exécute pour chaque ligne, le corps de la fonction pour un UDAF JavaScript est un module JavaScript contenant certaines fonctions exportées intégrées, qui sont appelées à différentes étapes du processus d'agrégation. Certaines de ces fonctions intégrées sont obligatoires, tandis que d'autres sont facultatives. Vous pouvez également ajouter vos fonctions JavaScript.

Fonctions d'agrégation JavaScript requises

Vous pouvez inclure vos fonctions JavaScript, mais le corps de la fonction JavaScript doit inclure les fonctions JavaScript exportables suivantes :

• initialState([nonAggregateParam]) : renvoie un objet JavaScript qui représente un état d'agrégation dans lequel aucune ligne n'a encore été agrégée.

• aggregate(state, aggregateParam[, ...][, nonAggregateParam]) : agrège une ligne de données, en mettant à jour l'état pour stocker le résultat de l'agrégation. Ne renvoie pas de valeur.

• merge(state, partialState, [nonAggregateParam]) : fusionne l'état d'agrégation partialState dans l'état d'agrégation state. Cette fonction est utilisée lorsque le moteur agrège différentes sections de données en parallèle et doit combiner les résultats. Ne renvoie pas de valeur.

• finalize(finalState, [nonAggregateParam]) : renvoie le résultat final de la fonction d'agrégation, en fonction d'un état d'agrégation final finalState.

Pour en savoir plus sur les fonctions requises, consultez la page Fonctions requises dans un objet UDAF JavaScript.

Fonctions de sérialisation JavaScript facultatives

Si vous souhaitez utiliser des états d'agrégation non sérialisables, l'UDAF JavaScript doit fournir les fonctions serialize et deserialize. La fonction serialize convertit l'état d'agrégation en objet sérialisable BigQuery. La fonction deserialize reconvertit l'objet sérialisable BigQuery en état d'agrégation.

• serialize(state) : renvoie un objet sérialisable contenant les informations dans l'état d'agrégation, à désérialiser via la fonction deserialize.

• deserialize(serializedState) : désérialise serializedState (précédemment sérialisée par la fonction serialize) dans un état d'agrégation pouvant être transmis aux fonctions serialize, aggregate, merge ou finalize.

Pour en savoir plus sur les fonctions de sérialisation JavaScript intégrées, consultez la section Fonctions de sérialisation pour un UDAF JavaScript.

Pour apprendre à sérialiser et à désérialiser des données avec un UDAF JavaScript, consultez la page Sérialiser et désérialiser des données dans un UDAF JavaScript.

Encodages de types SQL autorisés dans une fonction UDAF JavaScript

Dans les UDAF JavaScript, les types de données GoogleSQL compatibles suivants représentent les types de données JavaScript comme suit :

Type de données GoogleSQL	Type de données JavaScript	Remarques
ARRAY	Array	Un tableau de tableaux n'est pas accepté. Pour contourner cette limitation, utilisez les types de données Array<Object<Array>> (JavaScript) et ARRAY<STRUCT<ARRAY>> (GoogleSQL).
BIGNUMERIC	Number ou String	Identique à NUMERIC.
BOOL	Boolean
BYTES	Uint8Array
DATE	Date
FLOAT64	Number
INT64	BigInt
JSON	Différents types	Le type de données GoogleSQL JSON peut être converti en un type de données JavaScript Object, Array ou autre type de données JavaScript compatible avec GoogleSQL.
NUMERIC	Number ou String	Si une valeur NUMERIC peut être exactement représentée comme une valeur à virgule flottante au format IEEE 754 (plage [-253, 253]) et ne comporte aucune fraction, elle est encodée en tant que type de données Number. Sinon, elle est encodée en tant que type de données String.
STRING	String
STRUCT	Object	Chaque champ STRUCT est une propriété nommée dans le type de données Object. Un champ STRUCT sans nom n'est pas accepté.
TIMESTAMP	Date	Date contient un champ de microseconde avec la fraction de microsecondes de TIMESTAMP.

Appeler une fonction UDAF

Cette section décrit les différentes manières d'appeler une fonction UDAF persistante ou temporaire après sa création dans BigQuery.

Appeler une fonction UDAF persistante

Vous pouvez appeler une fonction UDAF persistante de la même manière qu'une fonction d'agrégation intégrée. Pour en savoir plus, consultez Appels de fonctions d'agrégation. Vous devez inclure l'ensemble de données dans le chemin de la fonction.

Dans l'exemple suivant, la requête appelle une fonction UDAF persistante appelée WeightedAverage :

SELECT my_project.my_dataset.WeightedAverage(item, weight, 2) AS weighted_average
FROM (
  SELECT 1 AS item, 2.45 AS weight UNION ALL
  SELECT 3 AS item, 0.11 AS weight UNION ALL
  SELECT 5 AS item, 7.02 AS weight
);

Une table contenant les résultats suivants est générée :

/*------------------*
 | weighted_average |
 +------------------+
 | 4.5              |
 *------------------*/

Appeler une fonction UDAF temporaire

Vous pouvez appeler une fonction UDAF temporaire de la même manière qu'une fonction d'agrégation intégrée. Pour en savoir plus, consultez Appels de fonctions d'agrégation.

La fonction temporaire doit être incluse dans une requête à plusieurs instructions ou une procédure contenant l'appel de fonction UDAF.

Dans l'exemple suivant, la requête appelle une fonction UDAF temporaire appelée WeightedAverage :

CREATE TEMP AGGREGATE FUNCTION WeightedAverage(...)

-- Temporary UDAF function call
SELECT WeightedAverage(item, weight, 2) AS weighted_average
FROM (
  SELECT 1 AS item, 2.45 AS weight UNION ALL
  SELECT 3 AS item, 0.11 AS weight UNION ALL
  SELECT 5 AS item, 7.02 AS weight
);

Une table contenant les résultats suivants est générée :

/*------------------*
 | weighted_average |
 +------------------+
 | 4.5              |
 *------------------*/

Ignorer ou inclure les lignes avec des valeurs NULL

Lorsqu'une fonction UDAF JavaScript est appelée avec l'argument IGNORE NULLS, BigQuery ignore automatiquement les lignes pour lesquelles tout argument d'agrégation renvoie NULL. Ces lignes sont complètement exclues de l'agrégation et ne sont pas transmises à la fonction JavaScript aggregate. Si l'argument RESPECT NULLS est fourni, le filtrage NULL est désactivé et chaque ligne est transmise à l'UDAF JavaScript, quelles que soient les valeurs NULL.

Lorsque ni l'argument IGNORE NULLS, ni l'argument RESPECT NULLS ne sont fournis, l'argument par défaut est IGNORE NULLS.

L'exemple suivant illustre le comportement NULL par défaut, le comportement IGNORE NULLS et le comportement RESPECT NULLS :

CREATE TEMP AGGREGATE FUNCTION SumPositive(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS r'''

  export function initialState() {
    return {sum: 0}
  }
  export function aggregate(state, x) {
    if (x == null) {
      // Use 1000 instead of 0 as placeholder for null so
      // that NULL values passed are visible in the result.
      state.sum += 1000;
      return;
    }
    if (x > 0) {
      state.sum += x;
    }
  }
  export function merge(state, partialState) {
    state.sum += partialState.sum;
  }
  export function finalize(state) {
    return state.sum;
  }

''';

-- Call the JavaScript UDAF.
WITH numbers AS (
  SELECT * FROM UNNEST([1.0, 2.0, NULL]) AS x)
SELECT
  SumPositive(x) AS sum,
  SumPositive(x IGNORE NULLS) AS sum_ignore_nulls,
  SumPositive(x RESPECT NULLS) AS sum_respect_nulls
FROM numbers;

/*-----+------------------+-------------------*
 | sum | sum_ignore_nulls | sum_respect_nulls |
 +-----+------------------+-------------------+
 | 3.0 | 3.0              | 1003.0            |
 *-----+------------------+-------------------*/

Supprimer une fonction UDAF

Cette section décrit les différentes manières de supprimer une fonction UDAF persistante ou temporaire après sa création dans BigQuery.

Supprimer une fonction UDAF persistante

Pour supprimer une fonction UDAF persistante, utilisez l'instruction DROP FUNCTION. Vous devez inclure l'ensemble de données dans le chemin de la fonction.

Dans l'exemple suivant, la requête supprime une fonction UDAF persistante appelée WeightedAverage :

DROP FUNCTION IF EXISTS my_project.my_dataset.WeightedAverage;

Supprimer une fonction UDAF temporaire

Pour supprimer une fonction UDAF temporaire, utilisez l'instruction DROP FUNCTION.

Dans l'exemple suivant, la requête supprime une fonction UDAF temporaire appelée WeightedAverage :

DROP FUNCTION IF EXISTS WeightedAverage;

Une fonction UDAF temporaire expire dès que la requête se termine. Il n'est pas nécessaire de la supprimer, sauf si vous souhaitez la supprimer prématurément d'une requête à plusieurs instructions ou d'une procédure.

Répertorier des fonctions UDAF

Les fonctions UDAF sont un type de routine. Pour répertorier toutes les routines d'un ensemble de données, consultez la page Répertorier des routines.

Conseils relatifs aux performances

Si vous souhaitez améliorer les performances de vos requêtes, tenez compte des points suivants :

• Préfiltrez votre entrée. Le traitement des données dans JavaScript est plus coûteux qu'en SQL. Il est donc préférable de filtrer autant que possible les entrées en SQL.

  La requête suivante est moins efficace, car elle filtre l'entrée à l'aide de x > 0 dans l'appel UDAF :

  SELECT JsFunc(x) FROM t;
  

  La requête suivante est plus efficace, car elle préfiltre l'entrée à l'aide de WHERE x > 0 avant l'appel de l'UDAF :

  SELECT JsFunc(x) FROM t WHERE x > 0;
  

• Dans la mesure du possible, utilisez des fonctions d'agrégation intégrées au lieu de JavaScript. La réimplémentation d'une fonction d'agrégation intégrée en JavaScript est plus lente que d'appeler une fonction d'agrégation intégrée qui effectue la même opération.

  La requête suivante est moins efficace, car elle met en œuvre un UDAF :

  SELECT SumSquare(x) FROM t;
  

  La requête suivante est plus efficace, car elle met en œuvre une fonction intégrée qui produit les mêmes résultats que la requête précédente :

  SELECT SUM(x*x) FROM t;
  

• Les fonctions JavaScript UDAF sont adaptées aux opérations d'agrégation plus complexes, qui ne peuvent pas être exprimées via des fonctions intégrées.

• Utilisez efficacement la mémoire. L'environnement de traitement JavaScript dispose d'une mémoire limitée pour chaque requête. Les requêtes UDAF JavaScript qui accumulent trop d'état local peuvent échouer en raison de l'épuisement de la mémoire. Veillez particulièrement à réduire la taille des objets d'état d'agrégation et évitez les états d'agrégation qui accumulent un grand nombre de lignes.

  La requête suivante n'est pas efficace, car la fonction aggregate utilise une quantité illimitée de mémoire lorsque le nombre de lignes traitées devient important.

  export function initialState() {
    return {rows: []};
  }
  export function aggregate(state, x) {
    state.rows.push(x);
  }
  ...
  

• Utilisez des tables partitionnées si possible. Les UDAF JavaScript s'exécutent généralement plus efficacement lors de l'interrogation d'une table partitionnée qu'avec une table non partitionnée, car une table partitionnée stocke les données dans de nombreux fichiers plus petits qu'une table non partitionnée. parallélisme.

Limites

• Les fonctions UDAF présentent les mêmes limites que les fonctions définies par l'utilisateur. Pour en savoir plus, consultez la section Limitations relatives aux fonctions définies par l'utilisateur.

• Seuls les littéraux, les paramètres de requête et les variables de script peuvent être transmis en tant qu'arguments non agrégés pour une fonction UDAF.

• L'utilisation de la clause ORDER BY dans un appel de fonction UDAF JavaScript n'est pas acceptée.

  SELECT MyUdaf(x ORDER BY y) FROM t; -- Error: ORDER BY is unsupported.
  

Tarification

Les fonctions UDAF sont facturées selon le modèle de tarification BigQuery standard.

Quotas et limites

Les fonctions UDAF sont soumises aux mêmes quotas et limites que les fonctions définies par l'utilisateur. Pour en savoir plus sur les quotas UDF, consultez la section Quotas et limites.