PostgreSQL 范围/多范围函数和操作符

概述

PostgreSQL 的范围类型（Range Types）和多范围类型（Multirange Types）提供了强大的功能来处理连续值的区间。这些类型在处理时间段、数值区间、库存管理等业务场景中非常有用。本章将详细介绍范围和多范围类型的专用操作符和函数。

范围类型基础

有关范围类型的基础概念，请参阅 PostgreSQL 文档第 8.17 节。范围类型包括 int4range、int8range、numrange、tsrange、tstzrange、daterange 等。

范围类型的比较机制

PostgreSQL 对范围类型的比较遵循特定的规则：

主要排序：首先按范围的下限排序
次要排序：当下限相等时，再比较上限
多范围比较：逐个比较每个范围，直到发现不相等的范围

排序注意事项

这种比较机制通常不会产生有用的整体排序，但它使得在范围上构建唯一索引成为可能。

范围操作符详解

包含关系操作符

包含关系是范围操作中最常用的功能，主要用于判断范围之间或范围与元素之间的包含关系。

`@>` 操作符（包含）

业务场景：员工工作时间管理系统

sql

-- 创建示例表
CREATE TABLE work_schedules (
    employee_id INT,
    work_period tsrange,
    break_period tsrange
);

-- 插入示例数据
INSERT INTO work_schedules VALUES
(1, '[2024-01-15 09:00, 2024-01-15 17:00)', '[2024-01-15 12:00, 2024-01-15 13:00)'),
(2, '[2024-01-15 08:30, 2024-01-15 16:30)', '[2024-01-15 11:30, 2024-01-15 12:30)');

-- 查询：休息时间是否完全在工作时间内
SELECT
    employee_id,
    work_period,
    break_period,
    work_period @> break_period AS is_break_within_work
FROM work_schedules;

输入数据：

Employee 1: 工作时间 [09:00, 17:00)，休息时间 [12:00, 13:00)
Employee 2: 工作时间 [08:30, 16:30)，休息时间 [11:30, 12:30)

输出结果：

employee_id | work_period                                    | break_period                                   | is_break_within_work
------------|------------------------------------------------|------------------------------------------------|--------------------
1           | ["2024-01-15 09:00:00","2024-01-15 17:00:00") | ["2024-01-15 12:00:00","2024-01-15 13:00:00") | t
2           | ["2024-01-15 08:30:00","2024-01-15 16:30:00") | ["2024-01-15 11:30:00","2024-01-15 12:30:00") | t

分析过程：

@> 操作符检查第一个范围是否完全包含第二个范围
在这个例子中，所有员工的休息时间都在工作时间范围内
这种检查对于验证业务规则（休息时间必须在工作时间内）很有用

`<@` 操作符（被包含）

业务场景：会议室预订冲突检测

sql

-- 创建会议室预订表
CREATE TABLE room_bookings (
    booking_id SERIAL PRIMARY KEY,
    room_id INT,
    booked_period tsrange,
    meeting_type VARCHAR(50)
);

-- 插入示例数据
INSERT INTO room_bookings (room_id, booked_period, meeting_type) VALUES
(101, '[2024-01-15 09:00, 2024-01-15 11:00)', '团队会议'),
(101, '[2024-01-15 14:00, 2024-01-15 16:00)', '客户演示'),
(102, '[2024-01-15 10:00, 2024-01-15 12:00)', '项目评审');

-- 检查新的预订请求是否在现有时间段内
WITH new_booking AS (
    SELECT tsrange('2024-01-15 09:30', '2024-01-15 10:30') AS requested_period
)
SELECT
    rb.booking_id,
    rb.booked_period,
    nb.requested_period,
    nb.requested_period <@ rb.booked_period AS is_within_existing
FROM room_bookings rb, new_booking nb
WHERE rb.room_id = 101;

重叠关系操作符

`&&` 操作符（重叠）

业务场景：资源调度冲突检测

sql

-- 创建设备使用计划表
CREATE TABLE equipment_schedule (
    equipment_id INT,
    usage_period tsrange,
    department VARCHAR(50),
    priority INT
);

-- 插入示例数据
INSERT INTO equipment_schedule VALUES
(1, '[2024-01-15 09:00, 2024-01-15 12:00)', 'IT部门', 1),
(1, '[2024-01-15 11:00, 2024-01-15 14:00)', '销售部门', 2),
(1, '[2024-01-15 15:00, 2024-01-15 17:00)', '人事部门', 1);

-- 查找时间冲突
SELECT
    a.department AS dept1,
    a.usage_period AS period1,
    b.department AS dept2,
    b.usage_period AS period2,
    a.usage_period && b.usage_period AS has_conflict
FROM equipment_schedule a, equipment_schedule b
WHERE a.equipment_id = b.equipment_id
    AND a.department != b.department
    AND a.usage_period && b.usage_period;

输出分析：

dept1    | period1                                        | dept2    | period2                                        | has_conflict
---------|------------------------------------------------|----------|------------------------------------------------|-------------
IT部门   | ["2024-01-15 09:00:00","2024-01-15 12:00:00") | 销售部门 | ["2024-01-15 11:00:00","2024-01-15 14:00:00") | t

这个查询识别出 IT 部门和销售部门在 11:00-12:00 时间段存在设备使用冲突。

位置关系操作符

`<<` 和 `>>` 操作符（严格左侧/右侧）

业务场景：生产线时间段安排

sql

-- 创建生产任务表
CREATE TABLE production_tasks (
    task_id SERIAL PRIMARY KEY,
    task_name VARCHAR(100),
    time_slot tsrange,
    line_id INT
);

-- 插入示例数据
INSERT INTO production_tasks (task_name, time_slot, line_id) VALUES
('准备阶段', '[2024-01-15 08:00, 2024-01-15 09:00)', 1),
('生产阶段', '[2024-01-15 09:30, 2024-01-15 15:00)', 1),
('清理阶段', '[2024-01-15 15:30, 2024-01-15 16:30)', 1);

-- 验证任务是否按正确顺序排列
SELECT
    t1.task_name AS earlier_task,
    t1.time_slot AS earlier_period,
    t2.task_name AS later_task,
    t2.time_slot AS later_period,
    t1.time_slot << t2.time_slot AS is_before
FROM production_tasks t1, production_tasks t2
WHERE t1.line_id = t2.line_id
    AND t1.task_id < t2.task_id;

分析结果：

准备阶段 << 生产阶段：true（准备完全在生产之前）
生产阶段 << 清理阶段：true（生产完全在清理之前）

这确保了生产流程的正确顺序。

算术操作符

`+` 操作符（并集）

业务场景：合并相邻的维护时间窗口

sql

-- 创建系统维护表
CREATE TABLE system_maintenance (
    system_id INT,
    maintenance_window tsrange,
    maintenance_type VARCHAR(50)
);

-- 插入示例数据
INSERT INTO system_maintenance VALUES
(1, '[2024-01-15 02:00, 2024-01-15 04:00)', '数据库维护'),
(1, '[2024-01-15 04:00, 2024-01-15 06:00)', '系统更新');

-- 合并相邻的维护窗口
SELECT
    system_id,
    maintenance_window,
    lead(maintenance_window) OVER (ORDER BY maintenance_window) AS next_window,
    CASE
        WHEN maintenance_window -|- lead(maintenance_window) OVER (ORDER BY maintenance_window)
        THEN maintenance_window + lead(maintenance_window) OVER (ORDER BY maintenance_window)
        ELSE NULL
    END AS merged_window
FROM system_maintenance
WHERE system_id = 1;

TIP

并集操作要求

+ 操作符要求范围必须重叠或相邻才能进行并集操作。如果范围不相交，操作会失败。对于不相交的范围，应该使用多范围类型。

`*` 操作符（交集）

业务场景：找出共同的可用时间

sql

-- 创建员工可用时间表
CREATE TABLE employee_availability (
    employee_id INT,
    available_period tsrange,
    date DATE
);

-- 插入示例数据
INSERT INTO employee_availability VALUES
(1, '[2024-01-15 09:00, 2024-01-15 17:00)', '2024-01-15'),
(2, '[2024-01-15 10:00, 2024-01-15 15:00)', '2024-01-15'),
(3, '[2024-01-15 08:00, 2024-01-15 16:00)', '2024-01-15');

-- 找出所有员工的共同可用时间
WITH employee_intersections AS (
    SELECT
        e1.available_period * e2.available_period * e3.available_period AS common_time
    FROM employee_availability e1, employee_availability e2, employee_availability e3
    WHERE e1.employee_id = 1 AND e2.employee_id = 2 AND e3.employee_id = 3
        AND e1.date = e2.date AND e2.date = e3.date
)
SELECT common_time
FROM employee_intersections
WHERE NOT isempty(common_time);

输出结果：

common_time
------------------------------------------------
["2024-01-15 10:00:00","2024-01-15 15:00:00")

这表示三名员工的共同可用时间是 10:00-15:00。

多范围操作符

多范围类型允许在单个值中存储多个不相交的范围，这在处理复杂的时间安排或不连续区间时非常有用。

多范围创建和基本操作

业务场景：员工的多个工作时段

sql

-- 创建灵活工作时间表
CREATE TABLE flexible_schedule (
    employee_id INT,
    work_periods tsmultirange,
    date DATE
);

-- 插入示例数据（员工有多个不连续的工作时段）
INSERT INTO flexible_schedule VALUES
(1, '{[2024-01-15 09:00, 2024-01-15 12:00), [2024-01-15 14:00, 2024-01-15 17:00)}', '2024-01-15'),
(2, '{[2024-01-15 08:00, 2024-01-15 11:00), [2024-01-15 13:00, 2024-01-15 16:00)}', '2024-01-15');

-- 检查特定时间是否在工作时段内
SELECT
    employee_id,
    work_periods,
    work_periods @> '2024-01-15 10:30'::timestamp AS working_at_1030,
    work_periods @> '2024-01-15 13:30'::timestamp AS working_at_1330,
    work_periods @> '2024-01-15 15:30'::timestamp AS working_at_1530
FROM flexible_schedule;

多范围并集操作

业务场景：合并多个部门的服务时间

sql

-- 创建部门服务时间表
CREATE TABLE department_service_hours (
    department VARCHAR(50),
    service_periods tsmultirange
);

-- 插入示例数据
INSERT INTO department_service_hours VALUES
('客服部门', '{[2024-01-15 08:00, 2024-01-15 12:00), [2024-01-15 14:00, 2024-01-15 18:00)}'),
('技术支持', '{[2024-01-15 09:00, 2024-01-15 17:00)}'),
('销售部门', '{[2024-01-15 10:00, 2024-01-15 16:00), [2024-01-15 19:00, 2024-01-15 21:00)}');

-- 计算总的服务覆盖时间
SELECT
    d1.service_periods + d2.service_periods + d3.service_periods AS total_coverage
FROM department_service_hours d1, department_service_hours d2, department_service_hours d3
WHERE d1.department = '客服部门'
    AND d2.department = '技术支持'
    AND d3.department = '销售部门';

范围函数详解

边界提取函数

`lower()` 和 `upper()` 函数

业务场景：分析促销活动的时间范围

sql

-- 创建促销活动表
CREATE TABLE promotions (
    promo_id SERIAL PRIMARY KEY,
    promo_name VARCHAR(100),
    active_period tsrange,
    discount_rate DECIMAL(3,2)
);

-- 插入示例数据
INSERT INTO promotions (promo_name, active_period, discount_rate) VALUES
('春节特惠', '[2024-02-01 00:00, 2024-02-15 23:59]', 0.20),
('情人节促销', '[2024-02-14 00:00, 2024-02-14 23:59]', 0.15),
('月末清仓', '[2024-01-25 00:00, 2024-01-31 23:59]', 0.30);

-- 分析促销活动的开始和结束时间
SELECT
    promo_name,
    active_period,
    lower(active_period) AS start_time,
    upper(active_period) AS end_time,
    upper(active_period) - lower(active_period) AS duration
FROM promotions
ORDER BY lower(active_period);

属性检查函数

`isempty()`、`lower_inc()`、`upper_inc()` 函数

业务场景：验证时间范围的有效性

sql

-- 创建事件计划表
CREATE TABLE event_schedule (
    event_id SERIAL PRIMARY KEY,
    event_name VARCHAR(100),
    time_range tsrange
);

-- 插入测试数据（包括一些边界情况）
INSERT INTO event_schedule (event_name, time_range) VALUES
('正常会议', '[2024-01-15 10:00, 2024-01-15 11:00)'),
('全天活动', '[2024-01-15 00:00, 2024-01-15 23:59]'),
('空时间段', 'empty');

-- 检查时间范围的属性
SELECT
    event_name,
    time_range,
    isempty(time_range) AS is_empty,
    CASE
        WHEN NOT isempty(time_range) THEN lower_inc(time_range)
        ELSE NULL
    END AS includes_start,
    CASE
        WHEN NOT isempty(time_range) THEN upper_inc(time_range)
        ELSE NULL
    END AS includes_end
FROM event_schedule;

输出分析：

event_name | time_range                                     | is_empty | includes_start | includes_end
-----------|------------------------------------------------|----------|----------------|-------------
正常会议   | ["2024-01-15 10:00:00","2024-01-15 11:00:00") | f        | t              | f
全天活动   | ["2024-01-15 00:00:00","2024-01-15 23:59:00"] | f        | t              | t
空时间段   | empty                                          | t        | NULL           | NULL

范围合并函数

`range_merge()` 函数

业务场景：计算项目的总体时间跨度

sql

-- 创建项目任务表
CREATE TABLE project_tasks (
    task_id SERIAL PRIMARY KEY,
    project_id INT,
    task_period tsrange,
    task_name VARCHAR(100)
);

-- 插入示例数据
INSERT INTO project_tasks (project_id, task_period, task_name) VALUES
(1, '[2024-01-15 09:00, 2024-01-20 17:00)', '需求分析'),
(1, '[2024-01-22 09:00, 2024-02-05 17:00)', '系统设计'),
(1, '[2024-02-06 09:00, 2024-02-28 17:00)', '开发实现');

-- 计算项目的总体时间跨度
WITH project_spans AS (
    SELECT
        project_id,
        task_period,
        task_name,
        ROW_NUMBER() OVER (PARTITION BY project_id ORDER BY lower(task_period)) as rn
    FROM project_tasks
    WHERE project_id = 1
),
merged_ranges AS (
    SELECT
        project_id,
        range_merge(
            (SELECT task_period FROM project_spans WHERE rn = 1),
            (SELECT task_period FROM project_spans WHERE rn = 3)
        ) AS total_span
    FROM project_spans
    WHERE rn = 1
)
SELECT
    project_id,
    total_span,
    lower(total_span) AS project_start,
    upper(total_span) AS project_end,
    upper(total_span) - lower(total_span) AS total_duration
FROM merged_ranges;

实际应用场景

酒店房间预订系统

sql

-- 创建房间预订管理系统
CREATE TABLE hotel_rooms (
    room_id INT PRIMARY KEY,
    room_type VARCHAR(50),
    price_per_night DECIMAL(8,2)
);

CREATE TABLE reservations (
    reservation_id SERIAL PRIMARY KEY,
    room_id INT REFERENCES hotel_rooms(room_id),
    guest_name VARCHAR(100),
    stay_period daterange,
    status VARCHAR(20) DEFAULT 'confirmed'
);

-- 插入示例数据
INSERT INTO hotel_rooms VALUES
(101, '标准间', 299.00),
(102, '豪华间', 499.00),
(201, '套房', 799.00);

INSERT INTO reservations (room_id, guest_name, stay_period) VALUES
(101, '张先生', '[2024-01-15, 2024-01-18)'),
(101, '李女士', '[2024-01-20, 2024-01-22)'),
(102, '王先生', '[2024-01-16, 2024-01-19)');

-- 查询：检查房间在特定日期的可用性
WITH requested_period AS (
    SELECT daterange('2024-01-17', '2024-01-21') AS check_period
)
SELECT
    hr.room_id,
    hr.room_type,
    hr.price_per_night,
    bool_and(NOT (r.stay_period && rp.check_period)) AS is_available
FROM hotel_rooms hr
CROSS JOIN requested_period rp
LEFT JOIN reservations r ON hr.room_id = r.room_id AND r.status = 'confirmed'
GROUP BY hr.room_id, hr.room_type, hr.price_per_night, rp.check_period;

员工班次管理系统

sql

-- 创建员工班次管理系统
CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50)
);

CREATE TABLE work_shifts (
    shift_id SERIAL PRIMARY KEY,
    employee_id INT REFERENCES employees(employee_id),
    shift_date DATE,
    work_periods tsmultirange,
    shift_type VARCHAR(20)
);

-- 插入示例数据
INSERT INTO employees (name, department) VALUES
('张三', 'IT部门'),
('李四', 'IT部门'),
('王五', '客服部门');

INSERT INTO work_shifts (employee_id, shift_date, work_periods, shift_type) VALUES
(1, '2024-01-15', '{[2024-01-15 09:00, 2024-01-15 12:00), [2024-01-15 13:00, 2024-01-15 17:00)}', '标准班'),
(2, '2024-01-15', '{[2024-01-15 14:00, 2024-01-15 22:00)}', '下午班'),
(3, '2024-01-15', '{[2024-01-15 08:00, 2024-01-15 12:00), [2024-01-15 13:00, 2024-01-15 16:00)}', '客服班');

-- 查询：找出在特定时间工作的所有员工
SELECT
    e.name,
    e.department,
    ws.work_periods,
    ws.work_periods @> '2024-01-15 15:30'::timestamp AS working_at_1530
FROM employees e
JOIN work_shifts ws ON e.employee_id = ws.employee_id
WHERE ws.shift_date = '2024-01-15'
    AND ws.work_periods @> '2024-01-15 15:30'::timestamp;

-- 查询：计算部门的总工作时间覆盖
SELECT
    e.department,
    COUNT(*) AS employee_count,
    -- 注意：这里简化了多范围的合并逻辑
    string_agg(ws.work_periods::text, ', ') AS all_periods
FROM employees e
JOIN work_shifts ws ON e.employee_id = ws.employee_id
WHERE ws.shift_date = '2024-01-15'
GROUP BY e.department;

性能优化技巧

范围类型索引

sql

-- 为范围字段创建 GiST 索引以提高查询性能
CREATE INDEX idx_reservations_stay_period ON reservations USING GIST (stay_period);
CREATE INDEX idx_work_shifts_periods ON work_shifts USING GIST (work_periods);

-- 使用索引的查询示例
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM reservations
WHERE stay_period @> '2024-01-16'::date;

查询优化建议

TIP

性能优化建议

使用 GiST 索引：对于范围类型字段，使用 GiST 索引可以显著提高包含、重叠等操作的性能
避免不必要的范围转换：直接使用范围类型进行比较，避免转换为其他类型
合理使用多范围：对于不相交的区间，使用多范围类型比多个单独的范围更高效
边界条件优化：明确指定范围的包含/排斥边界，避免歧义

常见陷阱和注意事项

空范围处理

sql

-- 演示空范围的特殊行为
SELECT
    'empty'::int4range AS empty_range,
    isempty('empty'::int4range) AS is_empty,
    'empty'::int4range @> 5 AS contains_five,
    int4range(1,10) @> 'empty'::int4range AS contains_empty,
    'empty'::int4range && int4range(1,10) AS overlaps_normal;

输出结果：

empty_range | is_empty | contains_five | contains_empty | overlaps_normal
------------|----------|---------------|----------------|----------------
empty       | t        | f             | t              | f

重要规则：

空范围不包含任何元素
任何范围都包含空范围
空范围与任何范围都不重叠
空范围在位置比较中既不在左侧也不在右侧

边界包含问题

sql

-- 演示边界包含的细微差别
SELECT
    '[1,5)'::int4range @> 5 AS excludes_upper,
    '[1,5]'::int4range @> 5 AS includes_upper,
    '[1,5)'::int4range -|- '[5,10)'::int4range AS adjacent_excludes,
    '[1,5]'::int4range -|- '[5,10)'::int4range AS adjacent_includes;

WARNING

边界注意事项

[1,5) 不包含 5，而 [1,5] 包含 5
相邻性检查会考虑边界的包含性
在业务逻辑中要明确边界的含义

总结

PostgreSQL 的范围和多范围类型提供了强大的区间处理能力，特别适用于：

时间管理：班次安排、会议预订、项目计划
资源调度：设备使用、房间预订、车辆调度
数值区间：价格范围、库存阈值、性能指标
地理空间：结合几何类型处理空间区间

通过合理使用范围操作符和函数，可以简化复杂的业务逻辑，提高查询效率，并确保数据的一致性和准确性。在实际应用中，建议结合适当的索引策略和查询优化技术，以获得最佳的性能表现。

PostgreSQL 范围/多范围函数和操作符 ​

概述 ​

范围类型的比较机制 ​

范围操作符详解 ​

包含关系操作符 ​

@> 操作符（包含） ​

<@ 操作符（被包含） ​

重叠关系操作符 ​

&& 操作符（重叠） ​

位置关系操作符 ​

<< 和 >> 操作符（严格左侧/右侧） ​

算术操作符 ​

+ 操作符（并集） ​

* 操作符（交集） ​

多范围操作符 ​

多范围创建和基本操作 ​

多范围并集操作 ​

范围函数详解 ​

边界提取函数 ​

lower() 和 upper() 函数 ​

属性检查函数 ​

isempty()、lower_inc()、upper_inc() 函数 ​

范围合并函数 ​

range_merge() 函数 ​

实际应用场景 ​

酒店房间预订系统 ​

员工班次管理系统 ​

性能优化技巧 ​

范围类型索引 ​

查询优化建议 ​

常见陷阱和注意事项 ​

空范围处理 ​

边界包含问题 ​

总结 ​

PostgreSQL 范围/多范围函数和操作符

概述

范围类型的比较机制

范围操作符详解

包含关系操作符

`@>` 操作符（包含）

`<@` 操作符（被包含）

重叠关系操作符

`&&` 操作符（重叠）

位置关系操作符

`<<` 和 `>>` 操作符（严格左侧/右侧）

算术操作符

`+` 操作符（并集）

`*` 操作符（交集）

多范围操作符

多范围创建和基本操作

多范围并集操作

范围函数详解

边界提取函数

`lower()` 和 `upper()` 函数

属性检查函数

`isempty()`、`lower_inc()`、`upper_inc()` 函数

范围合并函数

`range_merge()` 函数

实际应用场景

酒店房间预订系统

员工班次管理系统

性能优化技巧

范围类型索引

查询优化建议

常见陷阱和注意事项

空范围处理

边界包含问题

总结