CTE、窗口函数与 JSON 关系化

← 本站索引 ｜ 练习答案

可运行 SQL

USE mysql_course;

WITH RECURSIVE numbers(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 5
)
SELECT n FROM numbers ORDER BY n;

SELECT
  order_id,
  customer_id,
  total_amount,
  SUM(total_amount) OVER (
    PARTITION BY customer_id
    ORDER BY created_at, order_id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM sales_order;

SELECT jt.sku, jt.quantity
FROM JSON_TABLE(
  '[{"sku":"BOOK-001","quantity":2},{"sku":"MUG-001","quantity":1}]',
  '$[*]' COLUMNS (
    sku VARCHAR(40) PATH '$.sku',
    quantity INT PATH '$.quantity'
  )
) AS jt;

机制解释

递归 CTE 由锚点和递归成员组成，终止条件决定能否收敛。窗口函数在不折叠明细行的情况下计算分区结果；显式写 ROWS frame 可避免同值排序键导致默认 RANGE frame 一次纳入多行。

JSON_TABLE 把文档数组投影成关系行，便于约束、连接和验证。稳定、频繁查询的属性仍更适合普通列；必要时使用生成列或函数索引，并测量写入代价。

真实边界

• 递归层级需要防环和深度预算。
• 排名必须有稳定的最终排序键。
• JSON 路径缺失、类型不符和无效文档要有错误契约。
• “更短的 SQL”不等于“更低的执行成本”，仍需看实际计划。

练习

1. 取每位客户金额最高的三笔订单，明确并列是否占名额。
2. 计算客户累计订单金额，验证相同 created_at 时 frame 的差异。
3. 将 JSON 数组关系化后与 product 按 sku 连接，找出不存在的商品。

验收清单

• 递归查询有终止条件、顺序和深度边界。
• 排名明确并列规则并使用稳定排序键。
• 窗口 frame 与 JSON 路径的选择可由反例验证。

完成后核对答案

深度加餐：现代 SQL 让中间状态显式化

CTE、窗口函数和 JSON 各自解决不同问题，不要因为语法新就把所有逻辑塞进一条巨型查询。

先做一个可观察实验

WITH ranked AS (
  SELECT customer_id, order_no, total,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY placed_at DESC, id DESC
         ) AS rn
  FROM orders
)
SELECT customer_id, order_no, total
FROM ranked
WHERE rn <= 3;

预期：每位客户取最近三笔订单；窗口函数保留行，不像 GROUP BY 那样把粒度压扁。

原理下潜与工程边界

CTE 提升命名与复用，优化器可能合并或物化；窗口函数按分区排序并维护 frame；JSON 列适合半结构化扩展，核心筛选字段仍应关系化并建索引。

窗口 ORDER BY 不稳定会导致排名抖动；JSON_EXTRACT 返回 JSON 值，比较与类型转换要明确；CTE 不是自动更快，EXPLAIN 才能证明。

反例排错路线

先确认每一层的行粒度，再看 EXPLAIN 的临时表、排序与物化；JSON 查询慢时检查生成列/函数索引和数据分布。

练习加码（仍不提供答案）

加码：从订单 JSON 元数据中抽取渠道，按客户取最近三次来源，并比较 JSON 路径过滤与生成列索引的计划。

验收时请补充：一份可复现的环境/数据前提、正常结果、失败结果以及可回滚或清理动作。

继续查阅：MySQL 8.4 参考手册（外部资料已收录于证据账本） · InnoDB 文档（外部资料已收录于证据账本）