子乌笔记-SQL-窗口函数

ziwu1226

已于 2023-11-13 10:25:50 修改

阅读量133

点赞数 1

分类专栏： Python+大数据-精通SQL 文章标签：数据库 mysql

于 2023-09-07 15:09:28 首次发布

本文链接：https://blog.csdn.net/ziwu1226/article/details/132731414

版权

Python+大数据-精通SQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

窗口函数用法举例

序号函数：row_number()、rank()、dense_rank()

rank()
遇到重复值排序相同，会跳过序号排序。{1，1，1，4}
dense_rank()
遇到重复值排序相同，不会跳过序号排序。{1，1，1，2}
row_number()
遇到重复值，依旧顺序排序，不会出现重复排序。{1，2，3，4}

学生	成绩	rank()	dense_rank()	row_number
张三	90	1	1	1
李四	90	1	1	2
王五	90	1	1	3
赵六	59	4	2	4

分布函数：cume_dist、percent_rank

cume_dist
小于等于当前值（包括自己）的个数/分组内总个数
percent_rank
当前行的RANK值-1/分组内总行数-1

前后函数
头尾函数
聚合函数+窗口函数的联合使用

窗口函数介绍

窗口函数，又称OLAP（OnLine Anallytical Processing），窗口函数，即window functions，是在MySQL 8.0之后新增的功能，简化了数据分析工作中查询语句的书写。

备注：在没有窗口函数前，需要通过定义临时变量和大量的子查询才能完成的工作，使用窗口函数实现起来更加简洁、高效。

窗口函数就是对数据表中一组数据进行计算的函数，这组数据跟当前行相关。

使用窗口函数的优点：

优点	描述性息
简单	窗口函数更易于使用、语法耶很简单
快速	使用窗口函数比使用替代方法快得多，当你要处理成百上千兆字节数据时，这非常有用。

大致窗口函数样式如下：

<窗口函数> OVER ([PARTITION BY <列名清单>] ORDER BY <排序列名清单> [rows between 开始位置 and 结束位置])

添加数据内容

为了我们后续的学习，我们插入下列数据

例如，使用命令来完成：

（1）创建一个默认为utf8编码的数据库：班级db_window_function，并使用它；
（2）在库中，分别创建三张表：雇员表employee、部门表department、采购表purchase；
（3）分别给雇员表、部门表、采购表添加批量数据。
在这里插入图片描述

# 创建数据库
create database if not exists window_func charset=utf8;
# 使用
use window_func;

# 部门表
create table if not exists department(
    id int primary key auto_increment not null,
    name varchar(30) not null
)engine = InnoDB default charset utf8;

# 雇员表
create table if not exists employee(
    id int primary key auto_increment not null,
    first_name varchar(20) not null,
    last_name varchar(30) not null,
    department_id int not null,
    salary double not null,
    years_worked tinyint not null
)engine = InnoDB default charset utf8;

# 采购表
create table if not exists purchase(
    id int primary key auto_increment not null,
    department_id int not null,
    item varchar(30) not null,
    price double not null
)engine = InnoDB default charset utf8;

# 添加雇员数据
insert into employee values
(1, '建华', '刘', 1, 5330, 4),
(2, '璐', '陈', 1, 3617, 2),
(3, '建平', '翁', 1, 4877, 2),
(4, '静', '丁', 1, 5259, 3),
(5, '红霞', '刘', 2, 2094, 5),
(6, '平平', '王', 2, 5167, 5),
(7, '旭', '高', 2, 3762, 4),
(8, '小亮', '顾', 2, 6923, 3),
(9, '峰', '甘', 3, 2796, 4),
(10, '玉梅', '乔', 3, 4771, 5),
(11, '玲珑', '王', 3, 3782, 2),
(12, '桂荣', '刘', 3, 6419, 1),
(13, '淑华', '谢', 3, 6261, 1),
(14, '玲', '谭', 3, 4928, 8),
(15, '婷婷', '戴', 4, 6347, 6),
(16, '瑞', '王', 4, 6286, 1),
(17, '丽丽', '龙', 5, 5639, 3),
(18, '强', '马', 5, 3232, 1),
(19, '建辉', '姚', 5, 4653, 1),
(20, '海燕', '林', 5, 2076, 5);

# 添加部门数据
insert into department values
(1, '研发部'),
(2, '行政部'),
(3, '人事部'),
(4, '财务部'),
(5, '市场部');

# 添加采购数据
insert into purchase values
(1, 4, '显示器', 531),
(2, 1, '打印机', 315),
(3, 3, '白色书写板', 170),
(4, 5, '培训费', 117),
(5, 3, '电脑', 2190),
(6, 1, '显示器', 418),
(7, 3, '白色书写板', 120),
(8, 3, '显示器', 388),
(9, 5, 'A4纸', 37),
(10, 1, 'A4纸', 695),
(11, 3, '投影仪', 407),
(12, 4, '聚会', 986),
(13, 5, '投影仪', 481),
(14, 2, '椅子', 180),
(15, 2, '桌子', 854),
(16, 2, '贴纸', 15),
(17, 3, 'A4纸', 60),
(18, 2, '电视', 943),
(19, 2, '桌子', 478),
(20, 5, '键盘', 214);

窗口函数 over() 子句

最基本的窗口函数语法：

窗口函数名(字段名) over() [as 别名]

over()的意思是在每一条数据都挂上一个窗户(窗口函数(字段)）
这里的SQL语句并不复杂，主要是看over()是在谁的前面部分，就代表它的含义。

例如，使用over()来完成：

（1）请计算出平均工资值；
（2）查看员工信息（比如名字、姓氏、工资等）、平均工资值的所有员工信息；提示：使用avg(salary)来计算平均工资值，加上over()进行计算，所以就是在计算所有人的平均工资.

# （1）请计算出平均工资值；
select avg(salary) avg_sal
from employee;

# （2）查看员工信息（比如名字、姓氏、工资等）、平均工资值的所有员工信息；
select employee.*,
       avg(salary) over () avg_sal
from employee;

配合聚合函数使用

例如，使用over()配合其他聚合函数来完成：

（1）查询每个人的姓氏、名字、工资之外，还要统计出公司每月的总工资支出值；
（2）统计出采购表中的平均采购价格，并与物品明细、物品价格一起显示；
（3）同时统计出采购表中的平均采购价格和采购价格总和，并与采购id、物品明细、物品价格一起显示出来。

# （1）查询每个人的姓氏、名字、工资之外，还要统计出公司每月的总工资支出值；
select last_name,first_name,salary,
       sum(salary) over ()
from employee;

# （2）统计出采购表中的平均采购价格，并与物品明细、物品价格一起显示；
select purchase.*,
       avg(price) over ()
from purchase;

# （3） 同时统计出采购表中的平均采购价格和采购价格总和，并与采购id、物品明细、物品价格一起显示出来。
select purchase.*,
       avg(price) over (),
       sum(price) over ()
from purchase;

over()结果再计算

窗口函数的一些典型应用场景，就是将当前行与一组数据的聚合值进行比较出结果，此时，就需要将over()结果进一步计算使用。

例如，使用over()结果再次计算来完成：

（1）查询出员工的基本信息（比如姓氏、名字、薪资）、员工的薪资与平均薪资之间的差值；
（2）统计出基本信息（比如姓氏、名字、工龄）、每个员工的工龄与平均工龄之间的差值。

# （1）查询出员工的基本信息（比如姓氏、名字、薪资）、员工的薪资与平均薪资之间的差值；
select last_name,first_name,salary,
       round(salary - avg(salary) over (),2) 'salary差值'
from employee;

# （2）统计出基本信息（比如姓氏、名字、工龄）、每个员工的工龄与平均工龄之间的差值。
select last_name,first_name,years_worked,
       round(years_worked - avg(years_worked) over (),2) 'years_worked差值'
from employee;

over()与where配合使用

窗口函数也可以与Where条件语句一起配合使用。

要注意的是：窗口函数是在where条件语句后再执行。

例如，使用over()与where配合使用来完成：

（1）查询department_id为2的部门所采购的所有物品信息，并计算每项支出占总采购金额的占比值；
（2）统计出基本信息（比如姓氏、名字、薪资）、员工薪资超过4000的员工总数量；
（3）统计department_id=3的姓氏、名字、薪资，并将员工的薪资与公司员工平均薪资之间的差值；
（4）查询公司所有员工薪资高于平均薪资的员工信息。

# （1）查询department_id为2的部门所采购的所有物品信息，并计算每项支出占该部门总采购金额的占比值；
select purchase.*,
       round(price / sum(price) over (),2)
from purchase
where department_id = 2;

# （2）统计出基本信息（比如姓氏、名字、薪资）、员工薪资超过4000的员工总数量；
select last_name,first_name,salary,
       count(*) over ()
from employee
where salary > 4000;

# （3）统计department_id=3的姓氏、名字、薪资，并将员工的薪资与公司员工平均薪资之间的差值；
select temp.last_name,temp.first_name,temp.salary,
       round(temp.salary - all_avg_salary,2)
from (
     select *,
            avg(salary) over () all_avg_salary
    from employee
         ) as temp
where department_id = 3;

# （4）查询公司所有员工薪资高于平均薪资的员工信息。
select *
from (select *,
             avg(salary) over () avg_sal
    from employee) temp
where temp.salary > temp.avg_sal

over(partition by字段)的使用

partition by可用于在窗口函数中的分组处理。
语法：

窗口函数名(字段名) over(partition by 字段名1[,字段名2,...]) [as 别名]

说明：
（1）partition by的作用于group by类似，就是将数据按照传入的字段进行分组；
（2）partition by叫做分析函数；
（3）关键字执行顺序的优先级是：from > where > group by > having > order by > partition by；
（4）注意：partition by应用在所有关键字之后，可以简单理解为就是在执行完select查询的整个语句后，在所得结果集的基础上再进行partition by分组处理。

例如，使用over()与partition by配合使用来完成：

（1）查询雇员表中，每个部门（department_id）员工的姓氏、名字、部门id、同一部门员工的数量总数；
（2）查询雇员表中，每个员工的姓氏、名字、员工的姓氏相同的总数；
（3）按姓氏和名字分组，且分组中要求显示出薪资高于4000且低于6000的数据记录有哪些。

# （1）查询雇员表中，每个部门（department_id）员工的姓氏、名字、部门id、同一部门员工的数量总数；
select last_name,first_name,department_id,
        count(*) over (partition by department_id) '总数'
from employee;

# （2）查询雇员表中，每个员工的姓氏、名字、员工的姓氏相同的总数；
select last_name,first_name,
       count(*) over (partition by last_name)
from employee;

# （3）按姓氏和名字分组，且分组中要求显示出薪资高于4000且低于6000的数据记录有哪些。
select employee.*,
       count(*) over (partition by last_name,first_name)
from employee
where salary between 4000 and 6000;

over(order by字段)的使用

order by用于在窗口函数中的排序处理。
语法为：

ranking函数名() over([partition by 字段名1] order by 字段名 [asc | desc]) [as 别名]

说明：
（1） ranking函数名()可以是row_number()、rank()、dense_rank()这三个函数中的某一个，都用于返回结果集的分组内每行排名；

row_number()不管排名是否有相同的，都会加序号，且按照1、2、3、…、n顺序；
rank()是当排名相同时，名次一样，且同一排名有几个，后面排名就会跳过几次；
dense_rank()是当排名相同的名次一样，且后面名次不跳过；

（2）order by就是将数据按照传入的字段从小到大或从大到小进行排序。

接下来，一起来学习如何通过窗口函数实现排序。首先，先给雇员表里再添加几条相同薪资的数据：

# 添加研发部数据
insert into employee(first_name, last_name, department_id, salary, years_worked) values('兵', '江', 1, 15600, 4);
insert into employee(first_name, last_name, department_id, salary, years_worked) values('莹', '夏', 1, 14000, 3);
insert into employee(first_name, last_name, department_id, salary, years_worked) values('慧', '白', 1, 12000, 2);
insert into employee(first_name, last_name, department_id, salary, years_worked) values('玉华', '刘', 1, 12000, 4);
insert into employee(first_name, last_name, department_id, salary, years_worked) values('飞洋', '孙', 1, 12000, 3);
insert into employee(first_name, last_name, department_id, salary, years_worked) values('利剑', '赵', 1, 6000, 1);
insert into employee(first_name, last_name, department_id, salary, years_worked) values('琳', '张', 1, 6000, 1);
insert into employee(first_name, last_name, department_id, salary, years_worked) values('阳君', '宫', 1, 6000, 2);

例如，使用over()与order by配合使用来完成：

（1）在雇员表中，按照department_id进行分组，并使用row_number()对每一组的员工按照薪资值从高到低进行排序；
（2）在雇员表中，按照department_id进行分组，并使用rank()对每一组的员工按照薪资值从高到低进行排序；
（3）在雇员表中，按照department_id进行分组，并使用dense_rank()对每一组的员工按照薪资值从高到低进行排序；
（4）查询出雇员表中每个部门分组下的员工薪资排名前2位的员工信息，例如姓氏、名字、部门id、薪资值。

# （1）在雇员表中，按照department_id进行分组，并使用row_number()对每一组的员工按照薪资值从高到低进行排序；
select *,
       row_number() over (partition by department_id order by salary desc ) '薪资排名'
from employee;

# （2）在雇员表中，按照department_id进行分组，并使用rank()对每一组的员工按照薪资值从高到低进行排序；
select *,
       rank() over (partition by department_id order by salary desc ) '薪资排名'
from employee;

# （3）在雇员表中，按照department_id进行分组，并使用dense_rank()对每一组的员工按照薪资值从高到低进行排序；
select *,
       dense_rank() over (partition by department_id order by salary desc ) '薪资排名'
from employee;

# （4）查询出雇员表中每个部门分组下的员工薪资排名前2位的员工信息，例如姓氏、名字、部门id、薪资值。
select *
from (
     select *,
            dense_rank() over (partition by department_id order by salary desc ) rk
    from employee
         ) temp
where rk <=2;