窗口函数

第 17 课，共 21 课 · 第 4 部分 · 更强的查询 · 约 20 分钟

GROUP BY 把一组行压成一行。比如每个分类有几件商品：

1SELECT category, COUNT(*) AS products2FROM products3GROUP BY category4ORDER BY category;

10 件商品变成了 4 行，每件商品自己的名字、价格都看不到了。可有时候我们两样都想要：每件商品还是一行，旁边再写上它在整张表里的位置、它所在分类的平均价。这就是窗口函数干的事。

OVER：行不会变少

在聚合函数后面加上 OVER ()，它就不再把行压成一行，而是把算出来的值写到每一行旁边：

1SELECT name, price,2    ROUND(AVG(price) OVER (), 2) AS avg_price,3    price - ROUND(AVG(price) OVER (), 2) AS diff4FROM products5ORDER BY id;

还是 10 行。OVER () 里什么都没写，意思是「拿所有行一起算」：每一行看到的都是全部商品的平均价 19.08，于是一眼就能看出谁比平均贵、贵多少。

OVER 括号里的范围叫窗口：这一行算的时候，能「看到」哪些行。

ROW_NUMBER：排名次

ROW_NUMBER() 给每一行一个编号 1、2、3……按什么顺序编，写在 OVER (ORDER BY …) 里：

1SELECT name, price,2    ROW_NUMBER() OVER (ORDER BY price DESC) AS n3FROM products4ORDER BY n;

最贵的是 1 号。注意这里有两个 ORDER BY：OVER 里的那个决定怎么编号，最后那个决定结果按什么顺序显示。它们可以不一样，比如按价格编号、按 id 显示。

窗口函数是在 WHERE 之后、SELECT 的时候算的：先删掉不要的行，剩下的再编号。

1SELECT name,2    ROW_NUMBER() OVER (ORDER BY price DESC) AS n3FROM products4WHERE category = '数码'5ORDER BY n;

并列：RANK 和 DENSE_RANK

两行的值一样怎么办？ROW_NUMBER() 照样给不同的号，谁先谁后说不准。想让一样的值名次也一样，用 RANK() 或 DENSE_RANK()。看看每个客户下了几单：

1WITH counts AS (2    SELECT customer_id, COUNT(*) AS orders3    FROM orders4    GROUP BY customer_id5)6SELECT customer_id, orders,7    RANK() OVER (ORDER BY orders DESC) AS place,8    DENSE_RANK() OVER (ORDER BY orders DESC) AS dense_place9FROM counts10ORDER BY orders DESC, customer_id;

三个客户都是 2 单，并列第 2。区别在下一个：RANK() 跳过被占掉的名次，下一个是第 5；DENSE_RANK() 不跳，下一个是第 3。

注意： 别名不能叫 rank。在 MySQL 8 里 RANK 是保留字，AS rank 会报语法错误 ERROR 1064。换个名字，比如 place。

PARTITION BY：每组分开算

OVER (PARTITION BY category) 把行按分类分成几块，每块自己算。下面每件商品旁边是它那个分类的平均价：

1SELECT name, category, price,2    ROUND(AVG(price) OVER (PARTITION BY category), 2) AS category_avg3FROM products4ORDER BY category, price;

和开头的 GROUP BY 比一比：分组的方式一样，可行数还是 10 行。

PARTITION BY 和 ORDER BY 可以一起写：每个分类里各自排名，每块都从 1 开始编：

1SELECT category, name, price,2    ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS n3FROM products4ORDER BY category, n;

每组的第一名

想要每个分类里最贵的那件，只留 n = 1 的行就行。可直接写进 WHERE 不行：

1SELECT category, name, price2FROM products3WHERE ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) = 1;

ERROR 3593 (HY000): You cannot use the window function 'row_number' in this context.'

WHERE 比窗口函数先算（第 10 课讲过执行顺序），那时候还没有名次。办法是先用 WITH 把名次算好，再在外面筛：

1WITH ranked AS (2    SELECT category, name, price,3        ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS n4    FROM products5)6SELECT category, name, price7FROM ranked8WHERE n = 19ORDER BY category;

这个「先编号、再取第 1 名」的写法很常用：每个客户的第一单、每个月卖得最好的商品，都是这样查出来的。

累计：SUM() OVER

SUM() 加上 OVER (ORDER BY …)，算的是从第一行加到这一行，也就是累计。下面是每个订单的金额（不算取消的），和到这一单为止一共卖了多少：

1WITH totals AS (2    SELECT o.id, o.order_date, SUM(oi.quantity * oi.unit_price) AS total3    FROM orders AS o4    JOIN order_items AS oi ON oi.order_id = o.id5    WHERE o.status <> '已取消'6    GROUP BY o.id, o.order_date7)8SELECT id, order_date, total,9    SUM(total) OVER (ORDER BY order_date) AS running_total10FROM totals11ORDER BY order_date;

最后一行的 running_total 就是所有订单的总额。去掉 OVER 里的 ORDER BY，每一行就都是总额了。

窗口函数是 MySQL 8 才有的（老的 5.7 没有）；PostgreSQL、SQL Server、SQLite 也都有，写法一样。

练一练

写好查询，点检查。提示随便看，实在想不出来再看答案。OVER 里的 ORDER BY 别忘了写。

1SELECT name, salary2FROM employees;

1SELECT category, name, price2FROM products3ORDER BY category;

1SELECT customer_id, order_date2FROM orders;

这一课学会了

• 窗口函数在每一行旁边写上算出来的值，行数不变；GROUP BY 把一组压成一行。
• OVER () 拿所有行一起算；OVER (PARTITION BY 列) 每组分开算。
• ROW_NUMBER() 编号；RANK() 并列同名次、后面跳号；DENSE_RANK() 并列同名次、不跳号。
• OVER 里的 ORDER BY 决定怎么编号，最后的 ORDER BY 决定怎么显示，两个都要写。
• 窗口函数不能写在 WHERE 里：先用 WITH 算好，再在外面筛。
• SUM() OVER (ORDER BY …) 算累计。