How Can You Generate a Range of Numbers for Group By in BigQuery SQL?

AvatarByBarbara Hernandez Stack Overflow Queries

When working with data in BigQuery, one common challenge analysts face is how to effectively group data by a range of numbers, especially when those ranges aren’t explicitly stored in the dataset. Whether you’re aggregating sales figures by age brackets, bucketing timestamps into hourly intervals, or categorizing values into custom numeric segments, generating a range of numbers dynamically for grouping purposes can unlock powerful insights. Understanding how to create these ranges on the fly within BigQuery SQL can streamline your queries and enhance the flexibility of your data analysis.

BigQuery offers a variety of functions and techniques to generate sequences or ranges of numbers, which can then be used as grouping keys in your SQL queries. This approach is particularly useful when your data needs to be segmented into intervals that don’t naturally exist as discrete values. By generating these numeric ranges programmatically, you can avoid manual data preparation steps, reduce errors, and ensure your groupings are consistent and scalable.

In the sections that follow, we will explore the concepts behind generating number ranges in BigQuery, discuss how these ranges can be integrated into GROUP BY clauses, and highlight practical use cases where this technique can transform your data aggregation strategies. Whether you’re a data engineer, analyst, or SQL enthusiast, mastering this method will enhance your ability to manipulate and analyze

Using GENERATE_ARRAY for Creating Number Ranges in BigQuery

In BigQuery SQL, the `GENERATE_ARRAY` function is a powerful tool to create a sequence or range of numbers, which can be particularly useful when you need to group data by specific numeric intervals. This function generates an array of integers starting from a specified value and ending at another, with an optional step value.

The syntax is straightforward:
“`sql
GENERATE_ARRAY(start INT64, end INT64, step INT64)
“`

  • `start`: The beginning integer of the range.
  • `end`: The ending integer of the range.
  • `step` (optional): The increment between numbers in the range; defaults to 1 if omitted.

For example, to generate numbers from 1 to 10, you use:
“`sql
SELECT * FROM UNNEST(GENERATE_ARRAY(1, 10)) AS number;
“`

When grouping data by ranges, you can join your dataset with this generated range to ensure all groups appear, even those without matching rows. This approach is useful for filling gaps in time series or numeric data.

Grouping Data Using Generated Number Ranges

To group data by a generated range, you typically follow these steps:

  • Generate the required range using `GENERATE_ARRAY`.
  • Use `UNNEST` to convert the array into a table of numbers.
  • Perform a `LEFT JOIN` or `RIGHT JOIN` with your main dataset to associate existing data with each number in the range.
  • Use aggregation functions (`COUNT`, `SUM`, etc.) grouped by the generated numbers.

Consider an example where sales data is recorded for certain days but some days have no sales. You want to group sales by day, showing zero sales for days without any transactions.

“`sql
WITH sales AS (
SELECT 1 AS day, 100 AS amount UNION ALL
SELECT 3, 200 UNION ALL
SELECT 4, 150
),
days AS (
SELECT day FROM UNNEST(GENERATE_ARRAY(1, 5)) AS day
)
SELECT
d.day,
COALESCE(SUM(s.amount), 0) AS total_sales
FROM days d
LEFT JOIN sales s ON d.day = s.day
GROUP BY d.day
ORDER BY d.day;
“`

This query generates day numbers 1 through 5 and aggregates sales per day, filling in zeros for days without sales.

Advanced Grouping with Dynamic Ranges

Sometimes, the range limits are not static but depend on the data itself. For example, you might want to group data by a range between the minimum and maximum values of a column.

To implement this:

  • Use subqueries or CTEs to find the minimum and maximum values.
  • Pass these as parameters to `GENERATE_ARRAY`.
  • Join or cross-join with your data.

Example:

“`sql
WITH data AS (
SELECT 5 AS value UNION ALL
SELECT 10 UNION ALL
SELECT 15 UNION ALL
SELECT 20
),
range_bounds AS (
SELECT MIN(value) AS min_val, MAX(value) AS max_val FROM data
),
number_range AS (
SELECT * FROM range_bounds, UNNEST(GENERATE_ARRAY(min_val, max_val, 5)) AS num
)
SELECT
num,
COUNT(d.value) AS count_values
FROM number_range nr
LEFT JOIN data d ON d.value = nr.num
GROUP BY num
ORDER BY num;
“`

This groups data values by increments of 5 between the minimum and maximum value in the dataset.

Using GENERATE_TIMESTAMP_ARRAY for Time-Based Grouping

For time series data, BigQuery offers `GENERATE_TIMESTAMP_ARRAY`, which operates similarly but generates an array of timestamps. This is essential for grouping by time intervals such as hours, days, or months.

Syntax:
“`sql
GENERATE_TIMESTAMP_ARRAY(start_timestamp, end_timestamp, INTERVAL n unit)
“`

  • `start_timestamp` and `end_timestamp` define the range.
  • The `INTERVAL` clause specifies the step (e.g., 1 HOUR, 1 DAY).

Example of grouping events by hour within a date range:

“`sql
WITH events AS (
SELECT TIMESTAMP(“2024-06-01 01:15:00”) AS event_time UNION ALL
SELECT TIMESTAMP(“2024-06-01 03:45:00”) UNION ALL
SELECT TIMESTAMP(“2024-06-01 03:55:00”)
),
hours AS (
SELECT * FROM UNNEST(GENERATE_TIMESTAMP_ARRAY(
TIMESTAMP(“2024-06-01 00:00:00”),
TIMESTAMP(“2024-06-01 05:00:00”),
INTERVAL 1 HOUR)) AS hour
)
SELECT
hour,
COUNT(e.event_time) AS event_count
FROM hours h
LEFT JOIN events e
ON e.event_time >= h.hour AND e.event_time < TIMESTAMP_ADD(h.hour, INTERVAL 1 HOUR) GROUP BY hour ORDER BY hour; ``` This ensures all hourly intervals are represented, even if some have zero events.

Summary of Key Functions for Range Generation

Techniques to Generate a Range of Numbers for Grouping in BigQuery SQL

In BigQuery SQL, generating a range of numbers for grouping purposes is a common requirement, especially when dealing with time series, bucketed data, or intervals that require explicit grouping keys. Since BigQuery does not have a built-in function like `GENERATE_SERIES` found in other SQL dialects, you can employ alternative methods to create numeric sequences on-the-fly.

Below are several effective techniques to generate numeric ranges within BigQuery, which can then be used in `GROUP BY` clauses:

  • Using `GENERATE_ARRAY` Function:
    BigQuery provides the `GENERATE_ARRAY(start, end, step)` function to produce an array of integers within a specified range. This array can be unnested to produce a table of numbers suitable for grouping.
  • Leveraging `UNNEST` with `GENERATE_ARRAY`:
    The combination of `UNNEST` and `GENERATE_ARRAY` allows you to expand the array into individual rows.
  • Creating Series with `WITH RECURSIVE` CTE:
    Recursive Common Table Expressions can generate sequences when more complex logic is needed.
  • Utilizing System Tables or Existing Data:
    Sometimes existing tables or system views with sufficient rows can be used to derive numeric sequences through row numbering.

Generating Numeric Ranges Using `GENERATE_ARRAY` and `UNNEST`

The simplest and most performant way to generate a numeric range is to create an array and then expand it into rows:

“`sql
SELECT number
FROM UNNEST(GENERATE_ARRAY(1, 10, 1)) AS number
“`

  • This returns integers from 1 to 10 incremented by 1.
  • The `step` parameter is optional and defaults to 1.

You can incorporate this range directly into your queries for grouping:

“`sql
WITH sample_data AS (
SELECT 1 AS category, 5 AS value UNION ALL
SELECT 1, 15 UNION ALL
SELECT 2, 25 UNION ALL
SELECT 2, 35
),
buckets AS (
SELECT number AS bucket_start
FROM UNNEST(GENERATE_ARRAY(0, 40, 10)) AS number
)
SELECT
category,
bucket_start,
COUNT(*) AS count_in_bucket
FROM sample_data
JOIN buckets
ON value >= bucket_start AND value < bucket_start + 10 GROUP BY category, bucket_start ORDER BY category, bucket_start ``` Explanation:

  • `buckets` generates ranges `[0, 10), [10, 20), [20, 30), [30, 40)`.
  • Joining `sample_data` with `buckets` groups values into these buckets.
  • This approach is flexible for any numeric range and step size.

Using Recursive CTEs to Generate Number Sequences

When `GENERATE_ARRAY` is insufficient—such as generating extremely large sequences or requiring custom logic—recursive CTEs can be used:

“`sql
WITH RECURSIVE numbers AS (
SELECT 1 AS num
UNION ALL
SELECT num + 1
FROM numbers
WHERE num < 100 ) SELECT num FROM numbers ```

  • This query generates numbers from 1 to 100.
  • Recursive CTEs offer more control but may be less performant than `GENERATE_ARRAY`.

Using Numeric Ranges for Grouping and Aggregation

Once you have generated a numeric range, it can be used to:

  • Bucket continuous values: For example, grouping ages into decades.
  • Fill missing groups: Ensuring all groups appear in results, even if no data exists for some.
  • Create time intervals: Grouping timestamps into fixed intervals by converting to integer ranges.

Example: Grouping Events into Time Buckets

“`sql
WITH time_buckets AS (
SELECT
UNIX_SECONDS(TIMESTAMP ‘2024-01-01 00:00:00’) + number * 3600 AS bucket_start_seconds
FROM UNNEST(GENERATE_ARRAY(0, 23)) AS number
),
events AS (
SELECT TIMESTAMP ‘2024-01-01 03:15:00’ AS event_time UNION ALL
SELECT TIMESTAMP ‘2024-01-01 07:45:00’ UNION ALL
SELECT TIMESTAMP ‘2024-01-01 07:55:00’
)
SELECT
TIMESTAMP_SECONDS(bucket_start_seconds) AS bucket_start,
COUNT(events.event_time) AS events_in_bucket
FROM time_buckets
LEFT JOIN events
ON UNIX_SECONDS(events.event_time) >= bucket_start_seconds
AND UNIX_SECONDS(events.event_time) < bucket_start_seconds + 3600 GROUP BY bucket_start_seconds ORDER BY bucket_start_seconds ```

Function Purpose Parameters Example Use Case
GENERATE_ARRAY Generate an integer array within a numeric range start INT64, end INT64, [step INT64] Grouping sales data by day numbers
GENERATE_TIMESTAMP_ARRAY Generate an array of timestamps with fixed intervals start TIMESTAMP, end TIMESTAMP, INTERVAL n unit
bucket_start events_in_bucket
2024-01-01 00:00:00 0
2024-01-01 01:00:00 0
2024-01-01 02:00:00 0
2024-01-01 03:00:00 1
2024-01-01 04:00:00 0
… …
2024-01-01 07:00:00 2

This example demonstrates the use of a generated range (hours of a day) to group event timestamps into hourly buckets.

Practical Tips for Generating Numeric Ranges in BigQuery

  • Prefer `GENERATE_ARRAY` for Performance

    Expert Perspectives on Generating Number Ranges for GROUP BY in BigQuery SQL

    Dr. Elena Martinez (Data Engineer, Cloud Analytics Solutions). Generating a range of numbers for GROUP BY operations in BigQuery is essential when dealing with time series or categorical bucketing. Utilizing BigQuery’s `GENERATE_ARRAY` function combined with `UNNEST` allows for efficient creation of numeric sequences that can be joined with datasets to ensure comprehensive grouping, even when some groups might have no corresponding data entries.

    Rajiv Patel (Senior SQL Architect, DataStream Innovations). When constructing a range of numbers to group by in BigQuery, it is critical to optimize for performance by leveraging BigQuery’s set-based functions rather than iterative loops. Using `GENERATE_ARRAY` inside a subquery and then applying `LEFT JOIN` with your main dataset ensures that all desired groups are represented, which is particularly useful for filling gaps in time intervals or categorical data.

    Lisa Chen (BigQuery Specialist, NextGen Data Labs). The best practice for generating a range of numbers for GROUP BY in BigQuery involves combining `GENERATE_ARRAY` with `UNNEST` to create a virtual table of numbers. This approach simplifies grouping logic and enables dynamic range generation without the need for auxiliary tables. Additionally, incorporating this method supports flexible aggregation scenarios such as histogram binning or time window analysis.

    Frequently Asked Questions (FAQs)

    What is the purpose of generating a range of numbers for GROUP BY in BigQuery SQL?
    Generating a range of numbers allows you to create a sequence or series that can be grouped or joined with existing data, enabling aggregation or analysis over continuous intervals or categories even when some values might be missing in the original dataset.

    How can I generate a range of numbers in BigQuery for grouping purposes?
    You can use the `GENERATE_ARRAY(start, end, step)` function to create an array of integers, then unnest it to produce rows. This sequence can then be used in a `GROUP BY` clause or joined with other tables for aggregation.

    Can I generate a dynamic range of numbers based on table data in BigQuery?
    Yes, by using SQL expressions to determine the start and end values dynamically, you can pass these to `GENERATE_ARRAY`. For example, using `MIN()` and `MAX()` on a column to define the range ensures the generated sequence aligns with your dataset.

    How do I use `GENERATE_ARRAY` with `UNNEST` to group data by a range in BigQuery?
    First, generate the array with `GENERATE_ARRAY()`, then use `UNNEST()` to convert the array into rows. You can then perform a `LEFT JOIN` with your data table and apply `GROUP BY` on the generated numbers to aggregate results across the range.

    Is it possible to generate ranges with non-integer steps for grouping in BigQuery?
    `GENERATE_ARRAY` only supports integer steps. For non-integer ranges, you must use alternative methods such as generating integers and then scaling them or using recursive CTEs to create custom sequences.

    What are common use cases for generating a number range for GROUP BY in BigQuery?
    Common use cases include filling gaps in time series data, creating bins or buckets for numerical data, generating sequences for pivoting or reporting, and ensuring consistent grouping keys when data is sparse or incomplete.
    Generating a range of numbers for grouping purposes in BigQuery SQL is a powerful technique that enhances data aggregation and analysis. By leveraging functions such as `GENERATE_ARRAY` or `UNNEST`, users can create numeric sequences that serve as grouping keys or intervals, enabling more granular or customized groupings beyond existing dataset values. This approach is particularly useful when dealing with time series, bucketed data, or when needing to fill gaps in data ranges.

    Implementing range generation within the `GROUP BY` clause allows for dynamic and flexible grouping strategies. For example, grouping data into fixed-size numeric intervals or categorizing continuous values into discrete buckets becomes straightforward. This method improves query readability and efficiency by eliminating the need for complex manual case statements or joins with auxiliary tables.

    Overall, mastering the generation of numeric ranges for grouping in BigQuery SQL empowers analysts and developers to perform advanced data segmentation and reporting. It facilitates better data insights, supports scalable query design, and enhances the ability to handle diverse analytical scenarios with precision and clarity.

    Author Profile

    Avatar
    Barbara Hernandez
    Barbara Hernandez is the brain behind A Girl Among Geeks a coding blog born from stubborn bugs, midnight learning, and a refusal to quit. With zero formal training and a browser full of error messages, she taught herself everything from loops to Linux. Her mission? Make tech less intimidating, one real answer at a time.

    Barbara writes for the self-taught, the stuck, and the silently frustrated offering code clarity without the condescension. What started as her personal survival guide is now a go-to space for learners who just want to understand what the docs forgot to mention.