Menu

PostgreSQL generate_series:生成数字、日期与补齐空缺

generate_series 返回从起始值到结束值的一组行:数字、日期和时间戳。用它补齐报表中的空缺、生成测试数据和构建日历。

本页包含可运行的编辑器。编辑、运行,立即查看输出。

generate_series(start, stop, step) 从 start 开始,每次增加 step(省略时为 1),直到 stop(包含 stop),为每个值返回一行。它支持整数、numeric,以及以时间间隔为步长的时间戳。像表一样在 FROM 中使用它。

结果是 1、4、7 和 10 以及它们的平方。负步长表示倒数:generate_series(5, 1, -2) 得到 5、3、1。无法到达结束值的序列,比如 generate_series(5, 1),不返回任何行。generate_series(0, 1, 0.25) 按 numeric 步进。

生成日期序列

传入时间戳或日期,并用 interval 作为步长。这个函数没有 date 版本,所以返回值是 timestamptz;用 ::date 转换:

返回 5 天,从 2 月 26 日到 3 月 2 日,跨过了月底。任何时间间隔都可以作为步长:interval '1 hour'、interval '15 minutes'、interval '1 month'、interval '1 week'。

对于 timestamptz 值,步长 interval '1 day' 遵循会话 TimeZone 的日历。遇到夏令时切换时,一步可能是 23 或 25 小时。从 PostgreSQL 16 开始,第四个参数可以指定按哪个时区步进,与会话的设置无关:

SELECT generate_series(
    timestamptz '2026-03-28 00:00 Europe/Berlin',
    timestamptz '2026-03-31 00:00 Europe/Berlin',
    interval '1 day',
    'Europe/Berlin');

补齐报表中缺失的日期

对数据做 GROUP BY 只会返回有行的时间段。要显示每一天,没有订单的日子显示 0,就先生成日期,再把数据 LEFT JOIN 上去:

11 月 1 日有 2 个订单,11 月 4 日有 1 个,2 日、3 日和 5 日显示 0。要对被连接表的某一列计数(count(o.id)),而不是 count(*),否则每个空白日都会被算成 1。

用范围来比较 created_at,而不是写 created_at::date = d,这样 created_at 上的索引才能用上。用 interval '1 month' 和 date_trunc 套用同样的模式,就能补齐空白的月份(见日期函数)。

生成测试数据

用 INSERT ... SELECT 从序列中取值,一条语句就能创建所需数量的行:

表中得到 1,000 个用户,分布在 30 个注册日期上。这些值是由 i 计算出来的,所以每次运行结果都一样;想让它们变化就用 random()。用同样的方法生成一百万行,就可以试试索引,并用 EXPLAIN ANALYZE 比较执行计划。

给行编号,以及在连接中使用序列

WITH ORDINALITY 可以给任何返回集合的函数加上一个位置列:

结果列出从 09:00 到 10:00 的五个 15 分钟时段,编号为 1 到 5。序列也可以用在 CROSS JOIN 中,比如把每个用户和一个月中的每一天配对。

把 generate_series 写在 SELECT 列表中也会返回多行,但如果有两个这样的调用而长度不同,较短的那个会用 NULL 补齐。所以返回集合的函数要放在 FROM 中。对于单个函数无法表达的序列,比如遍历一棵树,用递归 CTE。

常见问题

generate_series 包含结束值吗?

如果步长正好落在结束值上,就包含:generate_series(1, 5) 返回 1 到 5。如果落不上,序列会停在不超过结束值的最后一个值,所以 generate_series(1, 10, 4) 返回 1、5 和 9。

MySQL 有 generate_series 吗?

没有。在 MySQL 中要用递归 CTE(WITH RECURSIVE)构造序列,这种写法在 PostgreSQL 中也能用,只是写起来更长。

为什么用日期调用 generate_series 返回的是时间戳?

这个函数没有 date 版本,所以 PostgreSQL 使用的是 timestamptz 版本。用 ::date 把每个值转换回来即可。

怎么为每一行生成一个随机数?

在选择列表中调用 random():SELECT i, random() FROM generate_series(1, 5) AS i 为每一行给出 0 到 1 之间的一个值,floor(random() * 100)::int 给出 0 到 99 之间的整数。从 PostgreSQL 17 开始,random(1, 100) 直接返回这个范围内的随机整数。

Coddy 编程语言插图

和 Coddy 一起学编程

开始