ARRAY_GENERATE_SERIES (Numerical)
Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API
Creates an array of sequential values within a range.
Syntax
1ARRAY_GENERATE_SERIES(<start>, <stop>[, <step>])
Arguments
Required
<start>: The starting value of the sequence.<stop>: The ending value of the sequence (inclusive).
Optional
<step>: The increment between values. The default is 1.
Returns
Returns an array that contains sequential values from start to stop, incrementing by step.
Considerations
- If you use a negative step, make sure
<start> is greater than <stop>. If <start> is less than or equal to <stop>, the function returns an empty array.
- A negative step generates a descending sequence. A positive step generates an ascending sequence.
- This function is equivalent to the generate_series (Numerical) function but returns an array instead of a set of rows.
- Both the
start and stop values are included in the generated series.
- 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.
Examples
Basic Usage
Create an array of integers from 1 to 5.
1SELECT ARRAY_GENERATE_SERIES(1, 5) AS result;
Returns [1, 2, 3, 4, 5].
Generate Sequence with Step
Create an array of integers from 1 to 5 with step 2.
1SELECT ARRAY_GENERATE_SERIES(1, 5, 2) AS result;
Returns [1, 3, 5].
Reverse Sequence with Negative Step
Create an array counting down from 5 to 1 in increments of 2.
1SELECT ARRAY_GENERATE_SERIES(5, 1, -2) AS result;
Returns [5, 3, 1].
Decimal Reverse Sequence
Create a descending array with decimal values.
1SELECT ARRAY_GENERATE_SERIES(3.7, 3.0, -0.3) AS result;
Returns [3.7, 3.4, 3.1].
Negative Step with Increasing Range (Empty Result)
When the step direction doesn’t match the range direction, an empty array is returned.
1SELECT ARRAY_GENERATE_SERIES(1, 5, -1) AS result;
Returns [].
Additional Examples
The examples in generate_series examples apply to array_generate_series.
Related Documentation
j