Fungsi agregat yang ditentukan pengguna

Untuk dukungan selama pratinjau, kirim email ke [email protected].

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

UDAF memungkinkan Anda membuat fungsi agregat menggunakan ekspresi yang berisi kode. UDAF menerima kolom input, melakukan penghitungan pada sekelompok baris dalam satu waktu, lalu menampilkan hasil penghitungan 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, artinya Anda dapat menggunakan kembali UDAF tersebut pada beberapa kueri. UDAF persisten aman untuk dipanggil jika dibagikan di antara pemilik. UDAF tidak dapat mengubah data, berkomunikasi dengan sistem eksternal, atau mengirim log ke Kemampuan observasi Google Cloud 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 kueri, skrip, sesi, atau prosedur tunggal.

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-agregat

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-agregasi adalah parameter fungsi skalar dengan nilai konstan untuk semua baris dalam grup. Parameter fungsi non-agregasi yang valid harus berupa literal. Di dalam definisi UDAF, parameter fungsi agregat hanya dapat muncul sebagai argumen fungsi untuk menggabungkan panggilan fungsi. 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-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, semua 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 dalam 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 )
);

Memanggil UDAF SQL

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

Memanggil UDAF SQL persisten

Anda dapat memanggil UDAF SQL persisten dengan cara yang sama seperti memanggil fungsi agregat bawaan. Untuk informasi selengkapnya, lihat Aturan panggilan fungsi. Anda harus menyertakan set data di jalur fungsi.

Misalnya, kueri berikut memanggil UDAF persisten yang disebut ScaledAverage:

SELECT myproject.mydataset.ScaledAverage(col1, 2) AS scaled_avg
FROM (
  SELECT 1 AS col1 UNION ALL
  SELECT 3 AS col1 UNION ALL
  SELECT 5 AS col1
);

Tabel dengan hasil berikut dihasilkan:

/*------------*
 | scaled_avg |
 +------------+
 | 1.5        |
 *------------*/

Memanggil UDAF SQL sementara

Anda dapat memanggil UDAF SQL sementara dengan cara yang sama seperti memanggil fungsi agregat bawaan. Untuk mengetahui detailnya, lihat Aturan panggilan fungsi.

Misalnya, kueri berikut memanggil UDAF sementara yang disebut ScaledAverage:

SELECT ScaledAverage(col1, 2) AS scaled_avg
FROM (
  SELECT 1 AS col1 UNION ALL
  SELECT 3 AS col1 UNION ALL
  SELECT 5 AS col1
);

Tabel dengan hasil berikut dihasilkan:

/*------------*
 | scaled_avg |
 +------------+
 | 1.5        |
 *------------*/

Menghapus UDAF SQL

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

Menghapus UDAF SQL persisten

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

Misalnya, kueri berikut menghapus UDAF persisten yang disebut ScaledAverage:

DROP FUNCTION IF EXISTS myproject.mydataset.ScaledAverage;

Menghapus UDAF SQL sementara

Untuk menghapus UDAF sementara, gunakan pernyataan DROP FUNCTION.

Misalnya, kueri berikut menghapus UDAF sementara yang disebut ScaledAverage:

DROP FUNCTION IF EXISTS ScaledAverage;

UDAF sementara akan berakhir segera setelah kueri selesai, dan 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.

Batasan

UDAF SQL memiliki batasan yang sama dengan UDF. Untuk mengetahui detailnya, lihat Batasan UDF.
Hanya literal yang dapat diteruskan sebagai argumen non-agregat untuk SQL UDAF.

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.