Aggregate

ANY_VALUE

BigQueryBigQuery

Mengembalikan salah satu nilai dari group secara non-deterministik. Berguna untuk mengambil nilai dari kolom yang tidak di-aggregate ketika semua nilai dalam group sama.

Tipe hasil: Sama dengan tipe inputDiperbarui: 7 Jan 2026

Syntax

SQL
ANY_VALUE(expression [HAVING {MAX | MIN} expression2])

Parameter

expressionanywajib

Kolom atau ekspresi yang akan diambil nilainya

HAVING MAX/MINclauseopsional

Mengambil nilai berdasarkan row dengan MAX/MIN dari expression2

Contoh Penggunaan

Mengambil Detail dari Group

SQL
1SELECT
2  order_id,
3  ANY_VALUE(customer_name) as customer_name,
4  ANY_VALUE(customer_email) as customer_email,
5  SUM(quantity) as total_items,
6  SUM(amount) as total_amount
7FROM `project.dataset.order_details`
8GROUP BY order_id;

Mengambil info customer (yang sama per order) tanpa aggregate.

Hasil
order_idcustomer_namecustomer_emailtotal_itemstotal_amount
O001John Doejohn@email.com51500000
O002Jane Smithjane@email.com3850000

ANY_VALUE dengan HAVING MAX

SQL
1SELECT
2  product_id,
3  ANY_VALUE(price HAVING MAX sale_date) as latest_price,
4  ANY_VALUE(sale_date HAVING MAX sale_date) as price_date
5FROM `project.dataset.price_history`
6GROUP BY product_id;

Mengambil harga terbaru berdasarkan tanggal terakhir.

Hasil
product_idlatest_priceprice_date
P00115000002024-06-25
P0028500002024-06-24

ANY_VALUE dengan HAVING MIN

SQL
1SELECT
2  employee_id,
3  ANY_VALUE(department HAVING MIN start_date) as first_department,
4  ANY_VALUE(department HAVING MAX start_date) as current_department
5FROM `project.dataset.employee_history`
6GROUP BY employee_id;

Melihat departemen pertama dan saat ini dari karyawan.

Hasil
employee_idfirst_departmentcurrent_department
E001Junior DevSenior Engineer
E002InternProduct Manager
Kembali ke BigQuery Functions