窗口函数的基本语法
<窗口函数> OVER ([PARTITION BY <用于分组的列>] ORDER BY <用于排序的列>)
※ []中的内容可以省略
注意:Mysql从8.0版本才开始支持窗口函数
ROW_NUMBER()、RANK()、DENSE_RANK()的区别
(1) ROW_NUMBER(): 依次排序,不会出现相同排名
(2) RANK(): 出现相同排名时,跳跃排序
(3) DENSE_RANK(): 出现相同排名时,连续排序
导入数据
DROP TABLE IF EXISTS order_content;
CREATE TABLE order_content(
order_id VARCHAR(8),
user_id VARCHAR(8),
order_price INT
)
ENGINE = InnoDB
DEFAULT CHARSET = utf8;
INSERT INTO
order_content (order_id,user_id,order_price)
VALUE ('o001','u001',800)
,('o002','u001',800)
,('o003','u001',1000)
,('o004','u001',1200)
,('o005','u002',400)
,('o006','u002',1500)
,('o007','u002',2100)
,('o008','u003',900)
,('o009','u003',700)
,('o010','u003',1700);
order_content表(订单内容表)
order_id:订单号 user_id:下单用户 order_price:订单金额
使用RANK()函数
SELECT *,
RANK() OVER (PARTITION BY user_id ORDER BY order_price) AS ranking
FROM order_content;
结果展示:
以user_id字段分组并在组内按照order_price字段升序排列;在每组内按照order_price字段排名,order_price字段值越小,则排名越靠前。在使用RANK()函数排名时,当要排名的数据值大小相同时,它们的排名是一致的。例如,o001和o002都是第一名,而之后的排名从第3名开始,如o003
使用DENSE_RANK()函数
SELECT *,
DENSE_RANK() OVER (PARTITION BY user_id ORDER BY order_price) AS dense_ranking
FROM order_content;
结果展示:
使用ROW_NUMBER()函数
SELECT *,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_price) AS row_num
FROM order_content;
结果展示:
删除上述代码中的PARTITION BY user_id,则结果和普通的ORDER BY结果一样
-- RANK()函数
SELECT *,
RANK() OVER (ORDER BY order_price) AS ranking
FROM order_content;
-- DENSE_RANK()函数
SELECT *,
DENSE_RANK() OVER (ORDER BY order_price) AS dense_ranking
FROM order_content;
-- ROW_NUMBER()函数
SELECT *,
ROW_NUMBER() OVER (ORDER BY order_price) AS row_num
FROM order_content;
结果展示:
RANK()函数
DENSE_RANK()函数
ROW_NUMBER()函数
查询每个用户订单金额最高的订单信息
-- 第一种解法
SELECT *
FROM (SELECT *,
RANK() OVER (PARTITION BY user_id ORDER BY order_price DESC)AS ranking
FROM order_content)AS a
WHERE a.ranking = 1;
-- 第二种解法
SELECT *
FROM (SELECT *,
DENSE_RANK() OVER (PARTITION BY user_id ORDER BY order_price DESC)AS dense_ranking
FROM order_content)AS a
WHERE a.dense_ranking = 1;
-- 第三种解法
SELECT *
FROM (SELECT *,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_price DESC)AS row_num
FROM order_content)AS a
WHERE a.row_num = 1;
结果展示:
在子查询内部使用RANK()函数 / DENSE_RANK()函数 / ROW_NUMBER()函数,通过子查询得到子查询内部结果,在子查询外部筛选使用RANK()函数 / DENSE_RANK()函数 / ROW_NUMBER()函数所得结果中的第一名,得到每个用户订单金额最高的订单信息结果
RANK()、DENSE_RANK()、ROW_NUMBER()函数的异同
SELECT *,
RANK() OVER (PARTITION BY user_id ORDER BY order_price)AS ranking,
DENSE_RANK() OVER (PARTITION BY user_id ORDER BY order_price)AS dense_ranking,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_price)AS row_num
FROM order_content;
结果展示:
从上面红色框中的结果可以看出:
rank函数
这个例子中是1位,1位,3位,4位,也就是如果有并列名次的行,会占用下一名次的位置
比如正常排名是1,2,3,4,但是现在前3名是并列的名次,结果是:1,1,1,4
dense_rank函数
这个例子中是1位,1位,2位,3位,也就是如果有并列名次的行,不占用下一名次的位置
比如正常排名是1,2,3,4,但是现在前3名是并列的名次,结果是:1,1,1,2
row_number函数
这个例子中是1位,2位,3位,4位,也就是不考虑并列名次的情况
比如前3名是并列的名次,排名是正常的1,2,3,4
练习案例
案例1
导入数据
DROP TABLE IF EXISTS SC;
create table SC(
SId varchar(10),
CId varchar(10),
score decimal(18,1)
)
ENGINE = InnoDB
DEFAULT CHARSET = utf8;
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);
sc表
1.1按各科成绩进行排序,并显示排名,Score重复时也继续排名
SELECT *,
ROW_NUMBER() OVER (PARTITION BY CId ORDER BY score DESC) AS 排名
FROM sc;
结果展示:
1.2按各科成绩进行排序,并显示排名,Score重复时合并名次
使用窗口函数rank()
SELECT *,
RANK() OVER (PARTITION BY CId ORDER BY score DESC) AS 排名
FROM sc;
结果展示:
使用窗口函数dense_rank()
SELECT *,
DENSE_RANK() OVER (PARTITION BY CId ORDER BY score DESC) AS 排名
FROM sc;
结果展示:
案例2:获取每个部门中当前员工薪水最高的相关信息
导入数据
drop table if exists `dept_emp` ;
drop table if exists `salaries` ;
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_emp VALUES(10003,'d002','1996-08-03','9999-01-01');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,92527,'2001-08-02','9999-01-01');
dept_emp表(员工表)
salaries表(薪水表)
问题:获取每个部门中当前员工薪水最高的相关信息,给出dept_no, emp_no以及其对应的salary,按照部门编号dept_no升序排列
SELECT temp.dept_no, temp.emp_no, temp.salary AS maxSalary
FROM (SELECT a.dept_no, b.emp_no, b.salary,
DENSE_RANK() OVER (PARTITION BY a.dept_no ORDER BY b.salary DESC)AS dense_ranking
FROM dept_emp AS a
INNER JOIN salaries AS b
ON a.emp_no = b.emp_no)AS temp
WHERE temp.dense_ranking = 1
ORDER BY temp.dept_no ASC;
结果展示:
案例3:获取当前薪水第二多的员工的emp_no以及其对应的薪水salary
导入数据
drop table if exists `salaries` ;
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
salaries表
问题:获取薪水第二多的员工的emp_no以及其对应的薪水salary,若有多个员工的薪水为第二多的薪水,则将对应的员工的emp_no和salary全部输出,并按emp_no升序排序
SELECT a.emp_no, a.salary
FROM(SELECT emp_no, salary,
DENSE_RANK() OVER(ORDER BY salary DESC)AS dense_ranking
FROM salaries)AS a
WHERE a.dense_ranking = 2
ORDER BY a.emp_no ASC;
结果展示: