Funzioni di aggregazione definite dall'utente

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

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

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

Crea una UDAF SQL

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

Crea una UDAF SQL permanente

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

Per creare una funzione UDAF permanente, 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 una funzione UDAF permanente denominata ScaledAverage:

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

Crea una funzione UDAF SQL temporanea

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

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

Ad esempio, la seguente query crea una funzione UDAF temporanea denominata 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 funzione UDAF SQL con parametri aggregati e non aggregati.

Le UDAF di solito 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 di funzione per aggregare le chiamate di funzione. I riferimenti ai parametri di funzione non aggregati possono essere presenti ovunque nella definizione di UDAF.

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

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

Utilizza il progetto predefinito nel corpo della funzione

Nel corpo di una funzione UDAF SQL, tutti i riferimenti a entità BigQuery, come tabelle o viste, devono includere l'ID progetto, a meno che l'entità non si trovi nello stesso progetto che contiene l'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 ha esito positivo perché my_table esiste in project1. Tuttavia, se esegui l'istruzione precedente da un altro progetto, l'istruzione avrà esito negativo. 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 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 )
);

Crea una UDAF JavaScript

Questa sezione descrive i vari modi in cui puoi creare una funzione JavaScript definita dall'utente in BigQuery. Quando si crea una UDAF JavaScript, occorre osservare alcune regole:

• Il corpo della funzione JavaScript UDAF deve essere un valore letterale di stringa tra virgolette che rappresenta il codice JavaScript. Per scoprire di più sui diversi tipi di valori letterali stringa tra virgolette che puoi utilizzare, consulta Formati per i valori letterali tra virgolette.

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

• Il corpo della funzione JavaScript deve includere quattro funzioni JavaScript che inizializzano, aggregano, uniscono e finalizzano i risultati dell'UDAF JavaScript (initialState, aggregate, merge e finalize). Per ulteriori informazioni, consulta Codifiche dei tipi SQL consentiti in un'UDAF JavaScript.

• Qualsiasi valore restituito dalla funzione initialState o rimasto nell'argomento state dopo la chiamata della funzione aggregate o merge deve essere serializzabile. Se vuoi lavorare con dati di aggregazione non in serie, come funzioni o campi di simboli, devi utilizzare le funzioni serialize e deserialize incluse. Per scoprire di più, consulta Serializzare e deserializzare i dati in una funzione UDAF JavaScript.

Crea una UDAF JavaScript permanente

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

Per creare una funzione UDAF permanente, 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 funzione JavaScript UDAF permanente 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 |
 *-----*/

Crea una UDAF JavaScript temporanea

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

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

La seguente query crea un UDAF JavaScript temporaneo denominato 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 JavaScript definita dall'utente

Puoi creare una funzione UDAF JavaScript con parametri sia aggregati che non aggregati.

Le UDAF di solito 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 di funzione per aggregare le chiamate di funzione. I riferimenti ai parametri di funzione non aggregati possono essere presenti ovunque nella definizione di UDAF.

Nell'esempio seguente, la funzione JavaScript UDAF contiene un parametro aggregato denominato s e un parametro non aggregato denominato 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 |
 *-----------------*/

Serializza e deserializza i dati in una UDAF JavaScript

BigQuery deve serializzare qualsiasi oggetto restituito dalla funzione initialState o rimasto nell'argomento state dopo la chiamata 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 esempio: 2, "abc", null, undefined).
• Un oggetto JavaScript per cui BigQuery supporta la serializzazione di tutti i valori dei campi.
• Un array JavaScript per cui 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 lavorare con stati di aggregazione non serializzabili, l'UDAF JavaScript deve includere le funzioni serialize e deserialize. La funzione serialize converte lo stato di aggregazione in un oggetto serializzabile; la funzione deserialize converte l'oggetto serializzabile di nuovo in uno stato di aggregazione.

Nell'esempio seguente, 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 di classe SumAggregator non è utilizzabile in serie su 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 di classe 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 JavaScript.

Includi variabili globali e funzioni personalizzate in una UDAF JavaScript

Il corpo della funzione JavaScript può includere codice JavaScript personalizzato, come le variabili globali JavaScript e le 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 potrebbero essere utili se devi eseguire operazioni di inizializzazione una tantum che non devono ripetersi per ogni gruppo di aggregazione, come nel caso delle funzioni initialState, aggregate, merge e finalize.

Non utilizzare variabili globali per archiviare lo stato di aggregazione. Limita invece lo stato dell'aggregazione agli oggetti trasferiti alle funzioni esportate. Utilizza le variabili globali solo per memorizzare nella cache operazioni costose che non sono specifiche per una determinata operazione di aggregazione.

Nella seguente query, la funzione SumOfPrimes calcola una somma, ma nel calcolo sono inclusi solo i numeri primi. Nel corpo della funzione JavaScript sono presenti due variabili globali, primes e maxTested, inizializzate per prime. Inoltre, esiste una funzione personalizzata denominata isPrime che controlla se si tratta di 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 consente di specificare librerie di codice esterne per l'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 dell'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 in esecuzione per ogni riga, il corpo della funzione di una funzione JavaScript definita dall'utente è un modulo JavaScript che contiene alcune funzioni esportate integrate, che vengono richiamate in varie fasi del processo di aggregazione. Alcune di queste funzioni integrate sono obbligatorie, mentre altre sono facoltative. Puoi anche aggiungere le tue funzioni JavaScript.

Funzioni di aggregazione JavaScript obbligatorie

Puoi includere le 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 ancora state aggregate righe.

• aggregate(state, aggregateParam[, ...][, nonAggregateParam]): aggrega una riga di dati, aggiornando lo stato per archiviare 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 aggregata, dato uno stato di aggregazione finale finalState.

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

Funzioni di serializzazione JavaScript facoltative

Se vuoi lavorare con stati di aggregazione non serializzabili, l'UDAF JavaScript deve fornire le funzioni serialize e deserialize. La funzione serialize converte lo stato di aggregazione in un oggetto serializzabile su BigQuery; la funzione deserialize converte 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 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 aggirare 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 un tipo di dati JavaScript 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 [-253, 253]) e non ha una 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 di microsecondi con la frazione di microsecondi di TIMESTAMP.

Chiama una UDAF

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

Chiama una UDAF permanente

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

Nell'esempio seguente, la query chiama una funzione UDAF permanente denominata 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 prodotta una tabella con i seguenti risultati:

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

Chiama una UDAF temporanea

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

La funzione temporanea deve essere inclusa in una query con più istruzioni o in una procedura contenente la chiamata alla funzione UDAF.

Nell'esempio seguente, la query chiama una funzione UDAF temporanea denominata 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 prodotta una tabella con i seguenti risultati:

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

Ignora o includi le righe con valori NULL

Quando una UDAF JavaScript viene chiamata con l'argomento IGNORE NULLS, BigQuery salta automaticamente le righe per le quali qualsiasi argomento aggregato restituisce NULL. Queste righe sono escluse completamente dall'aggregazione e non vengono passate alla funzione aggregate di JavaScript. Se viene fornito l'argomento RESPECT NULLS, il filtro NULL viene disabilitato e ogni riga viene passata all'UDAF JavaScript, indipendentemente dai valori NULL.

Quando non viene fornito né l'argomento IGNORE NULLS né RESPECT NULLS, l'argomento predefinito è IGNORE NULLS.

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

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

Elimina una funzione UDAF permanente

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

Nel seguente esempio, la query elimina una funzione UDAF permanente denominata WeightedAverage:

DROP FUNCTION IF EXISTS my_project.my_dataset.WeightedAverage;

Eliminare una funzione UDAF temporanea

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

Nell'esempio seguente, la query elimina una funzione UDAF temporanea denominata WeightedAverage:

DROP FUNCTION IF EXISTS WeightedAverage;

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

Elenco UDAF

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

Suggerimenti sulle prestazioni

Per migliorare il rendimento delle query, prendi in considerazione i seguenti aspetti:

• Prefiltra l'input. L'elaborazione dei dati in JavaScript è più costosa di quella in 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'UDAF:

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

• Se possibile, utilizza funzioni aggregate integrate anziché JavaScript. L'implementazione di una funzione di aggregazione integrata in JavaScript è più lenta rispetto alla chiamata di una funzione di aggregazione integrata che esegue la stessa operazione.

  La seguente query è meno efficiente perché implementa una funzione 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 per ogni query. Le query JavaScript UDAF che si accumulano troppo sullo stato locale potrebbero non riuscire a causa dell'esaurimento della memoria. Presta particolare attenzione a ridurre al minimo le dimensioni degli oggetti di stato dell'aggregazione ed evita stati di aggregazione che accumulano un numero elevato di righe.

  La seguente query non è efficiente perché la funzione aggregate utilizza una quantità di memoria illimitata 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. Le UDAF JavaScript in genere vengono eseguite in modo più efficiente quando si eseguono query su una tabella partizionata rispetto a una tabella non partizionata, perché una tabella partizionata archivia i dati in molti file più piccoli rispetto a una tabella non partizionata, consentendo così un maggiore parallelismo.

Limitazioni

• Le funzioni definite dall'utente hanno le stesse limitazioni che si applicano alle funzioni definite dall'utente. Per maggiori dettagli, consulta Limitazioni delle funzioni definite dall'utente.

• Solo i valori letterali, parametri di ricerca e le variabili di script possono essere passati come argomenti non aggregati per una funzione UDAF.

• L'utilizzo della clausola ORDER BY in una chiamata di funzione UDAF 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 di BigQuery standard.

Quote e limiti

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