Funzioni aggregate definite dall'utente

Questo documento descrive come creare, chiamare ed eliminare le funzioni aggregate definite dall'utente (UDAF) in BigQuery.

Una UDAF consente di creare una funzione di aggregazione utilizzando un'espressione che contiene codice. Una UDAF accetta colonne di input, esegue un calcolo su un gruppo di righe alla volta e restituisce il risultato del calcolo come singolo valore.

Creare una UDAF SQL

Preview

This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see the launch stage descriptions.

Per ricevere assistenza durante l'anteprima, invia un'email all'indirizzo [e-mail].

Questa sezione descrive i vari modi in cui puoi creare una UDAF SQL permanente o temporanea in BigQuery.

Creare una UDAF SQL persistente

Puoi creare una UDAF SQL permanente, il che significa che puoi riutilizzarla per più query. È possibile chiamare UDAF permanenti quando sono condivisi tra i proprietari. Le UDAF non possono modificare i dati, comunicare con sistemi esterni o inviare log a Google Cloud Observability o applicazioni simili.

Per creare una UDAF persistente, utilizza l'istruzione CREATE AGGREGATE FUNCTION senza la parola chiave TEMP o TEMPORARY. Devi includere il set di dati nel percorso della funzione.

Ad esempio, la seguente query crea un'UDAF persistente chiamata ScaledAverage:

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

Creare una UDAF SQL temporanea

Puoi creare una UDAF SQL temporanea, il che significa che la UDAF esiste solo nell'ambito di una singola query, script, sessione o procedura.

Per creare una UDAF temporanea, utilizza l'istruzione CREATE AGGREGATE FUNCTION con la parola chiave TEMP o TEMPORARY.

Ad esempio, la seguente query crea un'UDAF temporanea chiamata ScaledAverage:

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

Utilizzare parametri aggregati e non aggregati

Puoi creare una UDAF SQL con parametri aggregati e non aggregati.

In genere, le UDAF aggregano i parametri di funzione in tutte le righe di un gruppo. Tuttavia, puoi specificare un parametro di funzione come non aggregato con la parola chiave NOT AGGREGATE.

Un parametro di funzione non aggregata è un parametro di funzione scalare con un valore costante per tutte le righe di un gruppo. Un parametro di funzione non aggregata valido deve essere un valore letterale. All'interno della definizione UDAF, i parametri delle funzioni aggregate possono essere visualizzati solo come argomenti delle funzioni per le chiamate delle funzioni aggregate. I riferimenti ai parametri delle funzioni non aggregate possono essere visualizzati ovunque nella definizione di UDAF.

Ad esempio, la seguente funzione contiene un parametro aggregato chiamato dividend e un parametro non aggregato chiamato divisor:

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

Utilizzare il progetto predefinito nel corpo della funzione

Nel corpo di una UDAF SQL, tutti i riferimenti alle entità BigQuery, come tabelle o viste, devono includere l'ID progetto, a meno che l'entità non si trovi nello stesso progetto che contiene la UDAF.

Ad esempio, considera la seguente affermazione:

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

Se esegui l'istruzione precedente nel progetto project1, l'istruzione viene eseguita correttamente perché my_table esiste in project1. Tuttavia, se esegui l'istruzione precedente da un altro progetto, l'istruzione non va a buon fine. Per correggere l'errore, includi l'ID progetto nel riferimento della tabella:

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

Puoi anche fare riferimento a un'entità in un progetto o in un set di dati diverso da quello in cui crei la funzione:

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

Creare una funzione definita dall'utente di analisi dati avanzata JavaScript

Questa sezione descrive i vari modi in cui puoi creare una UDAF in JavaScript in BigQuery. Esistono alcune regole da osservare quando si crea una UDAF JavaScript:

• Il corpo dell'espressione UDAF JavaScript deve essere una stringa letterale tra virgolette che rappresenta il codice JavaScript. Per scoprire di più sui diversi tipi di stringhe tra virgolette che puoi utilizzare, consulta Formati per i valori letterali tra virgolette.

• Sono consentite solo alcune codifiche di tipo. Per ulteriori informazioni, consulta Codifiche dei tipi SQL consentite in una UDAF JavaScript.

• Il corpo della funzione JavaScript deve includere quattro funzioni JavaScript che inizializzano, aggregano, uniscono e finalizzano i risultati per la UDAF JavaScript (initialState, aggregate, merge e finalize). Per maggiori informazioni, consulta Codifiche dei tipi SQL consentite in una UDAF JavaScript.

• Qualsiasi valore restituito dalla funzione initialState o lasciato nell'argomento state dopo la chiamata della funzione aggregate o merge deve essere serializzabile. Se vuoi utilizzare dati di aggregazione non serializzabili, come funzioni o campi di simboli, devi utilizzare le funzioni serialize e deserialize incluse. Per saperne di più, consulta Eseguire la serializzazione e la deserializzazione dei dati in una UDAF JavaScript.

Creare una UDAF JavaScript persistente

Puoi creare una UDAF JavaScript permanente, il che significa che puoi riutilizzarla per più query. È possibile chiamare UDAF permanenti quando sono condivisi tra i proprietari. Le UDAF non possono modificare i dati, comunicare con sistemi esterni o inviare log a Google Cloud Observability o applicazioni simili.

Per creare una UDAF persistente, utilizza l'istruzione CREATE AGGREGATE FUNCTION senza la parola chiave TEMP o TEMPORARY. Devi includere il set di dati nel percorso della funzione.

La seguente query crea una UDAF JavaScript persistente denominata 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 |
 *-----*/

Creare una UDAF JavaScript temporanea

Puoi creare una UDAF JavaScript temporanea, il che significa che la UDAF esiste solo nell'ambito di una singola query, script, sessione o procedura.

Per creare una UDAF temporanea, utilizza l'istruzione CREATE AGGREGATE FUNCTION con la parola chiave TEMP o TEMPORARY.

La seguente query crea un'UDAF JavaScript temporanea chiamata 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 |
 *-----*/

Includi parametri non aggregati in una funzione UDAF JavaScript

Puoi creare una UDAF JavaScript con parametri aggregati e non aggregati.

In genere, le UDAF aggregano i parametri di funzione in tutte le righe di un gruppo. Tuttavia, puoi specificare un parametro di funzione come non aggregato con la parola chiave NOT AGGREGATE.

Un parametro di funzione non aggregata è un parametro di funzione scalare con un valore costante per tutte le righe di un gruppo. Un parametro di funzione non aggregata valido deve essere un valore letterale. All'interno della definizione UDAF, i parametri delle funzioni aggregate possono essere visualizzati solo come argomenti delle funzioni per le chiamate delle funzioni aggregate. I riferimenti ai parametri delle funzioni non aggregate possono essere visualizzati ovunque nella definizione di UDAF.

Nel seguente esempio, l'UDAF JavaScript contiene un parametro aggregato chiamato s e un parametro non aggregato chiamato 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 |
 *-----------------*/

Serializzare e deserializzare i dati in una UDAF JavaScript

BigQuery deve serializzare qualsiasi oggetto restituito dalla funzione initialState o lasciato nell'argomento state dopo l'uso della funzione aggregate o merge. BigQuery supporta la serializzazione di un oggetto se tutti i campi sono uno dei seguenti:

• Un valore primitivo JavaScript (ad es. 2, "abc", null, undefined).
• Un oggetto JavaScript per il quale BigQuery supporta la serializzazione di tutti i valori di campo.
• Un array JavaScript per il quale BigQuery supporta la serializzazione di tutti gli elementi.

I seguenti valori restituiti sono serializzabili:

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

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

I seguenti valori restituiti non sono serializzabili:

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

export function initialState() {
  return 2.3;
}

Se vuoi utilizzare stati di aggregazione non serializzabili, la UDAF JavaScript deve includere le funzioni serialize e deserialize. La funzione serialize converte lo stato di aggregazione in un oggetto serializzabile; la funzione deserialize reconverte l'oggetto serializzabile in uno stato di aggregazione.

Nel seguente esempio, una libreria esterna calcola le somme utilizzando un'interfaccia:

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

La seguente query non viene eseguita perché l'oggetto della classe SumAggregator non è serializzabile in BigQuery a causa della presenza di funzioni all'interno della 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;

Se aggiungi le funzioni serialize e deserialize alla query precedente, la query viene eseguita perché l'oggetto di classe SumAggregator viene convertito in un oggetto serializzabile in BigQuery e poi di nuovo in un oggetto di 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            |
 *-----------------*/

Per scoprire di più sulle funzioni di serializzazione, consulta Funzioni facoltative di serializzazione di JavaScript.

Includere variabili globali e funzioni personalizzate in una UDAF JavaScript

Il corpo della funzione JavaScript può includere codice JavaScript personalizzato, ad esempio variabili globali JavaScript e funzioni personalizzate.

Le variabili globali vengono eseguite quando il codice JavaScript viene caricato in BigQuery e prima dell'esecuzione della funzione initialState. Le variabili globali possono essere utili se devi eseguire un'operazione di inizializzazione una tantum che non deve essere ripetuta per ogni gruppo di aggregazione, come nel caso delle funzioni initialState, aggregate, merge e finalize.

Non utilizzare variabili globali per memorizzare lo stato di aggregazione. Limita invece lo stato di aggregazione agli oggetti passati alle funzioni esportate. Utilizza solo le variabili globali per memorizzare nella cache operazioni dispendiose che non sono specifiche per alcuna operazione di aggregazione particolare.

Nella query seguente, la funzione SumOfPrimes calcola una somma, ma nel calcolo vengono inclusi solo i numeri primi. Nel corpo della funzione JavaScript, sono presenti due variabili globali, primes e maxTested, che vengono inizializzate per prime. Inoltre, è disponibile una funzione personalizzata denominata isPrime che controlla se un numero è primo.

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  |
 *-----*/

Includi librerie JavaScript

Puoi estendere le UDAF JavaScript con l'opzione library nella clausola OPTIONS. Questa opzione ti consente di specificare librerie di codice esterne per la UDAF JavaScript e di importarle con la dichiarazione import.

Nell'esempio seguente, il codice in bar.js è disponibile per qualsiasi codice nel corpo della funzione della 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 ...;
  }

''';

Struttura JavaScript obbligatoria

A differenza di una funzione JavaScript definita dall'utente, in cui il corpo della funzione è JavaScript in formato libero eseguito per ogni riga, il corpo della funzione per una funzione UDAF JavaScript è un modulo JavaScript che contiene alcune funzioni esportate integrate, che vengono richiamate in vari passaggi del processo di aggregazione. Alcune di queste funzioni predefinite sono obbligatorie, mentre altre sono facoltative. Puoi anche aggiungere le tue funzioni JavaScript.

Funzioni di aggregazione JavaScript obbligatorie

Puoi includere le tue funzioni JavaScript, ma il corpo della funzione JavaScript deve includere le seguenti funzioni JavaScript esportabili:

• initialState([nonAggregateParam]): restituisce un oggetto JavaScript che rappresenta uno stato di aggregazione in cui non sono state ancora aggregate righe.

• aggregate(state, aggregateParam[, ...][, nonAggregateParam]): aggrega una riga di dati, aggiornando lo stato per memorizzare il risultato dell'aggregazione. Non restituisce un valore.

• merge(state, partialState, [nonAggregateParam]): unisce lo stato di aggregazione partialState allo stato di aggregazione state. Questa funzione viene utilizzata quando il motore aggrega sezioni diverse di dati in parallelo e deve combinare i risultati. Non restituisce un valore.

• finalize(finalState, [nonAggregateParam]): restituisce il risultato finale della funzione di aggregazione, dato uno stato di aggregazione finale finalState.

Per saperne di più sulle funzioni richieste, consulta Funzioni richieste in una UDAF JavaScript.

Funzioni di serializzazione JavaScript facoltative

Se vuoi utilizzare stati di aggregazione non serializzabili, la UDAF JavaScript deve fornire le funzioni serialize e deserialize. La funzione serialize converte lo stato di aggregazione in un oggetto serializzabile BigQuery; la funzione deserialize reconverte l'oggetto serializzabile BigQuery in uno stato di aggregazione.

• serialize(state): restituisce un oggetto serializzabile contenente le informazioni nello stato di aggregazione, da deserializzare tramite la funzione deserialize.

• deserialize(serializedState): deserializza serializedState (precedentemente serializzato dalla funzione serialize) in uno stato di aggregazione che può essere trasmesso alle funzioni serialize, aggregate, merge o finalize.

Per scoprire di più sulle funzioni di serializzazione di JavaScript integrate, consulta Funzioni di serializzazione per una UDAF JavaScript.

Per scoprire come serializzare e deserializzare i dati con una UDAF JavaScript, consulta Serializzare e deserializzare i dati in una UDAF JavaScript.

Codifiche dei tipi SQL consentite in una UDAF JavaScript

Nelle UDAF JavaScript, i seguenti tipi di dati GoogleSQL supportati rappresentano i tipi di dati JavaScript come segue:

Tipo di dati GoogleSQL	Tipo di dati JavaScript	Note
ARRAY	Array	Un array di array non è supportato. Per ovviare a questa limitazione, utilizza i tipi di dati Array<Object<Array>> (JavaScript) e ARRAY<STRUCT<ARRAY>> (GoogleSQL).
BIGNUMERIC	Number o String	Uguale a NUMERIC.
BOOL	Boolean
BYTES	Uint8Array
DATE	Date
FLOAT64	Number
INT64	BigInt
JSON	Vari tipi	Il tipo di dati JSON di GoogleSQL può essere convertito in Object, Array o un altro tipo di dati JavaScript supportato da GoogleSQL.
NUMERIC	Number o String	Se un valore NUMERIC può essere rappresentato esattamente come un valore a virgola mobile IEEE 754 (intervallo [-2^53, 2^53]), e non ha parte frazionaria, viene codificato come tipo di dati Number, altrimenti viene codificato come tipo di dati String.
STRING	String
STRUCT	Object	Ogni campo STRUCT è una proprietà denominata nel tipo di dati Object. Un campo STRUCT senza nome non è supportato.
TIMESTAMP	Date	Date contiene un campo microsecondi con la frazione in microsecondi di TIMESTAMP.

Nota: le codifiche SQL per le funzioni UDAF JavaScript sono diverse da quelle per le funzioni UDF JavaScript.

Chiamare una UDAF

Questa sezione descrive i vari modi in cui puoi chiamare una UDAF permanente o provvisoria dopo averla creata in BigQuery.

Nota: le chiamate UDAF JavaScript non supportano la clausola ORDER BY.

Chiamare una UDAF permanente

Puoi chiamare una UDAF permanente nello stesso modo in cui chiami una funzione aggregata incorporata. Per ulteriori informazioni, consulta Chiamate di funzioni aggregate. Devi includere il set di dati nel percorso della funzione.

Nell'esempio seguente, la query chiama una UDAF persistente chiamata 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
);

Viene generata una tabella con i seguenti risultati:

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

Chiamare una UDAF temporanea

Puoi chiamare una UDAF temporanea nello stesso modo in cui chiami una funzione aggregata incorporata. Per ulteriori informazioni, consulta Chiamate di funzioni aggregate.

La funzione temporanea deve essere inclusa in una query con più istruzioni o procedura che contiene la chiamata della funzione UDAF.

Nell'esempio seguente, la query chiama una UDAF temporanea chiamata 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
);

Viene generata una tabella con i seguenti risultati:

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

Ignorare o includere le righe con valori NULL

Quando viene chiamata una UDAF JavaScript con l'argomento IGNORE NULLS, BigQuery salta automaticamente le righe per le quali qualsiasi argomento aggregato ha valore NULL. Queste righe vengono escluse completamente dall'aggregazione e non vengono passate alla funzione aggregate di JavaScript. Quando viene fornito l'argomento RESPECT NULLS, la filtrazione per NULL viene disattivata e ogni riga viene passata alla UDAF JavaScript, indipendentemente dai valori di NULL.

Se non viene fornito l'argomento IGNORE NULLS o RESPECT NULLS, l'argomento predefinito è IGNORE NULLS.

L'esempio seguente illustra il comportamento predefinito di NULL, il comportamento di IGNORE NULLS e il comportamento di 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            |
 *-----+------------------+-------------------*/

Eliminare una UDAF

Questa sezione descrive i vari modi in cui puoi eliminare una UDAF permanente o provvisoria dopo averla creata in BigQuery.

Eliminare una UDAF persistente

Per eliminare una UDAF permanente, utilizza l'istruzione DROP FUNCTION. Devi includere il set di dati nel percorso della funzione.

Nell'esempio seguente, la query elimina un'espressione UDAF permanente chiamata WeightedAverage:

DROP FUNCTION IF EXISTS my_project.my_dataset.WeightedAverage;

Eliminare un UDAF temporaneo

Per eliminare una UDAF temporanea, utilizza l'istruzione DROP FUNCTION.

Nell'esempio seguente, la query elimina un'UDAF temporanea chiamata WeightedAverage:

DROP FUNCTION IF EXISTS WeightedAverage;

Una UDAF temporanea scade al termine della query. L'UDF non deve essere eliminato, a meno che tu non voglia rimuoverlo in anticipo da una query con più istruzioni o procedura.

Elenca UDAF

Le UDAF sono un tipo di routine. Per elencare tutte le routine in un set di dati, consulta Elenca routine.

Suggerimenti sulle prestazioni

Se vuoi migliorare il rendimento delle tue query, tieni presente quanto segue:

• Prefiltra i dati inseriti. L'elaborazione dei dati in JavaScript è più costosa rispetto a SQL, quindi è meglio filtrare il più possibile l'input in SQL.

  La seguente query è meno efficiente perché filtra l'input utilizzando x > 0 nella chiamata UDAF:

  SELECT JsFunc(x) FROM t;
  

  La seguente query è più efficiente perché prefiltra l'input utilizzando WHERE x > 0 prima della chiamata dell'UDF:

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

• Se possibile, utilizza le funzioni aggregate integrate anziché JavaScript. La reimplementazione di una funzione di aggregazione integrata in JavaScript è più lenta rispetto alla chiamata di una funzione di aggregazione integrata che fa la stessa cosa.

  La seguente query è meno efficiente perché implementa una UDAF:

  SELECT SumSquare(x) FROM t;
  

  La seguente query è più efficiente perché implementa una funzione integrata che produce gli stessi risultati della query precedente:

  SELECT SUM(x*x) FROM t;
  

• Le UDAF JavaScript sono appropriate per operazioni di aggregazione più complesse, che non possono essere espresse tramite funzioni integrate.

• Utilizza la memoria in modo efficiente. L'ambiente di elaborazione JavaScript ha una memoria limitata disponibile per ogni query. Le query UDAF JavaScript che accumulano troppo stato locale potrebbero non riuscire a causa dell'esaurimento della memoria. Presta particolare attenzione a ridurre al minimo le dimensioni degli oggetti stato di aggregazione ed evita gli stati di aggregazione che accumulano un numero elevato di righe.

  La seguente query non è efficiente perché la funzione aggregate utilizza una quantità illimitata di memoria quando il numero di righe elaborate aumenta.

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

• Se possibile, utilizza tabelle partizionate. In genere, le UDAF JavaScript vengono eseguite in modo più efficiente quando esegui query su una tabella partizionata rispetto a una tabella non partizionata, perché una tabella partizionata memorizza i dati in molti file più piccoli rispetto a una tabella non partizionata, consentendo così un parallelismo superiore.

Limitazioni

• Le UDAF presentano le stesse limitazioni delle UDF. Per maggiori dettagli, consulta Limitazioni delle funzioni definite dall'utente.

• Solo letterali, parametri di query e variabili di script possono essere passati come argument non aggregati per una UDAF.

• L'utilizzo della clausola ORDER BY in una chiamata alla funzione UDAF di JavaScript non è supportato.

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

Prezzi

Le UDAF vengono fatturate utilizzando il modello di prezzi BigQuery standard.

Quote e limiti

Le funzioni UDAF hanno le stesse quote e gli stessi limiti che si applicano alle funzioni UDF. Per informazioni sulle quote delle funzioni definite dall'utente, consulta Quote e limiti.