SQL 高级特性实战：窗口函数、JSONB 与多数据库兼容完全指南
为什么要聊 SQL 高级特性
做后端开发的都知道，日常跟数据库打交道最多的就是写 SQL。简单的增删改查大家都会，但真正把 SQL 用好其实有不少讲究。我在项目中用 KES 数据库做了不少复杂查询，过程中发现它的 SQL 能力比我预期的要强很多，尤其是在窗口函数、JSON 数据处理和多数据库兼容这几个方面。这篇文章把实战中积累的一些用法整理出来，希望能帮到正在写复杂 SQL 的朋友。
一、数据类型与常用函数
很多开发者建表的时候习惯性地用 VARCHAR、INT、TIMESTAMP 这几样打天下，其实数据库提供的数据类型远不止这些。选对数据类型不光能省存储空间，还能让查询逻辑更简洁。
它支持的数据类型比较丰富，除了常见的数值型、字符型、日期型之外，还有一些值得关注的类型：
CREATE TABLE product_info (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(200) NOT NULL,
tags TEXT[], -- 数组类型
attributes JSONB, -- JSON 二进制类型
price NUMERIC(10,2),
status SMALLINT DEFAULT 1,
created_at TIMESTAMP DEFAULT now(),
geo_point POINT -- 几何点类型
);
数组类型在实际场景中挺有用的。比如商品标签、用户角色列表这类一对多的关系，如果数据量不大且不需要单独查询，直接用一个数组字段存就够了，省得再建一张关联表。
字符串和日期函数
日常开发里字符串处理是高频需求。这里的字符串函数跟标准 SQL 比较接近，上手很快。
-- 字符串拼接，两种方式都行
SELECT concat(first_name, ' ', last_name) AS full_name FROM users;
SELECT first_name || ' ' || last_name AS full_name FROM users;
-- 字符串截取和定位
SELECT substring(description FROM 1 FOR 100) AS brief FROM articles;
SELECT position('error' IN log_message) AS pos FROM app_logs;
-- 正则替换，批量清洗数据时很有用
SELECT regexp_replace(phone, '(\d{3})\d{4}(\d{4})', '\1****\2') AS masked_phone
FROM user_contacts;
-- 按分隔符拆分成多行
SELECT unnest(string_to_array('apple,banana,cherry', ',')) AS fruit;
日期函数也是写得多了自然就熟了。几个我经常用的：
-- 日期加减
SELECT now() + INTERVAL '30 days' AS next_month;
SELECT created_at - INTERVAL '7 days' AS week_ago FROM orders;
-- 取日期部分
SELECT date_trunc('month', created_at) AS month_start,
count(*) AS order_count
FROM orders
GROUP BY 1
ORDER BY 1;
-- 两个日期的间隔
SELECT age(now(), created_at) AS account_age FROM users WHERE id = 1;
-- 提取星期几、第几周
SELECT extract(dow FROM now()) AS day_of_week,
extract(week FROM now()) AS week_number;
date_trunc 这个函数做报表统计的时候特别好用，按月、按周、按天聚合数据都靠它。比在应用层做日期格式化再分组要高效得多。
二、窗口函数与 LATERAL JOIN
窗口函数是我觉得 SQL 里最值得花时间学好的特性之一。它能在不改变结果集行数的前提下，对每一行执行某种聚合或排名计算。听起来抽象，看几个例子就明白了。
排名类函数
-- 各部门内按薪资排名
SELECT department_id,
employee_name,
salary,
RANK() OVER w AS rank_num,
DENSE_RANK() OVER w AS dense_rank_num,
ROW_NUMBER() OVER w AS row_num
FROM employees
WINDOW w AS (PARTITION BY department_id ORDER BY salary DESC);
RANK 遇到相同值会并列排名然后跳号，比如两个并列第 2 之后就是第 4。DENSE_RANK 也并列但不跳号，两个并列第 2 之后是第 3。ROW_NUMBER 不并列，即使值相同也会给不同序号。选哪个取决于业务需求，做排行榜的时候一般用 DENSE_RANK，分页的时候用 ROW_NUMBER。
WINDOW w AS 这种写法是定义一个命名窗口，后面可以复用。如果多个窗口函数用相同的 PARTITION 和 ORDER BY 规则，这样写能少打很多字，也不容易出错。
偏移取值函数
LAG 和 LEAD 可以拿到当前行前面或后面第 N 行的值，做同比环比分析的时候特别方便：
SELECT month,
revenue,
LAG(revenue, 1) OVER (ORDER BY month) AS prev_month,
revenue - LAG(revenue, 1) OVER (ORDER BY month) AS diff,
ROUND(
(revenue - LAG(revenue, 1) OVER (ORDER BY month))::numeric
/ NULLIF(LAG(revenue, 1) OVER (ORDER BY month), 0) * 100, 2
) AS growth_pct
FROM monthly_revenue;
这段 SQL 直接算出了每个月的营收环比增长率。NULLIF 用来防止除零错误，当上月营收为零的时候返回 NULL 而不是报错。这种计算如果放到应用层做，需要查出来再用循环处理，代码量和性能都不如直接在 SQL 里搞定。
聚合窗口
窗口函数里也能用 SUM、AVG、COUNT 这些聚合函数：
-- 计算累计销售额（Running Total）
SELECT order_date,
daily_total,
SUM(daily_total) OVER (ORDER BY order_date) AS cumulative
FROM daily_sales;
-- 每个部门的薪资占比
SELECT employee_name,
department_id,
salary,
ROUND(salary::numeric / SUM(salary) OVER (PARTITION BY department_id) * 100, 2) AS dept_pct
FROM employees;
累计求和在报表场景中很常见，比如财务上的累计回款、项目管理里的累计工时等等。以前这类需求通常是在应用层做循环累加，现在一条 SQL 就能搞定。
LATERAL JOIN 是一个很多人不太熟悉但非常实用的特性。简单说，它允许 JOIN 右侧的子查询引用左侧表的字段，实现一种"跨行关联"的效果。普通的 JOIN 或者子查询做不到这一点，要么只能关联外层查询的字段（关联子查询），要么两边各自独立查。
什么时候需要 LATERAL JOIN
最典型的场景就是"分组取 Top N"。比如查询每个部门薪资最高的前 3 名员工：
SELECT d.department_name, e.employee_name, e.salary
FROM departments d
JOIN LATERAL (
SELECT employee_name, salary
FROM employees
WHERE department_id = d.id
ORDER BY salary DESC
LIMIT 3
) e ON true;
如果没有 LATERAL，这个需求用标准 SQL 写起来会非常别扭。要么用窗口函数 ROW_NUMBER 套一层 CTE，要么写一个很复杂的关联子查询。LATERAL JOIN 让这种"对每一行执行一次子查询"的逻辑变得非常直观。
与关联子查询的对比
关联子查询也能引用外层查询的字段，但通常只能用在 SELECT 列表或者 WHERE 条件里，不能像 LATERAL 这样作为 JOIN 的一部分返回多列多行。
-- 关联子查询写法：只能在 SELECT 里返回单个值
SELECT d.department_name,
(SELECT employee_name FROM employees
WHERE department_id = d.id
ORDER BY salary DESC LIMIT 1) AS top_employee
FROM departments d;
-- LATERAL JOIN 写法：可以返回多列多行
SELECT d.department_name, e.employee_name, e.salary, e.hire_date
FROM departments d
JOIN LATERAL (
SELECT employee_name, salary, hire_date
FROM employees
WHERE department_id = d.id
ORDER BY salary DESC
LIMIT 3
) e ON true;
关联子查询那个写法只能拿到薪资最高的那一个人的名字，拿不到更多字段，也拿不到多条记录。LATERAL JOIN 的限制就少很多，子查询里想返回什么就返回什么，想返回几行就返回几行。
实际应用场景
我在做订单系统的时候遇到过一个需求：查询每个用户最近一笔订单的详情，包括订单号、金额和下单时间。用 LATERAL JOIN 写出来特别干净：
SELECT u.username, u.phone, o.order_no, o.amount, o.created_at
FROM users u
LEFT JOIN LATERAL (
SELECT order_no, amount, created_at
FROM orders
WHERE user_id = u.id
ORDER BY created_at DESC
LIMIT 1
) o ON true
WHERE u.status = 1;
这里用 LEFT JOIN LATERAL 而不是 JOIN LATERAL，是为了保证即使用户没有订单也能出现在结果里（订单相关的字段会是 NULL）。如果确定只查有订单的用户，直接用 JOIN LATERAL 就行。
还有一个场景是做"就近匹配"。比如物流系统里给每个仓库找最近的三个配送站：
SELECT w.warehouse_name, d.station_name, d.distance_km
FROM warehouses w
JOIN LATERAL (
SELECT station_name,
ROUND(
(point(d.longitude, d.latitude) <-> point(w.longitude, w.latitude))::numeric, 2
) AS distance_km
FROM delivery_stations d
ORDER BY point(d.longitude, d.latitude) <-> point(w.longitude, w.latitude)
LIMIT 3
) d ON true;
关于性能方面有一点需要注意：LATERAL 子查询对外层表的每一行都会执行一次，所以如果外层表行数很多，子查询的执行效率就很关键。建议在子查询涉及的字段上建好索引，比如上面订单那个例子，orders 表的 (user_id, created_at DESC) 上建复合索引，查询速度会快很多。
三、CTE 与递归查询
CTE（Common Table Expression）就是用 WITH 子句定义临时结果集，让复杂查询变得更有层次感。我个人自从学会 CTE 之后就很少写嵌套子查询了，因为层层嵌套的 SQL 读起来太费劲。
-- 用 CTE 拆分复杂逻辑
WITH active_users AS (
SELECT id, username, last_login
FROM users
WHERE status = 1
AND last_login > now() - INTERVAL '30 days'
),
user_orders AS (
SELECT u.id AS user_id,
u.username,
count(o.id) AS order_count,
sum(o.amount) AS total_amount
FROM active_users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.username
)
SELECT *
FROM user_orders
WHERE total_amount > 1000
ORDER BY total_amount DESC;
每一步做的事情一目了然，后续维护的人也容易看懂。我建议超过两层嵌套的查询都改用 CTE 重写，代码可读性会有质的提升。
递归 CTE 是另一个强大的特性，处理层级数据的时候特别有用。比如组织架构树、分类目录树这类场景：
-- 递归查某个节点的所有下级
WITH RECURSIVE dept_tree AS (
-- 起点：根节点
SELECT id, name, parent_id, 1 AS level, name::text AS path
FROM departments
WHERE id = 1
UNION ALL
-- 递归：找下级
SELECT d.id, d.name, d.parent_id, t.level + 1,
t.path || ' > ' || d.name
FROM departments d
JOIN dept_tree t ON d.parent_id = t.id
)
SELECT * FROM dept_tree ORDER BY path;
level 字段记录层级深度，path 字段拼出完整的层级路径。如果担心数据有循环引用导致无限递归，可以加一个 WHERE t.level < 10 之类的限制。递归 CTE 的执行效率比在应用层递归查数据库要高得多，因为只交互一次就把所有层级数据拿回来了。
四、JSONB 数据处理
JSONB 是我在 KES 上用得最多的非关系型特性。很多场景下数据模型不够确定，或者某些字段的属性经常变化，用 JSONB 存就非常灵活。
-- 插入 JSONB 数据
INSERT INTO user_profiles (user_id, profile) VALUES
(1, '{"name": "张三", "age": 28, "skills": ["Java", "Python", "SQL"], "address": {"city": "北京", "district": "朝阳"}}'),
(2, '{"name": "李四", "age": 32, "skills": ["Go", "Rust"], "address": {"city": "上海", "district": "浦东"}}');
-- 提取单个字段
SELECT profile->>'name' AS name,
(profile->'address'->>'city') AS city
FROM user_profiles;
-- 条件查询（支持索引）
SELECT * FROM user_profiles
WHERE profile @> '{"address": {"city": "北京"}}';
-- 检查数组是否包含某个元素
SELECT profile->>'name' AS name
FROM user_profiles
WHERE profile->'skills' ? 'Python';
-- 更新 JSONB 中的某个字段
UPDATE user_profiles
SET profile = jsonb_set(profile, '{age}', '29')
WHERE user_id = 1;
-- 删除 JSONB 中的某个 key
UPDATE user_profiles
SET profile = profile - 'address'
WHERE user_id = 2;
JSONB 相比 JSON 类型最大的好处是支持索引。对于经常用来做查询条件的 JSONB 字段，建一个 GIN 索引能让查询速度快很多：
CREATE INDEX idx_profile_gin ON user_profiles USING GIN (profile);
不过要注意一点，JSONB 虽然灵活但不能滥用。如果某个字段的查询和统计非常频繁，而且结构是稳定的，还是老老实实拆成普通列比较好。JSONB 最适合存那些"偶尔查一下"的扩展属性，比如用户偏好设置、表单动态字段之类的。
JSONB 聚合函数
除了基本的存取操作，JSONB 的聚合函数在实际开发中也特别实用，主要是 jsonb_agg 和 jsonb_object_agg 这两个。
jsonb_agg 可以把多行数据聚合成一个 JSON 数组，jsonb_object_agg 可以把键值对聚合成一个 JSON 对象。这两个函数在做 API 接口返回数据的时候特别好用，能直接在 SQL 层面拼好前端需要的 JSON 结构，省掉应用层的组装逻辑。
-- 把每个部门的员工聚合成 JSON 数组
SELECT department_id,
jsonb_agg(
jsonb_build_object(
'id', id,
'name', employee_name,
'salary', salary
)
) AS employees
FROM employees
GROUP BY department_id;
-- 把配置项的 key-value 聚合成一个 JSON 对象
SELECT jsonb_object_agg(config_key, config_value) AS settings
FROM app_config
WHERE app_name = 'order-service';
第二条查询跑完之后返回的就是一个完整的 JSON 对象，类似 {"max_retry": "3", "timeout": "30", "enable_cache": "true"} 这种结构，前端或者其他服务拿到就能直接用。
我之前做过一个用户画像的接口，需要把用户基本信息、标签列表、最近订单这些数据组装成一个嵌套的 JSON 返回。一开始是在 Java 里查三次数据库再拼装，后来用 jsonb_agg 配合子查询一条 SQL 就搞定了，响应时间从 200ms 降到了 50ms 左右，效果非常明显。
-- 组装用户完整画像
SELECT jsonb_build_object(
'user_id', u.id,
'username', u.username,
'tags', COALESCE(
(SELECT jsonb_agg(t.tag_name)
FROM user_tags t WHERE t.user_id = u.id), '[]'::jsonb
),
'recent_orders', COALESCE(
(SELECT jsonb_agg(
jsonb_build_object(
'order_no', o.order_no,
'amount', o.amount,
'created_at', to_char(o.created_at, 'YYYY-MM-DD')
)
)
FROM (SELECT * FROM orders WHERE user_id = u.id
ORDER BY created_at DESC LIMIT 5) o
), '[]'::jsonb
)
) AS user_profile
FROM users u
WHERE u.id = 1024;
关系型数据与 JSON 的互转
实际开发中经常需要在关系型数据和 JSON 之间做转换。有时候是把查询结果转成 JSON 给前端用，有时候是把 JSON 数据展开成关系表来查询。
-- 关系表转 JSON：把查询结果转成 JSON 数组
SELECT jsonb_agg(to_jsonb(t))
FROM (
SELECT id, product_name, price, stock
FROM products
WHERE category = '电子产品'
ORDER BY price DESC
LIMIT 10
) t;
-- JSON 转关系表：把 JSON 数组展开成多行
SELECT item->>'name' AS product_name,
(item->>'price')::numeric AS price,
(item->>'quantity')::int AS quantity
FROM (
SELECT jsonb_array_elements(order_items) AS item
FROM orders
WHERE order_no = 'ORD-2025-001'
) t;
-- row_to_json 把整行记录转成 JSON
SELECT row_to_json(t) FROM (
SELECT id, username, email, created_at
FROM users WHERE id = 1
) t;
row_to_json 和 to_jsonb 的区别在于前者返回 JSON 类型，后者返回 JSONB 类型。如果后续还需要对结果做 JSONB 的操作（比如用 @> 包含运算符），建议直接用 to_jsonb。
实战：动态表单数据存储
JSONB 最让我觉得物有所值的场景就是存储动态表单数据。我们之前做过一个工单系统，不同类型的工单有不同的字段。IT 报修工单需要设备编号和故障类型，行政申请工单需要审批流程和预算编号，人事变动工单需要原部门和新部门。
如果给每种工单都建一张表，维护成本太高，而且后续新增工单类型还得再建表。我们的做法是把公共字段（工单号、类型、提交人、提交时间等）做成普通列，各类型特有的字段全部塞到一个 JSONB 字段里：
CREATE TABLE work_orders (
id BIGSERIAL PRIMARY KEY,
order_no VARCHAR(50) NOT NULL,
order_type VARCHAR(30) NOT NULL,
submitter_id BIGINT NOT NULL,
status SMALLINT DEFAULT 0,
extra_fields JSONB DEFAULT '{}',
created_at TIMESTAMP DEFAULT now()
);
-- IT 报修工单
INSERT INTO work_orders (order_no, order_type, submitter_id, extra_fields)
VALUES ('WO-2025-001', 'IT_REPAIR', 101,
'{"device_id": "PC-1024", "fault_type": "蓝屏", "location": "3楼会议室", "urgency": "high"}');
-- 行政申请工单
INSERT INTO work_orders (order_no, order_type, submitter_id, extra_fields)
VALUES ('WO-2025-002', 'ADMIN_REQUEST', 205,
'{"request_type": "办公用品", "budget_code": "BUD-2025-Q2", "approval_chain": ["张经理", "李总监"], "estimated_cost": 1500}');
-- 按类型查询，直接取 JSONB 里的字段
SELECT order_no,
extra_fields->>'device_id' AS device_id,
extra_fields->>'fault_type' AS fault_type
FROM work_orders
WHERE order_type = 'IT_REPAIR'
AND extra_fields->>'urgency' = 'high';
这套方案用了一年多，整体效果不错。新增加工单类型的时候完全不用改表结构，只要在前端配好表单模板就行。写入的时候就是普通的 INSERT，读取的时候根据工单类型解析对应的 JSONB 字段。当然也有人质疑过这种设计不够"正规"，但在业务变化快、字段不确定的阶段，这种灵活性真的很重要。等后续业务稳定了，再把高频查询的字段拆出来也不迟。
五、兼容模式的使用
这是我觉得做得比较贴心的一个功能。很多项目是从 Oracle 或者 MySQL 迁移过来的，SQL 语法和函数不完全一样。如果逐条改写 SQL 工作量很大，兼容模式可以解决大部分问题。
通过 db_compatibility 参数切换兼容模式：
-- 查看当前兼容模式
SHOW db_compatibility;
-- 创建数据库时指定兼容模式
CREATE DATABASE myapp_db WITH db_compatibility = 'oracle';
CREATE DATABASE myapp_db WITH db_compatibility = 'mysql';
Oracle 兼容模式
切到 Oracle 模式后，很多 Oracle 特有的语法和函数就能直接用了：
-- DUAL 表
SELECT sysdate FROM dual;
-- NVL 函数（等同于 COALESCE）
SELECT NVL(phone, '未填写') FROM users;
-- DECODE 函数
SELECT DECODE(status, 1, '启用', 0, '禁用', '未知') FROM accounts;
-- 字符串连接用 ||
SELECT '工号: ' || emp_id || ' 姓名: ' || emp_name FROM employees;
-- ROWNUM 伪列（限制行数）
SELECT * FROM orders WHERE ROWNUM <= 10;
-- TO_CHAR / TO_DATE
SELECT TO_CHAR(created_at, 'YYYY-MM-DD HH24:MI:SS') FROM orders;
SELECT TO_DATE('2025-06-01', 'YYYY-MM-DD') + 30 FROM dual;
这些 Oracle 语法在迁移项目中可以大幅减少 SQL 改写的工作量。不过兼容模式并不能覆盖所有 Oracle 特性，一些高级特性（比如分析函数的特殊写法、CONNECT BY 等）可能还需要手动改写。
MySQL 兼容模式
MySQL 模式下常见的 MySQL 风格语法也基本支持：
-- IFNULL
SELECT IFNULL(nickname, username) AS display_name FROM users;
-- LIMIT 语法
SELECT * FROM products ORDER BY price DESC LIMIT 20 OFFSET 40;
-- GROUP_CONCAT（等同于 STRING_AGG）
SELECT department, GROUP_CONCAT(employee_name SEPARATOR ', ')
FROM employees
GROUP BY department;
-- 反引号引用标识符
SELECT `order`.`id`, `user`.`name`
FROM `order`
JOIN `user` ON `order`.`user_id` = `user`.`id`;
需要注意的是，兼容模式是数据库级别的设置，创建数据库时指定，之后不能在线切换。所以在规划阶段就要确定好使用哪种兼容模式。如果你的应用 SQL 是全新开发的，建议直接用默认模式（标准 SQL），这样代码更规范。
六、实用 SQL 技巧补充
最后分享几个在实战中积累的小技巧，单独看都不复杂，但组合起来能解决不少问题。
UPSERT
INSERT INTO user_scores (user_id, score, updated_at)
VALUES (1, 95, now())
ON CONFLICT (user_id)
DO UPDATE SET score = EXCLUDED.score, updated_at = EXCLUDED.updated_at;
这个语法在做数据同步或幂等操作时非常好用。一条语句搞定"有就更新没有就插入"的逻辑，不用先 SELECT 再判断。
COALESCE 处理 NULL 值
-- 返回第一个非 NULL 的值
SELECT COALESCE(phone, email, '无联系方式') AS contact FROM users;
-- 聚合时处理 NULL
SELECT department,
COALESCE(SUM(bonus), 0) AS total_bonus
FROM salaries
GROUP BY department;
STRING_AGG 行转列
-- 把多行拼成一个字符串
SELECT department,
STRING_AGG(employee_name, ', ' ORDER BY employee_name) AS members
FROM employees
GROUP BY department;
EXISTS 替代 IN 做关联查询
-- 当只需要判断存在性时，EXISTS 通常比 IN 更高效
SELECT u.username
FROM users u
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.user_id = u.id
AND o.created_at > now() - INTERVAL '7 days'
);
EXISTS 找到第一条匹配就返回，不需要把子查询的结果集全部算出来，在子查询结果较多的场景下性能优势明显。
条件聚合
-- 一条 SQL 统计多个维度的数据
SELECT department,
COUNT(*) AS total,
COUNT(*) FILTER (WHERE status = 'active') AS active_count,
COUNT(*) FILTER (WHERE salary > 20000) AS high_salary_count,
AVG(salary) FILTER (WHERE status = 'active') AS avg_active_salary
FROM employees
GROUP BY department;
FILTER 子句是标准 SQL 的写法，比用 CASE WHEN 在聚合函数里做条件判断更清晰。有些老版本的数据库不支持 FILTER，可以用 SUM(CASE WHEN ... THEN 1 ELSE 0 END) 来代替。
七、物化视图与表分区
物化视图的应用
普通视图（VIEW）只是一个保存下来的查询定义，每次查询视图的时候，底层的 SQL 都会重新执行一遍。当数据量增长到一定规模后，一些复杂的统计查询跑起来会越来越慢。物化视图（Materialized View）的思路是：把查询结果实际存储下来，后续查询直接读存储的数据，不用再跑一遍原始 SQL。
基本语法和用法
-- 创建物化视图
CREATE MATERIALIZED VIEW mv_dashboard_stats AS
SELECT
date_trunc('day', created_at) AS stat_date,
count(*) AS order_count,
sum(amount) AS total_amount,
count(DISTINCT user_id) AS unique_buyers,
avg(amount) AS avg_order_amount
FROM orders
WHERE status IN (1, 2, 3)
GROUP BY 1
ORDER BY 1;
-- 创建索引提升物化视图的查询速度
CREATE INDEX idx_mv_stats_date ON mv_dashboard_stats (stat_date);
创建的时候就把数据算好存下来了。后面查这个物化视图就跟查普通表一样快，因为它本质上就是在读一张已经计算好的表。
什么时候该用物化视图
物化视图最适合"查询频率高、源数据变化不频繁"的场景。比如：
- 仪表盘的统计面板，数据每小时或每天刷新一次就够
- 报表系统里的月度、季度汇总数据
- 复杂的多表关联查询结果，供多个下游服务读取
反过来，如果源数据一直在高频变化，而且业务要求实时准确，那物化视图就不太合适了。因为你看到的永远是上一次刷新时的快照。
刷新策略
物化视图的数据不会自动跟着源表变，需要手动刷新：
-- 全量刷新（会锁视图，刷新期间不能查询）
REFRESH MATERIALIZED VIEW mv_dashboard_stats;
-- 并发刷新（不锁视图，但要求物化视图上有唯一索引）
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_dashboard_stats;
全量刷新简单粗暴，但刷新过程中视图是被锁住的，其他查询会阻塞等待。并发刷新不会阻塞查询，但前提条件是物化视图上必须有唯一索引，不然会报错：
-- 先建唯一索引才能用 CONCURRENTLY
CREATE UNIQUE INDEX idx_mv_stats_date_uniq
ON mv_dashboard_stats (stat_date);
-- 然后就可以并发刷新了
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_dashboard_stats;
生产环境建议都用 CONCURRENTLY，不然刷新的时候卡住一堆查询就尴尬了。我之前踩过这个坑，用全量刷新一个统计视图，刷新期间整个看板页面都加载不出来，被运维同事吐槽了好一阵。
刷新的时机一般靠定时任务来控制。可以用数据库自带的定时任务，也可以在应用层用 cron 或者调度框架来触发。我一般会在业务低峰期做刷新，比如每天凌晨两点刷一次前一天的统计数据。
存储和性能的权衡
物化视图本质上是用存储空间换查询速度。一个复杂查询如果涉及几张大表的多表关联和聚合，原始查询可能要跑 30 秒，但物化视图查询只要几毫秒。代价是这份数据要额外占磁盘空间，而且需要定期维护刷新。
我的建议是：对于那些更新频率不高但查询频率很高的统计数据（比如每天更新一次的看板数据），物化视图的收益非常大。但对于实时性要求高的数据，还是老老实实查原始表，或者在应用层做缓存更合适。
表分区策略
当单表数据量增长到几千万甚至上亿条的时候，即使加了索引，查询和维护的成本也会越来越高。表分区是应对这种场景的一个利器。分区表把一个逻辑上的大表按照一定规则拆分成多个物理上的子表，但对应用层来说还是一张表，SQL 写法完全不用变。
按日期范围分区
范围分区（Range Partition）是最常用的分区方式，特别适合有时间维度的表，比如订单表、日志表、交易流水表。
-- 创建分区表
CREATE TABLE orders (
id BIGSERIAL,
order_no VARCHAR(50) NOT NULL,
user_id BIGINT NOT NULL,
amount NUMERIC(12,2),
status SMALLINT DEFAULT 0,
created_at TIMESTAMP DEFAULT now()
) PARTITION BY RANGE (created_at);
-- 按月创建分区
CREATE TABLE orders_2025_01 PARTITION OF orders
FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE orders_2025_02 PARTITION OF orders
FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
CREATE TABLE orders_2025_03 PARTITION OF orders
FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');
-- ... 后续月份以此类推
PARTITION BY RANGE 指定按 created_at 做范围分区，每个分区覆盖一个月的数据。FOR VALUES FROM ... TO ... 定义的是左闭右开区间，包含起始值但不包含结束值。
按类别列表分区
如果数据的分类比较固定，用列表分区（List Partition）更合适。比如按地区或按业务类型分区：
CREATE TABLE sales_data (
id BIGSERIAL,
region VARCHAR(20) NOT NULL,
product_id BIGINT NOT NULL,
amount NUMERIC(12,2),
sale_date DATE DEFAULT CURRENT_DATE
) PARTITION BY LIST (region);
CREATE TABLE sales_east PARTITION OF sales_data
FOR VALUES IN ('华东');
CREATE TABLE sales_south PARTITION OF sales_data
FOR VALUES IN ('华南');
CREATE TABLE sales_north PARTITION OF sales_data
FOR VALUES IN ('华北', '东北');
CREATE TABLE sales_west PARTITION OF sales_data
FOR VALUES IN ('西南', '西北');
列表分区的好处是每个分区的数据量可以根据实际业务来定，不一定要均匀分配。比如华东和华南的销售数据可能占大头，华北和东北数据量差不多可以放一个分区里。
自动创建分区
手动每个月去建分区太麻烦了，可以写一个函数自动生成下个月的分区：
CREATE OR REPLACE FUNCTION create_monthly_partition(target_month DATE)
RETURNS void AS $$
DECLARE
partition_name TEXT;
start_date TEXT;
end_date TEXT;
BEGIN
partition_name := 'orders_' || to_char(target_month, 'YYYY_MM');
start_date := to_char(target_month, 'YYYY-MM-DD');
end_date := to_char(target_month + INTERVAL '1 month', 'YYYY-MM-DD');
EXECUTE format(
'CREATE TABLE IF NOT EXISTS %I PARTITION OF orders FOR VALUES FROM (%L) TO (%L)',
partition_name, start_date, end_date
);
RAISE NOTICE '分区 % 已创建: [% ~ %)', partition_name, start_date, end_date;
END;
$$ LANGUAGE plpgsql;
-- 创建下个月的分区
SELECT create_monthly_partition(date_trunc('month', now()) + INTERVAL '1 month');
再配合定时任务每月初自动执行一下，就不用操心分区创建的事了。我之前有个项目就是因为忘了提前建分区，导致月初的 INSERT 直接报错，数据写不进去了。后来加了自动创建分区的定时任务，才算彻底解决。这个教训告诉我，分区表的运维自动化一定要提前搞好。
分区裁剪与查询性能
分区表最大的价值在于分区裁剪（Partition Pruning）。当查询的 WHERE 条件包含分区键时，数据库优化器会自动判断只需要扫描哪些分区，完全跳过不相关的分区。
-- 这条查询只会扫描 orders_2025_03 这个分区
SELECT count(*), sum(amount)
FROM orders
WHERE created_at >= '2025-03-01'
AND created_at < '2025-04-01';
-- 这条查询会扫描 orders_2025_01 到 orders_2025_03 三个分区
SELECT date_trunc('month', created_at) AS month,
count(*) AS order_count,
sum(amount) AS total_amount
FROM orders
WHERE created_at >= '2025-01-01'
AND created_at < '2025-04-01'
GROUP BY 1
ORDER BY 1;
可以用 EXPLAIN 来看执行计划，确认分区裁剪是否生效：
EXPLAIN (ANALYZE, COSTS OFF)
SELECT count(*) FROM orders
WHERE created_at >= '2025-03-01' AND created_at < '2025-04-01';
如果执行计划里只出现了相关分区的扫描，说明裁剪生效了。如果出现了所有分区的扫描，那就得检查一下 WHERE 条件里的分区键写法是不是有问题。常见的坑是在分区键上套了函数，比如 date_trunc('month', created_at) = '2025-03-01'，这种写法优化器可能识别不出来，导致全分区扫描。
性能提升的幅度跟分区数量和数据量直接相关。我之前在一个日志表上做过对比，分区前全表扫描要 12 秒左右，分区后同样查一个月的数据只要 0.3 秒，提升了差不多 40 倍。那个表总共有一年多的数据，按月分成了 14 个分区。
使用分区表有几点要注意：首先，主键或唯一约束必须包含分区键，不然数据库无法保证跨分区的唯一性。其次，跨分区的 ORDER BY 和聚合操作需要合并多个分区的结果，性能提升没那么明显，最好还是在查询条件里带上分区键，把扫描范围缩到尽量少的分区里。
小结
SQL 高级特性的价值在于把更多的数据处理逻辑下推到数据库层面完成，减少应用层的计算量和数据传输量。窗口函数、CTE、JSONB、LATERAL JOIN、物化视图、表分区这几个特性掌握好了，日常开发中能省掉不少代码，也能让系统性能上一个台阶。兼容模式在迁移项目中是个利器，但新项目还是建议用标准 SQL 从头写。
写 SQL 这件事，够用和用好之间差距其实挺大的。多看看执行计划，多试试不同的写法，慢慢就能写出既简洁又高效的查询了。KingbaseES 的 SQL 引擎在这些高级特性上的支持还是比较完整的，实际用下来没有遇到太多坑。遇到问题的时候多翻翻官方文档，很多细节在文档里都有说明。
