ANY_VALUE

any_value(expr)

Description

The ANY_VALUE function is used to randomly select and return a value from a set of data. When processing multiple data rows, this function can simplify queries and improve efficiency.

Parameter Description

• expr: An expression of any numeric type (such as tinyint, smallint, int, bigint, float, double, decimal) or string type (such as string, char, varchar) or complex type.

Return Result

• Returns a value of the same type as the input parameter expr.
• If the input parameter contains null values, null values will also be included in the calculation.

Usage Example

1. Randomly select a value from a set of integers:

<Notes> ``` > SELECT any_value(col) FROM VALUES (1), (2), (3), (4), (null) tab(col); Result: 1 ``` 2. Randomly select a value from a set of strings: ``` > SELECT any_value(col) FROM VALUES ('apple'), ('banana'), ('cherry') tab(col); Result: 'apple' ``` ### 3. Use the ANY_VALUE function in complex queries: {#use-any-value-function-in-complex-queries}

> SELECT any_value(city) FROM customers WHERE country = 'China';
Result: May return the name of a city in China, such as 'Beijing'

4. Randomly select a value from data containing null values:

> SELECT any_value(col) FROM VALUES (cast(null as int)), (5), (6) tab(col);
Result: 5