PGSQL生成序列 – 源码巴士

使用 generate_series 函数生成序列

PostgreSQL 中的 generate_series 函数可以用于生成一个数字或者时间序列集合。

生成一个连接的数字序列
generate_series 函数有 3 种形式，其中最简单的 generate_series(start, stop) 可以生成一个增量为 1 的等差数列。例如：

select * from generate_series(1, 5);

查询返回一个表，数据从 1 递增到 5。

生成一个间隔的数字序列
第二种形式的 generate_series(start, stop, step) 函数允许指定一个增量。例如：

select * from generate_series(1, 10, 2);

select * from generate_series(10, 0, -2.5);

select * from generate_series(1, 10, -2);

第一个函数返回了一个增量为 2 的数字序列；第二个函数返回了一个增量为 -2.5 的数字序列；第三个函数没有返回任何数据行，因为没有从 1 到 10 并且增量为 -2 的序列。

生成一个连续的字符序列
基于以上 generate_series 函数和 CHR(n) 函数可以生成连续的字符序列。例如：

select chr(v) from generate_series(65, 70) v;

A |
B |
C |
D |
E |
F |
以上函数生成了字符 A 到 F 的序列，chr(v) 函数用于将 ASCII 或者 Unicode 编码转化为相应的字符。

生成一个间隔的时间序列
第三种形式的 generate_series(start_timestamp, stop_timestamp, step_interval) 函数用于生成一个时间序列值。例如：

select * from generate_series(‘2020-01-01 00:00:00’::timestamp, ‘2020-01-01 12:00:00’::timestamp, interval ‘1 hour’);

2020-01-01 00:00:00|
2020-01-01 01:00:00|
2020-01-01 02:00:00|
2020-01-01 03:00:00|
2020-01-01 04:00:00|
2020-01-01 05:00:00|
2020-01-01 06:00:00|
2020-01-01 07:00:00|
2020-01-01 08:00:00|
2020-01-01 09:00:00|
2020-01-01 10:00:00|
2020-01-01 11:00:00|
2020-01-01 12:00:00|
以上查询返回了一个表，数据为 2020-01-01 00:00:00 到 2020-01-01 12:00:00，间隔为 1 小时的时间点。

如果生成的时间序列只包含日期部分，也可以使用之前的 generate_series 函数形式。例如：

select current_date + dates.val as val from generate_series(0, 28, 7) dates(val);

2020-07-14|
2020-07-21|
2020-07-28|
2020-08-04|
2020-08-11|
以上查询返回当前日期以及接下来四周对应的日期。

使用通用表表达式生成序列
生成一个等差数字序列
PostgreSQL 中的通用表表达式（Common Table Expression）支持递归调用，可以用于生成各种数列。例如：

with recursive t(n) as (
values (1)
union all
select n+2 from t where n < 9
)
select n from t;

1|
3|
5|
7|
9|
以上语句生成了一个从 1 递增到 9、增量为 2 的数列，执行过程如下：

首先，执行 CTE 中的初始化查询，生成一行数据（1）；
然后，第一次执行递归查询，判断 n < 9，生成一行数据 3（n+2）；
接着，重复执行递归查询，生成更多的数据；直到 n = 9 时不满足条件终止递归；此时临时表 t 中包含 5 条数据；
最后，执行主查询，返回所有的数据。
生成一个等比数字序列
generate_series 函数只能生成等差数列，通用表表达式则可以生成更复杂的数列，例如等比数列：

with recursive t(n) as (
values (1)
union all
select n * 3 from t where n < 100
)
select n from t;

1|
3|
9|
27|
81|
243|
从第二行开始，每个数字都是上一行的 3 倍。

生成斐波那契数列
斐波那契数列（Fibonacci series）是指从数字 0 和 1（或者从 1 和 1）开始，后面的每个数字等于它前面两个数字之和（0、1、1、2、3、5、8、13、21、…）。使用通用表表达式可以很容易地生成斐波那契数列：

with recursive fibonacci (n, fib_n, next_fib_n) as
(
select 1, 0, 1
union all
select n + 1, next_fib_n, fib_n + next_fib_n
from fibonacci where n < 10
)
select * from fibonacci;

n	fib_n	next_fib_n
1	0	1
2	1	1
3	1	2
4	2	3
5	3	5
6	5	8
7	8	13
8	13	21
9	21	34
10	34	55

其中，字段 n 表示该行包含了第 n 个斐波那契数列值；字段 fib_n 表示斐波那契数列值；字段 next_fib_n 表示下一个斐波那契数列值。

生成一个连续的字符序列
基于通用表表达式和 CHR(n) 函数同样可以生成连续的字符序列，例如：

with recursive t(n) as (
values (65)
union all
select n+1 from t where n < 70
)
select chr(n) from t;

A |
B |
C |
D |
E |
F |
生成一个间隔的时间序列
以下语句使用递归通用表表达式生成一个时间序列：

with recursive ts(v) as (
values (‘2020-01-01 00:00:00’::timestamp)
union all
select v + interval ‘1 hour’ from ts where v < ‘2020-01-01 12:00:00’::timestamp
)
select * from ts;

如果生成的时间序列只包含日期部分，可以使用以下语句：

with recursive ts(v) as (
values (current_date)
union all
select v + 7 from ts where v < current_date + 28
)
select * from ts;