公用表表达式和窗口函数(三十)

总是相信，又总是迷茫，仿佛这才是真相

二八佳人体似酥，腰悬利剑斩愚夫，虽然不见人头落,暗里教君骨髓枯。

上一章简单介绍了MySQL游标(二十九) ,如果没有看过,请观看上一章

一. 公用表表达式

公用表表达式（或通用表表达式）简称为CTE（Common Table Expressions）。

CTE是一个命名的临时结果集，作用范围是当前语句。

CTE可以理解成一个可以复用的子查询，当然跟子查询还是有点区别的，

CTE可以引用其他CTE，但子查询不能引用其他子查询。所以，可以考虑代替子查询。

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

一.一 普通公用表表达式

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

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

普通公用表表达式类似于子查询，不过，跟子查询不同的是，它可以被多次引用，而且可以被其他的普通公用表表达式所引用。

举例：

部门表:

员工表:

测试部没有人

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

select * from dept where id in 
(select distinct dept_id from user);

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

with tmp_dept_id 
as (select distinct dept_id from user)
select * 
from dept d join tmp_dept_id e
on d.id = e.dept_id;

例子说明，公用表表达式可以起到子查询的作用。

以后如果遇到需要使用子查询的场景，你可以在查询之前，先定义公用表表达式，然后在查询中用它来代替子查询。

而且，跟子查询相比，公用表表达式有一个优点，就是定义过公用表表达式之后的查询，可以像一个表一样多次引用公用表表达式，而子查询则不能。

更新:

with tmp_dept_id 
as (select distinct dept_id from user)
update dept d join tmp_dept_id e set d.name ='新名称'
where d.id = e.dept_id;

select * from dept;

删除:

with tmp_dept_id 
as (select distinct dept_id from user)
delete d from dept d ,tmp_dept_id e
where d.id = e.dept_id;


select * from dept;

一.二 递归公用表表达式

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

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

递归公用表表达式由 2 部分组成，分别是种子查询和递归查询，中间通过关键字 UNION [ALL]进行连接。这里的种子查询，意思就是获得递归的初始值。这个查询只会运行一次，以创建初始数据集，之后递归查询会一直执行，直到没有任何新的查询数据产生，递归返回。

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

下面我们尝试用查询语句列出所有具有下下属身份的人员信息。

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

• 第一步，先找出初代管理者，就是不以任何别人为管理者的人，把结果存入临时表；

• 第二步，找出所有以初代管理者为管理者的人，得到一个下属集，把结果存入临时表；

• 第三步，找出所有以下属为管理者的人，得到一个下下属集，把结果存入临时表。

• 第四步，找出所有以下下属为管理者的人，得到一个结果集。

如果第四步的结果集为空，则计算结束，第三步的结果集就是我们需要的下下属集了，否则就必须继续进行第四步，一直到结果集为空为止。比如上面的这个数据表，就需要到第五步，才能得到空结果集。而且，最后还要进行第六步：把第三步和第四步的结果集合并，这样才能最终获得我们需要的结果集。

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

• 用递归公用表表达式中的种子查询，找出初代管理者。字段 n 表示代次，初始值为 1，表示是第一代管理者。

• 用递归公用表表达式中的递归查询，查出以这个递归公用表表达式中的人为管理者的人，并且代次的值加 1。直到没有人以这个递归公用表表达式中的人为管理者了，递归返回。

• 在最后的查询中，选出所有代次大于等于 3 的人，他们肯定是第三代及以上代次的下属了，也就是下下属了。这样就得到了我们需要的结果集。

这里看似也是 3 步，实际上是一个查询的 3 个部分，只需要执行一次就可以了。而且也不需要用临时表保存中间结果，比刚刚的方法简单多了。

with recursive te 
as 
(
  select id,name,parent_id, 1 as n from user where parent_id = 0  -- 查询第一代
	union all 
	select a.id,a.name,a.parent_id,n+1 from user as a join te  -- 递归查询，找出以递归公用表表达式的人为领导的人
	on a.parent_id = te.id
)
select id,name,parent_id from te where n >=3;

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

如果用其他的查询方式，就比较复杂了。

公用表表达式的作用是可以替代子查询，而且可以被多次引用。递归公用表表达式对查询有一个共同根节点的树形结构数据非常高效，

可以轻松搞定其他查询方式难以处理的查询。

二. 窗口函数

二.一 使用窗口函数前后对比

假设我现在有这样一个数据表，它显示了某购物网站在每个城市每个区的销售额：

CREATE TABLE sales(
id INT PRIMARY KEY AUTO_INCREMENT,
city VARCHAR(15),
county VARCHAR(15),
sales_value DECIMAL

);

INSERT INTO sales(city,county,sales_value)
VALUES
('北京','海淀',20.00),
('北京','朝阳',80.00),
('上海','黄埔',40.00),
('上海','长宁',60.00);

查询:

select * from sales;

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

二.一.一 使用分组和聚合函数进行处理

第一步，计算总销售金额，并存入临时表 a：

CREATE TEMPORARY TABLE a       -- 创建临时表
SELECT SUM(sales_value) AS sales_value -- 计算总计金额
FROM sales;

select * from a;

第二步，计算每个城市的销售总额并存入临时表 b：

CREATE TEMPORARY TABLE b    -- 创建临时表
SELECT city,SUM(sales_value) AS sales_value  -- 计算城市销售合计
FROM sales
GROUP BY city;


select * from b;

第三步，计算各区的销售占所在城市的总计金额的比例，和占全部销售总计金额的比例。我们可以通过下面的连接查询获得需要的结果：

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;

结果显示：市销售金额、市销售占比、总销售金额、总销售占比都计算出来了。

使用公用表表达式进行处理:

with c as (
SELECT SUM(sales_value) AS sales_value -- 计算总计金额
FROM sales
),
d as (
SELECT city,SUM(sales_value) AS sales_value  -- 计算城市销售合计
FROM sales
GROUP BY city
)
SELECT s.city AS 城市,s.county AS 区,s.sales_value AS 区销售额,
    d.sales_value AS 市销售额,s.sales_value/d.sales_value AS 市比率,
     c.sales_value AS 总销售额,s.sales_value/c.sales_value AS 总比率
     FROM sales s
     JOIN d ON (s.city=d.city) -- 连接市统计结果临时表
     JOIN c                   -- 连接总计金额临时表
     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;

结果显示，我们得到了与上面那种查询同样的结果。

使用窗口函数，只用了一步就完成了查询。而且，由于没有用到临时表，执行的效率也更高了。

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

二.二 窗口函数分类和语法

二.二.一 分类

MySQL从8.0版本开始支持窗口函数。窗口函数的作用类似于在查询中对数据进行分组，不同的是，分组操作会把分组的结果聚合成一条记录，

而窗口函数是将结果置于每一条数据记录中。

窗口函数可以分为静态窗口函数和动态窗口函数。

• 静态窗口函数的窗口大小是固定的，不会因为记录的不同而不同；
• 动态窗口函数的窗口大小会随着记录的不同而变化。

MySQL官方网站窗口函数的网址为https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_row-number。

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

二.二.二 语法结构

窗口函数的语法结构是：

函数 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子句：为分区中的某个子集定义规则，可以用来作为滑动窗口使用。

二.三 分类讲解

提供表和数据

CREATE TABLE goods(
id INT PRIMARY KEY AUTO_INCREMENT,
category_id INT,
category VARCHAR(15),
NAME VARCHAR(30),
price DECIMAL(10,2),
stock INT
);

添加数据:

INSERT INTO goods(category_id,category,NAME,price,stock)
VALUES
(1, '女装/女士精品', 'T恤', 39, 1000),
(1, '女装/女士精品', '连衣裙', 79, 2500),
(1, '女装/女士精品', '卫衣', 89, 1500),
(1, '女装/女士精品', '牛仔裤', 89, 3500),
(1, '女装/女士精品', '百褶裙', 29, 500),
(1, '女装/女士精品', '呢绒外套', 399, 1200),
(2, '户外运动', '自行车', 399, 1000),
(2, '户外运动', '山地自行车', 1399, 2500),
(2, '户外运动', '登山杖', 59, 1500),
(2, '户外运动', '骑行装备', 399, 3500),
(2, '户外运动', '运动外套', 799, 500),
(2, '户外运动', '滑板', 499, 1200);

二.三.一 序号函数

1．ROW_NUMBER()函数

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 t.row_num <=3 ;

在名称为“女装/女士精品”的商品类别中，有两款商品的价格为89.90元，分别是卫衣和牛仔裤。两款商品的序号都应该为2，而不是一个为2，另一个为3。

此时，可以使用RANK()函数和DENSE_RANK()函数解决。

2．RANK()函数

使用RANK()函数能够对序号进行并列排序，并且会跳过重复的序号，比如序号为1、1、3。

举例：使用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()函数得出的序号为1、2、2、4，相同价格的商品序号相同，后面的商品序号是不连续的，跳过了重复的序号。

3．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;

可以看到，使用DENSE_RANK()函数得出的行号为1、2、2、3，相同价格的商品序号相同，后面的商品序号是连续的，并且没有跳过重复的序号。

二.三.二 分布函数

1．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);

2．CUME_DIST()函数

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

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

select CUME_DIST() over(PARTITION by category_id order by price asc) as cd,
id,category_id,category,`name`,price,stock
from goods;

二.三.三 前后函数

1．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;

2．LEAD(expr,n)函数

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

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

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;

二.三.四 首尾函数

1．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);

2．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);

二.三.五 其他函数

1．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);

2．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);

窗口函数的特点是可以分组，而且可以在分组内排序。另外，窗口函数不会因为分组而减少原表中的行数，

这对我们在原表数据的基础上进行统计和排序非常有用。

谢谢!!!