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.

  • <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.

Results:

generate_series
2
3
4

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

Results:

generate_series
1
3
5

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.

Results:

generate_seriesordinality
51
32
13

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

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

Results:

generate_series
1.1
2.4
3.7