generate_series
Description
Generates a series of values within the interval specified by
start
and
end
, and with an optional
step
.
generate_series() is a table function. A table function can return a row set for each input row. The row set can contain zero, one, or multiple rows. Each row can contain one or more columns.
To use generate_series() in StarRocks, you must enclose it in the TABLE keyword if the input parameters are constants. If the input parameters are expressions, such as column names, the TABLE keyword is not required (See Example 5).
This function is supported from v3.1.
Syntax
generate_series(start, end [,step])
Parameters
-
start
: the starting value of the series, required. Supported data types are INT, BIGINT, and LARGEINT.
-
end
: the ending value of the series, required. Supported data types are INT, BIGINT, and LARGEINT.
-
step
: the value to increment or decrement, optional. Supported data types are INT, BIGINT, and LARGEINT. If not specified, the default step is 1.
step
can be either negative or positive, but cannot be zero.
The three parameters must have the same data type, for example,
generate_series(INT start, INT end [, INT step])
.
Return value
Returns a series of values that have the same as the input parameters
start
and
end
.
-
When
step
is positive, zero rows are returned if
start
is greater than
end
. Conversely, when
step
is negative, zero rows are returned if
start
is less than
end
.
-
An error is returned if
step
is 0.
-
This function deals with nulls as follows: If any input parameter is a literal null, an error is reported. If any input parameter is an expression and the result of the expression is null, 0 rows are returned (See Example 5).
Examples
Example 1: Generate a sequence of values within the range
[2,5]
in ascending order with the default step
1
.
MySQL > select * from TABLE(generate_series(2, 5));
+
| generate_series |
+
| 2 |
| 3 |
| 4 |
| 5 |
+
Example 2: Generate a sequence of values within the range
[2,5]
in ascending order with the specified step
2
.
MySQL > select * from TABLE(generate_series(2, 5, 2));
+
| generate_series |
+
| 2 |
| 4 |
+
Example 3: Generate a sequence of values within the range
[5,2]
in descending order with the specified step
-1
.
MySQL > select * from TABLE(generate_series(5, 2, -1));
+
| generate_series |
+
| 5 |
| 4 |
| 3 |
| 2 |
+
Example 4: Zero rows are returned when
step
is negative and
start
is less than
end
.
MySQL > select * from TABLE(generate_series(2, 5, -1));
Empty set (0.01 sec)
Example 5: Use table columns as the input parameters of generate_series(). In this use case, you do not need to use
TABLE()
with generate_series().
CREATE TABLE t_numbers(start INT, end INT)
DUPLICATE KEY (start)
DISTRIBUTED BY HASH(start) BUCKETS 1;
INSERT INTO t_numbers VALUES
(1, 3),
(5, 2),
(NULL, 10),
(4, 7),
(9,6);
SELECT * FROM t_numbers;
+
| start | end |
+
| NULL | 10 |
| 1 | 3 |
| 4 | 7 |
| 5 | 2 |
| 9 | 6 |
+
SELECT * FROM t_numbers, generate_series(t_numbers.start, t_numbers.end);
+
| start | end | generate_series |
+
| 1 | 3 | 1 |
| 1 | 3 | 2 |
| 1 | 3 | 3 |
| 4 | 7 | 4 |
| 4 | 7 | 5 |
| 4 | 7 | 6 |
| 4 | 7 | 7 |
+
SELECT * FROM t_numbers, generate_series(t_numbers.start, t_numbers.end, -1);
+
| start | end | generate_series |
+
| 5 | 2 | 5 |
| 5 | 2 | 4 |
| 5 | 2 | 3 |
