generate_series (Numerical)

Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API

Generate a series of values, from start to stop with a given step size.

1generate_series(<start>, <stop> [, <step>])

• <start>: The start value of the series.
• <stop>: The end value of the series.

• <step>: The step size. The default step size is 1.

Returns a set of rows, where each row has a single column, containing the generated series of values.

There are two versions of generate_series:

• The numerical version
• The time-based version

This page describes the numerical version. The time-based version is described in the generate_series (Time-based) section.

The following considerations apply to both versions of generate_series:

• Both the start and stop values are included in the generated series. I.e., both boundaries are inclusive.
• The step size must be non-zero.
• The step size can also be negative. For negative step sizes, start must be greater than stop.
• For positive step sizes, if start is greater than stop, the function returns no rows.
• For negative step sizes, if start is less than stop, the function returns no rows.
• If any input is null, the function returns no rows.

See the generate_series (Time-based) page for additional considerations for the time-based version.

Without a step size, generate_series generates all values between start and stop, with a step size of 1. Note how the result set contains both 2 and 4, i.e. the start and end boundaries are inclusive.

1SELECT * FROM generate_series(2,4);

Results:

If an explicit step size is provided, generate_series generates all values between start and stop, with the given step size.

1SELECT * FROM generate_series(1,5,2);

Results:

For negative step sizes, the function returns the series in reverse order. start must be greater than stop in this case.

Note that in SQL, rows within a row set are usually unordered. Hence, the following query uses WITH ORDINALITY to instruct generate_series to also number the generated rows. The ORDER BY clause then ensures that the query indeed returns values in this specific order.

1SELECT *
2FROM generate_series(5,1,-2) WITH ORDINALITY
3ORDER BY ordinality ASC

Results:

For the following query, start is greater than stop, so the function returns an empty row set.

1SELECT * FROM generate_series(4,3);

You can also use fractional numbers as start, stop and even step values.

1SELECT generate_series(1.1, 4.1, 1.3);

Results:

• Set Returning Functions
• generate_series (Time-based) - the time-based version of this function
• ARRAY_GENERATE_SERIES (Numerical) - the array-returning version of this function