SQL高级特性实战：窗口函数、JSONB及多数据库兼容完全指南

为什么需要深入掌握 SQL 高级特性

作为一名后端开发工程师，日常与数据库打交道最多的莫过于编写 SQL 语句。基础的增删改查操作大家都能轻松完成，但真正把 SQL 运用到极致却有不少门道。根据多年的实战经验，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_phoneFROM 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_countFROM ordersGROUP BY 1ORDER 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_numFROM employeesWINDOW 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_pctFROM 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 cumulativeFROM daily_sales;-- 每个部门的薪资占比SELECT employee_name,       department_id,       salary,       ROUND(salary::numeric / SUM(salary) OVER (PARTITION BY department_id) * 100, 2) AS dept_pctFROM employees;

累计求和在报表场景中很常见，比如财务上的累计回款、项目管理中的累计工时等。以前这类需求通常需要在应用层做循环累加，现在一条 SQL 就能搞定。

LATERAL JOIN 是一个很多人不太熟悉但极其实用的特性。简单来说，它允许 JOIN 右侧的子查询引用左侧表的字段，实现一种“跨行关联”的效果。普通的 JOIN 或子查询做不到这一点——要么只能关联外层查询的字段（关联子查询），要么两边各自独立查询。

什么时候需要 LATERAL JOIN

最典型的场景就是“分组取 Top N”。比如查询每个部门薪资最高的前 3 名员工：

SELECT d.department_name, e.employee_name, e.salaryFROM departments dJOIN 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_employeeFROM departments d;-- LATERAL JOIN 写法：可以返回多列多行SELECT d.department_name, e.employee_name, e.salary, e.hire_dateFROM departments dJOIN 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_atFROM users uLEFT JOIN LATERAL (    SELECT order_no, amount, created_at    FROM orders    WHERE user_id = u.id    ORDER BY created_at DESC    LIMIT 1) o ON trueWHERE u.status = 1;

这里用 LEFT JOIN LATERAL 而不是 JOIN LATERAL，是为了保证即使用户没有订单也能出现在结果中（订单相关的字段会是 NULL）。如果确定只查有订单的用户，直接用 JOIN LATERAL 即可。

另一个场景是“就近匹配”。例如物流系统中给每个仓库找最近的三个配送站：

SELECT w.warehouse_name, d.station_name, d.distance_kmFROM warehouses wJOIN 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_ordersWHERE total_amount > 1000ORDER 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 cityFROM user_profiles;-- 条件查询（支持索引）SELECT * FROM user_profilesWHERE profile @> '{"address": {"city": "北京"}}';-- 检查数组是否包含某个元素SELECT profile->>'name' AS nameFROM user_profilesWHERE profile->'skills' ? 'Python';-- 更新 JSONB 中的某个字段UPDATE user_profilesSET profile = jsonb_set(profile, '{age}', '29')WHERE user_id = 1;-- 删除 JSONB 中的某个 keyUPDATE user_profilesSET 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 employeesFROM employeesGROUP BY department_id;-- 把配置项的 key-value 聚合成一个 JSON 对象SELECT jsonb_object_agg(config_key, config_value) AS settingsFROM app_configWHERE 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_profileFROM users uWHERE 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 quantityFROM (    SELECT jsonb_array_elements(order_items) AS item    FROM orders    WHERE order_no = 'ORD-2025-001') t;-- row_to_json 把整行记录转成 JSONSELECT 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_typeFROM work_ordersWHERE order_type = 'IT_REPAIR'  AND extra_fields->>'urgency' = 'high';

这套方案使用了一年多，整体效果不错。新增加工单类型时完全不需要改表结构，只要在前端配置好表单模板即可。写入时就是普通的 INSERT，读取时根据工单类型解析对应的 JSONB 字段。当然也有人质疑这种设计不够“正规”，但在业务变化快、字段不确定的阶段，这种灵活性至关重要。等后续业务稳定后，再把高频查询的字段拆出来也不迟。

五、兼容模式的使用

这是 KES 做得比较贴心的一个功能。很多项目是从 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_DATESELECT 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 风格语法也基本支持：

-- IFNULLSELECT 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 employeesGROUP 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;-- 聚合时处理 NULLSELECT department,       COALESCE(SUM(bonus), 0) AS total_bonusFROM salariesGROUP BY department;

STRING_AGG 行转列

-- 把多行拼成一个字符串SELECT department,       STRING_AGG(employee_name, ', ' ORDER BY employee_name) AS membersFROM employeesGROUP BY department;

EXISTS 替代 IN 做关联查询

-- 当只需要判断存在性时，EXISTS 通常比 IN 更高效SELECT u.usernameFROM users uWHERE 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_salaryFROM employeesGROUP 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 ASSELECT    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_amountFROM ordersWHERE status IN (1, 2, 3)GROUP BY 1ORDER 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;

全量刷新简单粗暴，但刷新过程中视图会被锁住，其他查询会阻塞等待。并发刷新不会阻塞查询，但前提条件是物化视图上必须有唯一索引，否则会报错：

-- 先建唯一索引才能用 CONCURRENTLYCREATE 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 ordersWHERE 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_amountFROM ordersWHERE created_at >= '2025-01-01'  AND created_at < '2025-04-01'GROUP BY 1ORDER BY 1;

可以使用 EXPLAIN 查看执行计划，确认分区裁剪是否生效：

EXPLAIN (ANALYZE, COSTS OFF)SELECT count(*) FROM ordersWHERE 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 引擎在这些高级特性上的支持比较完整，实际使用中没有遇到太多坑。遇到问题时多查阅官方文档，很多细节在文档中都有详细说明。