PostgreSQL 高级特性详解 —— 函数、高级查询、数据类型扩展、索引

✅ PostgreSQL 高级特性详解 —— 函数、高级查询、数据类型扩展、索引

本篇涵盖 PostgreSQL 核心高级能力，助你写出高性能、复杂逻辑的 SQL。

---

一、常用内置函数

---

✅ 1. 字符串函数

函数	说明	示例
LENGTH(str)	返回字符串长度	SELECT LENGTH('hello') → 5
UPPER(str) / LOWER(str)	大小写转换	SELECT UPPER('abc') → 'ABC'
TRIM(str)	去除首尾空格	SELECT TRIM(' abc ') → 'abc'
SUBSTRING(str FROM n FOR m)	截取子串	SELECT SUBSTRING('hello' FROM 2 FOR 3) → 'ell'
CONCAT(str1, str2, ...)	字符串拼接	SELECT CONCAT('a', 'b', 'c') → 'abc'
`		` 操作符
POSITION(sub IN str)	返回子串位置（从1开始）	SELECT POSITION('ll' IN 'hello') → 3
REPLACE(str, old, new)	替换子串	SELECT REPLACE('abc', 'b', 'x') → 'axc'
SPLIT_PART(str, delim, n)	按分隔符取第n部分	SELECT SPLIT_PART('a,b,c', ',', 2) → 'b'

---

✅ 2. 数学函数

函数	说明	示例
ABS(x)	绝对值	SELECT ABS(-5) → 5
CEIL(x) / FLOOR(x)	向上/向下取整	SELECT CEIL(3.2) → 4
ROUND(x, n)	四舍五入（n位小数）	SELECT ROUND(3.14159, 2) → 3.14
RANDOM()	返回0~1随机数	SELECT RANDOM()
POWER(x, y)	x的y次方	SELECT POWER(2, 3) → 8
SQRT(x)	平方根	SELECT SQRT(16) → 4
MOD(x, y)	取模	SELECT MOD(10, 3) → 1

---

✅ 3. 时间日期函数

函数	说明	示例
NOW() / CURRENT_TIMESTAMP	当前时间戳	SELECT NOW()
CURRENT_DATE	当前日期	SELECT CURRENT_DATE
CURRENT_TIME	当前时间	SELECT CURRENT_TIME
EXTRACT(part FROM timestamp)	提取时间部分	SELECT EXTRACT(YEAR FROM NOW()) → 2025
DATE_PART('part', timestamp)	同 EXTRACT	SELECT DATE_PART('month', NOW()) → 4
AGE(timestamp)	计算与当前时间的间隔	SELECT AGE('2000-01-01') → 25 years ...
DATE_TRUNC('part', timestamp)	截断到指定精度	SELECT DATE_TRUNC('month', NOW()) → 2025-04-01 00:00:00
TO_CHAR(timestamp, format)	格式化输出	SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS')
TO_TIMESTAMP(str, format)	字符串转时间戳	SELECT TO_TIMESTAMP('2025-04-01', 'YYYY-MM-DD')

---

二、高级查询

---

✅ 1. 子查询（Subquery）

▶ 表达式子查询（Scalar Subquery）

返回单值，可用于 SELECT/WHERE：

SELECT 
    name,
    (SELECT COUNT(*) FROM orders WHERE user_id = users.id) as order_count
FROM users;

▶ 派生表（Derived Table / FROM 子查询）

在 FROM 中作为临时表：

SELECT u.name, o.total_orders
FROM users u
JOIN (
    SELECT user_id, COUNT(*) as total_orders
    FROM orders
    GROUP BY user_id
) o ON u.id = o.user_id;

▶ EXISTS / NOT EXISTS

检查是否存在匹配行：

SELECT name FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 1000
);

---

✅ 2. 通用表表达式 CTE（WITH 子句）

▶ 基础 CTE（替代子查询，提升可读性）

WITH order_summary AS (
    SELECT 
        user_id,
        COUNT(*) as order_count,
        SUM(amount) as total_spent
    FROM orders
    GROUP BY user_id
)
SELECT 
    u.name,
    os.order_count,
    os.total_spent
FROM users u
JOIN order_summary os ON u.id = os.user_id;

▶ 递归 CTE（处理树形/层级结构）

-- 员工组织架构（id, name, manager_id）
WITH RECURSIVE org_tree AS (
    -- 初始查询：CEO（无上级）
    SELECT id, name, manager_id, 1 as level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- 递归部分：查找下级
    SELECT e.id, e.name, e.manager_id, ot.level + 1
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT 
    REPEAT('  ', level - 1) || name as hierarchy,
    level
FROM org_tree
ORDER BY level, name;

💡 递归 CTE 必须包含 UNION ALL，且递归部分不能包含 DISTINCT, GROUP BY, 聚合函数等。

---

✅ 3. 窗口函数（Window Functions）

解决分组内排序、累计、移动平均等复杂问题。

▶ 基本语法

函数() OVER (
    [PARTITION BY 分组列]
    [ORDER BY 排序列]
    [ROWS/RANGE 子句]
)

▶ 常用窗口函数

函数	说明	示例
ROW_NUMBER()	分组内行号（无并列）	ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC)
RANK()	排名（并列后跳号）	RANK() OVER (ORDER BY score DESC)
DENSE_RANK()	排名（并列后不跳号）	DENSE_RANK() OVER (ORDER BY score DESC)
SUM(col) OVER()	累计求和	SUM(sales) OVER (ORDER BY date)
AVG(col) OVER()	移动平均	AVG(price) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
LEAD(col, n)	取后第n行值	LEAD(salary, 1) OVER (ORDER BY hire_date)
LAG(col, n)	取前第n行值	LAG(salary, 1) OVER (ORDER BY hire_date)

▶ 实战示例：部门内薪资排名

SELECT 
    name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank,
    ROW_NUMBER() OVER (ORDER BY salary DESC) as global_rank
FROM employees;

▶ 示例：累计销售额

SELECT 
    sale_date,
    daily_sales,
    SUM(daily_sales) OVER (ORDER BY sale_date) as cumulative_sales
FROM daily_sales_report;

---

三、数据类型扩展

---

✅ 1. 数组（ARRAY）

-- 创建含数组的表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    tags TEXT[],          -- 文本数组
    prices NUMERIC[]      -- 数值数组
);

-- 插入数据
INSERT INTO products (name, tags, prices)
VALUES (
    '笔记本电脑',
    ARRAY['电子', '办公', '新品'],
    ARRAY[5999.00, 6999.00, 7999.00]
);

-- 查询
SELECT name, tags[1] as first_tag, prices[2] as mid_price
FROM products;

-- 数组函数
SELECT 
    name,
    ARRAY_LENGTH(tags, 1) as tag_count,
    ARRAY_TO_STRING(tags, ', ') as tag_list
FROM products;

---

✅ 2. JSON / JSONB（PostgreSQL 王牌特性）

类型	说明	存储方式	查询效率	是否支持索引
JSON	存储原始 JSON 文本	文本	慢	❌
JSONB	存储解析后的二进制格式	二进制	✅ 快	✅

💡 生产环境一律使用 JSONB！

▶ 基本操作

-- 创建表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    profile JSONB  -- 使用 JSONB！
);

-- 插入数据
INSERT INTO users (profile) VALUES
('{"name": "张三", "age": 30, "tags": ["developer", "postgres"], "address": {"city": "北京", "district": "朝阳"}}'),
('{"name": "李四", "age": 25, "tags": ["designer"], "skills": ["PS", "AI"]}');

▶ 查询 JSONB 数据

-- 获取 name 字段
SELECT profile->>'name' as name FROM users;

-- 获取嵌套字段
SELECT profile->'address'->>'city' as city FROM users;

-- 条件查询（使用 @> 包含操作符）
SELECT * FROM users WHERE profile @> '{"age": 30}';

-- 查询数组包含某元素
SELECT * FROM users WHERE profile->'tags' ? 'developer';

-- 更新 JSONB 字段
UPDATE users 
SET profile = profile || '{"age": 31}'::jsonb  -- 合并更新
WHERE profile->>'name' = '张三';

▶ JSONB 索引（GIN 索引）

-- 创建 GIN 索引（支持 @>, ?, ?& 等操作符）
CREATE INDEX idx_users_profile ON users USING GIN (profile);

-- 针对特定路径创建索引（更高效）
CREATE INDEX idx_users_profile_name ON users ((profile->>'name'));

---

✅ 3. 枚举类型（ENUM）

-- 创建枚举类型
CREATE TYPE user_status AS ENUM ('active', 'inactive', 'suspended');

-- 使用枚举
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT,
    status user_status DEFAULT 'active'
);

-- 插入
INSERT INTO users (name, status) VALUES ('Alice', 'active');

-- 查询
SELECT * FROM users WHERE status = 'active';

⚠️ 枚举值不易修改，建议用外键关联字典表替代。

---

✅ 4. 范围类型（Range Types）

用于表示区间（如时间范围、数值范围）。

-- 创建含范围的表
CREATE TABLE room_bookings (
    id SERIAL PRIMARY KEY,
    room_name TEXT,
    during TSRANGE  -- 时间范围
);

-- 插入数据（使用方括号/圆括号表示闭区间/开区间）
INSERT INTO room_bookings (room_name, during)
VALUES 
('会议室A', '[2025-04-01 09:00, 2025-04-01 12:00)'),
('会议室B', '[2025-04-01 14:00, 2025-04-01 17:00)');

-- 查询重叠预订（&& 操作符）
SELECT * FROM room_bookings 
WHERE during && '[2025-04-01 10:00, 2025-04-01 11:00)'::tsrange;

-- 获取范围边界
SELECT 
    room_name,
    LOWER(during) as start_time,
    UPPER(during) as end_time
FROM room_bookings;

支持类型：INT4RANGE, INT8RANGE, NUMRANGE, TSRANGE, TSTZRANGE, DATERANGE

---

四、索引详解

---

✅ 1. 索引的作用与原理

• 作用：加速 WHERE、JOIN、ORDER BY、DISTINCT 等操作
• 原理：B-Tree（默认）像“目录”，避免全表扫描

📈 索引代价：占用磁盘空间 + 降低 INSERT/UPDATE/DELETE 速度（需维护索引结构）

---

✅ 2. 索引类型对比

类型	适用场景	支持操作符	示例
B-Tree	默认，通用（=, <, >, BETWEEN, LIKE ‘abc%’）	所有比较操作符	CREATE INDEX idx_name ON table (column)
Hash	等值查询（=）	=	CREATE INDEX idx_name ON table USING HASH (column)
GIN	数组、JSONB、全文搜索	@>, ?, ?&, && 等	CREATE INDEX idx_json ON table USING GIN (jsonb_col)
GiST	范围、几何、全文搜索	&&, <@, @>, «, » 等	CREATE INDEX idx_range ON table USING GIST (range_col)
SP-GiST	空间、非平衡树（如IP范围）	同 GiST	CREATE INDEX idx_ip ON table USING SPGIST (ip_col inet_ops)
BRIN	大表、有序数据（如时间序列）	=, <, >	CREATE INDEX idx_time ON table USING BRIN (created_at)

💡 选择建议：

• 90% 场景用 B-Tree
• JSONB/数组 → GIN
• 范围查询 → GiST
• 超大表 + 有序列 → BRIN

---

✅ 3. 创建索引实战

-- 单列索引
CREATE INDEX idx_users_email ON users (email);

-- 多列索引（注意列顺序！）
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at);

-- 条件索引（部分索引）
CREATE INDEX idx_active_users ON users (email) WHERE is_active = true;

-- 表达式索引（函数索引）
CREATE INDEX idx_users_lower_name ON users (LOWER(name));

-- 唯一索引
CREATE UNIQUE INDEX idx_users_phone ON users (phone);

-- 并发创建索引（不阻塞写入）
CREATE INDEX CONCURRENTLY idx_big_table_id ON big_table (id);

---

✅ 4. 索引监控与维护

-- 查看表的索引
\d table_name

-- 查看索引使用情况（需开启 track_counts）
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan as times_used
FROM pg_stat_user_indexes
WHERE tablename = 'users';

-- 重建索引（碎片整理）
REINDEX INDEX index_name;
-- 或
REINDEX TABLE table_name;

-- 删除索引
DROP INDEX IF EXISTS index_name;

---

✅ 5. 索引设计原则

1. WHERE 条件列优先建索引
2. JOIN 关联列建索引
3. ORDER BY / GROUP BY 列考虑建索引
4. 避免过度索引（写多读少的表慎用）
5. 多列索引注意列顺序（最常用、选择性高的放前面）
6. 大文本/JSONB 用 GIN/GiST
7. 定期监控索引使用率，删除无用索引

---

🎯 综合实践任务

请完成以下操作：

1. 创建一个 products 表，包含 id, name, category, price, tags[], metadata JSONB
2. 插入 5 条测试数据（含 JSONB 和数组）
3. 编写查询：
   • 使用 CTE 计算每个类别的平均价格
   • 使用窗口函数显示每个类别内价格排名
   • 使用 JSONB 查询 metadata 中包含特定属性的产品
4. 为 category 和 price 创建 B-Tree 复合索引
5. 为 metadata 创建 GIN 索引
6. 使用 EXPLAIN ANALYZE 对比有无索引的查询性能差异