第二十一章聚合窗口与其他函数

本章目标

通过本章学习，你将能够：

理解窗口函数的设计理念及其与 GROUP BY 聚合的本质区别
掌握 OVER() 子句的三种形式：空括号、PARTITION BY、ORDER BY
熟练使用 ROW_NUMBER、RANK、DENSE_RANK、NTILE 进行排名和分组
掌握 LAG、LEAD、FIRST_VALUE、LAST_VALUE、NTH_VALUE 进行行间计算
理解窗口框架（Window Frame）的概念和 ROWS/RANGE 的区别
使用聚合函数 + OVER 实现运行总计和移动平均
掌握 CAST/CONVERT 类型转换、加密函数、UUID 和 JSON 函数

前置知识

在学习本章之前，你需要：

熟练掌握 GROUP BY 和聚合函数（SUM、AVG、COUNT、MAX、MIN）的用法
理解基本的 SQL 排序概念（ORDER BY）
知道 JSON 数据格式的基本结构（对象 {}、数组 []、键值对）
了解 MySQL 8.0 新特性（窗口函数、JSON_TABLE 等仅在 8.0+ 可用）
理解数据类型转换的必要性

21.1 窗口函数概述

21.1.1 什么是窗口函数

窗口函数（Window Function）在保持原始行数不变的前提下，对与当前行相关的一组行（窗口）进行计算。与 GROUP BY 不同，窗口函数不会折叠行。

sql

-- 直观对比：GROUP BY vs 窗口函数
-- 数据：
-- department | employee | salary
-- IT         | Alice    | 8000
-- IT         | Bob      | 6000
-- HR         | Charlie  | 5000
-- HR         | Diana    | 7000

-- GROUP BY：每部门一行（数据被折叠）
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;
-- IT    | 7000
-- HR    | 6000

-- 窗口函数：每员工一行 + 部门平均值（原始行数保留）
SELECT department, employee, salary,
       AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;
-- IT    | Alice   | 8000 | 7000
-- IT    | Bob     | 6000 | 7000
-- HR    | Charlie | 5000 | 6000
-- HR    | Diana   | 7000 | 6000

21.1.2 窗口函数语法结构

sql

-- 完整语法：
window_function([arguments]) OVER (
    [PARTITION BY column1, column2, ...]
    [ORDER BY column3 [ASC|DESC], ...]
    [window_frame_clause]
)

-- PARTITION BY：将行分组为多个分区（类似于 GROUP BY，但保留了每行）
-- ORDER BY：在每个分区内定义行的顺序
-- window_frame_clause：定义窗口的范围（如 ROWS BETWEEN ... AND ...）

21.1.3 OVER() 的三种基本形式

sql

-- 形式一：OVER() —— 整个结果集作为一个窗口
SELECT employee, salary,
       AVG(salary) OVER () AS overall_avg
FROM employees;

-- 形式二：OVER(PARTITION BY ...) —— 按列分组
SELECT department, employee, salary,
       AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

-- 形式三：OVER(PARTITION BY ... ORDER BY ...) —— 分组 + 排序
SELECT department, employee, salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept
FROM employees;

21.2 排名函数

21.2.1 ROW_NUMBER()

为分区内的每一行分配一个唯一的连续整数（1, 2, 3, ...）。即使值相同，编号也不同（非确定性，取决于 ORDER BY）。

sql

-- 基本用法
SELECT
    department, employee, salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS overall_rank,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

-- 分页查询的优雅实现（替代 LIMIT offset）
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM orders
) AS numbered
WHERE rn BETWEEN 21 AND 30;  -- 第 3 页（每页 10 条）

-- 去重：保留每组中的第一条
SELECT * FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) AS rn
    FROM users
) AS dedup
WHERE rn = 1;  -- 每个 email 只保留最早的一条

21.2.2 RANK() 和 DENSE_RANK()

sql

-- 数据准备
-- employee | salary
-- Alice    | 10000
-- Bob      | 8000
-- Charlie  | 8000
-- Diana    | 7000
-- Eve      | 6000

SELECT
    employee, salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
    RANK()       OVER (ORDER BY salary DESC) AS rnk,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees;

-- 结果：
-- employee | salary | row_num | rnk | dense_rnk
-- Alice    | 10000  | 1       | 1   | 1
-- Bob      | 8000   | 2       | 2   | 2
-- Charlie  | 8000   | 3       | 2   | 2
-- Diana    | 7000   | 4       | 4   | 3  ← RANK 有间隙, DENSE_RANK 无间隙
-- Eve      | 6000   | 5       | 5   | 4

-- RANK()：排名有间隙（1, 2, 2, 4, 5）
-- DENSE_RANK()：排名无间隙（1, 2, 2, 3, 4）

-- 实际应用：找出部门薪资排名前 3 的员工
SELECT * FROM (
    SELECT department, employee, salary,
           DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dr
    FROM employees
) AS ranked
WHERE dr <= 3;

21.2.3 NTILE(n)

将有序分区划分为 N 个大小尽可能相等的桶，返回每个行所属的桶编号（1 到 n）。

sql

-- 将员工按薪资分为 4 个等级
SELECT
    employee, salary,
    NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;

-- 10 行数据分 4 个桶：
-- 桶 1: 3 行（编号 1-3）
-- 桶 2: 3 行（编号 4-6）
-- 桶 3: 2 行（编号 7-8）
-- 桶 4: 2 行（编号 9-10）
-- 多余的分配到前面的桶

-- 实际应用：按消费金额将客户分为高/中/低三档
SELECT user_id, total_spent,
       CASE NTILE(3) OVER (ORDER BY total_spent DESC)
           WHEN 1 THEN '高价值'
           WHEN 2 THEN '中价值'
           WHEN 3 THEN '低价值'
       END AS customer_tier
FROM user_spending;

-- 按注册时间分批处理用户（每批 1000 个）
SELECT user_id, email,
       NTILE(10) OVER (ORDER BY id) AS batch_no
FROM users;

21.3 导航函数

21.3.1 LAG(col, offset, default) 和 LEAD(col, offset, default)

LAG 向前看（取前面行的值），LEAD 向后看（取后面行的值）。

sql

-- LAG：获取前一行的值
SELECT
    order_date, daily_sales,
    LAG(daily_sales, 1) OVER (ORDER BY order_date) AS prev_day_sales,
    LAG(daily_sales, 7) OVER (ORDER BY order_date) AS same_day_last_week
FROM daily_stats;

-- LEAD：获取后一行的值
SELECT
    order_date, daily_sales,
    LEAD(daily_sales, 1) OVER (ORDER BY order_date) AS next_day_sales
FROM daily_stats;

-- 设置默认值（当没有前/后一行时）
SELECT
    employee, salary,
    LAG(salary, 1, 0)   OVER (ORDER BY salary) AS prev_salary,
    LEAD(salary, 1, 0)  OVER (ORDER BY salary) AS next_salary
FROM employees;

-- 实际应用：计算环比增长
SELECT
    order_date,
    daily_sales,
    LAG(daily_sales, 1) OVER (ORDER BY order_date) AS prev_day,
    ROUND(
        (daily_sales - LAG(daily_sales, 1) OVER (ORDER BY order_date))
        / NULLIF(LAG(daily_sales, 1) OVER (ORDER BY order_date), 0) * 100,
        2
    ) AS day_over_day_pct
FROM daily_stats;

-- 计算用户相邻订单的时间间隔
SELECT
    user_id, order_id, created_at,
    LAG(created_at) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_order_at,
    TIMESTAMPDIFF(DAY,
        LAG(created_at) OVER (PARTITION BY user_id ORDER BY created_at),
        created_at
    ) AS days_since_last_order
FROM orders;

21.3.2 FIRST_VALUE(col) 和 LAST_VALUE(col)

获取窗口帧（Window Frame）中第一个和最后一个值。

重要警告：默认窗口帧是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，因此 LAST_VALUE 默认表现与当前行相同！必须显式指定窗口帧。

sql

-- 数据准备
-- product | sale_date  | revenue
-- A       | 2024-01-01 | 100
-- A       | 2024-01-02 | 150
-- A       | 2024-01-03 | 120
-- A       | 2024-01-04 | 200

-- FIRST_VALUE：默认表现正常（返回分区第一个值）
SELECT
    product, sale_date, revenue,
    FIRST_VALUE(revenue) OVER (PARTITION BY product ORDER BY sale_date) AS first_revenue
FROM sales;
-- 每行都显示产品 A 的第一天收入：100

-- LAST_VALUE：默认行为有陷阱！
SELECT
    product, sale_date, revenue,
    LAST_VALUE(revenue) OVER (PARTITION BY product ORDER BY sale_date) AS last_revenue
FROM sales;
-- 2024-01-01: last_revenue = 100 (窗口只有当前行！)
-- 2024-01-02: last_revenue = 150 (窗口是前两行)
-- ...

-- 正确写法：扩展窗口到整个分区
SELECT
    product, sale_date, revenue,
    LAST_VALUE(revenue) OVER (
        PARTITION BY product
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_revenue
FROM sales;
-- 每行都显示产品 A 的最后一天收入：200

21.3.3 NTH_VALUE(col, n)

获取窗口帧中第 n 行的值。n 从 1 开始计数。

sql

-- 获取每个产品第二个销售额
SELECT
    product, sale_date, revenue,
    NTH_VALUE(revenue, 2) OVER (
        PARTITION BY product
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS second_revenue
FROM sales;

-- 实际应用：获取每个部门第二高薪水的员工
SELECT
    department, employee, salary,
    NTH_VALUE(employee, 2) OVER (
        PARTITION BY department
        ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS second_highest_employee
FROM employees;

21.4 窗口框架（Window Frame）

窗口框架定义了窗口函数计算的行的范围。

21.4.1 ROWS vs RANGE

sql

-- ROWS：基于物理行
-- RANGE：基于逻辑值（相同的 ORDER BY 值视为同一范围）

-- 数据：sale_date | revenue
-- 2024-01-01 | 100
-- 2024-01-02 | 100  ← 与上一条 revenue 相同
-- 2024-01-03 | 150
-- 2024-01-04 | 200

-- ROWS 示例：前 1 行 + 当前行 + 后 1 行（总共 3 行物理行）
SELECT sale_date, revenue,
       SUM(revenue) OVER (ORDER BY sale_date
           ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS rolling_sum
FROM sales;
-- 2024-01-01: 100+100 = 200
-- 2024-01-02: 100+100+150 = 350
-- 2024-01-03: 100+150+200 = 450
-- 2024-01-04: 150+200 = 350

-- RANGE 示例（相同值的行视为同一范围）
SELECT sale_date, revenue,
       SUM(revenue) OVER (ORDER BY revenue
           RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS range_sum
FROM sales;
-- 当 revenue=100 时，所有 revenue=100 的行都被纳入窗口

21.4.2 窗口边界关键字

sql

-- 窗口帧的组成部分：
-- UNBOUNDED PRECEDING   -- 分区第一行
-- N PRECEDING           -- 当前行之前 N 行
-- CURRENT ROW           -- 当前行
-- N FOLLOWING           -- 当前行之后 N 行
-- UNBOUNDED FOLLOWING   -- 分区最后一行

-- 常用模式

-- 1. 累计和（Running Total）
SELECT sale_date, revenue,
       SUM(revenue) OVER (ORDER BY sale_date
           ROWS UNBOUNDED PRECEDING) AS running_total
FROM sales;

-- 2. 移动平均（Moving Average）—— 过去 7 天的平均
SELECT sale_date, revenue,
       AVG(revenue) OVER (ORDER BY sale_date
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma_7day
FROM sales;

-- 3. 滑动窗口（Sliding Window）—— 前 2 + 当前 + 后 2
SELECT sale_date, revenue,
       AVG(revenue) OVER (ORDER BY sale_date
           ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS centered_ma
FROM sales;

-- 4. 整个分区的聚合（每行都显示完整分区结果）
SELECT sale_date, revenue,
       SUM(revenue) OVER (PARTITION BY YEAR(sale_date)) AS yearly_total,
       revenue / SUM(revenue) OVER (PARTITION BY YEAR(sale_date)) * 100 AS pct_of_year
FROM sales;

21.4.3 聚合函数与 OVER 的结合

所有聚合函数（SUM、AVG、COUNT、MAX、MIN 等）都可以与 OVER 结合使用。

sql

-- SUM + OVER：累计销售额
SELECT
    DATE_FORMAT(created_at, '%Y-%m') AS month,
    SUM(amount) AS monthly_sales,
    SUM(SUM(amount)) OVER (ORDER BY DATE_FORMAT(created_at, '%Y-%m')
                            ROWS UNBOUNDED PRECEDING) AS cumulative_sales
FROM orders
GROUP BY DATE_FORMAT(created_at, '%Y-%m');

-- AVG + OVER：3 个月移动平均
SELECT
    DATE_FORMAT(created_at, '%Y-%m') AS month,
    SUM(amount) AS monthly_sales,
    AVG(SUM(amount)) OVER (ORDER BY DATE_FORMAT(created_at, '%Y-%m')
                            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS ma_3month
FROM orders
GROUP BY DATE_FORMAT(created_at, '%Y-%m');

-- COUNT + OVER：每个类别的产品占比
SELECT
    category, product_name,
    COUNT(*) OVER (PARTITION BY category) AS category_count,
    COUNT(*) OVER () AS total_count,
    ROUND(COUNT(*) OVER (PARTITION BY category) * 100.0 / COUNT(*) OVER (), 2) AS category_pct
FROM products;

-- MAX/MIN + OVER：与全局/分组极值对比
SELECT
    department, employee, salary,
    MAX(salary) OVER (PARTITION BY department) AS dept_max,
    MIN(salary) OVER (PARTITION BY department) AS dept_min,
    salary - MIN(salary) OVER (PARTITION BY department) AS diff_from_min
FROM employees;

21.5 命名窗口（Named Windows）

MySQL 8.0+ 支持使用 WINDOW 子句为窗口定义命名，避免重复书写。

sql

-- 基本语法：WINDOW window_name AS (window_spec)

SELECT
    department, employee, salary,
    ROW_NUMBER() OVER w AS row_num,
    RANK()       OVER w AS rnk,
    SUM(salary)  OVER w AS running_total,
    LAG(salary)  OVER w AS prev_salary
FROM employees
WINDOW w AS (PARTITION BY department ORDER BY salary DESC);

-- 多个命名窗口
SELECT
    sales_date, amount,
    SUM(amount) OVER month_window AS monthly_cumulative,
    AVG(amount) OVER week_window AS weekly_moving_avg
FROM sales
WINDOW
    month_window AS (ORDER BY sales_date ROWS UNBOUNDED PRECEDING),
    week_window   AS (ORDER BY sales_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW);

-- 命名窗口可以相互引用
SELECT
    sales_date, amount,
    ROW_NUMBER() OVER w1 AS rn,
    SUM(amount)  OVER w2 AS running_total
FROM sales
WINDOW
    w1 AS (ORDER BY sales_date),
    w2 AS (w1 ROWS UNBOUNDED PRECEDING);
    -- w2 继承 w1 的 ORDER BY，追加窗口帧

21.6 类型转换函数

21.6.1 CAST(expr AS type)

sql

-- CAST 基本语法：CAST(expr AS type)
-- 支持的类型：BINARY, CHAR[(N)], DATE, DATETIME, DECIMAL[(M[,D])],
--             SIGNED [INTEGER], UNSIGNED [INTEGER], TIME, JSON, DOUBLE, FLOAT

-- 字符串 → 数字
SELECT CAST('123' AS SIGNED);                  -- 123
SELECT CAST('123.45' AS DECIMAL(10, 2));       -- 123.45
SELECT CAST('123.45' AS UNSIGNED);              -- 123（截断）

-- 数字 → 字符串
SELECT CAST(12345 AS CHAR);                    -- '12345'
SELECT CONCAT('ID:', CAST(id AS CHAR)) FROM users;

-- 日期字符串 → 日期
SELECT CAST('2024-10-15' AS DATE);             -- 2024-10-15
SELECT CAST('2024-10-15 14:30:25' AS DATETIME); -- 2024-10-15 14:30:25

-- 日期 → 数字
SELECT CAST(NOW() AS SIGNED);                  -- 20241015143025

-- 字符串 → JSON
SELECT CAST('{"name": "Alice", "age": 30}' AS JSON);

-- 实际应用：排序时字符串数字按数值排序
SELECT * FROM products
ORDER BY CAST(product_code AS UNSIGNED);
-- 确保 'P9' < 'P10' < 'P100' 而非字典序 'P10' < 'P100' < 'P9'

21.6.2 CONVERT(expr, type)

CONVERT 与 CAST 功能基本相同，但语法不同。

sql

-- CONVERT 语法：CONVERT(expr, type)
SELECT CONVERT('123', SIGNED);                 -- 123
SELECT CONVERT('2024-10-15', DATE);            -- 2024-10-15

-- CONVERT 还可以转换字符集
SELECT CONVERT('你好' USING utf8mb4);          -- 转换为 utf8mb4
SELECT CONVERT('Hello' USING latin1);          -- 转换为 latin1

21.7 加密哈希函数

sql

-- MD5(str)：128 位哈希，返回 32 位十六进制字符串
SELECT MD5('hello');             -- 5d41402abc4b2a76b9719d911017c592
SELECT MD5('hello');             -- 每次相同（确定性）
SELECT MD5('Hello');             -- 大小写敏感，结果不同

-- SHA1(str) / SHA(str)：160 位哈希，返回 40 位十六进制字符串
SELECT SHA1('hello');            -- aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d

-- SHA2(str, hash_length)：支持多种强度
-- hash_length: 224, 256, 384, 512
SELECT SHA2('hello', 256);       -- 64 位十六进制字符串
SELECT SHA2('hello', 512);       -- 128 位十六进制字符串

-- 实际应用：密码存储（生产环境应使用 bcrypt/argon2，此处仅演示）
INSERT INTO users (username, password_hash)
VALUES ('admin', SHA2(CONCAT('my_password', 'random_salt'), 256));

-- 验证密码
SELECT * FROM users
WHERE username = 'admin'
  AND password_hash = SHA2(CONCAT('my_password', 'random_salt'), 256);

-- 数据完整性校验
SELECT
    id,
    SHA2(CONCAT(id, name, email, phone), 256) AS row_hash
FROM users;

-- 注意：
-- 1. MD5 和 SHA1 已不再安全，生产环境应使用 SHA2 或更强的算法
-- 2. 密码应加盐（salt）后再哈希
-- 3. MySQL 的加密函数不适用于密码存储（无 bcrypt/scrypt），应在应用层处理

21.8 UUID 函数

sql

-- UUID()：生成 UUID v1（基于时间戳 + MAC 地址）
SELECT UUID();
-- 结果示例: 123e4567-e89b-12d3-a456-426614174000

-- UUID 格式：xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
-- 第 4 段第一个字符表示版本（本例为 '1' = 版本 1）

-- UUID_SHORT()：生成 64 位自增整数
SELECT UUID_SHORT();
-- 结果示例: 99376042898341888

-- UUID vs UUID_SHORT 比较
SELECT UUID(), UUID_SHORT();

-- UUID_SHORT 特点：
-- 1. 值单调递增（基于 server_id + 启动时间 + 自增计数器）
-- 2. 比 UUID() 更小（8 字节 vs 36 字符）
-- 3. 适合作为 InnoDB 主键而不引起严重页分裂

-- 实际应用：生成唯一标识符
CREATE TABLE orders (
    id BINARY(16) PRIMARY KEY,
    order_uuid VARCHAR(36) DEFAULT (UUID()),
    -- ...
);

INSERT INTO orders (id, order_uuid)
VALUES (UUID_TO_BIN(UUID()), UUID());

-- UUID 存储优化（使用 BINARY(16) 而非 VARCHAR(36)）
-- UUID_TO_BIN：将 UUID 字符串转为 16 字节二进制
-- BIN_TO_UUID：将 16 字节二进制转回 UUID 字符串
SELECT UUID_TO_BIN('123e4567-e89b-12d3-a456-426614174000');
SELECT BIN_TO_UUID(UUID_TO_BIN(UUID()));

21.9 JSON 函数

21.9.1 JSON 路径语法

sql

-- JSON 路径使用 $ 表示根，用 . 或 [] 访问子元素
-- $           -- JSON 文档根
-- $.key       -- 对象的 key 属性
-- $[n]        -- 数组的第 n 个元素（从 0 开始）
-- $.*         -- 对象的所有值（通配符）
-- $**.key     -- 递归搜索所有 key 属性

21.9.2 JSON 提取函数

sql

-- 准备测试数据
SET @json = '{
    "name": "Alice",
    "age": 30,
    "skills": ["MySQL", "Python", "Java"],
    "address": {
        "city": "Beijing",
        "zip": "100000"
    }
}';

-- JSON_EXTRACT(doc, path)：提取 JSON 值（保留 JSON 类型）
SELECT JSON_EXTRACT(@json, '$.name');               -- "Alice"（带引号）
SELECT JSON_EXTRACT(@json, '$.age');                -- 30
SELECT JSON_EXTRACT(@json, '$.skills[0]');           -- "MySQL"
SELECT JSON_EXTRACT(@json, '$.address.city');        -- "Beijing"

-- -> 操作符：等价于 JSON_EXTRACT（保留引号）
SELECT @json -> '$.name';                            -- "Alice"

-- ->> 操作符：等价于 JSON_UNQUOTE(JSON_EXTRACT(...))（去除引号，转为字符串）
SELECT @json ->> '$.name';                           -- Alice（无引号）
SELECT @json ->> '$.age';                            -- 30（作为字符串）
SELECT @json ->> '$.skills[0]';                      -- MySQL

-- 实际应用：从 JSON 列中提取值
SELECT
    id,
    extra_data ->> '$.source' AS source,
    extra_data ->> '$.campaign' AS campaign,
    CAST(extra_data ->> '$.amount' AS DECIMAL(10,2)) AS amount
FROM orders
WHERE extra_data ->> '$.source' = 'app';

21.9.3 JSON 修改函数

sql

SET @json = '{"name": "Alice", "age": 30}';

-- JSON_SET：更新已有键或添加新键
SELECT JSON_SET(@json, '$.name', 'Bob', '$.city', 'Beijing');
-- 结果: {"name": "Bob", "age": 30, "city": "Beijing"}

-- JSON_INSERT：仅添加新键（已存在则忽略）
SELECT JSON_INSERT(@json, '$.name', 'Bob', '$.city', 'Beijing');
-- 结果: {"name": "Alice", "age": 30, "city": "Beijing"}（name 未被修改）

-- JSON_REPLACE：仅更新已有键（不存在则忽略）
SELECT JSON_REPLACE(@json, '$.name', 'Bob', '$.city', 'Beijing');
-- 结果: {"name": "Bob", "age": 30}（city 未被添加）

-- JSON_REMOVE：删除键
SELECT JSON_REMOVE(@json, '$.age');
-- 结果: {"name": "Alice"}

-- JSON_ARRAY_APPEND：向数组追加元素
SET @arr = '["a", "b"]';
SELECT JSON_ARRAY_APPEND(@arr, '$', 'c');
-- 结果: ["a", "b", "c"]

21.9.4 JSON 构造与查询函数

sql

-- JSON_ARRAY：构造 JSON 数组
SELECT JSON_ARRAY(1, 'text', NULL, NOW());
-- 结果: [1, "text", null, "2024-10-15 14:30:25.000000"]

-- JSON_OBJECT：构造 JSON 对象
SELECT JSON_OBJECT('name', 'Alice', 'age', 30, 'active', TRUE);
-- 结果: {"name": "Alice", "age": 30, "active": true}

-- JSON_CONTAINS：检查 JSON 是否包含某个值
SET @skills = '["MySQL", "Python", "Java"]';
SELECT JSON_CONTAINS(@skills, '"MySQL"');            -- 结果: 1 (true)
SELECT JSON_CONTAINS(@skills, '"Go"');               -- 结果: 0 (false)

-- 检查包含关系
SET @doc = '{"skills": ["MySQL", "Python"]}';
SELECT JSON_CONTAINS(@doc, '"MySQL"', '$.skills');   -- 结果: 1

-- JSON_KEYS：获取 JSON 对象的所有键
SELECT JSON_KEYS('{"name": "Alice", "age": 30}');
-- 结果: ["name", "age"]

-- JSON_PRETTY：格式化 JSON
SELECT JSON_PRETTY('{"a":1,"b":[2,3,4],"c":{"d":5}}');
/*
{
  "a": 1,
  "b": [2, 3, 4],
  "c": {
    "d": 5
  }
}
*/

-- JSON_TYPE：获取 JSON 值的类型
SELECT JSON_TYPE('{"a": 1}');      -- OBJECT
SELECT JSON_TYPE('[1, 2, 3]');     -- ARRAY
SELECT JSON_TYPE('"hello"');       -- STRING
SELECT JSON_TYPE('123');           -- INTEGER

-- JSON_VALID：检查字符串是否为有效 JSON
SELECT JSON_VALID('{"a": 1}');     -- 1 (valid)
SELECT JSON_VALID('{invalid}');    -- 0 (invalid)

21.9.5 JSON 与关系表的互转

sql

-- JSON → 行：JSON_TABLE()（MySQL 8.0.4+）
SET @data = '[
    {"name": "Alice", "age": 30},
    {"name": "Bob", "age": 25},
    {"name": "Charlie", "age": 35}
]';

SELECT jt.*
FROM JSON_TABLE(
    @data,
    '$[*]' COLUMNS (
        name VARCHAR(50) PATH '$.name',
        age  INT         PATH '$.age'
    )
) AS jt;
-- 结果：
-- name    | age
-- Alice   | 30
-- Bob     | 25
-- Charlie | 35

-- 行 → JSON：JSON_ARRAYAGG()（MySQL 8.0.14+）
SELECT JSON_ARRAYAGG(
    JSON_OBJECT('name', name, 'age', age)
) AS json_result
FROM users WHERE age > 25;
-- 将查询结果聚合为一个 JSON 数组

-- JSON_OBJECTAGG：将键值对聚合成 JSON 对象
SELECT JSON_OBJECTAGG(name, age) AS json_result
FROM users;
-- 结果: {"Alice": 30, "Bob": 25, "Charlie": 35}

21.10 IP 地址函数

sql

-- INET_ATON(ip)：将点分十进制 IP 转为整数
SELECT INET_ATON('192.168.1.1');          -- 结果: 3232235777
SELECT INET_ATON('10.0.0.1');             -- 结果: 167772161

-- INET_NTOA(num)：将整数转回 IP 字符串
SELECT INET_NTOA(3232235777);             -- 结果: 192.168.1.1
SELECT INET_NTOA(167772161);              -- 结果: 10.0.0.1

-- 实际应用：IP 范围查询（比字符串比较高效得多！）
-- 查询 IP 在 192.168.1.0/24 网段内的所有记录
SELECT ip_address
FROM access_logs
WHERE INET_ATON(ip_address) BETWEEN INET_ATON('192.168.1.0')
                                 AND INET_ATON('192.168.1.255');

-- 创建索引优化 IP 范围查询
ALTER TABLE access_logs ADD COLUMN ip_int INT UNSIGNED;
UPDATE access_logs SET ip_int = INET_ATON(ip_address);
CREATE INDEX idx_ip_int ON access_logs(ip_int);

-- 注意：INET_ATON 仅支持 IPv4，IPv6 使用 INET6_ATON/INET6_NTOA
SELECT INET6_ATON('::1');                 -- IPv6 转二进制
SELECT INET6_NTOA(INET6_ATON('::1'));     -- 转回 IPv6 字符串

常见错误

LAST_VALUE 默认行为不符合预期
- 错误：SELECT LAST_VALUE(salary) OVER (PARTITION BY dept ORDER BY salary) FROM emp; 显示的不是分区最后的 salary。
- 原因：默认窗口帧是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，所以 LAST_VALUE 就是当前行的值。
- 解决：显式指定 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。
RANK vs DENSE_RANK vs ROW_NUMBER 混淆
- 错误：使用 ROW_NUMBER 来排名，但期望相同值有相同排名。
- 原因：ROW_NUMBER 给每行唯一编号，不处理并列情况。
- 解决：需要处理并列时使用 RANK（有间隙）或 DENSE_RANK（无间隙）。
NULL 在窗口函数中的排序位置
- 错误：ORDER BY col ASC 时 NULL 排在最前面（MySQL 默认），导致窗口计算结果不符合预期。
- 解决：使用 ORDER BY col ASC NULLS LAST（MySQL 8.0 不支持 NULLS LAST 语法，需用 ORDER BY CASE WHEN col IS NULL THEN 1 ELSE 0 END, col ASC）。
JSON_EXTRACT 和 ->> 的返回类型混淆
- 错误：使用 ->> 提取数值后直接参与数值运算，可能遇到隐式转换问题。
- 原因：->> 返回字符串，-> 返回 JSON 类型。
- 解决：数值运算时使用 CAST(doc->>'$.field' AS type) 进行显式转换。
在非 8.0 版本使用窗口函数
- 错误：在 MySQL 5.7 中使用 ROW_NUMBER() 等窗口函数。
- 原因：窗口函数是 MySQL 8.0 引入的特性。
- 解决：升级到 MySQL 8.0+，或使用变量模拟（@row_num := @row_num + 1）。

本章练习

排名与分页：基于员工表（department, name, salary），使用窗口函数实现：每个部门内薪资排名（并列处理）、薪资最高的前 3 名（每部门）、全公司薪资排名、按薪资分为 5 个等级（NTILE）。
销售趋势分析：基于每日销售表（sale_date, amount），使用窗口函数计算：每日累计销售额、7 日移动平均、日环比增长率、与去年同期相比的增长率（使用 LAG 获取 365 天前的数据）。
用户行为分析：基于订单表（user_id, order_id, created_at, amount），使用窗口函数计算：每个用户的订单序号、相邻订单的时间间隔、首末订单信息（FIRST_VALUE/LAST_VALUE）、用户生命周期内的累计消费额。
JSON 数据操作：创建一个包含 JSON 列的表（存储用户偏好设置），练习 JSON_EXTRACT、JSON_SET、JSON_INSERT、JSON_REPLACE、JSON_REMOVE、JSON_CONTAINS 等函数的实际应用，并尝试使用 JSON_TABLE 将 JSON 数据展开为关系表。
类型转换与 IP 查询：在 IP 访问日志表中，使用 INET_ATON 将 IP 字符串转为整数并建立索引，然后编写查询找出某 IP 范围内的所有访问记录。同时使用 CAST/CONVERT 进行类型转换的综合练习。
窗口函数陷阱练习：故意使用默认窗口帧的 LAST_VALUE，观察"错误"的输出。然后修正为正确的窗口帧设置。比较 ROWS 和 RANGE 在遇到重复值时的不同表现。

第二十一章 聚合窗口与其他函数 ​

本章目标 ​

前置知识 ​

21.1 窗口函数概述 ​

21.1.1 什么是窗口函数 ​

21.1.2 窗口函数语法结构 ​

21.1.3 OVER() 的三种基本形式 ​

21.2 排名函数 ​

21.2.1 ROW_NUMBER() ​

21.2.2 RANK() 和 DENSE_RANK() ​

21.2.3 NTILE(n) ​

21.3 导航函数 ​

21.3.1 LAG(col, offset, default) 和 LEAD(col, offset, default) ​

21.3.2 FIRST_VALUE(col) 和 LAST_VALUE(col) ​

21.3.3 NTH_VALUE(col, n) ​

21.4 窗口框架（Window Frame） ​

21.4.1 ROWS vs RANGE ​

21.4.2 窗口边界关键字 ​

21.4.3 聚合函数与 OVER 的结合 ​

21.5 命名窗口（Named Windows） ​

21.6 类型转换函数 ​

21.6.1 CAST(expr AS type) ​

21.6.2 CONVERT(expr, type) ​

21.7 加密哈希函数 ​

21.8 UUID 函数 ​

21.9 JSON 函数 ​

21.9.1 JSON 路径语法 ​

21.9.2 JSON 提取函数 ​

21.9.3 JSON 修改函数 ​

21.9.4 JSON 构造与查询函数 ​

21.9.5 JSON 与关系表的互转 ​

21.10 IP 地址函数 ​

常见错误 ​

本章练习 ​