掌握 SQL 窗口函数与 JSONB，比较容易做到多数据库兼容

1️⃣ 先说痛点：为什么要学高级特性？

- 业务经常需要复杂聚合、排名、同比环比等查询，写普通聚合+子查询既繁琐又易出错。- 迁移到新 DB时语法差异导致大量手工 。其实，- 数据结构不断演化，传统表结构无法灵活适配，导致频繁改表。- 性能瓶颈往往在数据搬运与应用层计算特别是大表统计。

2️⃣ 数据类型与常用函数：选对类型先省空间后省心

痛点：开发者常把所有字段都用 VARCHAR 或 INT，忽视更合适的类型导致存储浪费和查询慢。

CREATE TABLE product_info (
id BIGSERIAL PRIMARY KEY,name VARCHAR NOT NULL。tags TEXT,attributes JSONB,price NUMERIC,status SMALLINT DEFAULT 0,created_at TIMESTAMP DEFAULT now,geo_point POINT
);

字符串 & 日期常用函数

-- 拼接
SELECT concat AS full_name FROM users;-- 字符截取
SELECT substring AS brief FROM articles;-- 日期加减
SELECT now + INTERVAL '30 days' AS next_month;-- 日期分组
SELECT date_trunc AS month_start,count AS order_count
FROM orders GROUP BY month_start;

3️⃣ 窗口函数 & LATERAL JOIN：一次写好，不再写子查询狂魔

痛点："排行榜"、"每行取前 N 条"往往只能用子查询或多层嵌套。

排名类窗口函数

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;

从偏移取值来看，环比分析一行搞定

SELECT month,revenue,LAG OVER AS prev_month。revenue - LAG OVER AS diff,ROUND)::numeric /
NULLIF,0)::numeric * 100,1) AS growth_pct
FROM monthly_revenue;

从聚合窗口示例来看，累计求和、占比计算等直观可读

-- 累计销售额
SELECT order_date。SUM OVER AS cumulative_sales
FROM daily_sales;-- 薪资占比
SELECT employee_name,salary / SUM OVER ::numeric * 100
AS dept_pct
FROM employees;

LATERAL JOIN：对每行执行子查询的工具，解决“分组取 Top N”难题

LATERAL JOIN 与关联子查询对比：

• LATERAL 可以返回多列多行；关联子查询只能返回单值，
• LATERAL 更直观，可直接在 SELECT 或 WHERE 外使用。
• LATERAL 性能受子查询成本影响，大表需索引支持。

-- 每个部门薪资最高的前 5 名员工
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 5
) e ON true;不过，

4️⃣ CTE 与递归查询：让复杂逻辑变得层次清晰可维护

痛点："深度嵌套子查询"让团队难以快速定位问题。说起来，

标准 CTE 示例：拆解业务逻辑一步步拆解清晰可读性提高显著。话说回来，

WITH active_users AS (
SELECT id,username
FROM users
WHERE status = 'active' AND last_login> now - INTERVAL '30 days'
),user_orders AS (
SELECT u.id AS user_id,u.username。COUNT::int AS order_count,SUM::numeric 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：树形结构快速遍历无须自建循环代码。

WITH RECURSIVE dept_tree
AS (
SELECT id,name,parent_id,'/'||name path
FROM departments WHERE parent_id IS NULL
UNION ALL
SELECT d.id。d.name,d.parent_id,t.path||'/'||d.name
FROM departments d JOIN dept_tree t ON d.parent_id = t.id)
SELECT * FROM dept_tree ORDER BY path;

5️⃣ JSONB 数据处理：灵活存储不牺牲性能的合适方案

痛点："业务字段频繁变更导致频繁改表"，传统模式维护成本高；需要高效检索嵌套字段，

基础 CRUD 操作示例 + GIN 索引加速检索

INSERT INTO user_profiles
VALUES;

CREATE INDEX idxprofilegin ON user_profiles USING GIN;

SELECT jsonbdata->'name' AS name,jsonbdata->'address'->'city' as city FROM userprofiles WHERE jsonbdata @> '{"address":{"city":"北京"}}';UPDATE userprofiles SET jsonbdata = jsonbset WHERE userid=1;DELETE FROM userprofiles WHERE jsonbdata?| ARRAY，

聚合 JSONB

sql -- 将部门员工列表聚成 JSON 数组返回给 API 一行完成拼装： SELECT department_id,jsonb_agg) FILTER as employees_json FROM employees GROUP BY department_id;

关系型 ↔️ JSON 转换

sql -- 把产品列表转为 JSON 数组返回给前端： SELECT jsonb_agg) FROM ( SELECT id。name,price FROM products LIMIT 10 ) t;

-- 把订单项数组展开成多行供报表使用： SELECT item->'productname' as productname。 ::int as qty,::numeric as price FROM orders CROSS JOIN LATERAL jsonbarrayelements;

动态表单存储实践

sql CREATE TABLE workorders( id BIGSERIAL PRIMARY KEY,orderno VARCHAR。ordertype VARCHAR,submitterid BIGINT NOT NULL,status SMALLINT DEFAULT 0,extrafields JSONB DEFAULT '{}'::jsonb,createdat TIMESTAMP DEFAULT now );

INSERT INTO workorders VALUES;INSERT INTO workorders VALUES;

-- 查询 IT 报修且紧急的工单： SELECT orderno,extrafields ->'device' as device。extrafields ->'fault' as faultdesc FROM workorders WHERE ordertype='ITREPAIR' AND extrafields @> '{"urgency":"high"}';

6️⃣ 多数据库兼容模式 — 一键切换减少 工作量

痛点："从 Oracle/Mysql 到 KingbaseES 时同名函数/语法差异导致大量手工 "

• Oracle 模式：  DUAL 表、NVL、DECODE 等直接可用；说起来，但高级分析功能如 CONNECT BY 不支持，需要手动重写。

MySQL 模式：  IFNULL、LIMIT、GROUP_CONCAT 等语法已被支持；但某些 MySQL 特有的 AUTO_INCREMENT 和 ENUM 在 PostgreSQL 风格下需要转换为序列+CHECK 或 TEXT + ENUM 类型。'

默认模式：   推荐新项目使用，以免后期迁移成本。'

如何启用兼容模式：  通过配置参数 db_compatibility 或创建数据库时指定；但不可在线切换，需要重新创建或 ALTER DATABASE 后重新启动。'

示例代码：  SHOW db_compatibility; 查看当前；CREATE DATABASE myapp_db WITH db_compatibility = 'oracle';老实说， 创建新 DB 并启用 Oracle 模式。

#7️⃣ 实际方法小抄 — 一句话搞定常见需求

技巧名称 & 用途
Lateral Join 示例: sql select d.name。e.employee_name,e.salary from departments d join lateral e on true;	MATERIALIZED VIEW 简易刷新策略: sql create materialized view mv_stats as select ...;refresh materialized view concurrently mv_stats;
CROSS APPLY 子句模拟: sql select u.*,o.* from users u left join lateral o on true;	PIVOT / UNPIVOT 类似功能 via CASE/FILTER: sql declare sales table;select region,sum) online。sum) offline from sales group by region;
SIMPLE UPSERT / INSERT ... ON CONFLICT: sql insert into counters values on conflict do update set count=excluded.count+10;	NESTED QUERY 调整建议: - 尽量使用 CTE 替代深层嵌套。- 对大数据集做分区裁剪后再做聚合。bold>.

#8️⃣ 大数据量场景下的物化视图与分区策略

技术方法概览
MATERIALIZED VIEW ： sql create materialized view mv_daily_stats as xselect date_trunc day。sum,count in orders where status in group by day;rw: norefresh materialized view mv_daily_stats;其实，rw: norefresh materialized view concurrently mv_daily_stats;cw: deploy unique index on mv_daily_stats;cw: freq refresh every hour via cron.	PARTITIONED TABLE ： sql create table orders。user bigint,status smallint,total numeric,timestamp created_at timestamp default now partition by range;不过，cw: define partitions per month. 再看cw。build create_monthly_partition;cw: yield partition pruning. best practice : primary key includes partition key. warn : cross‑partition aggregates may lose performance.\`\`\`

提示 ：如果你在迁移阶段遇到 “无法识别 DATE_TRUNC” 的错误，一般是因为把日期字段包裹了不必要的 date_trunc 函数导致调整器无法裁剪分区。

---

---