Question

You have this SQL Statement in Bigquery:
SELECT ANY_VALUE(fruit) as any_value
FROM UNNEST([“apple”, “banana”, “pear”]) as fruit;
What is the result of this query?

A. a table with 3 rows
B. Apple
C. Banana
D. Pear
E. one of the values, randomly

Answer 1

Correct Answer: E
The interesting elements in this query are:

• UNNEST: takes an ARRAY and returns a table, UNNEST may be used outside of the FROM clause with the IN operator
• Analytic Functions: a function that computes aggregate values over a group of rows.
  • ANY_VALUE function that returns a random value from the input or NULL if there are zero input rows
    So, from the table created on the fly from an array, you may pick a casual value each time you execute the query.
    A more complex statement is:
    SELECT fruit,
    ANY_VALUE(fruit) OVER (ORDER BY LENGTH(fruit) ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS any_value
    FROM UNNEST([“apple”, “banana”, “pear”]) as fruit;
    In the reference, you will find a complete explanation.