PostgreSQL 如何生成连续的数字/字符/时间序列

作者: 不剪发的Tony老师
毕业于北京航空航天大学,十多年数据库管理与开发经验,目前在一家全球性的金融公司从事数据库架构设计。CSDN学院签约讲师以及GitChat专栏作者。csdn上的博客收藏于以下地址:https://tonydong.blog.csdn.net

文章目录

        使用 generate_series 函数生成序列
            生成一个连接的数字序列
            生成一个间隔的数字序列
            生成一个连续的字符序列
            生成一个间隔的时间序列
        使用通用表表达式生成序列
            生成一个等差数字序列
            生成一个等比数字序列
            生成斐波那契数列
            生成一个连续的字符序列
            生成一个间隔的时间序列

大家好,我是只谈技术不剪发的 Tony 老师。今天我们介绍一下如何在 PostgreSQL 中生成连续的数字、字符以及时间序列值。

    📝如果你使用的是 MySQL 数据库,实现相同的功能可以参考这篇文章。
    📝如果你使用的是 Oracle 数据库,实现相同的功能可以参考这篇文章。
    📝如果你使用的是 SQL Server,实现相同的功能可以参考这篇文章。

使用 generate_series 函数生成序列

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

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

select * from generate_series(1, 5);
generate_series|
---------------|
              1|
              2|
              3|
              4|
              5|

    

查询返回一个表,数据从 1 递增到 5。
生成一个间隔的数字序列

第二种形式的 generate_series(start, stop, step) 函数允许指定一个增量。例如:

select * from generate_series(1, 10, 2);
generate_series|
---------------|
              1|
              3|
              5|
              7|
              9|

select * from generate_series(10, 0, -2.5);
generate_series|
---------------|
             10|
            7.5|
            5.0|
            2.5|
            0.0|

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



第一个函数返回了一个增量为 2 的数字序列;第二个函数返回了一个增量为 -2.5 的数字序列;第三个函数没有返回任何数据行,因为没有从 1 到 10 并且增量为 -2 的序列。
生成一个连续的字符序列

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

select chr(v) from generate_series(65, 70) v;
chr|
---|
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');
generate_series    |
-------------------|
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);
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;
n|
-|
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;
n  |
---|
  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;
chr|
---|
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;
v                  |
-------------------|
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 小时的时间点。

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

with recursive ts(v) as (
  values (current_date)
  union all
  select v + 7 from ts where v < current_date + 28
)
select * from ts;
v         |
----------|
2020-07-14|
2020-07-21|
2020-07-28|
2020-08-04|
2020-08-11|

 

以上查询返回当前日期以及接下来四周对应的日期。