GCP – Beyond GROUP BY: Introducing advanced aggregation functions in BigQuery

When it comes to large-scale data analysis, BigQuery is a powerhouse, with fundamental aggregate functions like SUM, AVG, and COUNT allowing you to extract meaningful insights for all types of workloads. And today, we’re excited to take your data analytics to the next level with a suite of advanced aggregation features that unlock more complex and sophisticated use cases. These aggregate functions fall into three categories: 

• Group by extension (grouping sets/cube, group by struct, array, group by all)

• User-defined aggregate functions (Javascript/SQL UDAFs) 

• Approximate aggregate functions (KLL quantiles, Apache DataSketches)

We built these functions as they were top feature requests from our customer council group. Here is what New York Times had to say:

“I just want to say thank you to BigQuery’s team who launched GROUP BY ROLLUP. We had a daily query taking more than 2 hours to run that now takes 10 minutes using this instead, and many other teams that want to use it now because of it. We saw slot consumption drop by about 96%!” – Edward Podojil, consultant, The New York Times

Let’s take a deeper look at these new aggregate functions, and how to use them in your data analytics workflows. 

Group-by extensions

Grouping sets provide flexibility in calculating aggregations in multiple dimensions in one single statement without having to use UNION ALL. Group by struct, array allows you to easily group by commonly used data types in BigQuery. Group by lets you group by all non-aggregate columns in the Select statement without having to repeat each column twice. 

GROUP BY GROUPING SETS, CUBE (GA) 

Users often need to slice and dice their data in multiple dimensions. Currently you have to rely on repeated UNION ALL/CROSS Join to group your data, which can make queries cumbersome and hard to understand. GROUP BY GROUPING SETS allow you to group different dimensions in a single statement. For example, the following query give you the sum of amount grouped by different combinations: 

• Date: total sales per day

• Region: total sales per region

• Product: total sales per product

GROUP BY CUBE(x, y) is a shorthand syntax for GROUP BY GROUPING SETS ((x,y), x, y, ()), so you can GROUP BY all combinations of different dimensions.

GROUP BY STRUCT, ARRAY (GA)
STRUCT and ARRAY are among the most commonly used data types in BigQuery today. Working with STRUCT and ARRAY data in BigQuery just got easier! You can now use GROUP BY and SELECT DISTINCT for these semi-structured data types directly. This means no more time-consuming workarounds like converting STRUCT/ARRAY to JSON strings. This will simplify your queries and boost performance, making complex analysis more efficient (documentation).

GROUP BY ALL (GA)
GROUP BY ALL deduces non-aggregated columns from the SELECT clause, eliminating the need to list the same columns twice in SELECT and GROUP BY. It’s often used in queries with many dimensions and few aggregations. Listing all columns twice would be long and tedious. For example, you can group Chicago taxi trips by company, payment_type and taxi_id with a simple GROUP BY ALL query.

User-defined aggregate functions (Javascript/SQL UDAF)

User-defined aggregate functions, or UDAFs, let you define custom aggregations once, and reuse them across projects and teams. You no longer need to write repeated logic again and again to unlock advanced functionality like weighted average, merging JSON data, or even simulating geospatial functions.

Javascript UDAF (GA)

JavaScript user-defined aggregate functions (JS UDAFs) let you create custom aggregation logic beyond built-in functions, so you can calculate metrics tailored precisely to your needs. For instance, you can craft UDAFs to compute weighted averages, specialized statistics, or even construct data sketches. Here’s an example of a JavaScript UDAF that simulates the mode() function, returning the most frequent value within a group.

SQL UDAF (GA)

SQL user-defined aggregate functions (SQL UDAFs) allow users to encapsulate complex aggregate expressions into a UDF, for composability and reusability without having to write the same code again and again. For example, you can wrap multiple aggregate function calls into a UDAF through a struct constructor.

Approximate aggregate functions 

Companies across ad-tech, retail, fin-tech usually have massive amounts of multi-dimensional data. However, extracting data insights from millions or billions of rows of user-behavior data can be very expensive and time-consuming. Many companies are willing to calculate approximate aggregated results within defined error bounds, to get faster responses. Sketches enable approximate estimates of distinct counts, quantiles, histograms, and other statistical measures — all with minimal memory and computational overhead, and with a single pass through the data at scale. 

KLL quantile functions (Preview)

BigQuery supports quantile calculations using native KLL quantile functions. For example, you could estimate the median trip duration and trip distance for all taxi rides on a given day or month. 

First, create daily KLL quantile sketches over trip_seconds and trip_miles from the `chicago_taxi_trips` data table. 

Then, with EXTRACT_POINT_INT64/FLOAT64 functions, you can get the median trip_seconds and trip_miles for all trips on a specific day.

You can also leverage the MERGE_POINT_INT64/FLOAT64 functions to estimate the median trip_seconds and trip_miles for all trips in a month. The query merges daily sketches at first, and then calculates quantile values over a month. 

Apache DataSketches (GA) 
Besides native support for sketches, BigQuery also supports open-source Apache DataSketches, a high-performance library of stochastic streaming algorithms. Initially developed by Yahoo, you can directly use them though the public UDF bigquery-utils repo powered by JS UDAFs. Here are some examples, just name a few (see more details in this blog post): 

1. Theta Sketch: designed for cardinality estimation and set operations (union, intersection, difference)

2. KLL Sketch: designed for quantile estimation

3. Tuple Sketch: an extension of the Theta Sketch that supports associating values with the estimated unique items

The following examples use Theta Sketch over a public dataset, Chicago taxi trips, to demonstrate the power of the UDAF sketches library. 

First, create a daily Theta Sketch to estimate the unique number of taxis running per day, using theta_sketch_agg_string.

You can estimate the unique number of taxis that run on both days, using theta_sketch_intersection.

You can estimate the unique number of taxis that run on either of these two days, using theta_sketch_union.

Do more with advanced aggregation

Using BigQuery’s advanced aggregation capabilities allows you to perform more complex analysis efficiently. Grouping sets provide flexibility in calculating aggregations at multiple dimensions. UDAFs empower you to define custom aggregations. Approximate aggregate functions provide speed, scalability and performance when approximate results are acceptable. By leveraging these features, you can unlock deeper insights from your data and help your team make timely business decisions. We can’t wait to hear your use cases and how you plan to use them in your day to day analysis!