Aggregate

ANY_VALUE

BigQuery

Returns one value from a group non-deterministically. Useful for retrieving a value from a column that is not being aggregated when all values in the group are the same.

Tipe hasil: Same as input typeDiperbarui: 13 Jun 2026

Syntax

SQL

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

Parameter

expressionanywajib

A column or expression to retrieve a value from

HAVING MAX/MINclauseopsional

Returns the value from the row with the MAX or MIN of a second expression

Contoh Penggunaan

Retrieve Details from a Group

SQL

1 SELECT
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
7 FROM `project.dataset.order_details`
8 GROUP BY order_id;

Retrieves customer info (which is the same per order) without aggregating.

Hasil

order_id	customer_name	customer_email	total_items	total_amount
O001	John Doe	john@email.com	5	1500000
O002	Jane Smith	jane@email.com	3	850000

ANY_VALUE with HAVING MAX

SQL

1 SELECT
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
5 FROM `project.dataset.price_history`
6 GROUP BY product_id;

Retrieves the most recent price based on the latest date.

Hasil

product_id	latest_price	price_date
P001	1500000	2024-06-25
P002	850000	2024-06-24

ANY_VALUE with HAVING MIN

SQL

1 SELECT
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
5 FROM `project.dataset.employee_history`
6 GROUP BY employee_id;

Shows each employee's first and current department.

Hasil

employee_id	first_department	current_department
E001	Junior Dev	Senior Engineer
E002	Intern	Product Manager

Pertanyaan Umum tentang ANY_VALUE

Apa itu fungsi ANY_VALUE di BigQuery?

Returns one value from a group non-deterministically. Useful for retrieving a value from a column that is not being aggregated when all values in the group are the same. Di BigQuery, fungsi ANY_VALUE termasuk dalam kelompok fungsi yang sering digunakan untuk mengolah dan menganalisis data secara efisien.

Bagaimana cara menggunakan ANY_VALUE di BigQuery?

Gunakan sintaks berikut: ANY_VALUE(expression [HAVING {MAX | MIN} expression2]). Pastikan argumen yang dimasukkan sudah sesuai dengan tipe data yang diharapkan.

Apa nilai yang dikembalikan oleh fungsi ANY_VALUE?

Fungsi ANY_VALUE mengembalikan nilai bertipe Same as input type. Pastikan tipe data hasil sudah sesuai dengan kebutuhan query atau formula kamu. Jika input mengandung nilai NULL, perilaku fungsi dapat berbeda — selalu periksa dokumentasi untuk memastikan hasilnya sesuai ekspektasi.

Apa saja parameter fungsi ANY_VALUE?

Fungsi ini memiliki 1 parameter wajib dan 1 parameter opsional. Parameter yang digunakan: expression (any, wajib): A column or expression to retrieve a value from; HAVING MAX/MIN (clause, opsional): Returns the value from the row with the MAX or MIN of a second expression.

Ngulik Langsung

Sudah paham ANY_VALUE? Latih langsung di browser

Latihan interaktif, langsung di browser.

Latihan Langsung →

Kembali ke BigQuery Functions

1	SELECT
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
7	FROM `project.dataset.order_details`
8	GROUP BY order_id;

1	SELECT
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
5	FROM `project.dataset.price_history`
6	GROUP BY product_id;

1	SELECT
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
5	FROM `project.dataset.employee_history`
6	GROUP BY employee_id;