Combining ANY_VALUE with HAVING in BigQuery

Here's another rather rare instance where I've used ANY_VALUE in
BigQuery. It's basically an aggregation function like SUM or COUNT except it retrieves a arbitrary value from the grouping.

I've posted about ANY_VALUE before, but today's query was a bit different.

In the example below, my goal is to find orders that contain a single value, belonging to a particular list of values.

In other words, which order consisted of exactly one item, with that being grapes or oranges?

We aggregate using COUNT to count the number of order lines in an order and ANY_VALUE to pick a random value from the list of order lines.

We then filter the aggregate results using HAVING, keeping only order that have exactly one order line and that order line being a grape or orange.

With ANY_VALUE of a single value being always that value, we can filter the result sets to what we need. It's entirely true that the same can be said of MIN or MAX for instance, but I think it would have been a little less obvious of why it was chosen like that.

As almost always with SQL, there are of course plenty of other ways to achieve the same result.

WITH input_data AS (

  SELECT 1 AS order_id, 'apples' AS order_line
  UNION ALL
  SELECT 1 AS order_id, 'pears' AS order_line
  UNION ALL
  SELECT 1 AS order_id, 'grapes' AS order_line
  UNION ALL
  SELECT 2 AS order_id, 'grapes' AS order_line
  UNION ALL
  SELECT 3 AS order_id, 'oranges' AS order_line
  UNION ALL
  SELECT 4 AS order_id, 'kiwi' AS order_line
)

SELECT
  order_id,
  COUNT(order_line) AS count_order_lines,
  ANY_VALUE(order_line) AS product_name

FROM input_data

GROUP BY order_id

HAVING COUNT(order_line) = 1 AND
       ANY_VALUE(order_line) IN ('grapes', 'oranges')