奉化市建设局网站,企业网站优化服务公司,wordpress插件ERP,国外域名需要实名吗目录 讲解一#xff1a;窗口函数
一、简介
二、常见操作
1. sumgroup by常规的聚合函数操作
2. sum窗口函数的聚合操作
三、基本语法
1. Function(arg1,..., argn)
1.1. 聚合函数
sum函数#xff1a;求和
min函数 #xff1a;最小值
1.2. 排序函数
1.3. 跨行函数…目录 讲解一窗口函数
一、简介
二、常见操作
1. sumgroup by常规的聚合函数操作
2. sum窗口函数的聚合操作
三、基本语法
1. Function(arg1,..., argn)
1.1. 聚合函数
sum函数求和
min函数 最小值
1.2. 排序函数
1.3. 跨行函数
2. OVER [PARTITION BY ...]
3. [ORDER BY ....]
4. [window_expression]
四、练习题
1. 建库建表语句
2. 题目
3. 答案解析
计算每日销售额总和分区按日期
计算每个区域的总销售额
为每个产品计算其销售排名按销售额降序
计算每个区域每天的平均销售额
计算每个产品的销售累计总额
计算每个区域每个产品的销售总额
计算每个区域最近7天的平均销售额
为每个产品的销售记录添加序列号按日期排序
计算每个区域每天相对于前一天的销售额变化
计算每个产品的销售占比按总销售额)
讲解二公用表达式CTE
一、简介
二、语法
三、示例
四、递归 CTE
1. 简介
2. 递归成员限制
3. 示例
4. 使用递归 CTE 遍历分层数据
五、CTE 与 Derived Table
1. 在 5.6 版本中
2. 在 5.7 版本中
3. 在 8.0 版本中 讲解一窗口函数
一、简介
窗口函数是一种SQL函数非常适合于数据分析其最大的特点就是输入值是从
SELECT语句的结果集中的一行或者多行的窗口中获取的也可以理解为窗口有大有
小行数有多有少。
通过OVER子句窗口函数与其他的SQL函数有所区别如果函数具有OVER子句
则它是窗口函数。如果它缺少了OVER子句则他就是个普通的聚合函数。
窗口函数可以简单地解释为类似于聚合函数的计算函数但是通过GROUP BY子句组
合的常规聚合会隐去正在聚合的各个行最终输出称为一行。但是窗口函数聚合完之
后还可以访问当前行的其他数据并且可以将这些行的某些属性添加到结果当中去。
下面可以通过两个图来区分普通的聚合函数和窗口函数 为了更加直观的反映窗口函数和普通聚合函数的区别让我们通过代码的形式感受一
下 首先让我们先添加测试数据并查看表。
CREATE DATABASE IF NOT EXISTS EmployeeDB;
USE EmployeeDB;
CREATE TABLE Employees (EmployeeID INT AUTO_INCREMENT PRIMARY KEY,Name VARCHAR(100),DepartmentID INT,Salary DECIMAL(10, 2)
);INSERT INTO Employees (Name, DepartmentID, Salary) VALUES
(Alice, 1, 50000),
(Bob, 1, 55000),
(Charlie, 2, 60000),
(David, 1, 50000),
(Eve, 2, 65000),
(Frank, 3, 45000),
(Grace, 3, 47000),
(Hannah, 3, 48000),
(Ian, 2, 70000),
(Jack, 1, 52000); 二、常见操作
1. sumgroup by常规的聚合函数操作
select DepartmentID, sum(salary) as total
from employees
group by DepartmentID; 我们可以看的出来常规聚合函数把id进行分组然后把每组的薪资综合计算出来放在
最后面。
2. sum窗口函数的聚合操作
select *, sum(Salary) over (partition by DepartmentID) total
from employees 我们可以通过这两个例子看出来聚合函数和窗口聚合函数的区别。就是窗口函数会
进行分组但不会把行进行合并。对于每一组窗口函数返回出来的结果都会重复的放在最后面。
三、基本语法
Function(arg1,..., argn) OVER ([PARTITION BY ...] [ORDER BY ....]
[window_expression])
对于以上的窗口函数的语法[ ]中的语法是可以根据自己的需求进行选择非必须写
入语法并且此语法严格按照上面的顺序来规定。
Function(arg1,..., argn)是表示函数的分类可以是下面分类中的任何一组。
聚合函数例如sum,min,avg,count等函数常用排序函数例如rank row_number dense_rank()等函数常用跨行函数lag lead 函数
OVER [PARTITION BY ...] 类似于group by 用于指定分组
每个分组你可以把它叫做窗口不分组的情况可以写成partition by null 或者直接不写partition by所有列为
一个大组
分组的情况下partition by 后面可以跟多个列例如partition by cid,cname
[ORDER BY ....] 用于指定每个分组内的数据排序规则 支持ASC、DESC
[window_expression] 用于指定每个窗口中 操作的数据范围 默认是窗口中所有行
1. Function(arg1,..., argn)
通常和partition by分组使用。
当然也可以不分组使用但也不分组使用通常没有意义。
聚合函数例如sum,min,avg,count等函数常用排序函数例如rank row_number dense_rank()等函数常用跨行函数lag lead 函数
1.1. 聚合函数
我们还通过上文的测试数据进行演示。我们就演示2个函数其他的聚合类函数都是
相同的用法。
sum函数求和
select *,sum(Salary) over (partition by DepartmentID) total
from employees min函数 最小值
select *,min(Salary) over (partition by DepartmentID) total
from employees; 其他的聚合函数都是同样的用法。
1.2. 排序函数
rank row_number dense_rank()等函数通常与order by函数一起使用。
row_number()函数对分组之后按照某些规则从高到低或者从低到高进行排序
(order by),然后打上序号,不考虑并列的情况。
select *,row_number() over (partition by DepartmentID order by Salary desc ) total
from employees; rank()函数对分组之后按照某些规则从高到低或者从低到高进行排序(order by),然
后打上序号考虑并列情况并且跳跃排名对此我们需要增添一组数据。
INSERT INTO Employees (Name, DepartmentID, Salary) VALUES(css,1,45000); select *,rank() over (partition by DepartmentID order by Salary desc ) total
from employees; dense_rank()函数
select *,dense_rank() over (partition by DepartmentID order by Salary desc ) total
from employees; 1.3. 跨行函数
LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值
第一个参数为列名第二个参数为往上第n行可选默认为1第三个参数为默认
值当往上第n行为NULL时候取默认值如不指定则为NULL
select *,lag(Salary,1) over (partition by DepartmentID order by Salary desc ) total
from employees; LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值
第一个参数为列名第二个参数为往下第n行可选默认为1第三个参数为默认
值当往下第n行为NULL时候取默认值如不指定则为NULL
select *,lead(Salary,1) over (partition by DepartmentID order by Salary desc ) total
from employees; FIRST_VALUE 取分组内排序后截止到当前行第一个值
select *,first_value(Salary) over (partition by DepartmentID order by Salary desc ) total
from employees; LAST_VALUE 取分组内排序后截止到当前行最后一个值
select *,last_value(Salary) over (partition by DepartmentID order by Salary desc ) total
from employees; 从这个数据我们有个疑问为啥不是去分组内的最后一个值呢
在这里我给大家解释一下对于我们分的窗口比如部门id1里面还有个小窗口
row函数
对于我们没有指定小窗口默认是当之前所有行到当前行这样理解可以很抽象我们
举个例子。对于部门id1来说我们从第一行来看心里默念从之前所有行到当前
行从之前所有行到当前行来看确实输出的值应该是55000.00那么我们看第二行
心里默念从之前所有行到当前行那么确实输出的是52000.00。这样我们通过row
函数来改变一下小窗口的范围。更清晰的感受一下这个函数。
select *,last_value(Salary) over (partition by DepartmentID order by Salary descrows between unbounded preceding and unbounded following ) total
from employees;
解释一下设置小窗口的含义rows between unbounded preceding and
unbounded following
之前所有的行到之后所有的行那么让我们输出一下。 我们可以很清晰的看出来输出的是每一组里面最后一个的薪资。
2. OVER [PARTITION BY ...]
over是窗口函数的标志partition by 用来指定分组把partition by 后面跟的字段
相同的放在一起
3. [ORDER BY ....]
用于指定每个分组内的数据排序规则 支持ASC、DESC 跟group by 中的order by
是一样的用法
4. [window_expression]
用于指定每个窗口中 操作的数据范围 默认是窗口中所有行
窗口子句操作的数据范围
起始行:N preceding/unbounded preceding当前行:currentrow终止行:N following/unbounded following
举例:
rows between unbounded preceding and current row
从之前所有的行到当前行
rows between 2 preceding and current row
从前面两行到当前行
rows between current row and unbounded following
从当前行到之后所有的行
rows between current row and 1following
从当前行到后面一行
注意:
排序子句后面缺少窗口子句窗口规范默认是
rows between unbounded preceding and current row
排序子句和窗口子句都缺失窗口规范默认是
rows between unbounded preceding and unbounded following
总体流程
通过partition by和 order by 子句确定大窗口(定义出上界unbounded preceding和下界unbounded following)通过row 子句针对每一行数据确定小窗口(滑动窗口)对每行的小窗口内的数据执行函数并生成新的列
四、练习题
1. 建库建表语句
CREATE DATABASE IF NOT EXISTS sales_db;
USE sales_db;CREATE TABLE IF NOT EXISTS sales (id INT AUTO_INCREMENT PRIMARY KEY,product_id INT comment 商品id,sale_date DATE comment 销售日期,amount DECIMAL(10, 2)comment 销售额,region VARCHAR(50) comment 地区
)comment 销售;-- 插入一些示例数据
INSERT INTO sales (product_id, sale_date, amount, region) VALUES
(1, 2023-01-01, 100.00, East),
(2, 2023-01-01, 150.00, West),
(1, 2023-01-02, 200.00, East),
(3, 2023-01-02, 120.00, South),
(2, 2023-01-03, 180.00, West),
(1, 2023-01-03, 220.00, East),
(3, 2023-01-04, 140.00, South),
(4, 2023-01-04, 300.00, North),
(2, 2023-01-05, 250.00, West),
(1, 2023-01-05, 280.00, East);
insert into sales(product_id, sale_date, amount, region) values
(1,2023-01-01,200.00,East); 2. 题目
计算每日销售额总和分区按日期计算每个区域的总销售额为每个产品计算其销售排名按销售额降序计算每个区域每天的平均销售额计算每个产品的销售累计总额计算每个区域每个产品的销售总额计算每个区域最近7天的平均销售额为每个产品的销售记录添加序列号按日期排序计算每个区域每天相对于前一天的销售额变化计算每个产品的销售占比销售额/总销售额
自己可以先把这些最基础的窗口函数做完之后再看下面的解析
3. 答案解析
计算每日销售额总和分区按日期
# 计算每日销售额总和分区按日期
SELECT *,sale_date, SUM(amount) OVER (PARTITION BY sale_date order by sale_date) AS total_daily_sales
FROM sales; 计算每个区域的总销售额
# 计算每个区域的总销售额
SELECT region, SUM(amount) OVER (PARTITION BY region) AS total_regional_sales
FROM sales; 为每个产品计算其销售排名按销售额降序
# 为每个产品计算其销售排名按销售额降序
select product_id,rank() over (order by sum(amount) desc )as 销售排名 from sales group by product_id ; 计算每个区域每天的平均销售额
# 计算每个区域每天的平均销售额
select *,avg(amount)over (partition by region,sale_date rows between unbounded preceding and unbounded following) from sales; 计算每个产品的销售累计总额
# 计算每个产品的销售累计总额
select *,sum(amount)over (partition by product_id) from sales; 计算每个区域每个产品的销售总额
# 计算每个区域每个产品的销售总额
select *,sum(amount)over (partition by product_id,region)from sales; 计算每个区域最近7天的平均销售额
# 计算每个区域最近7天的平均销售额
with t1 as ( select *,dense_rank() over(partition by region order by sale_date)as ttime from sales )
select *,avg(amount)over(partition by region) from t1 where ttime7;
; 为每个产品的销售记录添加序列号按日期排序
# 为每个产品的销售记录添加序列号按日期排序
select *,dense_rank() over (partition by product_id order by sale_date)from sales; 计算每个区域每天相对于前一天的销售额变化
# 计算每个区域每天相对于前一天的销售额变化
SELECTa.sale_date,a.region,a.amount,a.amount - LAG(a.amount) OVER (PARTITION BY a.region ORDER BY a.sale_date) AS daily_change
FROM sales a; 计算每个产品的销售占比按总销售额)
# 计算每个产品的销售占比按总销售额)
select *,sales.amount/sum(amount)over (partition by product_id)from sales; 讲解二公用表达式CTE
一、简介
官网MySQL :: MySQL 8.0 Reference Manual :: 15.2.20 WITH (Common Table Expressions)
MySQL 从 8.0 开始支持 WITH 语法即Common Table Expressions - CTE公用表表达式。
CTE 是一个命名的临时结果集合仅在单个 SQL 语句select、insert、update 或 delete的执行范
围内存在。
与派生表类似的是CTE 不作为对象存储仅在查询执行期间持续。
与派生表不同的是CTE 可以是自引用递归CTE也可以在同一查询中多次引用。
此外与派生表相比CTE 提供了更好的可读性和性能。
二、语法
CTE 的结构包括名称、可选列列表和定义 CTE 的查询。
定义 CTE 后可以像 select、insert、update、delete 或 create view 语句中的视图一样使用它。
with cte_name (column_list) as (query)
select * from cte_name;
查询中的列数必须与 column_list 中的列数相同。
如果省略 column_listCTE 将使用定义 CTE 的查询的列列表。
三、示例
初始化数据
-- create table
create table department
(id bigint auto_increment comment 主键IDprimary key,dept_name varchar(32) not null comment 部门名称,parent_id bigint default 0 not null comment 父级id
);-- insert values
insert into department values (null, 总部, 0);
insert into department values (null, 研发部, 1);
insert into department values (null, 测试部, 1);
insert into department values (null, 产品部, 1);
insert into department values (null, Java组, 2);
insert into department values (null, Python组, 2);
insert into department values (null, 前端组, 2);
insert into department values (null, 供应链测试组, 3);
insert into department values (null, 商城测试组, 3);
insert into department values (null, 供应链产品组, 4);
insert into department values (null, 商城产品组, 4);
insert into department values (null, Java1组, 5);
insert into department values (null, Java2组, 5);
1最基本的CTE语法
mysql with cte1 as (select * from department where id in (1, 2)),- cte2 as (select * from department where id in (2, 3))- select *- from cte1- join cte2- where cte1.id cte2.id;
----------------------------------------------------
| id | dept_name | parent_id | id | dept_name | parent_id |
----------------------------------------------------
| 2 | 研发部 | 1 | 2 | 研发部 | 1 |
----------------------------------------------------
1 row in set (0.00 sec)
2一个 CTE 引用另一个 CTE
mysql with cte1 as (select * from department where id 1),- cte2 as (select * from cte1)- select *- from cte2;
--------------------------
| id | dept_name | parent_id |
--------------------------
| 1 | 总部 | 0 |
--------------------------
1 row in set (0.00 sec)
四、递归 CTE
1. 简介
递归 CTE 是一个具有引用 CTE 名称本身的子查询的 CTE。递归 CTE 的语法为
with recursive cte_name as (initial_query -- anchor member
union all
recursive_query -- recursive member that references to the cte name
)
select * from cte_name;
递归 CTE 由三个主要部分组成
形成 CTE 结构的基本结果集的初始查询initial_query初始查询部分被称为锚成员。递归查询部分是引用 CTE 名称的查询因此称为递归成员。递归成员由一个 union all 或 union distinct 运算符与锚成员相连。终止条件是当递归成员没有返回任何行时确保递归停止。
递归 CTE 的执行顺序如下
首先将成员分为两个锚点和递归成员。接下来执行锚成员形成基本结果集R0并使用该基本结果集进行下一次迭代。然后将 Ri 结果集作为输入执行递归成员并将 Ri 1 作为输出。之后重复第三步直到递归成员返回一个空结果集换句话说满足终止条件。最后使用 union all 运算符将结果集从 R0 到 Rn 组合。
2. 递归成员限制
递归成功不能包含以下结构
聚合函数如 max、min、sum、avg、count 等。group by 子句order by 子句limit 子句distinct
上述约束不适用于锚点成员。 另外只有在使用 union 运算符时要禁止 distinct 才适用。
如果使用 union distinct 运算符则允许使用 distinct。
另外递归成员只能在其子句中引用 CTE 名称而不是引用任何子查询。
3. 示例
with recursive cte_count (n)as (select 1union allselect n 1from cte_countwhere n 3)
select n from cte_count;
在此示例中以下查询
select 1
是作为基本结果集返回 1 的锚成员。
以下查询
select n 1
from cte_count
where n 3
是递归成员因为它引用了 cte_count 的 CTE 名称。递归成员中的表达式 3 是终止条件。
当 n 等于 3递归成员将返回一个空集合将停止递归。
下图显示了上述 CTE 的元素 递归 CTE 返回以下输出
------
| n |
------
| 1 |
| 2 |
| 3 |
------
递归 CTE 的执行步骤如下
首先分离锚和递归成员。接下来锚定成员形成初始行 select 1因此第一次迭代在 n 1 时产生 1 1 2。然后第二次迭代对第一次迭代的输出 2 进行操作并且在 n 2 时产生 2 1 3。之后在第三次操作 n 3 之前满足终止条件 n 3 因此查询停止。最后使用 union all 运算符组合所有结果集 12 和 3。
4. 使用递归 CTE 遍历分层数据
查部门 id 2 的所有下级部门和本级
mysql with recursive cte_tab as (select id, dept_name, parent_id, 1 as level- from department- where id 2- union all- select d.id, d.dept_name, d.parent_id, level 1- from cte_tab c- inner join department d on c.id d.parent_id- )- select *- from cte_tab;
-----------------------------------
| id | dept_name | parent_id | level |
-----------------------------------
| 2 | 研发部 | 1 | 1 |
| 5 | Java组 | 2 | 2 |
| 6 | Python组 | 2 | 2 |
| 7 | 前端组 | 2 | 2 |
| 12 | Java1组 | 5 | 3 |
| 13 | Java2组 | 5 | 3 |
-----------------------------------
6 rows in set (0.00 sec)
五、CTE 与 Derived Table
针对 from 子句里面的 subqueryMySQL 在不同版本中是做过一系列的优化接下来我们就来看看。
1. 在 5.6 版本中
MySQL 会对每一个 Derived Table 进行物化生成一个临时表保存 Derived Table 的结果然后利用临时表来完
成父查询的操作具体如下
mysql explain- select * from (select * from department where id 1000) t1 join (select * from department where id 990) t2 on t1.id t2.id;
-------------------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-------------------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | department | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 1 | 100.00 | Using where |
| 1 | SIMPLE | department | NULL | eq_ref | PRIMARY | PRIMARY | 8 | pointer_mall.department.id | 1 | 100.00 | NULL |
-------------------------------------------------------------------------------------------------------------------------------------------
2 rows in set, 1 warning (0.01 sec)
2. 在 5.7 版本中
MySQL 引入了 Derived Merge 新特性允许符合条件的 Derived Table 中的子表与父查询的表进行合并具体如下
mysql explain- select * from (select * from department where id 1000) t1 join (select * from department where id 990) t2 on t1.id t2.id;
----------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
----------------------------------------------------------------------------------------------------------------------------
| 1 | PRIMARY | derived2 | NULL | ALL | NULL | NULL | NULL | NULL | 1900 | 100.00 | NULL |
| 1 | PRIMARY | derived3 | NULL | ref | auto_key0 | auto_key0 | 8 | t1.id | 2563 | 100.00 | NULL |
| 3 | DERIVED | department | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 4870486 | 100.00 | Using where |
| 2 | DERIVED | department | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 1900 | 100.00 | Using where |
----------------------------------------------------------------------------------------------------------------------------
4 rows in set, 1 warning (0.00 sec)
3. 在 8.0 版本中
我们可以使用 CTE 实现其执行计划也是和 Derived Table 一样
mysql explain- with t1 as (select * from department where id 1000),- t2 as (select * from department where id 990)- select * from t1 join t2 on t1.id t2.id;
-------------------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-------------------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | department | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 1 | 100.00 | Using where |
| 1 | SIMPLE | department | NULL | eq_ref | PRIMARY | PRIMARY | 8 | pointer_mall.department.id | 1 | 100.00 | NULL |
-------------------------------------------------------------------------------------------------------------------------------------------
2 rows in set, 1 warning (0.00 sec)
从测试结果来看CTE 似乎是 Derived Table 的一个替代品
其实不是的虽然 CTE 内部优化流程与 Derived Table 类似但是两者还是区别的具体如下
一个 CTE 可以引用另一个 CTECTE 可以自引用CTE 在语句级别生成临时表多次调用只需要执行一次提高性能
从上面介绍可以知道CTE 一方面可以非常方便进行 SQL 开发另一方面也可以提升 SQL 执行效率。
