Datawise
Practical SQL

Using ANY_VALUE() in BigQUERY

Constantin LunguUpdated 1 min read

Photo by Edge2Edge Media on Unsplash

Have you ever used ANY_VALUE in BigQuery?

It's an aggregate function like SUM or AVG, but it returns a non-deterministic (not random) row from the group. I've been using it in scenarios where there's one value anyway, such as when PIVOTing.

BigQuery console result of the input data for ANY_VALUE with PIVOT: columns Region, quarter and sales, with Europe and MEA sales for Q1 to Q4, such as Europe Q1 250000 and MEA Q3 300000.

WITH input_data AS (
  SELECT 'Europe' AS Region, 'Q1' AS quarter, 250000 AS sales
  UNION ALL
  SELECT 'Europe' AS Region, 'Q2' AS quarter, 225000 AS sales
  UNION ALL
  SELECT 'Europe' AS Region, 'Q3' AS quarter, 275000 AS sales
  UNION ALL
  SELECT 'Europe' AS Region, 'Q4' AS quarter, 290000 AS sales
  UNION ALL
  SELECT 'MEA' AS Region, 'Q1' AS quarter, 190000 AS sales
  UNION ALL
  SELECT 'MEA' AS Region, 'Q2' AS quarter, 210000 AS sales
  UNION ALL
  SELECT 'MEA' AS Region, 'Q3' AS quarter, 300000 AS sales
  UNION ALL
  SELECT 'MEA' AS Region, 'Q4' AS quarter, 220000 AS sales
)

SELECT * FROM input_data

PIVOT(ANY_VALUE(sales) as sales FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'));

BigQuery console result of PIVOT with ANY_VALUE(sales) FOR quarter: one row per Region with columns sales_Q1 to sales_Q4, Europe 250000, 225000, 275000, 290000 and MEA 190000, 210000, 300000, 220000.

Upon documenting myself for this post, I found an interesting thing - it supports the HAVING clause, allowing us to restrict the rows this function is aggregating, either by a MIN or MAX of a given expression.

Let's look at how it works. Say we have the following data:

BigQuery console result of sample sales data with columns country, product_id, quantity and price: Germany productA 200 at 5.0, productB 75 at 100.0, productC 100 at 120.0; Spain productA 300 at 5.0, productD 250 at 20.0, productE 100 at 15.0.

We're going to compute the product that has sold the highest by value and the product that has sold the least by quantity in each of the countries.

WITH input_data AS (
  SELECT 'Germany' AS country, 'productA' AS product_id, 200 AS quantity, 5.00 AS price
  UNION ALL
    SELECT 'Germany' AS country, 'productB' AS product_id, 75 AS quantity, 100.00 AS price
  UNION ALL
    SELECT 'Germany' AS country, 'productC' AS product_id, 100 AS quantity, 120.00 AS price
  UNION ALL
    SELECT 'Spain' AS country, 'productA' AS product_id, 300 AS quantity, 5.00 AS price
  UNION ALL
    SELECT 'Spain' AS country, 'productD' AS product_id, 250 AS quantity, 20.00 AS price
  UNION ALL
    SELECT 'Spain' AS country, 'productE' AS product_id, 100 AS quantity, 15.00 AS price
)

SELECT 
    country, 
    ANY_VALUE(product_id HAVING MAX quantity*price) AS highest_selling_by_value,
    ANY_VALUE(product_id HAVING MIN quantity) AS lowest_selling_by_quantity,
FROM input_data
GROUP BY country

Here's what the results would look like:

BigQuery console result of ANY_VALUE with HAVING MAX and HAVING MIN, grouped by country: Germany has highest_selling_by_value productC and lowest_selling_by_quantity productB, while Spain has productD and productE.

Thanks for reading!