PG实践｜内置函数之GENERATE_SERIES之深入理解（二）

📫 作者简介：「六月暴雪飞梨花」，专注于研究Java，就职于科技型公司后端工程师
🏆 近期荣誉：华为云云享专家、阿里云专家博主、腾讯云优秀创作者、ACDU成员
🔥 三连支持：欢迎 ❤️关注、👍点赞、👉收藏三连，支持一下博主~

背景

接着上一篇，这次继续深入聊聊 GENERATE_SERIES 这个函数的其他用法，由于最近一直忙于工作，也没得时间来深入学习、使用这个函数，每次都是潦草学习，终不得要领，所以得闲的时候就来写一写，学习一下。
近期在做一些数据处理的工作，工作中使用其他项目组平台来做数据开发，在数据开发过程中，使用了PostgreSQL的一个内置函数 GENERATE_SERIES。在使用过程中遇到问题，一般都是站在巨人的肩膀学习即可，也无需动脑子，慢慢的成了拿来主义者。
闲话家常里短这里就不拉了，继续上一篇的学习，下面开始吧。

1GENERATE_SERIES

1.1 释义

【函数释义】：数据集函数，按照一定参数规则返回数据集。主要用于生成示例数据或一些有规律的记录，generate_series允许您生成一组从某个点开始，到另一个点结束的数据，并可选择设置递增值。

当前测试（实验）环境为PostgreSQL 16.2，所有操作均以这个版本进行测试（这个后续会有说明）。

1.2 用法

上一篇也说过，在PostgreSQL的官网有如下三种的用法和描述（integer、bigint、numeric），可以借鉴下上一篇，这里说下日期类型参数。

【语法结构】

generate_series ( start timestamp, stop timestamp, step interval ) →
setof timestamp

generate_series ( start timestamp with time zone, stop
timestamp with time zone, step interval [, timezone text ] ) → setof
timestamp with time zone

【参数】
在处理日期类型时，可以将日期转换为整数类型计算，也可以按照日期类型来计算。

【应用场景】
PostgreSQL中的 generate_series 函数是一个非常强大且灵活的工具，它可以在多种应用场景中生成连续的序列。

1. 生成整数序列：上文已经讲述。
2. 生成时间序列：本篇重点讲述。
3. 生成IP地址序列：虽然generate_series函数本身不直接支持IP地址的生成，但可以通过一些技巧（如将IP地址转换为整数进行计算后再转换回IP地址）来间接实现。
4. 模拟测试数据：在应用功能数据库开发或测试阶段，经常需要生成大量的测试数据。generate_series可以方便地生成连续的整数或时间序列，用于填充测试表。
5. 批量更新或处理数据：generate_series可以与UPDATE、DELETE等语句结合使用，实现复杂的批量操作。
6. 报表和数据分析：generate_series可以生成一个连续的时间或数值序列，用于与数据表进行连接（JOIN）操作，从而方便地进行分组和聚合。

1.2.1 日期类型加减法

在当前日期上按照一定的数值序列进行加减法，例如当前时间为 2024-07-02，从 0 （当前日期基数）开始，按照步长 1 向上增加（具体的逻辑可以参考下上一篇，逻辑相同，这里主要是借助了generate_series函数自增的特性，如果从 1 开始，那么就不算当前日期）。
2024-07-02
2024-07-03
2024-07-04
2024-07-05
2024-07-06
2024-07-07 ，不符合在数据集内，所以舍去，那么这个时间结果集就为上述所示。

【代码示例】

-- 在当前日期上增加数值
SELECT current_date + gs.serial_num AS dates FROM generate_series(0,4,1) AS gs(serial_num);

其他例子也挺好，可以根据自身需求来设置或制作可以使用的参数，具体可以参考如下：

-- 在参数日期上增加数值（如果是在Navicat中可以执行，其他工具酌情处理修改后执行）
SELECT (CAST([${year_time}] as date) + gs.serial_num) AS dates FROM generate_series(0,4,1) AS gs(serial_num);

-- 可以在查询时直接应用数据集
SELECT generate_series('2024-07-02'::date, '2024-12-31'::date, '1 month'::interval);

-- 写法不限制，怎么写都是可以的，例如下面的方式
SELECT generate_series(CURRENT_DATE,  CURRENT_DATE + INTERVAL '6 days',  INTERVAL '1 day');

1.2.2 时间戳类型加减法

时间戳上数值加减法，这里利用了日期的周期性。例如从开始时间戳到结束时间戳，按照某一个周期进行加减法。从 2024-07-02 00:00 到 2024-07-10 00:00，按照步长 1 day 的周期进行增加后的结果如下：
从 2024-07-02 00:00:00 开始，判断在数据集内，罗列结果：
2024-07-02 00:00:00
2024-07-03 00:00:00
2024-07-04 00:00:00
2024-07-05 00:00:00
2024-07-06 00:00:00
2024-07-07 00:00:00
2024-07-08 00:00:00
2024-07-09 00:00:00
2024-07-10 00:00:00
2024-07-11 00:00:00 ，不符合在数据集内，所以舍去，那么这个时间结果集就为上述所示。

-- 时间戳上数值加减法（这里按照天计算），这里利用了日期的周期性
SELECT * FROM generate_series('2024-07-02 00:00'::timestamp, '2024-07-10 00:00', '1 day');

-- 时间戳上数值加减法（这里按照月计算）
SELECT * FROM generate_series('2024-07-02 00:00'::timestamp, '2024-10-10 00:00', '1 MONTH');

当然可以根据时间周期性计算 年、月、日、时、分、秒，关键字分别为：year、month、day、hour、minute、seconds，如果没有特别设置，不区分大小写，虽然偶尔写，有些时间也难记住这些单词，这里就不再赘述。

1.2.3 在时空下的日期时间加减法

生成从开始到停止的一系列值，步长为步长。在时区感知形式中，根据时区参数命名的时区计算一天中的时间和夏令时调整，如果省略，则根据当前的时区设置计算。一般情形下，这种使用方法很少，除非这是一些跨国业务需要处理。

SELECT * FROM generate_series(‘2024-07-02 08:00 -10:00’::timestamptz,
‘2024-07-08 10:00 -1:00’::timestamptz,
‘1 day’::interval, ‘Asia/Chungking’);

注意⚠️：
1、如果不知道如何获取时区，可以查询系统中已有的时区列表，默认情况下是使用UTC。

SELECT name FROM pg_timezone_names ORDER BY name;

-- 部分数据如下
……
-- 重庆
Asia/Chongqing
Asia/Chungking
-- 香港
Asia/Hong_Kong
-- 澳门
Asia/Macau
-- 上海
Asia/Shanghai
-- 台北
Asia/Taipei
……

2、在时间区间中使用的是 12H 制度，也就是我们常说的AP和PM。

总结

遇事不要慌，多看看官网，多找找原因，实在不行多翻一翻其他的博文，总有能帮助你的那一刻。下一篇总结下其他参数的使用方法。

---

[引用]

1. PostgreSQL 16(generate_series)：https://www.postgresql.org/docs/16/functions-srf.html
2. PostgreSQL 12(generate_series)：https://www.postgresql.org/docs/12/functions-srf.html

---

欢迎关注博主 「六月暴雪飞梨花」 或加入【六月暴雪飞梨花社区】一起学习和分享Linux、C、C++、Python、Matlab，机器人运动控制、多机器人协作，智能优化算法，滤波估计、多传感器信息融合，机器学习，人工智能等相关领域的知识和技术。