Google menggunakan teknologi AI untuk menerjemahkan konten ke dalam bahasa pilihan Anda. Terjemahan AI mungkin mengandung kesalahan.

Fungsi agregat yang ditentukan pengguna

Fungsi agregat yang ditentukan pengguna (UDAF) di BigQuery memungkinkan Anda membangun logika agregasi kustom menggunakan kode SQL atau JavaScript. Anda dapat memproses kolom input sebagai grup untuk melakukan penghitungan khusus pada beberapa baris sekaligus dan menampilkan hasilnya 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 saat dibagikan di antara pemilik. 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 konstan untuk semua baris dalam grup. Parameter fungsi non-agregat yang valid harus berupa literal. Di 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 gabungan yang disebut dividend, dan parameter non-gabungan 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 dalam isi fungsi

Dalam isi UDAF SQL, setiap referensi ke entity BigQuery, seperti tabel atau tabel virtual, 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 tersebut 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 mewakili 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 melakukan inisialisasi, agregasi, penggabungan, dan finalisasi hasil untuk UDAF JavaScript (initialState, aggregate, merge, dan finalize). Untuk mengetahui informasi selengkapnya, lihat Fungsi agregasi JavaScript yang diperlukan.
Setiap nilai yang ditampilkan oleh fungsi initialState atau yang tersisa dalam argumen state setelah fungsi aggregate atau merge dipanggil, harus dapat diserialisasi. Jika ingin menggunakan data gabungan yang tidak dapat diserialisasi, seperti fungsi atau kolom simbol, Anda harus menggunakan fungsi serialize dan deserialize yang disertakan. Untuk mempelajari lebih lanjut, lihat Melakukan serialisasi dan deserialisasi 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 saat dibagikan di antara pemilik. 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 yang disebut s dan parameter non-agregat yang disebut 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 |
 *-----------------*/

Melakukan serialisasi dan deserialisasi data dalam UDAF JavaScript

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

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

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 ke 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 menyertakan kode JavaScript kustom seperti variabel global JavaScript dan fungsi kustom.

Variabel global dieksekusi saat JavaScript dimuat ke BigQuery dan sebelum fungsi initialState dieksekusi. Variabel global mungkin berguna jika Anda perlu melakukan pekerjaan inisialisasi satu kali yang tidak boleh diulang untuk setiap grup agregasi, seperti halnya 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 meng-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. Dalam 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 di klausa OPTIONS. Opsi ini memungkinkan Anda menentukan library kode eksternal untuk UDAF JavaScript, lalu mengimpor library tersebut dengan deklarasi import.

Pada contoh berikut, kode dalam bar.js tersedia untuk kode apa pun dalam 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 ekspor bawaan, 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 mewakili status agregasi yang belum menggabungkan baris apa pun.
aggregate(state, aggregateParam[, ...][, nonAggregateParam]): menggabungkan satu baris data, memperbarui status untuk menyimpan hasil agregasi. Tidak menampilkan nilai.
merge(state, partialState, [nonAggregateParam]): menggabungkan status agregasi partialState ke dalam status agregasi state. Fungsi ini digunakan saat mesin menggabungkan berbagai bagian data secara paralel dan perlu menggabungkan hasilnya. Tidak menampilkan nilai.
finalize(finalState, [nonAggregateParam]): menampilkan hasil akhir fungsi agregat, dengan status agregasi akhir finalState.

Untuk mempelajari lebih lanjut fungsi yang diperlukan, 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): melakukan deserialisasi serializedState (sebelumnya diserialisasi oleh fungsi serialize) menjadi 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 dalam UDAF JavaScript.

Encoding jenis SQL yang diizinkan dalam UDAF JavaScript

Dalam UDAF JavaScript, jenis data GoogleSQL yang didukung berikut mewakili 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 lainnya 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` tanpa nama tidak didukung.
`TIMESTAMP`	`Date`	`Date` berisi kolom mikrodetik dengan pecahan mikrodetik `TIMESTAMP`.

Memanggil UDAF

Bagian ini menjelaskan berbagai cara untuk memanggil UDAF persisten atau sementara setelah Anda 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 agregat. Anda harus menyertakan set data di 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 agregat.

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 bernilai NULL. Baris tersebut sepenuhnya dikecualikan dari agregasi dan tidak diteruskan ke fungsi aggregate JavaScript. 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 atau RESPECT NULLS tidak diberikan, argumen defaultnya adalah IGNORE NULLS.

Contoh berikut mengilustrasikan 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 di 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 disebut 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.

Daftar 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 memfilter input terlebih dahulu menggunakan WHERE x > 0 sebelum UDAF dipanggil:
```
SELECT JsFunc(x) FROM t WHERE x > 0;
```
Gunakan fungsi agregat bawaan, bukan JavaScript, jika memungkinkan. Menerapkan 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 sesuai 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. Berhati-hatilah dalam meminimalkan ukuran objek status agregasi dan hindari status agregasi yang mengakumulasi baris dalam jumlah besar.

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.