公用表表达式

可代替子查询，适用范围更广泛，会话范围

窗口函数

类似聚合函数
分为静态窗口函数和动态窗口函数

举个例子对比一下

需求：现在计算这个网站在每个城市的销售总额、在全国的销售总额、每个区的销售额占所在城市销售额中的比率，以及占总销售额中的比率。
实现传统方法

#计算总销售金额，并存入临时表 a：
CREATE TEMPORARY TABLE a -- 创建临时表
SELECT SUM(sales_value) AS sales_value -- 计算总计金额
FROM sales;

#计算每个城市的销售总额并存入临时表 b：
CREATE TEMPORARY TABLE b -- 创建临时表
SELECT city,SUM(sales_value) AS sales_value -- 计算城市销售合计
FROM sales
GROUP BY city;

#计算各区的销售占所在城市的总计金额的比例，和占全部销售总计金额的比例。我们可以通过下面的连接查询获得需要的结果：
SELECT s.city AS 城市,s.county AS 区,s.sales_value AS 区销售额,
b.sales_value AS 市销售额,s.sales_value/b.sales_value AS 市比率,
a.sales_value AS 总销售额,s.sales_value/a.sales_value AS 总比率
FROM sales s
JOIN b ON (s.city=b.city) -- 连接市统计结果临时表
JOIN a -- 连接总计金额临时表
ORDER BY s.city,s.county;

假如使用窗口函数？

SELECT city AS 城市,county AS 区,sales_value AS 区销售额,
SUM(sales_value) OVER(PARTITION BY city) AS 市销售额, -- 计算市销售额
sales_value/SUM(sales_value) OVER(PARTITION BY city) AS 市比率,
SUM(sales_value) OVER() AS 总销售额, -- 计算总销售额
sales_value/SUM(sales_value) OVER() AS 总比率
FROM sales
ORDER BY city,county;

可以简化很多~组内对比返回单行结果

很显然，在这种需要用到分组统计的结果对每一条记录进行计算的场景下，使用窗口函数更好。

介绍

窗口函数总体上可以分为序号函数、分布函数、前后函数、首尾函数和其他函数

语法结构

函数 OVER（[PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC]）

函数 OVER 窗口名 … WINDOW 窗口名 AS （[PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC]）

OVER 关键字指定函数窗口的范围。

如果省略后面括号中的内容，则窗口会包含满足WHERE条件的所有记录，窗口函数会基于所
有满足WHERE条件的记录进行计算。
如果OVER关键字后面的括号不为空，则可以使用如下语法设置窗口。

窗口名：为窗口设置一个别名，用来标识窗口。

PARTITION BY子句：指定窗口函数按照哪些字段进行分组。分组后，窗口函数可以在每个分组中分别执行。

ORDER BY子句：指定窗口函数按照哪些字段进行排序。执行排序操作使窗口函数按照排序后的数据记录的顺序进行编号。

FRAME子句：为分区中的某个子集定义规则，可以用来作为滑动窗口使用。

分类讲解

序号函数

ROW_NUMBER()函数

举例：查询 goods 数据表中每个商品分类下价格降序排列的各个商品信息。

SELECT ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,
id, category_id, category, NAME, price, stock
FROM goods;

举例：查询 goods 数据表中每个商品分类下价格最高的3种商品信息。

SELECT *
FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num, id, category_id, category, NAME, price, stock
FROM goods) t
WHERE row_num <= 3;

RANK()函数

举例：使用RANK()函数获取 goods 数据表中各类别的价格从高到低排序的各商品信息
这种方式下标准字段一样但排序号一致！

SELECT RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num, 
id, category_id, category, NAME, price, stock
FROM goods;

举例：使用RANK()函数获取 goods 数据表中类别为“女装/女士精品”的价格最高的4款商品信息。

SELECT *
FROM(
SELECT RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num, 
id, category_id, category, NAME, price, stock
FROM goods) t
WHERE category_id = 1 AND row_num <= 4;

举例：使用RANK()函数获取 goods 数据表中类别为“女装/女士精品”的价格最高的4款商品信息。

SELECT *
FROM(
SELECT RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num, 
id, category_id, category, NAME, price, stock
FROM goods) t
WHERE category_id = 1 AND row_num <= 4;

DENSE_RANK()函数

DENSE_RANK()函数对序号进行并列排序，并且不会跳过重复的序号，比如序号为1、1、2。

举例：使用DENSE_RANK()函数获取 goods 数据表中各类别的价格从高到低排序的各商品信息。

SELECT DENSE_RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS
row_num,
id, category_id, category, NAME, price, stock
FROM goods;

分布函数

PERCENT_RANK()函数

PERCENT_RANK()函数是等级值百分比函数。按照如下方式进行计算。

(rank - 1) / (rows - 1)

其中，rank的值为使用RANK()函数产生的序号，rows的值为当前窗口的总记录数。

举例：计算 goods 数据表中名称为“女装/女士精品”的类别下的商品的PERCENT_RANK值

#写法一：
SELECT RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS r,
PERCENT_RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS pr, 
id, category_id, category, NAME, price, stock
FROM goods
WHERE category_id = 1;

#写法二：

SELECT RANK() OVER w AS r,
PERCENT_RANK() OVER w AS pr,
id, category_id, category, NAME, price, stock
FROM goods
WHERE category_id = 1 WINDOW w AS (PARTITION BY category_id ORDER BY price DESC);

CUME_DIST()函数

CUME_DIST()函数主要用于查询小于或等于某个值的比例。

举例：查询goods数据表中小于或等于当前价格的比例。

SELECT CUME_DIST() OVER(PARTITION BY category_id ORDER BY price ASC) AS cd,
id, category, NAME, price
FROM goods;

前后函数

LAG(expr,n)函数

LAG(expr,n)函数返回当前行的前n行的expr的值

举例：查询goods数据表中前一个商品价格与当前商品价格的差值。

SELECT id, category, NAME, price, pre_price, price - pre_price AS diff_price
FROM (
#子查询，上一行数据
SELECT id, category, NAME, price,LAG(price,1) OVER w AS pre_price
FROM goods
WINDOW w AS (PARTITION BY category_id ORDER BY price)) t;

LEAD(expr,n)函数

LEAD(expr,n)函数返回当前行的后n行的expr的值。

SELECT id, category, NAME, behind_price, price,behind_price - price AS diff_price
FROM(
#子查询
SELECT id, category, NAME, price,LEAD(price, 1) OVER w AS behind_price
FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price)) t;

首尾函数

FIRST_VALUE(expr)函数

FIRST_VALUE(expr)函数返回第一个expr的值。

举例：按照价格排序，查询第1个商品的价格信息。

SELECT id, category, NAME, price, stock,FIRST_VALUE(price) OVER w AS first_price
FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price);

LAST_VALUE(expr)函数

LAST_VALUE(expr)函数返回最后一个expr的值。

举例：按照价格排序，查询最后一个商品的价格信息。

SELECT id, category, NAME, price, stock,LAST_VALUE(price) OVER w AS last_price
FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price);

其他函数

NTH_VALUE(expr,n)函数

NTH_VALUE(expr,n)函数返回第n个expr的值。

举例：查询goods数据表中排名第2和第3的价格信息。

SELECT id, category, NAME, price,NTH_VALUE(price,2) OVER w AS second_price,
NTH_VALUE(price,3) OVER w AS third_price
FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price);

NTILE(n)函数

NTILE(n)函数将分区中的有序数据分为n个桶，记录桶编号。

举例：将goods表中的商品按照价格分为3组。

SELECT NTILE(3) OVER w AS nt,id, category, NAME, price
FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price);

小结

窗口函数的特点是可以分组，而且可以在分组内排序。另外，窗口函数不会因为分组而减少原表中的行数，这对我们在原表数据的基础上进行统计和排序非常有用。

WINDOW相当于一个位置函数

公用表表达式

CTE（Common Table Expressions）

CTE是一个命名的临时结果集，作用范围是当前语句。CTE可以理解成一个可以复用的子查询，当然跟子查询还是有点区别的，CTE可以引用其他CTE，但子查询不能引用其他子查询。所以，可以考虑代替子查询。

依据语法结构和执行方式的不同，公用表表达式分为 普通公用表表达式 和 递归公用表表达式 2 种。

普通公用表表达式

普通公用表表达式的语法结构是：

WITH CTE名称
AS （子查询）
SELECT|DELETE|UPDATE 语句;

举例：查询员工所在的部门的详细信息。

SELECT * FROM departments
WHERE department_id IN (
SELECT DISTINCT department_id
FROM employees
);

这个查询也可以用普通公用表表达式的方式完成：

WITH emp_dept_id
AS (SELECT DISTINCT department_id FROM employees)
SELECT *
FROM departments d JOIN emp_dept_id e
ON d.department_id = e.department_id;

递归公用表表达式

递归公用表表达式也是一种公用表表达式，只不过，除了普通公用表表达式的特点以外，它还有自己的特点，就是可以调用自己。它的语法结构是：

WITH RECURSIVE
CTE名称 AS （子查询）
SELECT|DELETE|UPDATE 语句;

案例：针对于我们常用的employees表，包含employee_id，last_name和manager_id三个字段。如果a是b的管理者，那么，我们可以把b叫做a的下属，如果同时b又是c的管理者，那么c就是b的下属，是a的下下属。

如果用我们之前学过的知识来解决，会比较复杂，至少要进行 4 次查询才能搞定：

第一步，先找出初代管理者，就是不以任何别人为管理者的人，把结果存入临时表；
第二步，找出所有以初代管理者为管理者的人，得到一个下属集，把结果存入临时表；
第三步，找出所有以下属为管理者的人，得到一个下下属集，把结果存入临时表。
第四步，找出所有以下下属为管理者的人，得到一个结果集。

如果用递归公用表表达式，就非常简单了。我介绍下具体的思路。

用递归公用表表达式中的种子查询，找出初代管理者。字段 n 表示代次，初始值为 1，表示是第一代管理者。
用递归公用表表达式中的递归查询，查出以这个递归公用表表达式中的人为管理者的人，并且代次的值加 1。直到没有人以这个递归公用表表达式中的人为管理者了，递归返回。
在最后的查询中，选出所有代次大于等于 3 的人，他们肯定是第三代及以上代次的下属了，也就是下下属了。这样就得到了我们需要的结果集。

WITH RECURSIVE cte
AS
(
SELECT employee_id,last_name,manager_id,1 AS n FROM employees WHERE employee_id = 100
-- 种子查询，找到第一代领导
UNION ALL
SELECT a.employee_id,a.last_name,a.manager_id,n+1 FROM employees AS a JOIN cte
ON (a.manager_id = cte.employee_id) -- 递归查询，找出以递归公用表表达式的人为领导的人
)
SELECT employee_id,last_name FROM cte WHERE n >= 3;

总之，递归公用表表达式对于查询一个有共同的根节点的树形结构数据，非常有用。它可以不受层级的限制，轻松查出所有节点的数据。如果用其他的查询方式，就比较复杂了。

MySQL8.0新特性——窗口函数和公用表表达式

公用表表达式

窗口函数

举个例子对比一下

介绍

语法结构

分类讲解

序号函数

ROW_NUMBER()函数

RANK()函数

DENSE_RANK()函数

分布函数

PERCENT_RANK()函数

CUME_DIST()函数

前后函数

LAG(expr,n)函数

LEAD(expr,n)函数

首尾函数

FIRST_VALUE(expr)函数

LAST_VALUE(expr)函数

其他函数

NTH_VALUE(expr,n)函数

NTILE(n)函数

小结

公用表表达式

普通公用表表达式

递归公用表表达式