Fungsi agregat yang ditentukan pengguna

Dokumen ini menjelaskan cara membuat, memanggil, dan menghapus fungsi agregat yang ditentukan pengguna (UDAF) di BigQuery.

UDAF memungkinkan Anda membuat fungsi agregat menggunakan ekspresi yang berisi kode. UDAF menerima kolom input, melakukan perhitungan pada sekelompok baris sekaligus, lalu menampilkan hasil perhitungan tersebut sebagai satu nilai.

Membuat UDAF SQL

Bagian ini menjelaskan berbagai cara untuk membuat UDAF SQL persisten atau sementara di BigQuery.

Membuat UDAF SQL persisten

Anda dapat membuat UDAF SQL yang persisten, yang berarti Anda dapat menggunakan kembali UDAF di beberapa kueri. UDAF persisten aman untuk dipanggil jika dibagikan antarpemilik. UDAF tidak dapat mengubah data, berkomunikasi dengan sistem eksternal, atau mengirim log ke Google Cloud Observability atau aplikasi serupa.

Untuk membuat UDAF persisten, gunakan pernyataan CREATE AGGREGATE FUNCTION tanpa kata kunci TEMP atau TEMPORARY. Anda harus menyertakan set data di jalur fungsi.

Misalnya, kueri berikut membuat UDAF persisten yang disebut ScaledAverage:

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

Membuat UDAF SQL sementara

Anda dapat membuat UDAF SQL yang bersifat sementara, yang berarti UDAF hanya ada dalam cakupan satu kueri, skrip, sesi, atau prosedur.

Untuk membuat UDAF sementara, gunakan pernyataan CREATE AGGREGATE FUNCTION dengan kata kunci TEMP atau TEMPORARY.

Misalnya, kueri berikut membuat UDAF sementara yang disebut ScaledAverage:

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

Menggunakan parameter gabungan dan non-gabungan

Anda dapat membuat UDAF SQL yang memiliki parameter agregat dan non-agregat.

UDAF biasanya menggabungkan parameter fungsi di semua baris dalam grup. Namun, Anda dapat menentukan parameter fungsi sebagai non-agregat dengan kata kunci NOT AGGREGATE.

Parameter fungsi non-agregat adalah parameter fungsi skalar dengan nilai konstanta untuk semua baris dalam grup. Parameter fungsi non-gabungan yang valid harus berupa literal. Dalam definisi UDAF, parameter fungsi agregat hanya dapat muncul sebagai argumen fungsi untuk panggilan fungsi agregat. Referensi ke parameter fungsi non-agregat dapat muncul di mana saja dalam definisi UDAF.

Misalnya, fungsi berikut berisi parameter agregat yang disebut dividend, dan parameter non-agregat yang disebut divisor:

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

Menggunakan project default di isi fungsi

Dalam isi UDAF SQL, setiap referensi ke entity BigQuery, seperti tabel atau tampilan, harus menyertakan project ID, kecuali jika entity tersebut berada dalam project yang sama yang berisi UDAF.

Misalnya, perhatikan pernyataan berikut:

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

Jika Anda menjalankan pernyataan sebelumnya di project project1, pernyataan akan berhasil karena my_table ada di project1. Namun, jika Anda menjalankan pernyataan sebelumnya dari project yang berbeda, pernyataan tersebut akan gagal. Untuk memperbaiki error tersebut, sertakan project ID dalam referensi tabel:

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

Anda juga dapat mereferensikan entity dalam project atau set data yang berbeda dengan set data tempat Anda membuat fungsi:

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

Membuat UDAF JavaScript

Bagian ini menjelaskan berbagai cara untuk membuat UDAF JavaScript di BigQuery. Ada beberapa aturan yang harus diperhatikan saat membuat UDAF JavaScript:

Isi UDAF JavaScript harus berupa literal string yang dikutip yang merepresentasikan kode JavaScript. Untuk mempelajari lebih lanjut berbagai jenis literal string yang dikutip yang dapat Anda gunakan, lihat Format untuk literal yang dikutip.
Hanya encoding jenis tertentu yang diizinkan. Untuk mengetahui informasi selengkapnya, lihat Encoding jenis SQL yang diizinkan dalam UDAF JavaScript.
Isi fungsi JavaScript harus menyertakan empat fungsi JavaScript yang menginisialisasi, menggabungkan, menggabungkan, dan menyelesaikan hasil untuk UDAF JavaScript (initialState, aggregate, merge, dan finalize). Untuk informasi selengkapnya, lihat Fungsi agregasi JavaScript yang diperlukan.
Nilai apa pun yang ditampilkan oleh fungsi initialState atau yang dibiarkan dalam argumen state setelah fungsi aggregate atau merge dipanggil, harus dapat diserialisasi. Jika Anda ingin menggunakan data agregasi yang tidak dapat diserialisasi, seperti fungsi atau kolom simbol, Anda harus menggunakan fungsi serialize dan deserialize yang disertakan. Untuk mempelajari lebih lanjut, lihat Menyerialkan dan mendeserialkan data dalam UDAF JavaScript.

Membuat UDAF JavaScript persisten

Anda dapat membuat UDAF JavaScript yang persisten, yang berarti Anda dapat menggunakan kembali UDAF di beberapa kueri. UDAF persisten aman untuk dipanggil jika dibagikan antarpemilik. UDAF tidak dapat mengubah data, berkomunikasi dengan sistem eksternal, atau mengirim log ke Google Cloud Observability atau aplikasi serupa.

Untuk membuat UDAF persisten, gunakan pernyataan CREATE AGGREGATE FUNCTION tanpa kata kunci TEMP atau TEMPORARY. Anda harus menyertakan set data di jalur fungsi.

Kueri berikut membuat UDAF JavaScript persisten yang disebut 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 |
 *-----*/

Membuat UDAF JavaScript sementara

Anda dapat membuat UDAF JavaScript yang bersifat sementara, yang berarti UDAF hanya ada dalam cakupan satu kueri, skrip, sesi, atau prosedur.

Untuk membuat UDAF sementara, gunakan pernyataan CREATE AGGREGATE FUNCTION dengan kata kunci TEMP atau TEMPORARY.

Kueri berikut membuat UDAF JavaScript sementara yang disebut 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 |
 *-----*/

Menyertakan parameter non-agregat dalam UDAF JavaScript

Anda dapat membuat UDAF JavaScript yang memiliki parameter agregat dan non-agregat.

UDAF biasanya menggabungkan parameter fungsi di semua baris dalam grup. Namun, Anda dapat menentukan parameter fungsi sebagai non-agregat dengan kata kunci NOT AGGREGATE.

Dalam contoh berikut, UDAF JavaScript berisi parameter agregat bernama s dan parameter non-agregat bernama 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 |
 *-----------------*/

Menyerialkan dan mendeserialkan data dalam UDAF JavaScript

BigQuery harus melakukan serialisasi objek apa pun yang ditampilkan oleh fungsi initialState atau yang ada di argumen state setelah fungsi aggregate atau merge dipanggil. BigQuery mendukung menserialisasi objek jika semua kolom adalah salah satu dari berikut ini:

Nilai primitif JavaScript (misalnya: 2, "abc", null, undefined).
Objek JavaScript yang didukung BigQuery untuk menyerialkan semua nilai kolom.
Array JavaScript yang semua elemennya didukung serialisasi oleh BigQuery.

Nilai yang ditampilkan berikut dapat diserialisasi:

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

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

Nilai yang ditampilkan berikut tidak dapat diserialisasi:

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

export function initialState() {
  return 2.3;
}

Jika Anda ingin menggunakan status agregasi yang tidak dapat diserialisasi, UDAF JavaScript harus menyertakan fungsi serialize dan deserialize. Fungsi serialize mengonversi status agregasi menjadi objek yang dapat diserialisasi; fungsi deserialize mengonversi objek yang dapat diserialisasi kembali menjadi status agregasi.

Dalam contoh berikut, library eksternal menghitung jumlah menggunakan antarmuka:

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

Kueri berikut tidak dijalankan karena objek class SumAggregator tidak dapat diserialisasi BigQuery, karena adanya fungsi di dalam class.

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;

Jika Anda menambahkan fungsi serialize dan deserialize ke kueri sebelumnya, kueri akan berjalan karena objek class SumAggregator dikonversi menjadi objek yang dapat diserialisasi BigQuery, lalu kembali menjadi objek class SumAggregator lagi.

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

Untuk mempelajari fungsi serialisasi lebih lanjut, lihat Fungsi serialisasi JavaScript opsional.

Menyertakan variabel global dan fungsi kustom dalam UDAF JavaScript

Isi fungsi JavaScript dapat mencakup kode JavaScript kustom seperti variabel global JavaScript dan fungsi kustom.

Variabel global dieksekusi saat JavaScript dimuat ke dalam BigQuery dan sebelum fungsi initialState dieksekusi. Variabel global mungkin berguna jika Anda perlu melakukan tugas inisialisasi satu kali yang tidak boleh diulang untuk setiap grup agregasi, seperti yang terjadi pada fungsi initialState, aggregate, merge, dan finalize.

Jangan gunakan variabel global untuk menyimpan status agregasi. Sebagai gantinya, batasi status agregasi ke objek yang diteruskan ke fungsi yang diekspor. Hanya gunakan variabel global untuk menyimpan dalam cache operasi mahal yang tidak spesifik untuk operasi agregasi tertentu.

Dalam kueri berikut, fungsi SumOfPrimes menghitung jumlah, tetapi hanya bilangan prima yang disertakan dalam penghitungan. Di isi fungsi JavaScript, ada dua variabel global, primes dan maxTested, yang diinisialisasi terlebih dahulu. Selain itu, ada fungsi kustom bernama isPrime yang memeriksa apakah suatu angka adalah bilangan prima.

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

Menyertakan library JavaScript

Anda dapat memperluas UDAF JavaScript dengan opsi library dalam klausa OPTIONS. Opsi ini memungkinkan Anda menentukan library kode eksternal untuk UDAF JavaScript, lalu mengimpor library tersebut dengan deklarasi import.

Dalam contoh berikut, kode di bar.js tersedia untuk kode apa pun di isi fungsi 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 ...;
  }

''';

Struktur JavaScript yang diperlukan

Tidak seperti UDF JavaScript, yang isi fungsinya adalah JavaScript bentuk bebas yang berjalan untuk setiap baris, isi fungsi untuk UDAF JavaScript adalah modul JavaScript yang berisi beberapa fungsi bawaan yang diekspor, yang dipanggil pada berbagai tahap dalam proses agregasi. Beberapa fungsi bawaan ini wajib ada, sedangkan yang lain bersifat opsional. Anda juga dapat menambahkan fungsi JavaScript.

Fungsi agregasi JavaScript yang diperlukan

Anda dapat menyertakan fungsi JavaScript, tetapi isi fungsi JavaScript harus menyertakan fungsi JavaScript yang dapat diekspor berikut:

initialState([nonAggregateParam]): menampilkan objek JavaScript yang merepresentasikan status agregasi yang belum mengagregasi baris.
aggregate(state, aggregateParam[, ...][, nonAggregateParam]): menggabungkan satu baris data, memperbarui status untuk menyimpan hasil penggabungan. Tidak menampilkan nilai.
merge(state, partialState, [nonAggregateParam]): menggabungkan status agregasi partialState ke dalam status agregasi state. Fungsi ini digunakan saat mesin mengagregasi berbagai bagian data secara paralel dan perlu menggabungkan hasilnya. Tidak menampilkan nilai.
finalize(finalState, [nonAggregateParam]): menampilkan hasil akhir dari fungsi agregat, mengingat status agregasi akhir finalState.

Untuk mempelajari fungsi yang diperlukan lebih lanjut, lihat Fungsi yang diperlukan dalam UDAF JavaScript.

Fungsi serialisasi JavaScript opsional

Jika Anda ingin menggunakan status agregasi yang tidak dapat diserialisasi, UDAF JavaScript harus menyediakan fungsi serialize dan deserialize. Fungsi serialize mengonversi status agregasi menjadi objek yang dapat diserialisasi BigQuery; fungsi deserialize mengonversi objek yang dapat diserialisasi BigQuery kembali menjadi status agregasi.

serialize(state): menampilkan objek yang dapat diserialisasi yang berisi informasi dalam status agregasi, yang akan dideserialisasi melalui fungsi deserialize.
deserialize(serializedState): mendeserialisasi serializedState (sebelumnya diserialisasi oleh fungsi serialize) ke dalam status agregasi yang dapat diteruskan ke fungsi serialize, aggregate, merge, atau finalize.

Untuk mempelajari lebih lanjut fungsi serialisasi JavaScript bawaan, lihat Fungsi serialisasi untuk UDAF JavaScript.

Untuk mempelajari cara melakukan serialisasi dan deserialisasi data dengan UDAF JavaScript, lihat Melakukan serialisasi dan deserialisasi data di UDAF JavaScript.

Encoding jenis SQL yang diizinkan dalam UDAF JavaScript

Dalam UDAF JavaScript, jenis data GoogleSQL yang didukung berikut merepresentasikan jenis data JavaScript sebagai berikut:

Jenis data GoogleSQL	Jenis data JavaScript	Catatan
`ARRAY`	`Array`	Array array tidak didukung. Untuk mengatasi batasan ini, gunakan jenis data `Array<Object<Array>>` (JavaScript) dan `ARRAY<STRUCT<ARRAY>>` (GoogleSQL).
`BIGNUMERIC`	`Number` atau `String`	Sama seperti `NUMERIC`.
`BOOL`	`Boolean`
`BYTES`	`Uint8Array`
`DATE`	`Date`
`FLOAT64`	`Number`
`INT64`	`BigInt`
`JSON`	Berbagai jenis	Jenis data `JSON` GoogleSQL dapat dikonversi menjadi `Object`, `Array`, atau jenis data JavaScript lain yang didukung GoogleSQL.
`NUMERIC`	`Number` atau `String`	Jika nilai `NUMERIC` dapat direpresentasikan secara persis sebagai nilai floating point IEEE 754 (rentang `[-2⁵³, 2⁵³]`), dan tidak memiliki bagian pecahan, nilai tersebut akan dienkode sebagai jenis data `Number`, jika tidak, nilai tersebut akan dienkode sebagai jenis data `String`.
`STRING`	`String`
`STRUCT`	`Object`	Setiap kolom `STRUCT` adalah properti bernama dalam jenis data `Object`. Kolom `STRUCT` yang tidak diberi nama tidak didukung.
`TIMESTAMP`	`Date`	`Date` berisi kolom mikrodetik dengan fraksi mikrodetik `TIMESTAMP`.

Memanggil UDAF

Bagian ini menjelaskan berbagai cara Anda dapat memanggil UDAF persisten atau sementara setelah membuatnya di BigQuery.

Memanggil UDAF persisten

Anda dapat memanggil UDAF persisten dengan cara yang sama seperti memanggil fungsi agregat bawaan. Untuk mengetahui informasi selengkapnya, lihat Panggilan fungsi agregasi. Anda harus menyertakan set data dalam jalur fungsi.

Dalam contoh berikut, kueri memanggil UDAF persisten yang disebut 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
);

Tabel dengan hasil berikut akan dibuat:

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

Memanggil UDAF sementara

Anda dapat memanggil UDAF sementara dengan cara yang sama seperti memanggil fungsi agregat bawaan. Untuk mengetahui informasi selengkapnya, lihat Panggilan fungsi agregasi.

Fungsi sementara harus disertakan dalam kueri multi-pernyataan atau prosedur yang berisi panggilan fungsi UDAF.

Dalam contoh berikut, kueri memanggil UDAF sementara yang disebut 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
);

Tabel dengan hasil berikut akan dibuat:

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

Mengabaikan atau menyertakan baris dengan nilai `NULL`

Saat UDAF JavaScript dipanggil dengan argumen IGNORE NULLS, BigQuery akan otomatis melewati baris yang argumen agregatnya dievaluasi menjadi NULL. Baris tersebut sepenuhnya dikecualikan dari agregasi dan tidak diteruskan ke fungsi JavaScript aggregate. Jika argumen RESPECT NULLS diberikan, pemfilteran NULL akan dinonaktifkan, dan setiap baris akan diteruskan ke UDAF JavaScript, terlepas dari nilai NULL.

Jika argumen IGNORE NULLS maupun RESPECT NULLS tidak diberikan, argumen defaultnya adalah IGNORE NULLS.

Contoh berikut menggambarkan perilaku NULL default, perilaku IGNORE NULLS, dan perilaku 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            |
 *-----+------------------+-------------------*/

Menghapus UDAF

Bagian ini menjelaskan berbagai cara untuk menghapus UDAF persisten atau sementara setelah Anda membuatnya di BigQuery.

Menghapus UDAF persisten

Untuk menghapus UDAF persisten, gunakan pernyataan DROP FUNCTION. Anda harus menyertakan set data dalam jalur fungsi.

Dalam contoh berikut, kueri menghapus UDAF persisten yang disebut WeightedAverage:

DROP FUNCTION IF EXISTS my_project.my_dataset.WeightedAverage;

Menghapus UDAF sementara

Untuk menghapus UDAF sementara, gunakan pernyataan DROP FUNCTION.

Dalam contoh berikut, kueri menghapus UDAF sementara yang dipanggil WeightedAverage:

DROP FUNCTION IF EXISTS WeightedAverage;

UDAF sementara akan berakhir segera setelah kueri selesai. UDAF tidak perlu dihapus kecuali jika Anda ingin menghapusnya lebih awal dari kueri multi-pernyataan atau prosedur.

Mencantumkan UDAF

UDAF adalah jenis rutinitas. Untuk mencantumkan semua rutinitas dalam set data, lihat Mencantumkan rutinitas.

Tips performa

Jika Anda ingin meningkatkan performa kueri, pertimbangkan hal berikut:

Filter input Anda terlebih dahulu. Memproses data di JavaScript lebih mahal daripada di SQL, jadi sebaiknya filter input sebanyak mungkin di SQL terlebih dahulu.

Kueri berikut kurang efisien karena memfilter input menggunakan x > 0 dalam panggilan UDAF:
```
SELECT JsFunc(x) FROM t;
```
Kueri berikut lebih efisien karena melakukan prapenyaringan input dengan menggunakan WHERE x > 0 sebelum UDAF dipanggil:
```
SELECT JsFunc(x) FROM t WHERE x > 0;
```
Gunakan fungsi agregat bawaan, bukan JavaScript, jika memungkinkan. Mengimplementasikan ulang fungsi agregat bawaan di JavaScript lebih lambat daripada memanggil fungsi agregat bawaan yang melakukan hal yang sama.

Kueri berikut kurang efisien karena menerapkan UDAF:
```
SELECT SumSquare(x) FROM t;
```
Kueri berikut lebih efisien karena menerapkan fungsi bawaan yang menghasilkan hasil yang sama dengan kueri sebelumnya:
```
SELECT SUM(x*x) FROM t;
```
UDAF JavaScript cocok untuk operasi agregasi yang lebih kompleks, yang tidak dapat dinyatakan melalui fungsi bawaan.
Menggunakan memori secara efisien. Lingkungan pemrosesan JavaScript menyediakan memori yang terbatas untuk setiap kueri. Kueri UDAF JavaScript yang mengakumulasi terlalu banyak status lokal mungkin gagal karena kehabisan memori. Terutama perhatikan untuk meminimalkan ukuran objek status agregasi dan hindari status agregasi yang mengakumulasi sejumlah besar baris.

Kueri berikut tidak efisien karena fungsi aggregate menggunakan jumlah memori yang tidak terbatas saat jumlah baris yang diproses menjadi besar.
```
export function initialState() {
  return {rows: []};
}
export function aggregate(state, x) {
  state.rows.push(x);
}
...
```
Gunakan tabel berpartisi jika memungkinkan. UDAF JavaScript biasanya berjalan lebih efisien saat membuat kueri terhadap tabel berpartisi dibandingkan dengan tabel yang tidak berpartisi, karena tabel berpartisi menyimpan data dalam banyak file yang lebih kecil dibandingkan dengan tabel yang tidak berpartisi, sehingga memungkinkan paralelisme yang lebih tinggi.

Batasan

UDAF memiliki batasan yang sama dengan yang berlaku untuk UDF. Untuk mengetahui detailnya, lihat Batasan UDF.
Hanya literal, parameter kueri, dan variabel skrip yang dapat diteruskan sebagai argumen non-agregat untuk UDAF.

Penggunaan klausa ORDER BY dalam panggilan fungsi UDAF JavaScript tidak didukung.

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

Harga

UDAF ditagih menggunakan model harga BigQuery standar.

Kuota dan batas

UDAF memiliki kuota dan batas yang sama dengan yang berlaku untuk UDF. Untuk mengetahui informasi tentang kuota UDF, lihat Kuota dan batas.