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.stepcan 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
stepis positive, zero rows are returned ifstartis greater thanend. Conversely, whenstepis negative, zero rows are returned ifstartis less thanend. - An error is returned if
stepis 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 |
+-------+------+
-- Generate multiple rows for rows (1,3) and (4,7) with step 1.
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 |
+-------+------+-----------------+
-- Generate multiple rows for rows (5,2) and (9,6) with step -1.
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 |
| 5 | 2 | 2 |
| 9 | 6 | 9 |
| 9 | 6 | 8 |
| 9 | 6 | 7 |
| 9 | 6 | 6 |
+-------+------+-----------------+
The input row (NULL, 10) has a NULL value and zero rows are returned for this row.
keywords
table function, generate series