范围函数

范围操作符解决"关系判断"，范围函数解决"内容拆解与构造"。lower() 取起点、upper() 取终点、range_merge() 合并区间、range_agg() 聚合区间——这些函数是把范围类型接入报表与业务逻辑的必经环节。本节逐一梳理并给出可直接运行的示例。

前置知识

阅读本节前，建议先了解：范围操作符

基础概念

函数总表

函数	返回类型	作用
`lower(range)`	子类型	下界（无下界返回 NULL）
`upper(range)`	子类型	上界（无上界返回 NULL）
`isempty(range)`	`boolean`	是否为空范围
`lower_inc(range)`	`boolean`	下界是否包含
`upper_inc(range)`	`boolean`	上界是否包含
`lower_inf(range)`	`boolean`	下界是否无界
`upper_inf(range)`	`boolean`	上界是否无界
`range_merge(a, b)`	范围	两个范围的最小覆盖区间
`range_merge(anyrange)`	范围	聚合：多行范围的最小覆盖区间
`range_agg(anyrange)`	多范围	聚合：把多行范围合并为多范围（PG14+）
`range_intersect_agg(anyrange)`	范围	聚合：多行范围的交集（PG14+）
`unnest(multirange)`	集合	把多范围展开成多行单范围（PG14+）
`multirange(range)`	多范围	把单范围提升为多范围（PG14+）
构造函数	对应范围	由子类型边界构造范围

取值类函数的分工

lower() / upper() 取边界值，lower_inc() / upper_inc() 问边界是否包含，lower_inf() / upper_inf() 问边界是否存在。三者经常一起用：

sql

SELECT period,
       lower(period)      AS lo,        -- 边界值，无界时为 NULL
       upper(period)      AS hi,
       lower_inc(period)  AS lo_closed, -- 下界含不含
       upper_inc(period)  AS hi_closed,
       lower_inf(period)  AS lo_unbounded,  -- 有没有下界
       upper_inf(period)  AS hi_unbounded
FROM (VALUES
  ('[2026-04-01 09:00+08, 2026-04-01 12:00+08)'::tstzrange),
  ('[2026-04-01 09:00+08,)'::tstzrange),
  ('(,2026-04-01 12:00+08]'::tstzrange),
  ('empty'::tstzrange)
) AS t(period);

text

              period               |         lo          |         hi          | lo_closed | hi_closed | lo_unbounded | hi_unbounded
-----------------------------------+---------------------+---------------------+-----------+-----------+--------------+--------------
 ["2026-04-01 09:00:00+08","2026-04-01 12:00:00+08") | 2026-04-01 09:00:00+08 | 2026-04-01 12:00:00+08 | t | f | f | f
 ["2026-04-01 09:00:00+08",)       | 2026-04-01 09:00:00+08 | (null)          | t         | f         | f            | t
 (,"2026-04-01 12:00:00+08"]       | (null)              | 2026-04-01 12:00:00+08 | f | t | t | f
 empty                             | (null)              | (null)          | f         | f         | t            | t

空范围的陷阱

lower_inc('empty'::tstzrange) 返回 false、lower_inf('empty'::tstzrange) 返回 true，但 lower() 返回 NULL。空范围在所有"边界属性"询问上都给出看似合理的答案，实际毫无意义。用 isempty() 先过滤掉空范围是最稳妥的做法。

合并类函数的区别

range_merge() 和 range_agg() 名字相近，语义完全不同：

sql

-- 场景：三条不连续的预约
--   A: [1,3)   B: [5,7)   C: [10,12)

range_merge('[1,3)', '[5,7)')      -- [1,7)   把两个区间"撑"成一个最小覆盖范围，中间空档被吞掉
range_merge(聚合所有行)              -- [1,12)  把三行撑成一个 [1,12)，空档全丢
range_agg(聚合所有行)                -- {[1,3),[5,7),[10,12)}   保留原始多段结构（PG14+）
range_intersect_agg(聚合所有行)      -- empty   三行没有共同交集

一句话区分：range_merge 会"吞掉空档"，range_agg 会"保留空档"。

函数	调用身份	返回类型	空档处理	典型用途
`range_merge(a, b)`	双参数函数	范围	吞掉空档	求两个区间的最小覆盖
`range_merge(anyrange)`	聚合函数	范围	吞掉空档	求一批范围的整体跨度
`range_agg(anyrange)`	聚合函数	多范围	保留空档	求一批范围的真实并集
`range_intersect_agg(anyrange)`	聚合函数	范围	——	求一批范围的公共交集
`unnest(multirange)`	集合返回函数	多行范围	——	把多范围拆回逐段
`multirange(range)`	转换函数	多范围	——	让单范围参与多范围运算

术语提示

下界（Lower Bound）、上界（Upper Bound）、无界（Unbounded）、最小覆盖区间（Minimal Covering Range）、多范围（Multirange）。本节函数围绕这几个概念命名。

语法与用法

取值与属性

text

lower(range) → subtype
upper(range) → subtype
isempty(range) → boolean
lower_inc(range) → boolean
upper_inc(range) → boolean
lower_inf(range) → boolean
upper_inf(range) → boolean

合并与构造

text

range_merge(a anyrange, b anyrange) → anyrange       -- 两个区间的最小覆盖
range_merge(anyrange) → anyrange                     -- 聚合形式，跨行最小覆盖
range_agg(anyrange) → anymultirange                  -- 聚合，多范围（PG14+）
range_intersect_agg(anyrange) → anyrange             -- 聚合，交集（PG14+）
int4range(lower, upper [, bounds])
int8range(lower, upper [, bounds])
numrange(lower, upper [, bounds])
tsrange(lower, upper [, bounds])
tstzrange(lower, upper [, bounds])
daterange(lower, upper [, bounds])

示例表

sql

DROP TABLE IF EXISTS bookings;
CREATE TABLE bookings (
  id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  room_id  TEXT      NOT NULL,
  booker   TEXT      NOT NULL,
  period   TSTZRANGE NOT NULL,
  guests   INT       NOT NULL
);

INSERT INTO bookings (room_id, booker, period, guests) VALUES
  ('A101', '张三', '[2026-05-01 09:00+08, 2026-05-01 10:00+08)', 3),
  ('A101', '李四', '[2026-05-01 10:00+08, 2026-05-01 12:00+08)', 5),
  ('A101', '王五', '[2026-05-01 14:00+08, 2026-05-01 16:00+08)', 2),
  ('A102', '赵六', '[2026-05-01 08:00+08, 2026-05-01 09:30+08)', 4),
  ('A102', '孙七', '[2026-05-01 09:30+08, 2026-05-01 11:00+08)', 6),
  ('A102', '周八', '[2026-05-01 13:00+08, 2026-05-01 18:00+08)', 1);
-- INSERT 0 6

详细说明

取值类函数

sql

SELECT room_id, booker,
       lower(period) AS start_at,
       upper(period) AS end_at,
       guests
FROM bookings
WHERE room_id = 'A101'
ORDER BY lower(period);

text

 room_id | booker |       start_at      |       end_at        | guests
---------+--------+---------------------+---------------------+--------
 A101    | 张三   | 2026-05-01 09:00:00+08 | 2026-05-01 10:00:00+08 |      3
 A101    | 李四   | 2026-05-01 10:00:00+08 | 2026-05-01 12:00:00+08 |      5
 A101    | 王五   | 2026-05-01 14:00:00+08 | 2026-05-01 16:00:00+08 |      2

把边界拆成独立列后，lower(period) / upper(period) 就是普通的 timestamptz，可以直接参与 date_trunc()、GROUP BY 与算术运算：

sql

-- 按小时统计各房间的占用情况
SELECT room_id,
       date_trunc('hour', lower(period) AT TIME ZONE 'Asia/Shanghai') AS hour_slot,
       count(*) AS bookings,
       sum(guests) AS total_guests
FROM bookings
GROUP BY room_id, hour_slot
ORDER BY room_id, hour_slot;

text

 room_id |     hour_slot      | bookings | total_guests
---------+--------------------+----------+--------------
 A101    | 2026-05-01 09:00   |        1 |            3
 A101    | 2026-05-01 10:00   |        1 |            5
 A101    | 2026-05-01 14:00   |        1 |            2
 A102    | 2026-05-01 08:00   |        1 |            4
 A102    | 2026-05-01 09:00   |        1 |            6
 A102    | 2026-05-01 13:00   |        1 |            1

若还想要"每条预约落在哪个整点段"这种维度，直接在 date_trunc('hour', lower(period) AT TIME ZONE 'Asia/Shanghai') 上做 GROUP BY 即可，无需额外的辅助列。

计算时长与无界处理

sql

-- 有界范围直接相减得到 interval
SELECT room_id, booker, upper(period) - lower(period) AS duration
FROM bookings
WHERE NOT isempty(period)
  AND NOT lower_inf(period)
  AND NOT upper_inf(period)
ORDER BY duration DESC;

text

 room_id | booker | duration
---------+--------+----------
 A102    | 周八   | 05:00:00
 A101    | 李四   | 02:00:00
 A101    | 王五   | 02:00:00
 A102    | 孙七   | 01:30:00
 A102    | 赵六   | 01:30:00
 A101    | 张三   | 01:00:00

对可能无界的范围，用 COALESCE 给出兜底：

sql

SELECT period,
       COALESCE(upper(period), 'infinity'::timestamptz) AS end_at,
       COALESCE(upper(period), now()) - lower(period) AS duration_so_far
FROM (VALUES ('[2026-05-01 09:00+08,)'::tstzrange)) AS t(period);

text

                period                 |        end_at        |   duration_so_far
---------------------------------------+----------------------+----------------------
 ["2026-05-01 09:00:00+08",)           | infinity             | 100 days 03:12:45.123

duration_so_far 用 now() 兜底，得到"从开始到现在已经过去多久"。若直接用 upper(period)（即 infinity）相减，结果会是 NULL——interval 无法表示无穷。

边界属性函数

sql

-- 校验：所有时间段都必须是 [) 形式
SELECT id, room_id, booker, period,
       lower_inc(period) AS lo_closed,
       upper_inc(period) AS hi_closed
FROM bookings
WHERE NOT lower_inc(period) OR upper_inc(period);

text

 id | room_id | booker | period | lo_closed | hi_closed
----+---------+--------+--------+-----------+-----------
 (0 rows)

由于建表时所有字面量都用了 [)，查询返回 0 行。这类校验 SQL 可以放进测试用例，防止有人插入 [] 风格的脏数据。

合并函数：`range_merge`

sql

-- 两个区间的最小覆盖
SELECT range_merge('[1,3)'::int4range, '[5,7)'::int4range) AS merged;    -- [1,7)
SELECT range_merge('[1,3)'::int4range, '[3,7)'::int4range) AS adjacent;  -- [1,7) 相邻也合并
SELECT range_merge('[5,7)'::int4range, '[1,3)'::int4range) AS swapped;   -- [1,7) 与参数顺序无关

range_merge 的聚合形式可以把一个房间的全部分段"撑"成一个总范围：

sql

SELECT room_id,
       range_merge(period) AS covering_range,
       lower(range_merge(period)) AS first_start,
       upper(range_merge(period)) AS last_end
FROM bookings
GROUP BY room_id
ORDER BY room_id;

text

 room_id |                 covering_range                  |     first_start     |      last_end
---------+-------------------------------------------------+---------------------+---------------------
 A101    | ["2026-05-01 09:00:00+08","2026-05-01 16:00:00+08") | 2026-05-01 09:00:00+08 | 2026-05-01 16:00:00+08
 A102    | ["2026-05-01 08:00:00+08","2026-05-01 18:00:00+08") | 2026-05-01 08:00:00+08 | 2026-05-01 18:00:00+08

注意 A101 的 covering_range 是 09:00-16:00，把 12:00-14:00 这段实际空闲的时间也吞了进去。这就是"最小覆盖"的定义——它不等于"总占用时长"。

要算真实占用总时长，应该用 range_agg 得到多范围后逐段累加：

sql

-- PostgreSQL 14+
SELECT room_id,
       sum(upper(seg) - lower(seg)) AS busy_total
FROM (
  SELECT room_id, unnest(range_agg(period)) AS seg
  FROM bookings
  GROUP BY room_id
) t
GROUP BY room_id
ORDER BY room_id;

text

 room_id | busy_total
---------+------------
 A101    | 05:00:00
 A102    | 08:00:00

A101 三段分别是 1 小时、2 小时、2 小时，合计 5 小时——与 covering_range 的 7 小时形成鲜明对比。这是 range_merge 与 range_agg 最关键的区别。

聚合函数：`range_agg` 与 `range_intersect_agg`

sql

-- 把每行范围聚合成多范围，相邻的自动合并
SELECT room_id, range_agg(period) AS merged_periods
FROM bookings
GROUP BY room_id
ORDER BY room_id;

text

 room_id |                                    merged_periods
---------+---------------------------------------------------------------------------------------
 A101    | {["2026-05-01 09:00:00+08","2026-05-01 12:00:00+08"),["2026-05-01 14:00:00+08","2026-05-01 16:00:00+08")}
 A102    | {["2026-05-01 08:00:00+08","2026-05-01 11:00:00+08"),["2026-05-01 13:00:00+08","2026-05-01 18:00:00+08")}

A101 的前两段（09-10、10-12）相邻，被合并成 09-12；第三段（14-16）独立保留。A102 同理。

sql

-- 所有房间都空闲的共同时段（交集）
SELECT range_intersect_agg(period) AS common_free
FROM bookings;

text

 common_free
-------------
 empty

三行以上的范围全部有公共交集才会非空。此处各房间时段互不重叠，交集为空。

sql

-- unnest 把多范围拆回多行
SELECT room_id, seg
FROM (
  SELECT room_id, range_agg(period) AS mr
  FROM bookings GROUP BY room_id
) t, unnest(mr) AS seg
ORDER BY room_id, lower(seg);

text

 room_id |                    seg
---------+-----------------------------------------------
 A101    | ["2026-05-01 09:00:00+08","2026-05-01 12:00:00+08")
 A101    | ["2026-05-01 14:00:00+08","2026-05-01 16:00:00+08")
 A102    | ["2026-05-01 08:00:00+08","2026-05-01 11:00:00+08")
 A102    | ["2026-05-01 13:00:00+08","2026-05-01 18:00:00+08")

版本提示

range_agg、range_intersect_agg、unnest(multirange)、multirange() 全部是 PostgreSQL 14 新增。PostgreSQL 13 及更早版本只能先用 array_agg(period) 聚合成数组，再在应用层做区间归并。

构造函数的边界参数

sql

SELECT int4range(1, 5)         AS a,   -- [1,5)
       int4range(1, 5, '[]')   AS b,   -- [1,6)  离散类型规范化
       numrange(1, 5, '[]')    AS c,   -- [1,5]  连续类型不规范化
       tsrange('2026-01-01', '2026-02-01', '(]') AS d,
       daterange('2026-01-01', '2026-01-31', '[]') AS e;   -- [2026-01-01,2026-02-01)

text

   a    |   b    |   c   |                        d                        |            e
--------+--------+-------+-------------------------------------------------+---------------------------
 [1,5)  | [1,6)  | [1,5] | ("2026-01-01 00:00:00","2026-02-01 00:00:00"] | [2026-01-01,2026-02-01)

实战示例

用 bookings 表完成一套"房间利用率报表"。

第 1 步：准备数据

sql

DROP TABLE IF EXISTS bookings;
CREATE TABLE bookings (
  id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  room_id  TEXT      NOT NULL,
  booker   TEXT      NOT NULL,
  period   TSTZRANGE NOT NULL,
  guests   INT       NOT NULL,
  CONSTRAINT chk_bookings_period CHECK (
    NOT isempty(period) AND lower_inc(period) AND NOT upper_inc(period)
  )
);
CREATE INDEX idx_bookings_period ON bookings USING GIST (period);

INSERT INTO bookings (room_id, booker, period, guests) VALUES
  ('A101', '张三', '[2026-05-01 09:00+08, 2026-05-01 10:00+08)', 3),
  ('A101', '李四', '[2026-05-01 10:00+08, 2026-05-01 12:00+08)', 5),
  ('A101', '王五', '[2026-05-01 14:00+08, 2026-05-01 16:00+08)', 2),
  ('A102', '赵六', '[2026-05-01 08:00+08, 2026-05-01 09:30+08)', 4),
  ('A102', '孙七', '[2026-05-01 09:30+08, 2026-05-01 11:00+08)', 6),
  ('A102', '周八', '[2026-05-01 13:00+08, 2026-05-01 18:00+08)', 1);
-- INSERT 0 6

第 1 步里的 CHECK 约束一次性校验三条：非空、[) 形式。任何不合规的写入都会在入库时被拒绝。

第 2 步：拆解每条预约的边界

sql

SELECT id, room_id, booker,
       lower(period) AS start_at,
       upper(period) AS end_at,
       upper(period) - lower(period) AS duration,
       guests
FROM bookings
ORDER BY room_id, lower(period);

text

 id | room_id | booker |     start_at        |      end_at         | duration | guests
----+---------+--------+---------------------+---------------------+----------+--------
  1 | A101    | 张三   | 2026-05-01 09:00:00+08 | 2026-05-01 10:00:00+08 | 01:00:00 |      3
  2 | A101    | 李四   | 2026-05-01 10:00:00+08 | 2026-05-01 12:00:00+08 | 02:00:00 |      5
  3 | A101    | 王五   | 2026-05-01 14:00:00+08 | 2026-05-01 16:00:00+08 | 02:00:00 |      2
  4 | A102    | 赵六   | 2026-05-01 08:00:00+08 | 2026-05-01 09:30:00+08 | 01:30:00 |      4
  5 | A102    | 孙七   | 2026-05-01 09:30:00+08 | 2026-05-01 11:00:00+08 | 01:30:00 |      6
  6 | A102    | 周八   | 2026-05-01 13:00:00+08 | 2026-05-01 18:00:00+08 | 05:00:00 |      1

第 3 步：按房间汇总占用时长

sql

-- PostgreSQL 14+
WITH merged AS (
  SELECT room_id,
         range_agg(period) AS mr,
         range_merge(period) AS cover
  FROM bookings
  GROUP BY room_id
)
SELECT room_id,
       cover,
       (SELECT sum(upper(seg) - lower(seg)) FROM unnest(mr) AS seg) AS busy_total
FROM merged
ORDER BY room_id;

text

 room_id |                       cover                        | busy_total
---------+----------------------------------------------------+------------
 A101    | ["2026-05-01 09:00:00+08","2026-05-01 16:00:00+08") | 05:00:00
 A102    | ["2026-05-01 08:00:00+08","2026-05-01 18:00:00+08") | 08:00:00

cover 是"最早开始到最晚结束"，busy_total 是真实占用。A101 有 2 小时空档（12:00-14:00），所以两者差 2 小时。

第 4 步：找出空闲时段

空闲 = 覆盖区间减去占用多范围：

sql

-- PostgreSQL 14+
WITH merged AS (
  SELECT room_id, range_merge(period) AS cover, range_agg(period) AS busy
  FROM bookings GROUP BY room_id
)
SELECT room_id,
       multirange(cover) - busy AS free_periods
FROM merged
ORDER BY room_id;

text

 room_id |               free_periods
---------+---------------------------------------------
 A101    | {["2026-05-01 12:00:00+08","2026-05-01 14:00:00+08")}
 A102    | {["2026-05-01 11:00:00+08","2026-05-01 13:00:00+08")}

multirange(cover) 把覆盖区间提升为多范围，再减去占用多范围，剩下的就是空闲多范围。

第 5 步：检查某时刻的占用情况

sql

SELECT room_id, booker, guests
FROM bookings
WHERE period @> '2026-05-01 09:45+08'::timestamptz
ORDER BY room_id;

text

 room_id | booker | guests
---------+--------+--------
 A102    | 孙七   |      6

第 6 步：验证约束拦截不合规数据

sql

INSERT INTO bookings (room_id, booker, period, guests) VALUES
  ('A101', '违规者', '[2026-05-01 13:00+08, 2026-05-01 14:00+08]', 2);   -- 用了右闭 ]

text

ERROR:  new row for relation "bookings" violates check constraint "chk_bookings_period"
DETAIL:  Failing row contains (7, A101, 违规者, ["2026-05-01 13:00:00+08","2026-05-01 14:00:00+08"], 2).

第 7 步：清理

sql

DROP TABLE IF EXISTS bookings;

注意事项

lower() / upper() 对无界范围返回 NULL。 参与算术或比较前必须用 COALESCE 或先判断 lower_inf() / upper_inf()。
range_merge 会吞掉空档。 用它算"总占用时长"会高估，正确做法是 unnest(range_agg(...)) 后逐段求和。
range_agg 要求 PostgreSQL 14+。 低版本需降级为 array_agg(period) + 应用层归并。
range_agg 的输入顺序不影响结果。 内部会做规范化与排序，无需先 ORDER BY。
unnest(multirange) 返回的是集合（set-returning）。 放在 SELECT 列表里会展开成多行，注意与普通列的对应关系。
upper(x) - lower(x) 对 int4range 返回整数，对 tstzrange 返回 interval。 结果类型随子类型变化。
isempty() 必须显式检查。 空范围的 lower_inc 为 false、lower_inf 为 true，据此判断会得出矛盾结论。
daterange 是离散类型，upper() 是"最后一天的下一天"。 想取真实最后一天用 upper(dr) - 1。
range_intersect_agg 无交集时返回空范围而非 NULL。 用 isempty() 判断而不是 IS NULL。
多范围与范围不能直接混合运算。 range_agg(...) && '[a,b)'::tstzrange 需要显式转换。
range_merge(a, b) 是双参数函数，range_merge(anyrange) 是聚合。 同名不同身份，GROUP BY 环境下会选聚合形式。
构造函数第三个参数只接受 '[)' '(]' '[]' '()' 四种字符串。 写成 '[[' 之类的会直接报错。

最佳实践

建表时用 CHECK 锁定 [) 约定。 NOT isempty(period) AND lower_inc(period) AND NOT upper_inc(period) 一行防住三类脏数据。
耗时统计区分"覆盖时长"与"占用时长"。 range_merge 给前者，unnest(range_agg()) 求和给后者，报表里两个都要有。
空闲时段用多范围差集计算。 multirange(cover) - busy 是最简洁的表达，避免手写区间拼接逻辑。
拆解边界后立刻转本地时区。 to_char(lower(period) AT TIME ZONE 'Asia/Shanghai', 'HH24:MI') 才是给用户看的东西。
报表类查询把 lower() / upper() 的结果放进 CTE。 重复调用函数不利于优化器，先物化再聚合更清晰。
range_agg 后接 unnest 是标准套路。 需要逐段处理（算时长、查重叠、生成空闲）时都走这条路。
多范围运算是 PostgreSQL 14+ 专属。 若有低版本兼容需求，在应用层实现区间归并并写单元测试覆盖。
校验类 SQL 放进迁移脚本或测试用例。 像"检查所有范围是否为 [)"这样的语句应该定期跑，而不是只在建表时执行一次。
lower() / upper() 上建表达式索引要谨慎。 范围列上的普通索引（GiST）通常更通用；仅当查询模式明确限定为"按起始时间排序/过滤"时才考虑 B-tree 表达式索引。

继续学习：自定义范围类型

参考链接

PostgreSQL 官方文档：Range Functions and Operators
PostgreSQL 官方文档：Range Types
PostgreSQL 官方文档：Multirange Types
PostgreSQL 官方文档：Aggregate Functions
PostgreSQL 官方文档：CREATE TYPE

范围函数 ​

基础概念 ​

函数总表 ​

取值类函数的分工 ​

合并类函数的区别 ​

语法与用法 ​

取值与属性 ​

合并与构造 ​

示例表 ​

详细说明 ​

取值类函数 ​

计算时长与无界处理 ​

边界属性函数 ​

合并函数：range_merge ​

聚合函数：range_agg 与 range_intersect_agg ​

构造函数的边界参数 ​

实战示例 ​

第 1 步：准备数据 ​

第 2 步：拆解每条预约的边界 ​

第 3 步：按房间汇总占用时长 ​

第 4 步：找出空闲时段 ​

第 5 步：检查某时刻的占用情况 ​

第 6 步：验证约束拦截不合规数据 ​

范围函数

基础概念

函数总表

取值类函数的分工

合并类函数的区别

语法与用法

取值与属性

合并与构造

示例表

详细说明

取值类函数

计算时长与无界处理

边界属性函数

合并函数：`range_merge`

聚合函数：`range_agg` 与 `range_intersect_agg`

构造函数的边界参数

实战示例

第 1 步：准备数据

第 2 步：拆解每条预约的边界

第 3 步：按房间汇总占用时长

第 4 步：找出空闲时段

第 5 步：检查某时刻的占用情况

第 6 步：验证约束拦截不合规数据