Returns an array of elements from start to stop (inclusive), incrementing by step.
Syntax
sequence(start, stop[, step])Supported types
| Scenario | start and stop | step |
|---|---|---|
| Integer sequence | TINYINT, SMALLINT, INT, or BIGINT | Same type as start and stop |
| Date/time sequence | DATE, DATETIME, or TIMESTAMP | IntervalDayTime or IntervalYearMonth |
For integer sequences, the default step is 1 when start ≤ stop, and -1 when start > stop.
For date/time sequences, the default step is 1 day when start ≤ stop, and -1 day when start > stop.
Parameters
| Parameter | Required | Description |
|---|---|---|
start | Yes | The first element in the sequence (inclusive). |
stop | Yes | The last element in the sequence (inclusive). |
step | No | The increment between elements. Must be positive if start ≤ stop, and negative if start > stop. |
Return value
Returns an ARRAY containing the generated elements.
The following inputs cause an error:
start>stopandstepis positivestart≤stopandstepis negative
By default, SEQUENCE generates a maximum of 10,000 elements. To change this limit, set the odps.sql.max.sequence.length parameter.
Examples
Integer sequence — ascending:
SELECT sequence(1, 5);Output:
[1, 2, 3, 4, 5]Integer sequence — descending:
SELECT sequence(5, 1);Output:
[5, 4, 3, 2, 1]Date sequence with a monthly step:
SELECT sequence(to_date('2018-01-01'), to_date('2018-03-01'), interval 1 month);Output:
[2018-01-01, 2018-02-01, 2018-03-01]Related functions
SEQUENCE is a complex type function. For functions that process ARRAY, MAP, STRUCT, and JSON data, see Complex type functions.