GENERATE_SERIES
GENERATE_SERIES generates a sequence of consecutive integers from from to to, with both bounds inclusive.
Syntax
GENERATE_SERIES(BIGINT from, BIGINT to)Parameters
|
Parameter |
Type |
Description |
|
|
BIGINT |
The inclusive lower bound of the series. |
|
|
BIGINT |
The inclusive upper bound of the series. |
Example
Test data
|
s (BIGINT NOT NULL) |
e (BIGINT NOT NULL) |
|
1 |
3 |
|
-2 |
1 |
Test code
CREATE TEMPORARY TABLE input_table(
s BIGINT NOT NULL,
e BIGINT NOT NULL
) WITH (
'connector' = 'datagen'
);
CREATE TEMPORARY TABLE output_table(
s BIGINT NOT NULL,
e BIGINT NOT NULL,
v BIGINT NOT NULL
) WITH (
'connector' = 'print'
);
INSERT INTO output_table
SELECT s, e, v FROM input_table, LATERAL TABLE(GENERATE_SERIES(s, e)) AS t(v);Test result
|
s (BIGINT) |
e (BIGINT) |
v (BIGINT) |
|
1 |
3 |
1 |
|
1 |
3 |
2 |
|
1 |
3 |
3 |
|
-2 |
1 |
-2 |
|
-2 |
1 |
-1 |
|
-2 |
1 |
0 |
|
-2 |
1 |
1 |
For input row (1, 3), the function emits 1, 2, 3. For input row (-2, 1), it emits -2, -1, 0, 1.