GENERATE_SERIES()¶
GENERATE_SERIES() can generate a sequence from the start value to the end value, usually used in the following scenarios:
Function description¶
GENERATE_SERIES() can generate a sequence from the start value to the end value, usually used in the following scenarios:
Generate continuous numbers: Used to generate a series of integers, such as 1 to 10.
Generate date and time series: time series at daily, hourly and other intervals can be generated.
Combined table query: used to dynamically generate or expand data.
Function syntax¶
>select *from generate_series(start, stop [, step]) g
Parameter explanation¶
Parameters |
Description |
|---|---|
start |
starting value |
stop |
end value |
step |
step size, default is 1 |
Example¶
-Example 1:
--Generate a sequence of 1-5 integers
mysql> select *from generate_series(5) g;
+--------+
| result |
+--------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+--------+
5 rows in set (0.01 sec)
--Generate a sequence of 2-5 integers
mysql> select *from generate_series(2, 5) g;
+--------+
| result |
+--------+
| 2 |
| 3 |
| 4 |
| 5 |
+--------+
4 rows in set (0.00 sec)
--Generate a sequence of 1-5 integers and specify a step size of 2
mysql> select *from generate_series(1, 5,2) g;
+--------+
| result |
+--------+
| 1 |
| 3 |
| 5 |
+--------+
3 rows in set (0.01 sec)
--Generate date sequence
mysql> select *from generate_series('2020-02-28 00:00:00','2021-03-01 00:01:00', '1 year') g;
+---------------------+
| result |
+---------------------+
| 2020-02-28 00:00:00 |
| 2021-02-28 00:00:00 |
+---------------------+
2 rows in set (0.00 sec)