Categories:

Aggregate Functions (General) , Window Functions (General)

ANY_VALUE

Returns some value of the expression from the group. The result is non-deterministic.

Syntax

Aggregate function

ANY_VALUE( [ DISTINCT ] <expr1> )

Window function

ANY_VALUE( [ DISTINCT ] <expr1> ) OVER ( [ PARTITION BY <expr2> ] )

Usage Notes

  • The DISTINCT keyword can be specified for this function, but it does not have any effect.

  • When used as a window function:

    • This function does not support:

      • ORDER BY sub-clause in the OVER() clause.

      • Window frames.

Using with GROUP BY Statements

ANY_VALUE can be used to simplify and optimize the performance of GROUP BY statements. A common problem for many queries is that the result of a query with a GROUP BY clause can only contain expressions used in the GROUP BY clause itself, or results of aggregate functions. For example:

SELECT customer.id , customer.name , SUM(orders.value)
    FROM customer
    JOIN orders ON customer.id = orders.customer_id
    GROUP BY customer.id , customer.name;

In this query, the customer.name attribute needs to be in the GROUP BY to be included in the result. This is unnecessary (e.g. when customer.id is known to be unique) and makes the computation possibly more complex and slower. Another option is to use an aggregate function. For example:

SELECT customer.id , MIN(customer.name) , SUM(orders.value)
    FROM customer
    JOIN orders ON customer.id = orders.customer_id
    GROUP BY customer.id;

This simplifies the GROUP BY clause, but still requires computing the MIN function, which incurs an extra cost.

With ANY_VALUE, you can execute the following query:

SELECT customer.id , ANY_VALUE(customer.name) , SUM(orders.value)
    FROM customer
    JOIN orders ON customer.id = orders.customer_id
    GROUP BY customer.id;

Examples

Setup:

CREATE OR REPLACE TABLE example_any(k int, d decimal(10,5));
INSERT INTO example_any VALUES (1, 1), (1, 5), (1, 10), (2, 2), (2, NULL), (2, 20);

SELECT * FROM example_any;

+---+----------+
| K |        D |
|---+----------|
| 1 |  1.00000 |
| 1 |  5.00000 |
| 1 | 10.00000 |
| 2 |  2.00000 |
| 2 |     NULL |
| 2 | 20.00000 |
+---+----------+

Return any value from example_any, grouped by k:

SELECT k, ANY_VALUE(d) FROM example_any GROUP BY k;

+---+--------------+
| K | ANY_VALUE(D) |
|---+--------------|
| 1 |      1.00000 |
| 2 |      2.00000 |
+---+--------------+

This example is identical to the previous example, demonstrating the non-determinism of the results:

SELECT k, ANY_VALUE(d) FROM example_any GROUP BY k;

+---+--------------+
| K | ANY_VALUE(D) |
|---+--------------|
| 1 |      5.00000 |
| 2 |     20.00000 |
+---+--------------+