ANY_VALUE
One of the new functions you learned that is specific to GoogleSQL is the ANY_VALUE aggregate that allows you to return a value from a text column. You can use this in combination with HAVING to find a maximum or minimum value. This allows us to have some flexibility to return a single value when aggregating across a large dataset.
This is a part of the course
“Introduction to BigQuery”
Exercise instructions
- Complete the query by using the
ANY_VALUEaggregate function on theproduct_category_name_englishcolumn, including theHAVINGclause to find the maximum value ofproduct_photos_qty, and assign the aliasrandom_productto the result.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Finish the query below by adding the ANY_VALUE function
SELECT
product_weight_g,
-- Complete the query by adding the ANY_VALUE function on the product category column
___
FROM ecommerce.ecomm_products
GROUP BY product_weight_g;This exercise is part of the course
Introduction to BigQuery
Unlock BigQuery's power: grasp its fundamentals, execute queries, and optimize workflows for efficient data analysis.
Learn how to query data using common table expressions, aggregations, and window functions.
Exercise 1: Common table expressionsExercise 2: Filtering data with CTEsExercise 3: Optimizing using CTEsExercise 4: Using multiple CTEsExercise 5: AggregationsExercise 6: Classifying aggregate functionsExercise 7: Using COUNTIFExercise 8: Filtering with HAVINGExercise 9: ANY_VALUEExercise 10: Special aggregations in BigQueryExercise 11: Logical aggregatesExercise 12: Using STRING_AGG and ARRAY_CONCAT_AGGExercise 13: Approximate statistical functionsExercise 14: WINDOW FunctionsExercise 15: Writing a window queryExercise 16: RANK and LEAD/LAGExercise 17: Using row based WINDOW functionsExercise 18: Filtering with QUALIFYWhat is DataCamp?
Learn the data skills you need online at your own pace—from non-coding essentials to data science and machine learning.