前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >PostgreSQL函数|内置函数之GENERATE_SERIES详解(二)

PostgreSQL函数|内置函数之GENERATE_SERIES详解(二)

原创
作者头像
Aion
发布2024-07-04 09:05:55
2420
发布2024-07-04 09:05:55
举报
文章被收录于专栏:开发运维工程师

前言

这里接着上一篇,还是想在这次继续深入聊聊 GENERATE_SERIES 这个函数以及其他用法,由于最近一直忙于工作,也没有时间来深入学习、使用、总结这个函数,每次都潦草学习,终不得要领,所以得闲的时候就来写一写,学习一下。

近期在做一些数据处理的工作,工作中使用其他项目组平台来做数据开发的比较多,在数据开发过程中,使用了PostgreSQL的一个内置函数 GENERATE_SERIES。在使用过程中遇到问题,一般都是站在巨人的肩膀学习即可,也无需动脑子,慢慢的成了「拿来主义者」。

闲话家常里短这里就不拉了,继续上一篇的学习,下面开始吧。

1GENERATE_SERIES

1.1 释义

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

实验环境很重要,当前测试(实验)环境为PostgreSQL 16.2,所有操作均以这个版本进行测试(这个后续会有说明)。

1.2 用法

上一篇也说过,在PostgreSQL的官网有如下三种的用法和描述(integerbigintnumeric),可以借鉴下上一篇,这里说下日期类型参数。

【语法结构】

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,不符合在数据集内,所以舍去,那么这个时间结果集就为上述所示。

【代码示例】

代码语言:javascript
复制
-- 在当前日期上增加数值
SELECT current_date + gs.serial_num AS dates FROM generate_series(0,4,1) AS gs(serial_num);

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

代码语言:javascript
复制
-- 在参数日期上增加数值(如果是在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:002024-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 ,不符合在数据集内,所以舍去,那么这个时间结果集就为上述所示。

代码语言:javascript
复制
-- 时间戳上数值加减法(这里按照天计算),这里利用了日期的周期性
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 在时空下的日期时间加减法

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

代码语言:javascript
复制
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。

代码语言:javascript
复制
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

我正在参与2024腾讯技术创作特训营最新征文,快来和我瓜分大奖!

原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 前言
  • 1GENERATE_SERIES
    • 1.1 释义
      • 1.2 用法
        • 1.2.1 日期类型加减法
        • 1.2.2 时间戳类型加减法
        • 1.2.3 在时空下的日期时间加减法
    • 总结
    相关产品与服务
    NAT 网关
    NAT 网关(NAT Gateway)提供 IP 地址转换服务,为腾讯云内资源提供高性能的 Internet 访问服务。通过 NAT 网关,在腾讯云上的资源可以更安全的访问 Internet,保护私有网络信息不直接暴露公网;您也可以通过 NAT 网关实现海量的公网访问,最大支持1000万以上的并发连接数;NAT 网关还支持 IP 级流量管控,可实时查看流量数据,帮助您快速定位异常流量,排查网络故障。
    领券
    问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档