sequence function

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Generates an array of elements from start to stop (inclusive), incrementing by step.

Syntax 

sequence(start, stop [, step] )

Arguments

  • start: An expression of an integral numeric type, DATE, or TIMESTAMP.

  • stop: If start is numeric an integral numeric, a DATE or TIMESTAMP otherwise.

  • step: An INTERVAL expression if start is a DATE or TIMESTAMP, or an integral numeric otherwise.

Returns

An ARRAY of least common type of start and stop.

By default step is 1 if start is less than or equal to stop, otherwise -1.

For the DATE or TIMESTAMP sequences default step is INTERVAL ‘1’ DAY and INTERVAL ‘-1’ DAY respectively.

If start is greater than stop then step must be negative, and vice versa.

Examples 

> SELECT sequence(1, 5);
 [1,2,3,4,5]

> SELECT sequence(5, 1);
 [5,4,3,2,1]

> SELECT sequence(DATE'2018-01-01', DATE'2018-03-01', INTERVAL 1 MONTH);
 [2018-01-01,2018-02-01,2018-03-01]