SQL 数据分析核心实战指南

核心定位：以企业生产实际案例为基础，覆盖十四大模块，兼顾深度与广度
数据库范围：MySQL 8.0+ / PostgreSQL 14+ / Oracle 19c / SQL Server 2022

---

📖 目录

第一部分：基础入门

1. 模块一：基础概念

2. 模块二：数据检索与过滤

3. 模块三：高级检索技巧

4. 模块四：函数应用

5. 模块五：汇总与分组

6. 模块六：子查询与联结

7. 模块七：组合查询

第二部分：企业级进阶

8. 模块八：CTE 与复杂查询模式

9. 模块九：窗口函数深度进阶

10. 模块十：数据仓库建模

11. 模块十一：性能调优体系

12. 模块十二：跨库兼容与迁移策略

13. 模块十三：SQL 安全与注入防护

14. 模块十四：数据清洗与 ETL 模式

第三部分：实战汇总

15. 企业级 SQL 开发规范与避坑指南

16. 复杂场景实战案例

---

·

第一部分：基础入门

模块一：基础概念

在动手写 SQL 之前，你必须理解关系型数据库的结构化词汇。这些概念是每一条 SQL 语句的基石。

1.1 核心概念：严谨定义 vs 生产实际

概念	严谨定义	生产环境实际含义
DBMS（数据库管理系统）	数据库软件，负责数据的存储、查询、管理与安全控制	企业里常说的"数据库"大多指 DBMS，如 MySQL、PostgreSQL、Oracle、SQL Server
数据库（Database）	有组织数据的物理容器，通常是一个或一组文件	按业务域隔离的数据集，如电商业务的订单库、用户库，实现数据和权限隔离
Schema（模式）	描述数据库/表的结构、约束、关系的元数据集合	MySQL：Schema ≈ 数据库，二者几乎等价；PostgreSQL/Oracle：数据库下的二级命名空间，用于权限隔离和表分类
表（Table）	同类型结构化数据的清单，是数据存储的基本单元	对应一个业务实体，如用户表、订单表；全局唯一由「库名 + 表名」保证

🔧 生产视角：理解你所在组织的 RDBMS 版本至关重要。窗口函数、CTE、JSON 操作等高级特性在 MySQL 5.7 vs 8.0、PostgreSQL 12 vs 14 之间差异巨大。

🔧 企业选型参考：互联网业务首选 MySQL；数据分析、复杂查询首选 PostgreSQL；传统金融、大型企业多用 Oracle；微软技术栈常用 SQL Server。

1.2 SQL 语法基础

• 语句以分号 ; 结尾（部分 DBMS 支持省略，但生产中建议统一书写）

• SQL 关键字不区分大小写，生产规范统一关键字大写，提升可读性

• 表名、列名的大小写敏感性取决于 DBMS 和操作系统（Linux 下 MySQL 表名区分大小写），生产统一用小写 + 下划线命名

• 注释：单行用 -- 注释内容，多行用 /* 注释内容 */

1.3 SQL 执行顺序（核心底层）

很多语法困惑都源于不了解执行顺序。

书写顺序：SELECT → FROM → JOIN → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT

实际执行顺序：

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

1. FROM / JOIN：加载关联表，生成笛卡尔积

2. WHERE：过滤行数据（分组前过滤）

3. GROUP BY：对过滤后的数据分组

4. HAVING：过滤分组结果

5. SELECT：计算列、表达式、别名

6. DISTINCT：去重

7. ORDER BY：排序

8. LIMIT：截断结果集

"先找数据(FROM)，再筛行(WHERE)，再分组(GROUP BY)，
 再筛组(HAVING)，再算列(SELECT)，再排序(ORDER BY)，最后截断(LIMIT)"

-- ❌ 错误：WHERE 里不能用 SELECT 别名
SELECT price * 0.9 AS discounted_price
FROM products
WHERE discounted_price > 10;
-- 报错：Unknown column 'discounted_price'

-- ✅ 正确：WHERE 里用原始表达式
SELECT price * 0.9 AS discounted_price
FROM products
WHERE price * 0.9 > 10;

-- ✅ 正确：ORDER BY 里可以用别名（因为它在 SELECT 之后执行）
SELECT price * 0.9 AS discounted_price
FROM products
ORDER BY discounted_price DESC;

核心结论：
WHERE / GROUP BY 执行在 SELECT 之前，所以 WHERE 里不能用 SELECT 定义的别名；
ORDER BY 执行在 SELECT 之后，所以可以用别名。

1.4 表的核心组成

列（Column）与数据类型

列是表的字段，代表实体的一个属性。每一列必须定义数据类型。

🔧 生产核心原则：数据类型选型宁小勿大，够用即可。

字段	错误写法	正确写法	原因
年龄	INT（4字节）	TINYINT（1字节）	0~127 足够，省 75% 空间
手机号	TEXT（无限长）	VARCHAR(20)	手机号固定 11 位，加国际码 20 位足够
金额	FLOAT（精度丢失）	DECIMAL(10,2)	浮点数计算有精度误差，财务数据必用 DECIMAL
邮箱	VARCHAR(100)	VARCHAR(255)	RFC 标准邮箱最长 254 字符

⚠️ 注意：SQL 不兼容的核心来源之一就是数据类型命名差异。比如同样的变长字符串，MySQL 叫 VARCHAR，Oracle 叫 VARCHAR2。

行（Row）

表中的一条记录，对应一个业务实体实例。表的数据按行存储。

主键（Primary Key）

主键是一列或多列的组合，用于唯一标识表中的每一行，是表的灵魂。

主键的 4 个核心特性（SQL 标准）：

特性	说明
唯一性	任意两行主键值不能重复
非空性	主键列不允许为 NULL
稳定性	语法上允许修改，但 生产环境绝对禁止修改主键，会引发关联表数据不一致、索引重建等严重问题
不可复用	语法允许复用，但生产中禁止，避免历史数据关联混乱

企业主键选型：

类型	说明	适用场景
技术主键（代理主键）	无业务含义的自增 ID、雪花 ID	✅ 生产首选，不受业务规则变更影响
业务主键	用业务字段做主键（如身份证号、订单号）	仅在业务字段绝对唯一且永久不变时使用
联合主键	多列组合做主键	多用于多对多关联表（如订单商品关联表用 order_id + prod_id），不建议在主表使用

---

模块二：数据检索与过滤

这是数据分析的日常基本功。重点不在于"怎么写"，而在于"怎么写才对、怎么写才快"。

2.1 SELECT 基础检索

-- 检索单列
SELECT prod_name FROM products;

-- 检索多列
SELECT prod_id, prod_name, price FROM products;

-- 检索所有列
SELECT * FROM products;

🔴 生产红线：严禁 SELECT *

在企业中，SELECT * 是大忌。

危害	说明
I/O 浪费	数据库需要读取更多数据页，增加磁盘 I/O
索引失效	如果查询的列不在索引中（覆盖索引），数据库必须回表查询，效率极低
网络开销	传输不必要的数据浪费带宽
耦合风险	表结构变更时，依赖 SELECT * 的代码会突然报错

-- ❌ 错误写法（学习常见，生产禁止）
SELECT * FROM orders;

-- ✅ 优化后写法（只查业务需要的字段）
SELECT order_id, user_id, order_amount, order_time, status
FROM orders
WHERE order_time >= '2024-01-01' AND order_time < '2024-02-01'
  AND status = 1;

🔧 企业规范：永远显式写出需要的列，只查业务需要的字段。

2.2 DISTINCT 去重

-- 单列去重
SELECT DISTINCT category FROM products;

-- 多列去重：多列组合唯一才会去重
SELECT DISTINCT category, price FROM products;

🔧 生产注意事项：

• DISTINCT 是对所有查询列的组合去重，不能只对部分列生效

• 大数据量下去重性能开销大，会触发全表扫描和排序；千万级表慎用

• 业务上能通过 WHERE 条件过滤的，优先用 WHERE，不要依赖 DISTINCT 去重

2.3 分页查询

不同 DBMS 分页语法不同：

DBMS	分页语法	说明
MySQL / PostgreSQL / SQLite	LIMIT n OFFSET m	跳过 m 行，返回 n 行
Oracle	WHERE ROWNUM <= n	行号过滤
SQL Server	OFFSET n ROWS FETCH NEXT n ROWS ONLY	后者支持偏移

-- PostgreSQL / MySQL / SQLite
SELECT prod_name FROM products LIMIT 5 OFFSET 5;  -- 第6~10行

-- MySQL 简写（注意顺序与含义）
SELECT prod_name FROM products LIMIT 3, 2; -- 跳过前3行，取2行

-- 简写：只写一个数字，表示行数，偏移量为0
SELECT ... LIMIT row_count;

🔴 深分页陷阱：LIMIT 100000, 10

-- ❌ 慢查询：偏移量 10 万
SELECT * FROM orders ORDER BY order_id LIMIT 100000, 10;

原理：数据库会扫描前 100010 行数据，然后丢弃前 10 万行，只取最后 10 行。随着偏移量增大，查询性能指数级下降。

企业优化方案：书签法（主键锚定分页）

-- ✅ 优化后写法：利用主键索引，O(log n) vs O(n)
-- 假设上一页最后一条数据的 order_id = 100000
SELECT * FROM orders
WHERE order_id > 100000
ORDER BY order_id
LIMIT 10;

2.4 WHERE 条件体系

WHERE 是 SQL 中最常用的过滤子句，在分组前对行数据进行筛选。

2.4.1 基础比较操作符

操作符	含义	生产注意事项
=	等于	不能匹配 NULL 值
!= / <>	不等于	同样不匹配 NULL；WHERE status != 1 不会返回 status 为 NULL 的行
> / < / >= / <=	大小比较	数值、日期、字符串均可比较
IS NULL / IS NOT NULL	空值判断	判断 NULL 的唯一正确方式

⚠️ 高频踩坑：NULL 参与任何比较运算结果都是 UNKNOWN，都会被 WHERE 过滤。

-- ❌ 漏数据：status 为 NULL 的行不会被返回
SELECT user_id FROM users WHERE status != 1;

-- ✅ 正确写法：补充 NULL 判断
SELECT user_id FROM users WHERE status != 1 OR status IS NULL;

2.4.2 范围查询：BETWEEN

-- 查询价格在 50~100 之间的商品（闭区间，包含两端）
SELECT prod_name, price FROM products
WHERE price BETWEEN 50 AND 100;

⚠️ 日期类型巨坑：WHERE order_time BETWEEN '2024-01-01' AND '2024-01-31' 会漏掉 1 月 31 日 0 点之后的所有带时分秒的订单。

-- ❌ 错误：日期时间类型漏数据
WHERE order_time BETWEEN '2024-01-01' AND '2024-01-31'

-- ✅ 正确：左闭右开原则（推荐写法）
WHERE order_time >= '2024-01-01' AND order_time < '2024-02-01'

2.4.3 集合查询：IN

-- 查询分类为"手机"和"电脑"的商品
SELECT prod_name FROM products
WHERE category IN ('手机', '电脑');

-- 查询供应商为 'DLL01' 或 'BRS01' 的商品
SELECT prod_name FROM products
WHERE vend_id IN ('DLL01', 'BRS01');

对比项	IN	OR
语法	更简洁直观，尤其选项多时	选项多时代码冗长
性能	多数 DBMS 中执行效率更高	多个 OR 可能无法走索引
子查询	支持包含子查询	不支持
注意事项	IN 列表长度建议不超过 1000，过大时性能下降	—

🔧 生产注意：IN 列表中出现 NULL 时，不影响匹配逻辑。

2.4.4 逻辑运算符：AND / OR / NOT

优先级：NOT > AND > OR

-- ✅ 正确写法：用括号明确优先级，不要依赖默认优先级
SELECT prod_name, prod_price
FROM products
WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01')
  AND prod_price >= 5;

2.4.5 模糊匹配：LIKE 通配符

通配符	含义	注意事项
%	匹配 0~N 个字符	不能匹配 NULL 值
_	只匹配一个字符	—
[ ]	字符集匹配	仅 SQL Server 支持

-- 以"苹果"开头的产品
SELECT * FROM products WHERE prod_name LIKE '苹果%';

-- 包含"手机"的产品
SELECT * FROM products WHERE prod_name LIKE '%手机%';

-- 第二个字符是"e"的产品
SELECT * FROM products WHERE prod_name LIKE '_e%';

🔴 生产性能红线：LIKE

写法	能否走索引	风险
LIKE '前缀%'	✅ 可以（前缀匹配）	安全
LIKE '%中间'	❌ 全表扫描	⚠️ 百万级表禁止
LIKE '%末尾%'	❌ 全表扫描	🔴 千万级表绝对禁止

-- ❌ 禁止：左模糊匹配（% xxx），触发全表扫描
SELECT * FROM users WHERE name LIKE '%Smith';

-- ✅ 正确：前缀匹配，可走索引
SELECT * FROM users WHERE name LIKE 'Smith%';

-- ✅ 正确：全文模糊搜索用搜索引擎，不要用 LIKE
-- SELECT * FROM products WHERE prod_name LIKE '%手机%';
-- → 改用 Elasticsearch / 全文索引

🔧 业务需要全文模糊搜索时：不要用 LIKE，应使用全文索引或 Elasticsearch 等搜索引擎。

2.5 搜索参数原则（SARGable）

SARGable（Search Argument）原则：WHERE 条件必须能利用索引。

类型	示例	索引是否生效
范围查询	WHERE order_date >= '2020-01-01'	✅ 生效
直接比较	WHERE age = 20	✅ 生效
前缀模糊	WHERE name LIKE 'Smith%'	✅ 生效
列上用函数	WHERE YEAR(order_date) = 2020	❌ 失效
列上运算	WHERE age + 10 = 30	❌ 失效
左模糊	WHERE name LIKE '%Smith'	❌ 失效

2.6 NULL 的隐形陷阱

NULL 不是"空字符串"，也不是"0"，它是一个未知/不存在的状态。SQL 使用三值逻辑：TRUE、FALSE、UNKNOWN。NULL 参与任何比较都会变成 UNKNOWN。

2.6.1 NULL 的基本规则

表达式	结果	说明
NULL = NULL	UNKNOWN	NULL 不等于 NULL！
NULL = 0	UNKNOWN	NULL 不等于 0
NULL = ''	UNKNOWN	NULL 不等于空字符串
NULL + 1	NULL	任何运算结果都是 NULL
NULL * 100	NULL	同上
COUNT(*)	计数	不忽略 NULL
COUNT(列名)	计数	忽略 NULL

2.6.2 致命陷阱：NOT IN 中的 NULL

场景：找出没有下过单的用户

users 表：

user_id	user_name
U001	张三
U002	李四
U003	王五

orders 表（注意：有个 user_id 为 NULL 的记录）：

order_id	user_id	amount
O001	U001	100
O002	NULL	200

❌ 错误写法：NOT IN

SELECT * FROM users
WHERE user_id NOT IN (SELECT user_id FROM orders);

逐步推演：

1. 子查询 (SELECT user_id FROM orders) 返回：U001, NULL

2. NOT IN 等价于：user_id != 'U001' AND user_id != NULL

3. user_id != NULL 的结果是 UNKNOWN（不是 TRUE，也不是 FALSE！）

4. TRUE AND UNKNOWN = UNKNOWN

5. WHERE 只接受 TRUE，UNKNOWN 会被过滤掉

6. 结果：返回 0 行！（所有用户都被过滤了）

✅ 正确写法：NOT EXISTS

SELECT * FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.user_id
);

逐步推演：

• U001：在 orders 中找到匹配 → NOT EXISTS = FALSE → 过滤

• U002：在 orders 中找不到匹配 → NOT EXISTS = TRUE → 保留

• U003：在 orders 中找不到匹配 → NOT EXISTS = TRUE → 保留

结果：U002 和 U003（正确！）

2.6.3 WHERE 中判断 NULL 的正确方式

-- ❌ 错误：NULL 参与比较的结果是 UNKNOWN，会被过滤
SELECT * FROM users WHERE status = NULL;
SELECT * FROM users WHERE status != 1;  -- status 为 NULL 的行也会被过滤

-- ✅ 正确：使用 IS NULL / IS NOT NULL
SELECT * FROM users WHERE status IS NULL;
SELECT * FROM users WHERE status != 1 OR status IS NULL;

2.6.4 NULL 处理工具函数

函数	作用	示例
COALESCE(a, b)	返回第一个非 NULL 值	COALESCE(discount, 0) → 如果 discount 为 NULL，用 0
IFNULL(a, b)	MySQL 专用，同 COALESCE	IFNULL(phone, '未填写')
NULLIF(a, b)	a=b 时返回 NULL，否则返回 a	NULLIF(count, 0) → 避免除以 0

---

模块三：高级检索技巧

3.1 ORDER BY 排序

• 位置：必须是 SELECT 语句的最后一条子句（仅 LIMIT 之前）

• 方向：ASC（升序，默认），DESC（降序）

• 多列排序：ORDER BY col1, col2 DESC（仅 col2 降序，col1 仍为升序）

• 按位置排序：ORDER BY 2, 3（按 SELECT 列表中的第 2、3 列排序）→ 不推荐生产使用

-- 单列排序（默认升序 ASC）
SELECT prod_name, price FROM products ORDER BY price DESC;

-- 多列排序：先按价格降序，价格相同按名称升序
SELECT prod_id, price, prod_name
FROM products
ORDER BY price DESC, prod_name ASC;

🔧 排序与索引

场景	说明	性能
按索引列排序	数据库可直接利用索引顺序，无需额外排序	⚡ 极快
按非索引列排序	触发文件排序（Filesort）	🐌 大表下性能极差

🔧 NULL 值排序规则

不同 DBMS 默认行为不同：

DBMS	升序 NULL 位置	降序 NULL 位置
MySQL	最前	最后
PostgreSQL	最前	最后
SQL Server	最后	最前

生产中如果对 NULL 位置有要求：

-- 手动控制 NULL 排在最后（升序）
SELECT name, age FROM users
ORDER BY CASE WHEN age IS NULL THEN 1 ELSE 0 END, age ASC;

3.2 书签法分页优化（回顾深化）

书签法的核心思想：不依赖偏移量，而是依赖上一页最后一条记录的主键值。

-- 第一页：取前 20 条
SELECT * FROM orders
ORDER BY order_id
LIMIT 20;
-- 返回结果，记录最后一条 order_id = 10234

-- 第二页：从 10234 之后取 20 条
SELECT * FROM orders
WHERE order_id > 10234
ORDER BY order_id
LIMIT 20;

-- 第三页：从上一页最后一条开始
SELECT * FROM orders
WHERE order_id > 10254   -- 第二页最后一条
ORDER BY order_id
LIMIT 20;

🔧 优势：无论翻到第几页，查询时间都是稳定的 O(log n)，不随偏移量增加而变慢。前端需要记录上一页的最大 ID 作为"书签"传给后端。

---

模块四：函数应用

4.1 字符串拼接与 NULL 处理

不同 DBMS 语法差异巨大，这是导致 SQL 不兼容的主因之一。

DBMS	拼接操作符	函数（推荐）	NULL 处理
PostgreSQL	||	concat()、concat_ws()	|| 遇 NULL 结果为 NULL；concat 忽略 NULL
MySQL	不支持 ||	concat()、concat_ws()	concat() 遇 NULL 结果为 NULL；concat_ws() 忽略 NULL
SQL Server	+	concat()	+ 遇 NULL 结果为 NULL；concat 忽略 NULL
Oracle	||	concat()（只两个参数）	|| 和 concat 均忽略 NULL
SQLite	||	无	|| 遇 NULL 结果为 NULL

案例：生成用户登录名

需求：登录名 = 联系人前 2 个字符大写 + 城市前 3 个字符大写

-- ✅ MySQL
SELECT user_id, user_name,
       CONCAT(UPPER(LEFT(contact, 2)), UPPER(LEFT(city, 3))) AS user_login
FROM users;

-- ✅ PostgreSQL / Oracle
SELECT user_id, user_name,
       UPPER(SUBSTR(contact, 1, 2)) || UPPER(SUBSTR(city, 1, 3)) AS user_login
FROM users;

🔧 空值处理通用方案：COALESCE

-- 无论哪个数据库，都返回第一个非 NULL 的值
SELECT CONCAT(name, ' - ', COALESCE(job_title, '未分配职位')) AS display_name
FROM employees;

-- 生产最佳实践：优先使用 concat_ws
-- 自动跳过 NULL 值，分隔符只出现在有效值之间
SELECT CONCAT_WS(' - ', name, job_title) AS display_name
FROM employees;

函数	NULL 行为	推荐度
CONCAT(a, b)	任一参数为 NULL，结果为 NULL	⚠️ 需配合 COALESCE 使用
CONCAT_WS(',', a, b)	自动跳过 NULL 值	✅ 首选
COALESCE(a, b, c)	返回第一个非 NULL 值	✅ ANSI 标准，跨 DB 兼容
|| 操作符	各 DBMS 行为不同	⚠️ 不推荐，兼容性差

4.2 算术计算与类型转换

基础算术运算

-- 计算订单明细的商品总价
SELECT order_id, prod_id, quantity, unit_price,
       quantity * unit_price AS item_total
FROM order_items;

🔴 类型转换：隐式转换陷阱

-- ❌ 错误：隐式类型转换导致索引失效
-- 假设 phone 字段是 VARCHAR(20)
SELECT * FROM users WHERE phone = 13800000000;  -- 用数字查字符串，全表扫描

-- ✅ 正确：两边类型保持一致
SELECT * FROM users WHERE phone = '13800000000';  -- 用字符串查字符串，走索引

🔧 生产规范：比较两边的类型必须保持一致，避免隐式转换。标准 SQL 写法：CAST(表达式 AS 目标类型)。

4.3 高频文本处理函数

生产中最常用于数据清洗、格式统一：

函数	作用	场景
UPPER() / LOWER()	转大写 / 小写	统一大小写匹配
TRIM() / LTRIM() / RTRIM()	去除两端 / 左 / 右空格	清洗导入的脏数据
SUBSTR() / SUBSTRING()	提取子串	提取身份证地区、手机号段
LENGTH()	字符串长度	校验数据格式

-- 清洗脏数据：去除前后空格，统一大写
SELECT TRIM(UPPER(name)) FROM users;

-- 提取手机号前三位（归属地）
SELECT SUBSTR(phone, 1, 3) AS area_code FROM users;

-- 校验邮箱格式长度
SELECT user_id, email FROM users WHERE LENGTH(email) > 255;

⚠️ 注意：RTRIM(str, chars) 是按字符集合逐个删除，不是删除整个子串。比如 RTRIM('helloxxx', 'x') 得到 hello，但 RTRIM('helloabc', 'cba') 也会得到 hello。

4.4 日期时间函数（企业统计核心）

不同 DBMS 日期函数差异最大：

提取日期部分

DBMS	提取年份写法
MySQL	YEAR(order_time) / EXTRACT(YEAR FROM order_time)
PostgreSQL	EXTRACT(YEAR FROM order_time) / DATE_PART('year', order_time)
Oracle	EXTRACT(YEAR FROM order_time)
SQL Server	DATEPART(yy, order_time)
SQLite	strftime('%Y', order_time)

-- ❌ 索引失效：对列使用函数
SELECT order_id FROM orders WHERE YEAR(order_time) = 2024;

-- ✅ 索引生效：范围查询（千万级表推荐）
SELECT order_id FROM orders
WHERE order_time >= '2024-01-01' AND order_time < '2025-01-01';

时区处理

-- 场景：跨国系统，存储 UTC 时间，查询时转为北京时间

-- MySQL
SELECT CONVERT_TZ(order_time, '+00:00', '+08:00') AS beijing_time
FROM orders;

-- PostgreSQL
SELECT order_time AT TIME ZONE 'Asia/Shanghai' AS beijing_time
FROM orders;

计算工龄 / 时长

DBMS	函数
MySQL	DATEDIFF(end_date, start_date)
PostgreSQL	AGE(end_date, start_date)
Oracle	MONTHS_BETWEEN(end_date, start_date)

4.5 数值处理函数

常用：ABS() 绝对值、ROUND() 四舍五入、FLOOR() 向下取整、CEIL() 向上取整，多用于报表数据格式化。

4.6 JSON 处理函数（JSON_EXTRACT + JSON_TABLE）

JSON 类型是现代关系型数据库的核心扩展能力，常用于存储业务扩展字段、动态属性、数组型明细数据。MySQL 8.0+、PostgreSQL 14+、Oracle 19c 均原生支持 JSON 操作，其中 JSON_EXTRACT（字段提取） 和 JSON_TABLE（数组拆表） 是数据分析最高频使用的两个核心函数。

---

4.6.1 前置说明：各数据库 JSON 类型差异

数据库	JSON 类型	生产推荐类型	核心特点
MySQL 8.0+	JSON	JSON	原生二进制格式，自动校验 JSON 格式，支持生成列索引
PostgreSQL 14+	json / jsonb	jsonb	jsonb 为二进制解析后存储，支持 GIN 索引，查询与复杂操作性能远高于 json
Oracle 19c+	JSON（底层为 BLOB 或 CLOB）	JSON	支持 IS JSON 约束，12c 后原生支持标准 JSON 函数
SQL Server 2022	JSON (nvarchar)	—	无原生 JSON 类型，通过函数处理 nvarchar 中的 JSON，支持索引视图和计算列索引

生产选型建议：PostgreSQL 优先使用 jsonb，复杂查询和索引支持更完善；MySQL 使用原生 JSON 类型，禁止用 TEXT/VARCHAR 存储 JSON。

---

4.6.2 JSON_EXTRACT：字段提取函数

JSON_EXTRACT 用于从 JSON 结构中按路径提取指定字段，是日常数据清洗、字段扩展查询的基础函数。各数据库语法虽不同，但思想一致。

1. MySQL 版本

语法与操作符

-- 完整语法
JSON_EXTRACT(json_doc, path[, path] ...)

-- 简写操作符（生产最常用）
col -> '$.path'     -- 等价于 JSON_EXTRACT(col, '$.path')，返回 JSON 类型（字符串带双引号）
col ->> '$.path'    -- 等价于 JSON_UNQUOTE(JSON_EXTRACT(col, '$.path'))，返回纯文本/原生类型

路径表达式规则

• $ ：代表 JSON 根节点

• $.key ：取根节点下的 key 字段

• $.key.subkey ：取嵌套子字段

• $.array[0] ：取数组第 1 个元素（下标从 0 开始）

• $.array[*] ：匹配数组所有元素（用于 JSON_TABLE，见后）

案例 1：基础单层字段提取

原始数据（users 表，ext_info 为 JSON 类型）

user_id	user_name	ext_info
1	张三
2	李四
3	王五

SELECT 
  user_id,
  user_name,
  ext_info -> '$.age' AS age_json,       -- 返回 JSON 数字类型
  ext_info ->> '$.city' AS city_text,    -- 返回纯文本字符串
  ext_info ->> '$.level' AS user_level,
  ext_info ->> '$.phone' AS phone
FROM users;

执行结果

user_id	user_name	age_json	city_text	user_level	phone
1	张三	28	北京	VIP	13800000001
2	李四	32	上海	普通	NULL
3	王五	25	广州	NULL	NULL

逐点解析

1. -> 返回 JSON 原生类型，数字不带引号，字符串会保留双引号；->> 返回 SQL 原生字符串/数值，是数据分析首选写法。

2. 键不存在时（如王五没有 level 字段），静默返回 NULL，不会触发 SQL 报错。

3. JSON 内部的 null 值，提取后对应 SQL 的 NULL。

案例 2：嵌套 JSON + 数组提取

原始数据（orders 表，order_detail 为 JSON 类型）

order_id	user_id	order_detail
O001	1	{"order_no": "20240701001", "items": [{"prod_id": 101, "prod_name": "手机", "price": 3999}, {"prod_id": 102, "prod_name": "耳机", "price": 299}], "total_amount": 4298}
O002	2	{"order_no": "20240701002", "items": [{"prod_id": 201, "prod_name": "笔记本", "price": 5999}], "total_amount": 5999}

SELECT 
  order_id,
  order_detail ->> '$.order_no' AS order_no,
  order_detail ->> '$.items[0].prod_name' AS first_prod,
  JSON_LENGTH(order_detail -> '$.items') AS item_count
FROM orders;

执行结果

order_id	order_no	first_prod	item_count
O001	20240701001	手机	2
O002	20240701002	笔记本	1

• $.items[0].prod_name 先定位到 items 数组第 0 个元素，再取其中的 prod_name 字段。
• JSON_LENGTH() 用于获取 JSON 数组的长度，是统计商品数量、标签数量的高频函数。

2. PostgreSQL 版本

PostgreSQL 通过操作符实现等价功能，语法更简洁：

-- 返回 json/jsonb 类型
col -> 'key'          -- 取子字段
col -> 0              -- 按下标取数组元素
col #> '{key1,key2}'  -- 按路径数组取嵌套字段

-- 返回 text 纯文本类型（生产最常用）
col ->> 'key'         -- 取子字段返回文本
col #>> '{key1,key2}' -- 按路径数组取嵌套字段返回文本

-- 等价的函数形式
jsonb_extract_path_text(col, 'key1', 'key2')

对应案例 1（PostgreSQL 写法）

SELECT 
  user_id,
  user_name,
  ext_info ->> 'age' AS age_text,
  ext_info ->> 'city' AS city_text,
  ext_info ->> 'level' AS user_level
FROM users;

执行结果与 MySQL ->> 完全一致。

3. Oracle / SQL Server 写法概览

操作	Oracle	SQL Server
提取标量值	JSON_VALUE(col, '$.path')	JSON_VALUE(col, '$.path')
提取对象/数组	JSON_QUERY(col, '$.path')	JSON_QUERY(col, '$.path')

Oracle 12c+ 也支持类似 col.fieldName 的点记法，但标准函数更稳健。

4. 常见踩坑与生产规范

1. 索引失效红线：WHERE ext_info->>'$.city' = '北京' 在千万级表上会触发全表扫描，违反 SARGable 原则。

   • 优化方案：MySQL 对高频查询字段建立生成列 + 普通索引；PostgreSQL 对 jsonb 建立 GIN 索引或表达式索引。

2. 类型转换陷阱：-> 返回 JSON 类型，直接与整数比较可能触发隐式转换，推荐用 ->> 提取后再显式 CAST。

3. 路径不存在静默返回 NULL：不会报错，容易漏统计数据，建议配合 COALESCE 设置默认值。

---

4.6.3 JSON_TABLE：JSON 数组转关系表（核心进阶）

JSON_TABLE 是 SQL:2016 标准函数，支持将 JSON 数组（对象数组）拆解为标准的行和列，像普通表一样进行 JOIN、分组、过滤。这是数据分析中处理 JSON 明细数据的核心工具，替代了传统需要程序解析的繁琐流程。

支持版本：MySQL 8.0.4+、PostgreSQL 14+、Oracle 12c+、SQL Server 2016+

1. 核心语法（以 MySQL 为基准，PG 类似）

JSON_TABLE(
  json_expression,    -- JSON 数据源：列、变量或 JSON 字符串
  path_expression     -- 数组路径，如 '$.items[*]'，遍历数组每个元素
  COLUMNS (           -- 列定义：将每个数组元素映射为表的列
    列名 类型 [FOR ORDINALITY]  -- 序号列，自动生成 1,2,3...
    [PATH '$.字段路径']         -- 对应 JSON 内的字段路径
    [DEFAULT '默认值' ON EMPTY] -- 字段不存在时的默认值
    [NULL ON ERROR]             -- 解析错误时返回 NULL 兜底
  )
) AS 表别名

2. 案例 1：一维对象数组拆解（最高频生产场景）

需求：将订单表中 order_detail.items 里的每个商品拆分为独立行，方便后续统计商品销量、销售额。

原始数据（orders 表）

order_id	user_id	order_detail
O001	1	{"items": [{"prod_id": 101, "prod_name": "手机", "price": 3999, "quantity": 1}, {"prod_id": 102, "prod_name": "耳机", "price": 299, "quantity": 2}]}
O002	2	{"items": [{"prod_id": 201, "prod_name": "笔记本", "price": 5999, "quantity": 1}]}
O003	3

SQL 查询（MySQL 写法）

SELECT 
  o.order_id,
  o.user_id,
  jt.prod_id,
  jt.prod_name,
  jt.price,
  jt.quantity
FROM orders o,
JSON_TABLE(
  o.order_detail,
  '$.items[*]'
  COLUMNS (
    prod_id INT PATH '$.prod_id',
    prod_name VARCHAR(50) PATH '$.prod_name',
    price DECIMAL(10,2) PATH '$.price',
    quantity INT PATH '$.quantity'
  )
) AS jt;

执行结果

order_id	user_id	prod_id	prod_name	price	quantity
O001	1	101	手机	3999.00	1
O001	1	102	耳机	299.00	2
O002	2	201	笔记本	5999.00	1

逐步解析

1. '$.items[*]' 定位到 items 数组，[*] 表示遍历数组中每一个元素。

2. COLUMNS 子句将每个数组对象里的 4 个字段映射为 4 列，并指定了 SQL 数据类型。

3. 写法 FROM orders o, JSON_TABLE(...) 是隐式 CROSS JOIN，等价于 CROSS JOIN JSON_TABLE(...)。

4. O003 的 items 是空数组，拆解后没有行，不会出现在结果中。如果需要保留该行，改用 LEFT JOIN ... ON true。

PostgreSQL 写法（14+）

PostgreSQL 的 JSON_TABLE 语法与标准高度一致，仅路径表达式习惯使用 '$' 开头：

SELECT 
  o.order_id,
  o.user_id,
  jt.prod_id,
  jt.prod_name,
  jt.price,
  jt.quantity
FROM orders o,
JSON_TABLE(
  o.order_detail,
  '$.items[*]'
  COLUMNS (
    prod_id INT PATH '$.prod_id',
    prod_name TEXT PATH '$.prod_name',
    price NUMERIC(10,2) PATH '$.price',
    quantity INT PATH '$.quantity'
  )
) AS jt;

3. 案例 2：带序号 + 默认值的稳健拆解

需求：给每个商品加行号，缺失的折扣字段填充默认值 0，避免 NULL 影响计算。

SELECT 
  o.order_id,
  jt.item_no,
  jt.prod_id,
  jt.discount
FROM orders o,
JSON_TABLE(
  o.order_detail,
  '$.items[*]'
  COLUMNS (
    item_no INT FOR ORDINALITY,          -- 自动生成 1,2,3... 的连续行号
    prod_id INT PATH '$.prod_id',
    discount DECIMAL(5,2) DEFAULT 0.00 ON EMPTY  -- 字段不存在时默认 0
  )
) AS jt;

执行结果（假设原始数据没有 discount 字段）

order_id	item_no	prod_id	discount
O001	1	101	0.00
O001	2	102	0.00
O002	1	201	0.00

• FOR ORDINALITY 自动生成从 1 开始的连续序号，对应数组下标 +1。
• DEFAULT ... ON EMPTY 处理字段缺失的情况，是生产环境的稳健写法，避免 NULL 导致求和、平均值计算偏差。

4. 案例 3：多层嵌套 JSON 级联拆解

原始数据（store_data 表，store_json 字段）

{
  "store": "北京旗舰店",
  "categories": [
    {
      "cate_name": "数码产品",
      "products": [
        {"name": "手机", "stock": 100},
        {"name": "耳机", "stock": 200}
      ]
    },
    {
      "cate_name": "家居用品",
      "products": [
        {"name": "台灯", "stock": 50}
      ]
    }
  ]
}

需求：拆解到最细粒度的商品库存行，同时保留所属分类和门店信息。

SELECT 
  jt1.store_name,
  jt1.cate_name,
  jt2.prod_name,
  jt2.stock
FROM store_data s,
JSON_TABLE(
  s.store_json,
  '$.categories[*]'
  COLUMNS (
    store_name VARCHAR(50) PATH '$.store',  -- 继承上层根节点字段
    cate_name VARCHAR(50) PATH '$.cate_name',
    products JSON PATH '$.products'         -- 内层数组先作为 JSON 列取出
  )
) AS jt1,
JSON_TABLE(
  jt1.products,
  '$[*]'
  COLUMNS (
    prod_name VARCHAR(50) PATH '$.name',
    stock INT PATH '$.stock'
  )
) AS jt2;

执行结果

store_name	cate_name	prod_name	stock
北京旗舰店	数码产品	手机	100
北京旗舰店	数码产品	耳机	200
北京旗舰店	家居用品	台灯	50

• 多层嵌套数组需要用两次 JSON_TABLE 级联拆解：第一层拆分类数组，第二层拆每个分类下的商品数组。

• 上层的字段（如 store_name）可以在第一层提取后，自动传递到最终结果集。

5. 案例 4：结合聚合统计的生产实战

需求：基于订单 JSON 明细，统计每个商品的总销量和总销售额。

SELECT 
  jt.prod_id,
  jt.prod_name,
  SUM(jt.quantity) AS total_sold,
  SUM(jt.price * jt.quantity) AS total_revenue
FROM orders o,
JSON_TABLE(
  o.order_detail,
  '$.items[*]'
  COLUMNS (
    prod_id INT PATH '$.prod_id',
    prod_name VARCHAR(50) PATH '$.prod_name',
    price DECIMAL(10,2) PATH '$.price',
    quantity INT PATH '$.quantity'
  )
) AS jt
GROUP BY jt.prod_id, jt.prod_name
ORDER BY total_revenue DESC;

执行结果（基于案例 1 数据）

prod_id	prod_name	total_sold	total_revenue
201	笔记本	1	5999.00
101	手机	1	3999.00
102	耳机	2	598.00

• 拆解后的 jt 结果集可以直接当作普通表使用，支持 GROUP BY、JOIN、WHERE 等所有 SQL 操作。
• 这是生产环境最常用的模式：业务表存 JSON 明细，分析时用 JSON_TABLE 拆解后做统计，无需额外 ETL 流程。

6. 生产注意事项

1. 性能边界：单条 JSON 数组元素过多（如单条存上千个商品）时，拆解性能会下降，建议控制单条 JSON 大小在 1MB 以内。

2. 保留空数组行：如果需要保留空数组的行（比如统计没有商品的订单），使用 LEFT JOIN JSON_TABLE(...) ON 1=1 替代隐式 CROSS JOIN。

3. 类型兜底：COLUMNS 中指定的类型要和 JSON 内的数据类型匹配，否则会触发转换错误，建议加 NULL ON ERROR 兜底。

4. 跨库兼容：PostgreSQL 14 开始支持标准 JSON_TABLE，但在旧版本中可使用 jsonb_to_recordset() 或 jsonb_array_elements() 配合横向子查询实现类似效果。

---

4.6.4 高频辅助 JSON 函数

1. 行转 JSON：聚合生成 JSON 数组/对象

与 JSON_TABLE 相反，将多行结果合并为 JSON 数组/对象，常用于接口输出、数据导出。

• MySQL：JSON_ARRAYAGG()、JSON_OBJECTAGG()
• PostgreSQL：jsonb_agg()、jsonb_object_agg()
• Oracle：JSON_ARRAYAGG()、JSON_OBJECTAGG()
• SQL Server：FOR JSON PATH 或 STRING_AGG 结合 JSON 函数手动构建

示例（MySQL）

-- 按订单聚合商品列表为 JSON 数组
SELECT 
  order_id,
  JSON_ARRAYAGG(JSON_OBJECT('prod_id', prod_id, 'name', prod_name)) AS items_json
FROM order_items
GROUP BY order_id;

2. 存在性判断

• MySQL：JSON_CONTAINS(json_doc, value, path)，判断指定路径是否包含某个值。
• PostgreSQL：@> 包含操作符，如 ext_info @> '{"city": "北京"}'::jsonb。
• Oracle：JSON_EXISTS(col, '$.path') 检查路径是否存在。

3. 结构探查

• JSON_KEYS(json_doc)：返回 JSON 对象的所有顶层键名（MySQL）。

• JSON_LENGTH(json_doc, path)：返回数组长度或对象键的数量（MySQL / PG jsonb_array_length()）。

• JSON_TYPE(json_doc, path)：返回路径处的值类型。

• JSON_VALID(str)：校验字符串是否为合法 JSON（MySQL / PG jsonb_valid()）。

---

4.6.5 生产级 JSON 使用规范

1. 静态字段不要存 JSON：固定业务字段（如订单金额、状态）单独建列，JSON 只存动态扩展、低频查询的属性。

2. 高频过滤字段建索引：PostgreSQL 用 GIN 索引或表达式索引，MySQL 用生成列 + 普通索引。

3. 避免深度嵌套：超过 3 层嵌套的 JSON 会大幅提升解析和维护成本，尽量扁平化设计。

4. 禁止大表全表扫描 JSON：千万级表禁止在 WHERE 中直接用 JSON 函数过滤，必须走索引或提前拆解到明细表。

5. 统一 ->> 提取习惯：数据分析场景一律用 ->> 或 JSON_VALUE 提取为标量，避免返回 JSON 类型引发隐式转换。

6. 对空数组和缺失字段采用防御性编程：使用 LEFT JOIN + DEFAULT ON EMPTY 确保分析结果不受数据缺失影响。

模块五：汇总与分组

分组聚合是数据分析的核心能力，企业中 90% 的统计报表都依赖 GROUP BY。

5.1 聚集函数深度辨析

函数	作用	NULL 处理	生产说明
COUNT(*)	统计总行数	不忽略 NULL	统计行数首选，InnoDB 做了优化，性能最高
COUNT(1)	统计总行数	不忽略 NULL	和 COUNT(*) 性能几乎无差异
COUNT(列名)	统计该列非空行数	忽略 NULL	统计有效数据量时使用
COUNT(DISTINCT 列名)	统计去重后的非空行数	忽略 NULL	统计 UV、独立用户数时使用，大数据量性能开销大
AVG(列名)	求平均值	忽略 NULL	自动跳过 NULL 行
SUM(列名)	求和	忽略 NULL	空表返回 NULL
MAX() / MIN()	最大值 / 最小值	忽略 NULL	索引列上执行极快

-- 统计总行数、有效手机号数、独立用户数
SELECT
    COUNT(*) AS total_count,
    COUNT(phone) AS valid_phone_count,
    COUNT(DISTINCT user_id) AS uv
FROM orders;

5.2 GROUP BY 分组

逻辑顺序：WHERE（过滤行）→ GROUP BY（分组）→ HAVING（过滤分组）→ SELECT

语法规则

1. SELECT 中除了聚集函数，所有普通列 必须出现在 GROUP BY 中

2. GROUP BY 中可以写表达式，但 不能用 SELECT 中的别名

3. 分组列中有 NULL 时，所有 NULL 行会单独分为一组

4. GROUP BY 必须在 WHERE 之后、ORDER BY 之前

⚠️ 重要规范：MySQL 5.7+ 默认开启 ONLY_FULL_GROUP_BY 模式，违反规则会直接报错。生产环境必须开启该模式，否则会出现分组后随机取值的脏数据。

5.3 HAVING 分组过滤

-- 统计下单数≥2 的用户
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) >= 2;

WHERE vs HAVING 本质区别

对比项	WHERE	HAVING
执行时机	分组前过滤行	分组后过滤分组
过滤对象	单行数据	分组结果
能否用聚集函数	❌ 不能（如 WHERE SUM(...) 报错）	✅ 能（如 HAVING SUM(...) > 100）
性能影响	先过滤再分组，数据量小，快	先分组再过滤，数据量大，慢
典型用法	WHERE city = '北京'	HAVING COUNT(*) > 5

🔧 黄金法则：能在 WHERE 里过滤的条件，绝对不要放到 HAVING 里，先过滤再分组可以大幅减少计算量。

-- ❌ 低效：先分组再过滤
SELECT city, COUNT(*) AS order_count
FROM orders
GROUP BY city
HAVING order_time >= '2024-01-01';  -- 报错：WHERE 条件不能放 HAVING

-- ✅ 高效：先过滤再分组
SELECT city, COUNT(*) AS order_count
FROM orders
WHERE order_time >= '2024-01-01'
GROUP BY city;

5.4 分组扩展：WITH ROLLUP

生成带合计行的统计结果，财务、运营报表高频使用：

对比项	MySQL	PostgreSQL
ROLLUP 语法	GROUP BY 列 WITH ROLLUP	GROUP BY ROLLUP(列)
反引号 `	用于标识符	不支持，改用双引号 "
GROUPING()	MySQL 8.0.12+	原生支持

-- MySQL示例
SELECT COALESCE(资产状态) AS 资产状态, COUNT(*) AS 设备数量
FROM (
    SELECT A.`设备名`, A.`资产状态`
    FROM  A
) AS eligible_devices
GROUP BY 资产状态 WITH ROLLUP;

-- 1. 按资产状态分组（处理原始 NULL 为 '未知'）
SELECT
    CASE WHEN 资产状态 IS NULL THEN 'NULL' ELSE 资产状态 END AS 资产状态,
    COUNT(*) AS 设备数量
FROM (
    SELECT A.`设备名`, A.`资产状态`
    FROM A) AS eligible_devices
GROUP BY 资产状态

UNION ALL

-- 2. 总计行
SELECT '总计', COUNT(*)
FROM (
    SELECT A.`设备名`, A.`资产状态`
    FROM  A) AS eligible_devices;

⚠️ 如果数据本身就有 资产状态 = NULL 的行，方法二会把数据 NULL 和汇总 NULL 都显示为"总计"，无法区分。

5.5 窗口函数

窗口函数不像 GROUP BY 那样把数据"合并"，而是"保持原样，额外添加计算列"。

排序函数：RANK / DENSE_RANK / ROW_NUMBER

GROUP BY vs 窗口函数：对比演示

函数	同分处理	场景
RANK()	1, 1, 3, 4（有跳号）	有并列时跳过名次
DENSE_RANK()	1, 1, 2, 3（不跳号）	有并列时不跳名次
ROW_NUMBER()	1, 2, 3, 4（强制唯一）	排序取 TOP N

原始数据（employees 表）：

emp_name	dept_id	salary
张三	研发部	10000
李四	研发部	12000
王五	研发部	10000
赵六	销售部	8000
钱七	销售部	9000
孙八	销售部	8000

场景：按部门排名

GROUP BY 的做法：

SELECT dept_id, AVG(salary)
FROM employees
GROUP BY dept_id;

结果（数据被合并了，原始行消失）：

dept_id	AVG(salary)
研发部	10666.67
销售部	8333.33

窗口函数的做法：

SELECT emp_name, dept_id, salary,
    RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rank_in_dept
FROM employees;

结果（原始行完整保留，额外添加了排名列）：

emp_name	dept_id	salary	rank_in_dept
李四	研发部	12000	1
张三	研发部	10000	2
王五	研发部	10000	2
钱七	销售部	9000	1
赵六	销售部	8000	2
孙八	销售部	8000	2

✅ 窗口函数不会合并数据！它只是给每一行"打标签"。

聚合窗口函数

📌 聚合窗口函数 = 聚合函数 + OVER()，本质就是 SUM/AVG/COUNT/MAX/MIN 这些聚合函数加上了窗口语法，让它们在不合并行的前提下完成聚合计算。

SELECT emp_name, dept_id, salary,
    SUM(salary)   OVER (PARTITION BY dept_id) AS dept_total_salary,
    AVG(salary)   OVER (PARTITION BY dept_id) AS dept_avg_salary,
    COUNT(*)      OVER (PARTITION BY dept_id) AS dept_count,
    MAX(salary)   OVER (PARTITION BY dept_id) AS dept_max_salary,
    MIN(salary)   OVER (PARTITION BY dept_id) AS dept_min_salary
FROM employees;

结果：

emp_name	dept_id	salary	dept_total	dept_avg	dept_count	dept_max	dept_min
张三	研发部	10000	32000	10666.67	3	12000	10000
李四	研发部	12000	32000	10666.67	3	12000	10000
王五	研发部	10000	32000	10666.67	3	12000	10000
赵六	销售部	8000	25000	8333.33	3	9000	8000
钱七	销售部	9000	25000	8333.33	3	9000	8000
孙八	销售部	8000	25000	8333.33	3	9000	8000

偏移窗口函数

• LAG() — 取前一行的值

• LEAD() — 取后一行的值

• FIRST_VALUE() — 取窗口内第一行的值

• LAST_VALUE() — 取窗口内最后一行的值

场景：对比今天和昨天的销售额

原始数据（daily_sales 表）：

order_date	daily_revenue
2024-01-01	1000
2024-01-02	1500
2024-01-03	1200
2024-01-04	1800
2024-01-05	2000

SELECT order_date, daily_revenue,
    LAG(daily_revenue, 1) OVER (ORDER BY order_date) AS yesterday_revenue,
    daily_revenue - LAG(daily_revenue, 1) OVER (ORDER BY order_date) AS change
FROM daily_sales;

逐步推演：

order_date	daily_revenue	LAG(前一天)	变化量
2024-01-01	1000	NULL（没有前一天）	NULL
2024-01-02	1500	1000	+500
2024-01-03	1200	1500	-300
2024-01-04	1800	1200	+600
2024-01-05	2000	1800	+200

类比理解：LAG(col, 1) 就像你站在队列中，回头看前面一个人的编号。

场景：每个员工想知道自己部门的最高薪资是多少

emp_name	dept_id	salary
张三	研发部	10000
李四	研发部	12000
王五	研发部	10000
赵六	销售部	8000
钱七	销售部	9000
孙八	销售部	8000

SELECT emp_name, dept_id, salary,
    FIRST_VALUE(salary) OVER (
        PARTITION BY dept_id 
        ORDER BY salary DESC
    ) AS dept_highest_salary
FROM employees;

emp_name	dept_id	salary	dept_highest_salary
李四	研发部	12000	12000
张三	研发部	10000	12000
王五	研发部	10000	12000
钱七	销售部	9000	9000
赵六	销售部	8000	9000
孙八	销售部	8000	9000

5.6 企业案例：电商销售维度分析

需求：统计 2024 年每个城市的下单用户数、订单总数、总销售额，筛选出总销售额大于 10000 的城市。

SELECT
    u.city,
    COUNT(DISTINCT o.user_id) AS user_count,      -- 独立用户数
    COUNT(o.order_id) AS order_count,              -- 订单总数
    SUM(o.order_amount) AS total_amount            -- 总销售额
FROM orders o
JOIN users u ON o.user_id = u.user_id
WHERE o.order_time >= '2024-01-01' AND o.order_time < '2025-01-01'
  AND o.status = 1
GROUP BY u.city
HAVING SUM(o.order_amount) > 10000
ORDER BY total_amount DESC;

---

模块六：子查询与联结

6.1 子查询

子查询就是嵌套在其他查询中的查询，分为 WHERE 子查询和 SELECT 子查询。

IN vs EXISTS 性能对比

场景	推荐写法	原因
子查询结果集小	IN	先执行子查询得到结果集，再外层查询
外层表小、子查询表大且有索引	EXISTS	先执行外层查询，再代入子查询校验

-- ✅ 推荐：使用 EXISTS（性能通常更优）
SELECT user_name, contact
FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.user_id
      AND o.order_id IN (
          SELECT order_id FROM order_items WHERE prod_id = 'RGAN01'
      )
);

🔴 NOT IN 的 NULL 陷阱

-- ❌ 致命错误：NOT IN 子查询结果中有 NULL，最终结果为空集
SELECT * FROM users
WHERE user_id NOT IN (SELECT user_id FROM orders WHERE prod_id = 'A');
-- 如果子查询返回 NULL，整个 NOT IN 结果为空！

-- ✅ 正确：使用 NOT EXISTS
SELECT * FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.user_id AND o.prod_id = 'A'
);

🔧 黄金法则：生产环境中优先用 NOT EXISTS 替代 NOT IN，避免 NULL 导致的空集问题。

关联子查询（SELECT 计算字段子查询）

-- 查询每个用户的订单总数
SELECT
    user_name,
    city,
    (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.user_id) AS order_count
FROM users u
ORDER BY user_name;

⚠️ 生产避坑：关联子查询每行都会执行一次，大数据量下性能极差，优先用 JOIN + GROUP BY 替代。

-- ❌ 关联子查询：每行执行一次子查询
SELECT u.user_name,
       (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.user_id) AS order_count
FROM users u;

-- ✅ 优化后：JOIN + GROUP BY，一次扫描
SELECT u.user_name, COUNT(o.order_id) AS order_count
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id, u.user_name;

6.2 多表联结：SQL 核心能力

企业数据分散在多张表中，90% 的业务 SQL 都会用到多表联结。

联结的本质

联结的核心是 笛卡尔积 + 条件过滤：两张表的所有行两两组合，再通过联结条件筛选出匹配的行。

🔴 生产事故红线：漏写联结条件会导致笛卡尔积，两张百万表联结会产生万亿行结果，直接打挂数据库。写联结 SQL 后，必须先校验联结条件。

联结类型与适用场景

联结类型	保留规则	企业使用场景
INNER JOIN（内联结）	只保留两边都匹配的行	最常用，如订单关联用户，只保留有对应关系的数据
LEFT JOIN（左外联结）	保留左表所有行，右表匹配不上的字段为 NULL	高频使用，如统计所有用户的订单数（包含没下单的用户）
RIGHT JOIN（右外联结）	保留右表所有行	生产极少使用，统一改写为 LEFT JOIN，逻辑更清晰
FULL OUTER JOIN（全外联结）	两边所有行都保留	数据对账、两边数据补全场景；MySQL 不支持，PG/Oracle 支持

🔴 生产核心：ON vs WHERE 的区别

左联结中，条件写在 ON 和 WHERE 里结果完全不同，是新手最高频的错误。

-- ✅ 正确：条件写在 ON 中
-- 先过滤右表（orders），再与左表（users）联结
-- 左表所有用户都保留，没有效订单的用户 order_id 为 NULL
SELECT u.user_id, o.order_id
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id AND o.status = 1;

-- ❌ 错误：条件写在 WHERE 中
-- 先联结，再整体过滤
-- 没有有效订单的用户被 WHERE 过滤掉，退化成内联结
SELECT u.user_id, o.order_id
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE o.status = 1;

类比理解：

• ON 是"配对规则"——决定谁和谁握手
• WHERE 是"入场门票"——握手完成后，不符合条件的请离场

自联结（Self Join）

同一张表自己和自己联结，用于处理层级结构或同表对比场景。

-- 案例：查询和"张三"同城市的所有用户

-- 写法一：子查询
SELECT * FROM users
WHERE city = (SELECT city FROM users WHERE user_name = '张三');

-- 写法二：自联结（性能更优，推荐）
SELECT u1.*
FROM users u1
JOIN users u2 ON u1.city = u2.city
WHERE u2.user_name = '张三';

企业联结最佳实践

规则	说明
字段类型一致	联结条件的字段类型必须一致，避免隐式转换导致索引失效
小表驱动大表	减少匹配次数，提高性能
控制联结数量	单查询联结表数量建议不超过 5 张，过多联结会导致性能指数下降
必须起别名	提升可读性
多表字段重名时	必须指定表名 / 别名，避免歧义

-- ✅ 规范写法
SELECT
    c.cust_id,
    c.cust_name,
    SUM(oi.quantity * p.prod_price) AS total_amount
FROM customers c
INNER JOIN orders o ON c.cust_id = o.cust_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.prod_id = p.prod_id
GROUP BY c.cust_id, c.cust_name;

---

模块七：组合查询

7.1 UNION vs UNION ALL

对比项	UNION	UNION ALL
行为	自动去重，需要排序去重	直接合并，不去重
性能	开销大	性能高很多
适用场景	确实需要去重	确定没有重复数据或允许重复

-- ✅ 推荐：合并已归档客户和当前客户的邮件列表
-- 假设数据源已保证无重复，用 UNION ALL 性能更高
SELECT cust_name, cust_email FROM active_customers
UNION ALL
SELECT cust_name, cust_email FROM archived_customers
ORDER BY cust_name;

🔧 生产规范：确定没有重复数据或允许重复时，一律用 UNION ALL，不要用 UNION。

7.2 适用场景

场景	说明
分表数据合并	如订单按年分表，合并多年数据
多业务线数据汇总	不同表的同结构数据统一输出
单表多条件拆分	复杂条件拆分为多个简单查询合并

-- 案例：合并分表数据（订单按年分表）
SELECT order_id, user_id, order_time FROM orders_2023
UNION ALL
SELECT order_id, user_id, order_time FROM orders_2024;

7.3 排序规则

• ORDER BY 只能写在最后一条 SELECT 之后，对整个合并结果排序
• 排序字段必须使用第一条 SELECT 的列名或别名

---

第二部分：企业级进阶

模块八：CTE 与复杂查询模式

CTE（Common Table Expression）是企业级 SQL 开发的分水岭。它将复杂查询拆解为可读逻辑块，是编写可维护 SQL 的核心技能。

8.1. CTE（公共表表达式）

CTE 看起来像一个临时表，但又不是。很多人不理解 CTE 到底"做了什么"。

8.1.1 生活类比：做菜的"备菜步骤"

想象你要做一道复杂的红烧肉：

步骤1：切肉 → 步骤2：焯水 → 步骤3：炒糖色 → 步骤4：炖煮 → 步骤5：收汁

CTE 就像这些"备菜步骤"：

WITH 步骤1_切肉 AS (
    -- 准备原始食材：过滤、清洗
    SELECT * FROM orders WHERE status = 'COMPLETED'
),
步骤2_焯水 AS (
    -- 初步加工：计算字段
    SELECT *, quantity * unit_price AS line_total FROM 步骤1_切肉
),
步骤3_炒糖色 AS (
    -- 进一步处理：分组聚合
    SELECT customer_id, SUM(line_total) AS total_spent
    FROM 步骤2_焯水
    GROUP BY customer_id
),
步骤4_炖煮 AS (
    -- 业务规则：打标签
    SELECT customer_id, total_spent,
        CASE WHEN total_spent > 10000 THEN 'VIP' ELSE '普通' END AS level
    FROM 步骤3_炒糖色
)
-- 步骤5_收汁：最终输出
SELECT * FROM 步骤4_炖煮 ORDER BY total_spent DESC;

8.1.2 逐步推演：CTE 每一步的结果

原始数据（orders 表）：

order_id	customer_id	status	quantity	unit_price
1	C001	COMPLETED	2	100
2	C001	COMPLETED	1	200
3	C002	COMPLETED	3	50
4	C003	CANCELLED	1	150

Layer 1：raw_cleaned（数据清洗）

SELECT order_id, customer_id, quantity * unit_price AS line_total
FROM orders
WHERE status = 'COMPLETED'

order_id	customer_id	line_total
1	C001	200
2	C001	200
3	C002	150

-- C003 被过滤了（status = CANCELLED）

Layer 2：customer_metrics（客户聚合）

SELECT customer_id,
    COUNT(*) AS order_count,
    SUM(line_total) AS total_revenue
FROM raw_cleaned
GROUP BY customer_id

customer_id	order_count	total_revenue
C001	2	400
C002	1	150

Layer 3：customer_segments（客户分层）

SELECT customer_id, total_revenue,
    CASE WHEN total_revenue > 10000 THEN 'VIP' ELSE '普通' END AS segment
FROM customer_metrics

customer_id	total_revenue	segment
C001	400	普通
C002	150	普通

最终 SELECT：输出结果

customer_id | total_revenue | segment
------------|---------------|--------
C001        | 400           | 普通
C002        | 150           | 普通

8.1.3 CTE vs 子查询：对比

子查询写法（嵌套深，难读）：

SELECT * FROM (
    SELECT customer_id, SUM(line_total) AS total_revenue
    FROM (
        SELECT *, quantity * unit_price AS line_total
        FROM orders WHERE status = 'COMPLETED'
    ) t1
    GROUP BY customer_id
) t2
WHERE total_revenue > 100;

CTE 写法（平铺直叙，像读菜谱）：

WITH cleaned AS (...),
     aggregated AS (...)
SELECT * FROM aggregated
WHERE total_revenue > 100;

8.2 多层嵌套 CTE 模式

-- 多层嵌套: 每一层 CTE 负责一个逻辑阶段
WITH
-- Layer 1: 数据清洗与初步聚合
raw_cleaned AS (
    SELECT
        order_id, customer_id, product_id,
        quantity, unit_price,
        quantity * unit_price AS line_total,
        order_date::date AS order_day
    FROM orders
    WHERE status = 'COMPLETED'
      AND order_date >= CURRENT_DATE - INTERVAL '1 year'
),
-- Layer 2: 客户维度聚合
customer_metrics AS (
    SELECT
        customer_id,
        COUNT(DISTINCT order_id) AS order_count,
        SUM(line_total) AS total_revenue,
        AVG(line_total) AS avg_order_value,
        COUNT(DISTINCT order_day) AS active_days,
        MIN(order_day) AS first_order,
        MAX(order_day) AS last_order
    FROM raw_cleaned
    GROUP BY customer_id
),
-- Layer 3: 客户分层
customer_segments AS (
    SELECT
        customer_id, order_count, total_revenue, avg_order_value,
        CASE
            WHEN total_revenue > 100000 AND order_count > 20 THEN 'VIP'
            WHEN total_revenue > 50000  THEN 'Premium'
            WHEN order_count >= 5       THEN 'Regular'
            ELSE 'New'
        END AS segment
    FROM customer_metrics
),
-- Layer 4: 跨维度统计
segment_stats AS (
    SELECT
        segment,
        COUNT(*) AS customer_count,
        SUM(total_revenue) AS segment_revenue,
        AVG(total_revenue) AS avg_revenue_per_customer,
        SUM(order_count) * 1.0 / COUNT(*) AS avg_orders_per_customer
    FROM customer_segments
    GROUP BY segment
)
SELECT
    segment,
    customer_count,
    segment_revenue,
    ROUND(avg_revenue, 2) AS avg_revenue,
    ROUND(avg_orders, 2) AS avg_orders
FROM segment_stats
ORDER BY segment_revenue DESC;

CTE 设计模式——四层管道架构：

层级	职责	示例
Layer 1 数据清洗	过滤脏数据、类型转换、初步聚合	过滤无效订单、计算行小计
Layer 2 指标计算	聚合指标、衍生指标	计算用户总消费、下单次数
Layer 3 业务规则	应用业务逻辑、分层/分类	客户分层、风险评估
Layer 4 输出统计	最终输出、汇总展示	各层统计结果、排名

8.3 递归 CTE（Recursive CTE）像剥洋葱一样一层层展开

本质就是：递归成员引用了CTE自身 → 引擎看到这种结构 → 自动进入"把上一轮输出喂回给自己"的循环。

问题	答案
什么让查询递归？	WITH RECURSIVE + 递归成员中 FROM cte自身
什么让递归继续？	上一轮产出了新行，引擎自动代回递归成员再跑
什么让递归停止？	某一轮产不出新行了，引擎自动退出

场景 1：组织层级树遍历

CEO (ID=1)
├── 技术总监 (ID=2)
│   ├── 前端开发 (ID=4)
│   └── 后端开发 (ID=5)
└── 产品总监 (ID=3)
    └── 产品经理 (ID=6)

-- 递归 CTE 基础结构:
-- WITH RECURSIVE cte_name AS (
--     -- 锚点成员 (非递归，只执行一次)
--     SELECT ...
--     UNION ALL
--     -- 递归成员 (反复执行直到返回空结果集)
--     SELECT ... FROM cte_name
--)
-- SELECT * FROM cte_name;

WITH RECURSIVE org_tree AS (
    -- 锚点: 根节点 (顶层管理者)
    SELECT
        employee_id, name, manager_id,0 AS level,ARRAY[name] AS path,name AS root_manager
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL
    -- 递归: 子节点
    SELECT
        e.employee_id, e.name, e.manager_id,ot.level + 1,ot.path || e.name,ot.root_manager
    FROM employees e
    INNER JOIN org_tree ot ON e.manager_id = ot.employee_id
)
SELECT
    LPAD('', level * 4, ' ') || name AS org_chart,
    level,
    path AS reporting_chain,
    root_manager
FROM org_tree
ORDER BY level, root_manager, name;

• LPAD('', level * 4, ' ') 的作用是：根据层级数，在名字前面填充空格。
  level 0 → 0 * 4 = 0 个空格 → CEO
  level 1 → 1 * 4 = 4 个空格 → 技术总监
  level 2 → 2 * 4 = 8 个空格 → 前端开发

• ARRAY[name] 和 ot.path || e.name 构建的是一个数组，记录了从根节点到当前节点的完整路径。

逐步推演：

迭代次数	level	返回的员工	说明
第1次	0	CEO	锚点，只执行一次
第2次	1	技术总监, 产品总监	找 ID=1 的下属
第3次	2	前端开发, 后端开发, 产品经理	找 ID=2,3 的下属
第4次	3	（无）	没有下属了，递归终止

类比理解：递归 CTE 就像"传话游戏"——第一个人知道后传给第二个人，第二个人再传给第三个人，直到没人知道了为止。

总结：递归的三要素

要素	作用	对应代码
锚点成员	提供种子数据（第 0 轮）	WHERE manager_id IS NULL
递归成员	用上一轮的输出去 JOIN 原表，产出下一轮数据	INNER JOIN tree t ON e.manager_id = t.employee_id
终止条件	当递归成员查不出新行时，自动停止	无需显式写，查不到行就自然终止

📌 所以递归的本质就是：自己调用自己，每轮用上一轮的结果去找下一层数据，直到找不到为止。

场景 2：盒子套娃递归展开

• 写出递归 CTE，计算每个盒子里实际包含的最底层物品总数。

• 提示：大箱子有 1 个，里面装了 3 个中箱子，每个中箱子里有 5 个小盒子。

box_id	box_name	parent_id	items_count
1	大箱子	NULL	1
2	中箱子	1	3
3	小盒子	2	5

WITH RECURSIVE box_calc AS (
    -- 锚点：最外层大箱子
    SELECT box_id, box_name, parent_id, items_count AS total_items
    FROM boxes
    WHERE parent_id IS NULL

    UNION ALL

    -- 递归：进入下一层，数量相乘
    SELECT b.box_id, b.box_name, b.parent_id, bc.total_items * b.items_count
    FROM boxes b
    INNER JOIN box_calc bc ON b.parent_id = bc.box_id
)
SELECT * FROM box_calc;

结果推演：

• 第 0 轮：大箱子，total_items = 1

• 第 1 轮：中箱子，total_items = 1 * 3 = 3

• 第 2 轮：小盒子，total_items = 3 * 5 = 15

• 第 3 轮：查不到，终止。最终小盒子里的实际物品数是 15。

box_id	box_name	parent_id	total_items
1	大箱子	NULL	1
2	中箱子	1	3
3	小盒子	2	15

场景3:逆向思维（向上找）

员工A 的 emp_id = 4，请写出递归 CTE，向上追溯他的完整汇报线，直到 CEO 为止。

emp_id	name	manager_id
1	CEO	NULL
2	总监	1
3	经理	2
4	员工A	3

WITH RECURSIVE report_chain AS (
    -- 锚点：从员工A开始
    SELECT emp_id, name, manager_id, 0 AS step_up
    FROM employees
    WHERE emp_id = 4

    UNION ALL

    -- 递归：去找他的上级（JOIN 条件反转）
    SELECT e.emp_id, e.name, e.manager_id, rc.step_up + 1
    FROM employees e
    INNER JOIN report_chain rc ON e.emp_id = rc.manager_id
)
SELECT * FROM report_chain;

结果推演：

• 第 0 轮（锚点）：员工A (step_up 0)

• 第 1 轮：找 emp_id = 3 的 -> 经理 (step_up 1)

• 第 2 轮：找 emp_id = 2 的 -> 总监 (step_up 2)

• 第 3 轮：找 emp_id = 1 的 -> CEO (step_up 3)

• 第 4 轮：CEO 的 manager_id 是 NULL，找不到 emp_id = NULL 的人，终止。

emp_id	name	manager_id	step_up
4	员工A	3	0
3	经理	2	1
2	总监	1	2
1	CEO	NULL	3

场景 4：日期序列生成

-- 不需要从系统表查询，自己生成日期序列
WITH RECURSIVE date_series AS (
    SELECT DATE '2024-01-01' AS dt
    UNION ALL
    SELECT dt + INTERVAL '1 day'
    FROM date_series
    WHERE dt < DATE '2024-12-31'
)
SELECT dt FROM date_series ORDER BY dt;

8.4 递归 CTE 最佳实践

-- ✅ 最佳实践 1: 始终添加终止条件防止无限循环
WITH RECURSIVE safe_tree AS (
    SELECT id, parent_id, name, 0 AS depth
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    SELECT c.id, c.parent_id, c.name, st.depth + 1
    FROM categories c
    INNER JOIN safe_tree st ON c.parent_id = st.id
    WHERE st.depth < 50  -- 安全限制
)
SELECT * FROM safe_tree;

-- ✅ 最佳实践 2: 使用 UNION ALL 而非 UNION 提升性能
-- UNION 会检查重复行，在递归中几乎总是多余的

-- ✅ 最佳实践 3: 检测环
WITH RECURSIVE cycle_check AS (
    SELECT id, parent_id, ARRAY[id] AS visited
    FROM categories WHERE parent_id IS NULL

    UNION ALL

    SELECT c.id, c.parent_id, cc.visited || c.id
    FROM categories c
    JOIN cycle_check cc ON c.parent_id = cc.id
    WHERE NOT (c.id = ANY(cc.visited))  -- 防止环
)
SELECT * FROM cycle_check;

⚠️ 注意：各数据库递归限制不同

• MySQL 8.0+：默认最大 1000 层
• PostgreSQL：默认无限制
• SQL Server：默认 100 层，可用 OPTION (MAXRECURSION 0) 取消
• Oracle：默认无限制，但建议添加 LEVEL 条件

8.5 CTE 性能注意事项

-- PostgreSQL 12+: CTE 优化
-- 默认情况下，CTE 可能被优化器内联
-- PostgreSQL 16+ 支持 MATERIALIZED / NOT MATERIALIZED 提示

-- 明确物化 (当 CTE 被多次引用时强制物化)
WITH materialized_data AS MATERIALIZED (
    SELECT customer_id, SUM(amount) AS total
    FROM transactions
    GROUP BY customer_id
)
SELECT customer_id, total FROM materialized_data WHERE total > 1000
UNION ALL
SELECT customer_id, total FROM materialized_data ORDER BY total DESC LIMIT 10;

---

模块九：窗口函数深度进阶

窗口函数是初级与高级开发的分水岭。企业报表很少只用 GROUP BY，更多是用窗口函数做高级分析。

9.1 窗口函数核心概念

窗口函数 = 聚合函数 + OVER (PARTITION BY ... ORDER BY ... [Frame])

函数	场景	同分处理
ROW_NUMBER()	强制唯一序号	1, 2, 3, 4
RANK()	有并列时跳过名次	1, 1, 3, 4
DENSE_RANK()	有并列时不跳名次	1, 1, 2, 3
NTILE(n)	分组（如四分位）	均匀分配

9.2 滑动窗口（Sliding Window）

场景 1：7 天移动平均

SELECT
    order_date,
    daily_revenue,
    AVG(daily_revenue) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7d,
    STDDEV(daily_revenue) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS stddev_7d
FROM (
    SELECT order_date, SUM(total) AS daily_revenue
    FROM orders GROUP BY order_date
) daily;

场景 2：滚动累计量（Year-to-Date）

SELECT
    order_date,
    daily_revenue,
    SUM(daily_revenue) OVER (
        ORDER BY order_date
        ROWS UNBOUNDED PRECEDING
    ) AS ytd_revenue
FROM (
    SELECT order_date, SUM(total) AS daily_revenue
    FROM orders
    WHERE EXTRACT(YEAR FROM order_date) = EXTRACT(YEAR FROM CURRENT_DATE)
    GROUP BY order_date
) daily;

9.3 LAG / LEAD 时间对比

-- 日环比 / 周环比
SELECT
    order_date,
    daily_revenue,
    LAG(daily_revenue, 1) OVER (ORDER BY order_date) AS prev_day,
    daily_revenue - LAG(daily_revenue, 1) OVER (ORDER BY order_date) AS day_over_day_change,
    LAG(daily_revenue, 7) OVER (ORDER BY order_date) AS prev_week,
    ROUND(100.0 * (daily_revenue - LAG(daily_revenue, 7) OVER (ORDER BY order_date))
        / NULLIF(LAG(daily_revenue, 7) OVER (ORDER BY order_date), 0), 2) AS wow_change_pct
FROM daily_revenue;

9.4 间隔窗口（RANGE Window）

-- 30 天内交易总额
SELECT
    customer_id, transaction_date, amount,
    SUM(amount) OVER (
        PARTITION BY customer_id
        ORDER BY transaction_date
        RANGE BETWEEN INTERVAL '30 days' PRECEDING AND CURRENT ROW
    ) AS rolling_30d_total
FROM transactions;

9.5 累积分析

累积占比（帕累托分析）

-- 客户消费帕累托分析
SELECT
    customer_id,
    order_date,
    total,
    RANK() OVER (PARTITION BY customer_id ORDER BY total DESC) AS spend_rank,
    ROUND(100.0 * SUM(total) OVER (
        PARTITION BY customer_id
        ORDER BY total DESC
        ROWS UNBOUNDED PRECEDING
    ) / SUM(total) OVER (PARTITION BY customer_id), 2) AS cumulative_pct,
    ROUND(total - AVG(total) OVER (PARTITION BY customer_id), 2) AS deviation_from_avg
FROM orders;

首次值 / 最后一次值

-- 客户首次和最后一次下单
SELECT DISTINCT
    customer_id,
    FIRST_VALUE(order_date) OVER (
        PARTITION BY customer_id ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS first_order,
    LAST_VALUE(order_date) OVER (
        PARTITION BY customer_id ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_order,
    FIRST_VALUE(total) OVER (
        PARTITION BY customer_id ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS first_order_total
FROM orders;

9.6 同比分析（进阶）

WITH monthly_stats AS (
    SELECT
        customer_id,
        DATE_TRUNC('month', order_date) AS month,
        SUM(total) AS monthly_total,
        COUNT(*) AS order_count
    FROM orders
    GROUP BY customer_id, DATE_TRUNC('month', order_date)
),
with_lag AS (
    SELECT
        customer_id, month, monthly_total, order_count,
        LAG(monthly_total, 12) OVER (
            PARTITION BY customer_id ORDER BY month
        ) AS same_month_last_year,
        ROUND(100.0 * (
            monthly_total - LAG(monthly_total, 12) OVER (
                PARTITION BY customer_id ORDER BY month
            )
        ) / NULLIF(LAG(monthly_total, 12) OVER (
            PARTITION BY customer_id ORDER BY month
        ), 0), 2) AS yoy_growth_pct
    FROM monthly_stats
)
SELECT * FROM with_lag ORDER BY customer_id, month;

🔧 关键点：NULLIF 防止除以零错误；LAG 实现同比环比；窗口函数避免了复杂的自联结。

---

模块十：数据仓库建模

数据仓库是 BI 分析、报表系统的核心基础设施。理解维度建模是进阶数据分析师的必经之路。

10.1 星型模型（Star Schema）

-- 星型模型: 一张事实表 + 多张维度表，呈星状结构

-- 事实表 (Fact Table)
CREATE TABLE fact_orders (
    order_key       BIGINT PRIMARY KEY,       -- 代理键
    date_key        INT NOT NULL,             -- 关联日期维度
    customer_key    INT NOT NULL,             -- 关联客户维度
    product_key     INT NOT NULL,             -- 关联产品维度
    store_key       INT NOT NULL,             -- 关联门店维度
    order_quantity  INT NOT NULL,
    unit_price      DECIMAL(10, 2) NOT NULL,
    discount_pct    DECIMAL(5, 2) DEFAULT 0,
    order_total     DECIMAL(12, 2) NOT NULL,
    cost_total      DECIMAL(12, 2) NOT NULL,
    profit          DECIMAL(12, 2) NOT NULL,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_fact_date ON fact_orders (date_key);
CREATE INDEX idx_fact_customer ON fact_orders (customer_key);
CREATE INDEX idx_fact_product ON fact_orders (product_key);

-- 日期维度
CREATE TABLE dim_date (
    date_key    INT PRIMARY KEY,              -- YYYYMMDD
    full_date   DATE NOT NULL,
    year        SMALLINT NOT NULL,
    quarter     SMALLINT NOT NULL,
    month       SMALLINT NOT NULL,
    day         SMALLINT NOT NULL,
    day_of_week SMALLINT NOT NULL,
    month_name  VARCHAR(10) NOT NULL,
    is_weekend  BOOLEAN NOT NULL,
    is_holiday  BOOLEAN DEFAULT FALSE
);

-- 客户维度
CREATE TABLE dim_customer (
    customer_key  INT PRIMARY KEY,
    customer_id   VARCHAR(32) NOT NULL,        -- 业务主键
    customer_name VARCHAR(200),
    email         VARCHAR(200),
    segment       VARCHAR(50),
    region        VARCHAR(50),
    country       VARCHAR(50),
    registration_date DATE,
    is_active     BOOLEAN DEFAULT TRUE
);

-- 常用星型查询
SELECT
    d.year,
    d.month_name,
    c.segment,
    SUM(f.order_quantity) AS total_qty,
    SUM(f.order_total) AS total_revenue,
    SUM(f.profit) AS total_profit,
    ROUND(AVG(f.unit_price), 2) AS avg_price
FROM fact_orders f
JOIN dim_date d    ON f.date_key = d.date_key
JOIN dim_customer c ON f.customer_key = c.customer_key
JOIN dim_product p ON f.product_key = p.product_key
WHERE d.year = 2024 AND c.segment IS NOT NULL
GROUP BY d.year, d.month_name, c.segment
ORDER BY d.year DESC, d.month DESC, total_revenue DESC;

10.2 雪花模型 vs 星型模型

维度	星型模型	雪花模型
结构	维度表不规范化	维度表进一步规范化
JOIN 数量	少，查询简单	多，查询复杂
数据冗余	高	低
BI 工具友好度	✅ 高	⚠️ 中等
数据一致性	需额外保障	✅ 好
适用场景	现代数仓首选	数据量大且维度层级深的场景

现代数仓（ClickHouse, Snowflake, BigQuery）倾向星型模型：计算能力强，JOIN 代价低。

10.3 缓慢变化维（SCD）

类型	策略	适用场景
SCD Type 1	覆盖更新，无历史	错误修正，无历史分析需求
SCD Type 2	新增行，保留完整历史	✅ 最常见，需要追踪历史变化
SCD Type 3	增加列，保留有限历史	只需保留上一个值

SCD Type 2 完整实现

-- SCD Type 2: 保留完整历史 (新增行)
CREATE TABLE dim_customer_scd2 (
    customer_key    INT PRIMARY KEY,       -- 代理键
    customer_id     VARCHAR(32) NOT NULL,   -- 业务主键
    customer_name   VARCHAR(200),
    email           VARCHAR(200),
    segment         VARCHAR(50),
    region          VARCHAR(50),
    valid_from      TIMESTAMP NOT NULL,
    valid_to        TIMESTAMP,              -- NULL 表示当前有效
    is_current      BOOLEAN DEFAULT TRUE
);

-- 增量更新: 检测变化并创建新记录
WITH
-- 1. 获取源数据
source AS (
    SELECT customer_id, customer_name, email, segment, region
    FROM source_customers
),
-- 2. 当前有效记录
current_records AS (
    SELECT * FROM dim_customer_scd2 WHERE is_current = TRUE
),
-- 3. 检测变化
changes AS (
    SELECT
        s.customer_id, s.customer_name, s.email, s.segment, s.region,
        c.customer_key AS old_key,
        CASE
            WHEN s.customer_name != c.customer_name
              OR s.email != c.email
              OR s.segment != c.segment
              OR s.region != c.region
            THEN 'CHANGED'
            ELSE 'UNCHANGED'
        END AS change_flag
    FROM source s
    LEFT JOIN current_records c ON s.customer_id = c.customer_id
),
-- 4. 执行更新: 关闭旧记录
to_update AS (
    SELECT * FROM changes WHERE change_flag = 'CHANGED'
)
UPDATE dim_customer_scd2
SET is_current = FALSE, valid_to = CURRENT_TIMESTAMP
WHERE customer_key IN (SELECT old_key FROM to_update);

-- 查询某时刻的客户状态 (As-Of 查询)
SELECT * FROM dim_customer_scd2
WHERE customer_id = 'C001'
  AND valid_from <= '2024-06-15 00:00:00'
  AND (valid_to IS NULL OR valid_to > '2024-06-15 00:00:00');

10.4 增量加载模式

-- 基于时间戳的增量加载
-- 1. 记录上次加载时间
CREATE TABLE etl_metadata (
    table_name  VARCHAR(100) PRIMARY KEY,
    last_loaded TIMESTAMP DEFAULT '1970-01-01',
    status      VARCHAR(20) DEFAULT 'success'
);

-- 2. 增量 INSERT INTO
INSERT INTO fact_orders (order_key, date_key, customer_key, product_key,
    order_quantity, unit_price, order_total, created_at)
SELECT
    ROW_NUMBER() OVER () AS order_key,
    DATE_PART('year', o.created_at) * 10000 + DATE_PART('month', o.created_at) * 100 + DATE_PART('day', o.created_at) AS date_key,
    c.customer_key, p.product_key,
    o.quantity, o.unit_price, o.total,
    o.created_at
FROM source_orders o
JOIN dim_customer c ON o.customer_id = c.customer_id
JOIN dim_product p ON o.product_id = p.product_id
WHERE o.created_at > (SELECT last_loaded FROM etl_metadata WHERE table_name = 'fact_orders')
ON CONFLICT DO NOTHING;  -- PostgreSQL 去重

-- 3. 更新元数据
UPDATE etl_metadata
SET last_loaded = CURRENT_TIMESTAMP, status = 'success'
WHERE table_name = 'fact_orders';

---

模块十一：性能调优体系

11.1 执行计划深度解析

PostgreSQL 执行计划解读

-- 基础: EXPLAIN 与 EXPLAIN ANALYZE
EXPLAIN (FORMAT JSON, VERBOSE, ANALYZE, BUFFERS)
SELECT c.name, COUNT(o.order_id) AS order_count, SUM(o.total)
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE c.created_at >= '2024-01-01'
GROUP BY c.name;

关键执行计划模式识别：

节点类型	含义	优化建议
Seq Scan	全表扫描	大数据量时考虑加索引
Index Scan	通过索引定位	理想情况
Index Only Scan	覆盖索引，无需回表	✅ 最优
Hash Join	哈希连接	适合两表均无合适索引
Nested Loop	嵌套循环	适合小表连接大表
Sort	文件排序	考虑添加索引避免
Materialize	CTE 物化	结果被多次引用时合理

MySQL 执行计划解读

-- EXPLAIN FORMAT=TREE (MySQL 8.0.18+)
EXPLAIN FORMAT=TREE
SELECT c.name, SUM(o.total)
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE c.country = 'US'
GROUP BY c.name;

-- 输出解读:
-- -> Inner Join  (cost=2345.67 rows=1234)
--   -> Filter: (c.country = 'US')  (cost=1234.56 rows=5678)
--     -> Table scan on c  (cost=1234.56 rows=50000)
--   -> Index lookup on o using idx_customer_id (cost=1111.11 rows=3)
--       -> Using index
--
-- key 字段解读:
--   ALL: 全表扫描(最差的)
--   range: 索引范围扫描
--   ref: 通过索引列等值匹配查找
--   idx: 覆盖索引扫描

11.2 索引优化策略

索引类型与适用场景

索引类型	适用场景	示例
B-Tree	最常用，支持 =, >, <, LIKE 'prefix%'	CREATE INDEX idx ON t(a, b)
复合索引	多列联合查询	按选择性排序列顺序
部分索引	仅索引满足条件的行	WHERE status = 'ACTIVE'
唯一索引	同时保证唯一性	CREATE UNIQUE INDEX ...
GIN	JSONB、数组、全文搜索	USING GIN (metadata)
GiST	几何数据、全文搜索	USING GIST (location)
BRIN	10亿+行按物理位置聚集	USING BRIN (created_at)

复合索引设计原则

-- 查询: SELECT * FROM orders WHERE customer_id = ? AND status = ? AND order_date >= ? ORDER BY order_date DESC;

-- 多列索引 (按选择性排序)
CREATE INDEX idx_orders_customer_status_date
    ON orders (customer_id, status, order_date DESC);
-- customer_id 选择性高 -> 最先
-- status 选择性低 -> 其次(用于过滤)
-- order_date -> 最后(用于排序和范围扫描)

-- 覆盖索引 (避免回表)
CREATE INDEX idx_orders_covering
    ON orders (customer_id, status, order_date DESC, total, payment_method);

最左前缀原则：索引 (A, B, C) 可用于 (A)、(A, B)、(A, B, C)，但不可用于 (B)、(B, C)、(A, C)。

索引维护

-- PostgreSQL: 监控索引使用情况
SELECT
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS times_used,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;  -- 找出从未使用的索引

-- 修复碎片
VACUUM ANALYZE orders;
REINDEX INDEX idx_orders_customer_date;

11.3 查询重写：9 种经典模式

模式	重写前	重写后
避免 SELECT *	SELECT *	显式列名
EXISTS 替代 IN	IN (子查询)	WHERE EXISTS (...)
JOIN 替代子查询	标量子查询	LEFT JOIN + GROUP BY
CASE 替代 UNION	多重 UNION ALL	CASE WHEN
CTE 替代嵌套子查询	多层 SELECT * FROM (SELECT ...)	命名 CTE
LATERAL JOIN	相关子查询	LEFT JOIN LATERAL
INTERSECT / EXCEPT	NOT EXISTS	INTERSECT
PARTITION BY 替代自连接	自连接对比	窗口函数
CTE 分步 HAVING	复杂 HAVING	CTE + WHERE

-- 示例: 用 LATERAL JOIN 替代复杂子查询
-- ❌ 差: 相关子查询每行执行
SELECT customer_name,
    (SELECT MAX(order_date) FROM orders WHERE customer_id = c.id) AS last_order,
    (SELECT COUNT(*) FROM orders WHERE customer_id = c.id AND total > 100) AS big_orders
FROM customers c;

-- ✅ 好: 单次 LATERAL 扫描
SELECT c.customer_name, last.last_order, big.big_orders
FROM customers c
LEFT JOIN LATERAL (
    SELECT MAX(order_date) AS last_order
    FROM orders WHERE customer_id = c.id
) last ON true
LEFT JOIN LATERAL (
    SELECT COUNT(*) AS big_orders
    FROM orders WHERE customer_id = c.id AND total > 100
) big ON true;

---

模块十二：跨库兼容与迁移策略

12.1 主要数据库方言差异

特性	PostgreSQL	MySQL	SQL Server	Oracle	SQLite
数组类型	✅	❌	❌	❌	❌
JSONB	✅	✅	✅	✅	✅
递归 CTE	✅	✅ (8.0+)	✅	✅	❌
FULL OUTER JOIN	✅	❌	✅	✅	❌
分页语法	LIMIT/OFFSET	LIMIT/OFFSET	OFFSET/FETCH	ROWNUM/FETCH	LIMIT
字符串拼接	||	CONCAT	+, CONCAT	||	||
当前时间	NOW()	NOW()	GETDATE()	SYSDATE	DATETIME
自增主键	SERIAL	AUTO_INCREMENT	IDENTITY	SEQUENCE	AUTOINCREMENT

12.2 标准 SQL 兼容写法

-- 1. 分页查询 (跨库兼容)
-- PostgreSQL / MySQL / SQLite
SELECT * FROM products ORDER BY price DESC LIMIT 20 OFFSET 40;

-- SQL Server / Oracle (12c+)
SELECT * FROM products ORDER BY price DESC OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;

-- 2. NULL 处理 (所有库支持 COALESCE)
SELECT COALESCE(discount, 0) AS actual_discount FROM orders;

-- 3. CASE WHEN (所有库支持)
SELECT order_total,
    CASE WHEN order_total > 1000 THEN 'High'
         WHEN order_total > 100 THEN 'Medium'
         ELSE 'Low'
    END AS tier
FROM orders;

-- 4. 类型转换 (标准 SQL)
SELECT CAST('2024-01-01' AS DATE) AS date_val;

12.3 迁移策略

阶段	策略	说明
第 1 阶段	双写 + 一致性校验	新旧库同时写入，对比行数、SUM、抽样
第 2 阶段	增量同步	启用 binlog 同步增量数据
第 3 阶段	灰度切换	10% → 50% → 100% 逐步切换流量

---

模块十三：SQL 安全与注入防护

13.1 SQL 注入攻击向量

类型	示例输入	攻击效果
UNION 注入	' OR 1=1 --	绕过认证
延迟盲注	' AND SLEEP(5) --	通过响应时间探测
数据导出	'; LOAD_FILE('/etc/passwd') --	读取服务器文件
堆叠查询	'; DROP TABLE users; --	执行破坏性操作

13.2 参数化查询（核心防御）

# Python: psycopg2 (PostgreSQL)
cur.execute(
    "SELECT * FROM users WHERE email = %s AND status = %s",
    ("user@example.com", "active")
)

# Python: pymysql (MySQL)
cursor.execute(
    "SELECT * FROM users WHERE email = %s AND age > %s",
    ("user@example.com", 18)
)

# Python: sqlite3
cur.execute("SELECT * FROM users WHERE name = ? AND age > ?", ("John", 18))

# Node.js
const result = await client.query(
    'SELECT * FROM users WHERE email = $1 AND status = $2',
    ['user@example.com', 'active']
);

13.3 无法使用参数化的场景

场景	替代方案
动态表名	白名单验证 + 转义
动态列名（ORDER BY）	白名单
动态 IN 参数	ANY($1) / 动态占位符 / JOIN

-- 动态表名: 白名单验证
const allowedTables = ['users', 'orders', 'products', 'customers'];
if (!allowedTables.includes(table)) {
    throw new Error(`Table '${table}' is not allowed`);
}
const sql = `SELECT * FROM ${table} WHERE id = $1`;

-- 动态 ORDER BY: 白名单
const allowedSortColumns = ['name', 'email', 'created_at', 'id'];
const safeColumn = allowedSortColumns.includes(sortColumn) ? sortColumn : 'created_at';
const sql = `SELECT * FROM users ORDER BY ${safeColumn} DESC`;

13.4 纵深防御体系

第 1 层: 参数化查询 (核心防御, 不可替代)
第 2 层: ORM 框架 (自动参数化)
第 3 层: 输入验证与白名单
第 4 层: 最小权限原则
第 5 层: WAF (Web 应用防火墙)
第 6 层: 日志与监控

-- 数据库层面权限控制
CREATE ROLE app_read_only WITH LOGIN PASSWORD '***';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_read_only;

CREATE ROLE app_read_write WITH LOGIN PASSWORD '***';
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO app_read_write;
-- 明确拒绝: 不授予 DROP, TRUNCATE, CREATE, ALTER 权限

---

模块十四：数据清洗与 ETL 模式

14.1 数据去重

方法	适用数据库	说明
ROW_NUMBER	通用	用 CTE + DELETE 删除重复行
NOT EXISTS	通用	插入不重复的记录
DISTINCT ON	PostgreSQL	一次性取每组的最新记录

-- 方法 1: ROW_NUMBER 去重
WITH ranked AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY customer_id, email ORDER BY created_at DESC) AS rn
    FROM raw_customers
)
DELETE FROM raw_customers WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

-- 方法 3: DISTINCT ON (PostgreSQL)
INSERT INTO customers (customer_id, email, name, created_at)
SELECT DISTINCT ON (customer_id, email)
    customer_id, email, name, created_at
FROM raw_customers
ORDER BY customer_id, email, created_at DESC;

14.2 数据标准化

-- 1. 电话号码标准化: 去除非数字字符，统一 + 前缀
UPDATE customers
SET phone = REGEXP_REPLACE(REGEXP_REPLACE(phone, '[^0-9+]', '', 'g'), '^00', '+')
WHERE phone IS NOT NULL;

-- 2. 邮箱标准化: 转小写 + 去除多余空格
UPDATE customers
SET email = LOWER(TRIM(REGEXP_REPLACE(email, '[\t\r\n]', ' ', 'g')))
WHERE email IS NOT NULL;

-- 3. 日期格式统一: 转换为 ISO 8601
UPDATE records
SET date_val = CASE
    WHEN date_val ~ '^\d{4}-\d{2}-\d{2}' THEN date_val::DATE
    WHEN date_val ~ '^\d{2}/\d{2}/\d{4}' THEN TO_DATE(date_val, 'MM/DD/YYYY')::DATE
    WHEN date_val ~ '^\d{2}-\d{2}-\d{4}' THEN TO_DATE(date_val, 'MM-DD-YYYY')::DATE
    ELSE NULL
END;

14.3 数据验证与异常检测

-- 完整性检查
SELECT 'null_customer_id' AS check_name, COUNT(*) AS violations
FROM orders WHERE customer_id IS NULL
UNION ALL SELECT 'null_order_date', COUNT(*) FROM orders WHERE order_date IS NULL
UNION ALL SELECT 'null_total', COUNT(*) FROM orders WHERE total IS NULL;

-- 孤儿记录检测（外键一致性）
SELECT o.order_id FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
WHERE c.id IS NULL;

-- 统计异常值检测 (Z-Score)
WITH order_stats AS (
    SELECT AVG(total) AS mean, STDDEV(total) AS stddev
    FROM orders WHERE total > 0
)
SELECT o.order_id, o.total,
    ROUND(ABS(o.total - s.mean) / NULLIF(s.stddev, 0), 2) AS z_score
FROM orders o CROSS JOIN order_stats s
WHERE ABS(o.total - s.mean) > 3 * s.stddev  -- Z-score > 3 视为异常
ORDER BY z_score DESC;

-- 数据质量评分
SELECT
    'orders' AS table_name,
    'no_null_total' AS check_name,
    COUNT(*) AS total_rows,
    COUNT(*) FILTER (WHERE total IS NULL) AS violations,
    ROUND(100.0 * (1 - COUNT(*) FILTER (WHERE total IS NULL)::DECIMAL / COUNT(*)), 2) AS quality_score
FROM orders;

14.4 复杂 ETL 管道模式

-- 批量 ETL: 加载 → 清洗 → 转换 → 加载到 DW

-- 第 1 阶段: 加载原始数据到 staging
CREATE TEMP TABLE staging_orders AS
SELECT * FROM external_orders WHERE load_batch = CURRENT_DATE;

-- 第 2 阶段: 清洗与标准化
CREATE TEMP TABLE cleaned_orders AS
SELECT DISTINCT ON (order_id)
    order_id,
    LOWER(customer_email) AS customer_email,
    TO_DATE(order_date, 'YYYY-MM-DD') AS order_date,
    COALESCE(unit_price, 0) * quantity AS line_total,
    CASE WHEN total > 0 THEN total ELSE NULL END AS total,
    c.customer_key
FROM staging_orders so
LEFT JOIN dim_customer c ON LOWER(so.customer_email) = c.email
WHERE so.order_id IS NOT NULL;

-- 第 3 阶段: 增量合并到事实表
MERGE INTO fact_orders target
USING cleaned_orders source ON target.order_id = source.order_id
WHEN MATCHED THEN UPDATE SET total = source.total, updated_at = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN INSERT (order_id, customer_key, order_date, total, updated_at)
    VALUES (source.order_id, source.customer_key, source.order_date, source.total, CURRENT_TIMESTAMP);

---

企业级 SQL 开发规范与避坑指南

命名规范

对象	规范	示例
表名	小写 + 下划线，见名知意	user_orders、product_categories
列名	小写 + 下划线，见名知意	user_id、order_amount
索引	idx_ 前缀	idx_user_orders_user_id
唯一约束	uk_ 前缀	uk_users_email

🔴 性能红线（生产禁止）

1. 禁止使用 SELECT * → 明确指定列名

2. 禁止无 WHERE 条件的 UPDATE / DELETE → 先 SELECT 验证再执行

3. 禁止大表上的无索引排序、分组 → 用 EXPLAIN 确认索引

4. 禁止左模糊匹配 %xxx → 百万级以上表触发全表扫描

5. 禁止深偏移量分页直接用 LIMIT → 用书签法

6. 禁止在 WHERE 条件的字段上使用函数 → 改用范围条件

安全与质量规范

规范	说明
上线前必须 EXPLAIN	查看执行计划，确认索引使用
批量操作先 SELECT 验证	先查出影响范围，确认无误再执行变更
禁止生产库直接大表 DDL	先在测试环境验证
所有 SQL 必须经过测试环境	避免语法错误和逻辑错误上线

---

复杂场景实战案例

案例一：行转列（Pivot）

SELECT
    YEAR(order_date) AS year,
    SUM(CASE WHEN MONTH(order_date) = 1 THEN amount ELSE 0 END) AS month_01,
    SUM(CASE WHEN MONTH(order_date) = 2 THEN amount ELSE 0 END) AS month_02,
    SUM(CASE WHEN MONTH(order_date) = 3 THEN amount ELSE 0 END) AS month_03,
    SUM(CASE WHEN MONTH(order_date) = 4 THEN amount ELSE 0 END) AS month_04,
    SUM(CASE WHEN MONTH(order_date) = 5 THEN amount ELSE 0 END) AS month_05,
    SUM(CASE WHEN MONTH(order_date) = 6 THEN amount ELSE 0 END) AS month_06,
    SUM(CASE WHEN MONTH(order_date) = 7 THEN amount ELSE 0 END) AS month_07,
    SUM(CASE WHEN MONTH(order_date) = 8 THEN amount ELSE 0 END) AS month_08,
    SUM(CASE WHEN MONTH(order_date) = 9 THEN amount ELSE 0 END) AS month_09,
    SUM(CASE WHEN MONTH(order_date) = 10 THEN amount ELSE 0 END) AS month_10,
    SUM(CASE WHEN MONTH(order_date) = 11 THEN amount ELSE 0 END) AS month_11,
    SUM(CASE WHEN MONTH(order_date) = 12 THEN amount ELSE 0 END) AS month_12
FROM orders
GROUP BY YEAR(order_date)
ORDER BY year DESC;

案例二：列转行

-- 把季度销售额列转为行
SELECT year, 'Q1' AS quarter, q1_amount AS amount FROM sales_report
UNION ALL
SELECT year, 'Q2' AS quarter, q2_amount AS amount FROM sales_report
UNION ALL
SELECT year, 'Q3' AS quarter, q3_amount AS amount FROM sales_report
UNION ALL
SELECT year, 'Q4' AS quarter, q4_amount AS amount FROM sales_report;

• 原始数据：sales_report 表

year	q1_amount	q2_amount	q3_amount	q4_amount
2023	12000.00	15000.00	18000.00	22000.00
2024	15000.00	17000.00	20000.00	25000.00

• 最终执行结果

这条 SQL 的作用是列转行（Unpivot）：把原表中横向排列的 4 个季度列，转换成纵向的行数据。执行后结果如下：

year	quarter	amount
2023	Q1	12000.00
2024	Q1	15000.00
2023	Q2	15000.00
2024	Q2	17000.00
2023	Q3	18000.00
2024	Q3	20000.00
2023	Q4	22000.00
2024	Q4	25000.00

案例三：递归查询（组织架构）

WITH RECURSIVE OrgTree AS (
    -- 锚点成员: 从 CEO 开始
    SELECT id, name, manager_id, 0 AS level
    FROM employees WHERE id = 1

    UNION ALL

    -- 递归成员: 寻找下属
    SELECT e.id, e.name, e.manager_id, o.level + 1
    FROM employees e
    INNER JOIN OrgTree o ON e.manager_id = o.id
)
SELECT id, name, level FROM OrgTree ORDER BY level, id;

案例四：同环比分析

WITH monthly_sales AS (
    SELECT DATE_FORMAT(order_time, '%Y-%m') AS month,
           SUM(order_amount) AS total_amount
    FROM orders GROUP BY DATE_FORMAT(order_time, '%Y-%m')
)
SELECT
    month, total_amount,
    LAG(total_amount, 1) OVER (ORDER BY month) AS prev_month_amount,
    ROUND((total_amount - LAG(total_amount, 1) OVER (ORDER BY month))
          / NULLIF(LAG(total_amount, 1) OVER (ORDER BY month), 0) * 100, 2) AS mom_growth_pct,
    LAG(total_amount, 12) OVER (ORDER BY month) AS same_month_last_year,
    ROUND((total_amount - LAG(total_amount, 12) OVER (ORDER BY month))
          / NULLIF(LAG(total_amount, 12) OVER (ORDER BY month), 0) * 100, 2) AS yoy_growth_pct
FROM monthly_sales ORDER BY month;

---

SQL法则

#	法则	说明
1	永远不要用 SELECT *	列出每个需要的列
2	永远不要在 UPDATE/DELETE 时不用 WHERE	先用 SELECT 确认
3	日期范围永远用左闭右开	>= start AND < end，不用 BETWEEN
4	始终显式处理 NULL	用 IS NULL、COALESCE、NULLIF
5	用 EXISTS / NOT EXISTS 替代 IN / NOT IN	避免 NULL 陷阱，性能更好
6	理解 ON vs WHERE 在 JOIN 中的区别	ON 过滤右表，WHERE 过滤结果集
7	用窗口函数做高级分析	PARTITION BY、RANK、LAG/LEAD
8	用书签法分页，不要用深 OFFSET	O(log n) vs O(n)
9	写 SARGable 查询	不在列上使用函数
10	上线前必须跑 EXPLAIN	确认索引使用
11	使用 CTE 拆分复杂查询	四层管道架构
12	索引选择性高的列放前面	复合索引设计原则
13	数仓优先星型模型	减少 JOIN，BI 友好
14	参数化查询防注入	所有用户输入必须参数化
15	增量加载 + 质量校验	幂等设计，可追溯