PostgreSQL技术问答07 generate_series

本文是《PostgreSQL技术问答》系列文章中的一篇。关于这个系列的由来，可以参阅开篇文章：

文章的编号只是一个标识，在系列中没有明确的逻辑顺序和意义。读者进行阅读时，不用太关注这个方面。

本文主要讨论的内容是generate_series，即生成序列。

什么是generate_series(GS)

generate_series(以下简称GS)是PostgreSQL提供的一个内置的函数，可以用在SQL语句中，生成一个数字或时间序列的记录集。

GS如何使用

作为一个函数，GS的标准范式是： generate_series(start, stop, step)，这里面的参数分别是序列开始值，序列结束值和步长值。这个形式有点像编程语言中的for循环，即 i= start;i<=stop;i+=step 的样式。如果是整数序列，则step可以省略，默认为1。这个方法的结果是一个只有一个字段的记录集，记录集的行数就是条件循环的次数，内容就是各个循环计算的结果值。

当然，在PG中，GS的用法可以很丰富，下面总结了几种不同的用法，要特别注意参数对结果的影响：

// 最普通的用法
select generate_series(1,5);
 generate_series 
-----------------
               1
               2
               3
               4
               5
(5 rows)

// 使用步长
 select generate_series(1,5,2);
 generate_series 
-----------------
               1
               3
               5
(3 rows)

// 浮点数使用步长
select generate_series(1.5,7.5,2) vl;
 vl  
-----
 1.5
 3.5
 5.5
 7.5
(4 rows)

// 作为子查询或者记录集
select n, n^2 nn from generate_series(1,5) n;
 n | nn 
---+----
 1 |  1
 2 |  4
 3 |  9
 4 | 16
 5 | 25
(5 rows)

select sum(n)  from generate_series(1,101) n;
 sum  
------
 5151
(1 row)

// 日期序列
SELECT * FROM generate_series('2022-01-01'::date, '2022-01-05'::date, '1 day'::interval);
    generate_series     
------------------------
 2022-01-01 00:00:00+00
 2022-01-02 00:00:00+00
 2022-01-03 00:00:00+00
 2022-01-04 00:00:00+00
 2022-01-05 00:00:00+00
(5 rows)

从上面的示例，我们可以总结出一些要点：

• 各个参数使用的值，不限于整数，也可以使用浮点数，甚至支持日期类型
• 在使用数值类型时，步长参数可以省略，默认为1
• 步长可以为负数，用于倒序序列
• 支持时间序列，但必须指定步长
• 这个函数执行结果是一个记录集，只有一个字段，默认名称为generate_series，当然，可以重新命名字段名称
• 序列结束的判断逻辑是 <= stop
• 可以在select子句或者from子句中使用，即作为子查询使用

有那些使用场景

合理使用GS，可以构造出很多有趣的场景，有些情况下可以大幅度的降低开发的复杂性。GS的在应用开发中使用场景包括但不限于：

• 快速生成测试数据集

利用GS，可以快速生成大量的数据(如百万级)，用于填充测试用的业务数据和信息表。

• 快速生成大量随机数据

使用GS，结合随机函数如random()或者random_noraml()，就可以生成大量随机数据。

• 生成编码序列

结合相关的字符串操作，可以利用GS生成一个规则化的编码序列。

select 'ULSC-' || LPAD(ss::text,4,'0') from generate_series(20,60) ss ;

• 作为循环用的基础记录集

这里就涉及一种认识方式，就是GS不仅可以作为一个数值序列，本质上它其实是一种循环模式。这样就可以作为一种循环操作的数据基础。

• 快速复制数据

如果想对数据表中的记录快速进行复制，可以使用生成序列，然后对原始数据进行关联查询，但不指定关联条件，就生成了一个序列记录集和原始数据记录集的笛卡尔积的记录集，即将原始数据复制了多个。

• 生成时间序列

可以以指定的开始时间和间隔，生成一个时间序列，并进行相关的业务操作。例如进行时间周期内的统计分析等等。

如果同时使用多个序列，会发生什么

纯属好奇哈，测试一下就知道了，不知道这里是什么逻辑。

 select generate_series(1,4) m, generate_series(5,10) n;
 m | n  
---+----
 1 |  5
 2 |  6
 3 |  7
 4 |  8
   |  9
   | 10
(6 rows)

// 在看看这个呢？
select generate_series(1,4)*n m from generate_series(5,10) n ;

select generate_series(1,4) * generate_series(10,5);

小结

本文探讨了总结了PostgreSQL中的生成序列(generate_series)技术，讨论了其基本概念，一般形式，应用方式，应用中需要注意的问题，应用的场景等等相关内容。