分步骤的查询与跨行计算：CTE 和窗口函数

CTE 可以把长查询拆成有名称的步骤。窗口函数不减少结果行数，也能计算排名、累计值和相邻行的值。

WITH：带名字的派生表

上一课结束在一个嵌在 FROM 里的派生表。同一条查询写成公用表表达式是这样：

1

WITH category_stats AS (
  SELECT cat.name AS category,
         COUNT(*) AS products,
         ROUND(AVG(p.price), 2) AS average
  FROM products p
  JOIN categories cat ON cat.id = p.category_id
  GROUP BY cat.id
)
SELECT *
FROM category_stats
WHERE products >= 10
ORDER BY average DESC;
在 Planche 中运行

结果 — 共 26 行，这里是前 5 行

categoryproductsaverage
Wearables111672.29
Programming101585.67
Tablets171529.45
Audio111482.89
Kitchen121443.21

浏览器会另开一个标签页打开 Planche，示例数据库已经载入，这条查询也已经放好。本页其他示例都会复用同一个标签页；在你按下 F9 之前，什么都不会执行。

结果完全一样，但现在是往前读的：先定义 category_stats，再用它。这个名字只在这一条语句里有效——什么都没被创建，也不需要收拾。

一步接一步

CTE 可以串联，后面的能用前面的，于是嵌套查询变成了一串有名字的步骤。

2

WITH paid_orders AS (
  SELECT id, customer_id, total
  FROM orders
  WHERE status IN ('paid', 'shipped', 'delivered')
),
per_customer AS (
  SELECT customer_id,
         COUNT(*) AS orders,
         ROUND(SUM(total), 2) AS revenue
  FROM paid_orders
  GROUP BY customer_id
)
SELECT c.first_name || ' ' || c.last_name AS customer,
       c.country,
       pc.orders,
       pc.revenue
FROM per_customer pc
JOIN customers c ON c.id = pc.customer_id
ORDER BY pc.revenue DESC
LIMIT 20;
在 Planche 中运行

结果 — 共 20 行，这里是前 5 行

customercountryordersrevenue
Sofia PatelBR1070324.42
Zara NovakJP570149.53
Elena RossiKR1058867.23
Ines ChoiCA658455.76
Noah CostaES457721.52

三个有名字的步骤：先缩小订单范围，再按客户汇总，最后接上客户姓名。写成嵌套子查询就得从里往外读；这样写则是按发生顺序排列的一串步骤。查询开始难跟了，先试着拆成 CTE，通常是最有效的一招。

窗口函数：聚合但不压缩

GROUP BY 把多行变成一行。窗口函数在一批行上做计算，同时保留每一行。

3

SELECT p.name,
       p.price,
       RANK()       OVER (ORDER BY p.price DESC) AS price_rank,
       DENSE_RANK() OVER (ORDER BY p.price DESC) AS dense_price_rank,
       ROW_NUMBER() OVER (ORDER BY p.price DESC) AS row_no
FROM products p
ORDER BY p.price DESC
LIMIT 20;
在 Planche 中运行

结果 — 共 20 行，这里是前 5 行

namepriceprice_rankdense_price_rankrow_no
Glide GPS Watch Pro2484.4111
Terra 3D Puzzle2481.77222
Aria Castle Kit2479.41333
Aria LED Strip Pro2463.41444
Nova Brief Answers2445.49555

OVER (ORDER BY p.price DESC) 就是窗口：这次计算要看哪些行、按什么顺序看。价格打平时，三个函数给出三种答案：

  • ROW_NUMBER() — 1、2、3、4。永远互不相同，平局随便断。
  • RANK() — 1、2、2、4。平局共用一个名次，下一个数字会跳过去。
  • DENSE_RANK() — 1、2、2、3。平局共用名次，数字不跳。

用哪个取决于你的问题。"给我正好十行"要 ROW_NUMBER；"谁是第二名"要的是两种排名之一。

每组前 N 名

没有窗口函数的话，这需要一条相关子查询去数同分类里有多少商品更贵。PARTITION BY 会为每一组重新开始编号。

4

WITH ranked AS (
  SELECT cat.name AS category,
         p.name AS product,
         p.price,
         ROW_NUMBER() OVER (PARTITION BY cat.id ORDER BY p.price DESC) AS rank_in_category
  FROM products p
  JOIN categories cat ON cat.id = p.category_id
)
SELECT category, product, price
FROM ranked
WHERE rank_in_category <= 3
ORDER BY category, rank_in_category;
在 Planche 中运行

结果 — 共 81 行，这里是前 5 行

categoryproductprice
AudioAtlas Earbuds2334.28
AudioKite Speaker2331.15
AudioQuill Speaker2177.84
BagsPulse Crossbody Mini2377
BagsZen Crossbody2295.35

累计值

给窗口加上 ORDER BY，每一行就能看到直到它自己为止的全部。

5

WITH monthly AS (
  SELECT substr(ordered_at, 1, 7) AS month,
         ROUND(SUM(total), 2) AS revenue
  FROM orders
  WHERE status IN ('paid', 'shipped', 'delivered')
    AND ordered_at >= '2025-01-01'
  GROUP BY month
)
SELECT month,
       revenue,
       ROUND(SUM(revenue) OVER (ORDER BY month), 2) AS running_total
FROM monthly
ORDER BY month;
在 Planche 中运行

结果 — 共 12 行，这里是前 5 行

monthrevenuerunning_total
2025-01311707.73311707.73
2025-02350775.62662483.35
2025-03351184.961013668.31
2025-04374465.941388134.25
2025-05381541.091769675.34

CTE 产出每月一行，SUM(revenue) OVER (ORDER BY month) 沿着这张列表往下累加。同一招还能做累计计数、累计百分比和燃尽图——凡是某一行的答案依赖它前面那些行的，都是这个形状。

和上一行比较

6

WITH monthly AS (
  SELECT substr(ordered_at, 1, 7) AS month,
         ROUND(SUM(total), 2) AS revenue
  FROM orders
  WHERE status IN ('paid', 'shipped', 'delivered')
    AND ordered_at >= '2025-01-01'
  GROUP BY month
)
SELECT month,
       revenue,
       LAG(revenue) OVER (ORDER BY month) AS previous_month,
       ROUND(revenue - LAG(revenue) OVER (ORDER BY month), 2) AS change
FROM monthly
ORDER BY month;
在 Planche 中运行

结果 — 共 12 行，这里是前 5 行

monthrevenueprevious_monthchange
2025-01311707.73NULLNULL
2025-02350775.62311707.7339067.89
2025-03351184.96350775.62409.34
2025-04374465.94351184.9623280.98
2025-05381541.09374465.947075.15

LAG(revenue) 在窗口里往回够一行，LEAD 往前够一行。本来需要把表按偏移量和自己连接才能算的环比，现在一行就够了。第一行的 previous_month 是 NULL——它前面什么都没有——所以变化也是 NULL，这比一个会误导人的零要诚实。

支持情况

窗口函数是标准特性，你可能遇到的引擎基本都有：PostgreSQL、SQL Server 和 Oracle 多年前就有了，SQLite 从 3.25（2018）起，MySQL 从 8.0（2018）起，MariaDB 从 10.2 起。你还可能碰到的、没有窗口函数的，大概只剩 MySQL 5.7。

现在你能做什么

读数据方面，你基本能把结果整成任何需要的形状了。最后两课讲的是工作的另一半：安全地改数据，以及查清一条查询为什么慢。

自己动手写

  1. 每个国家里，忠诚度积分最高的那一位客户是谁？

    查看答案

    查看答案

    WITH ranked AS (
  SELECT country,
         first_name || ' ' || last_name AS customer,
         loyalty_points,
         ROW_NUMBER() OVER (PARTITION BY country ORDER BY loyalty_points DESC) AS rn
  FROM customers
)
SELECT country, customer, loyalty_points
FROM ranked
WHERE rn = 1
ORDER BY loyalty_points DESC;
    在 Planche 中运行

    结果 — 共 12 行，这里是前 5 行

    countrycustomerloyalty_points
    FRMinjun Meyer5000
    CAHugo Sato4994
    JPLina Smith4992
    KRLucas Ahmed4992
    AUSana Ahmed4988

    和课上一样的"每组前 N 名"形状，只是把 <= 3 换成 rn = 1。注意 ROW_NUMBER 会随便断平局；如果并列时希望两个人都返回，就改用 RANK。

  2. 2025 年每月的订单数，旁边再放一列订单数的累计值。

    查看答案

    查看答案

    WITH monthly AS (
  SELECT substr(ordered_at, 1, 7) AS month, COUNT(*) AS orders
  FROM orders
  WHERE ordered_at >= '2025-01-01'
  GROUP BY month
)
SELECT month,
       orders,
       SUM(orders) OVER (ORDER BY month) AS running_orders
FROM monthly
ORDER BY month;
    在 Planche 中运行

    结果 — 共 12 行，这里是前 5 行

    monthordersrunning_orders
    2025-017474
    2025-0272146
    2025-0384230
    2025-0470300
    2025-0581381

    先用 CTE 算出每月计数，再在它上面用窗口函数。任何"累计"列都是这个形状：先聚合，再 SUM(...) OVER (ORDER BY ...)。