窗口函数的基本语法
<窗口函数> OVER ([PARTITION BY <用于分组的列>] ORDER BY <用于排序的列>)
※ []中的内容可以省略
注意:Mysql从8.0版本才开始支持窗口函数
数据导入
DROP TABLE IF EXISTS order_content;
CREATE TABLE order_content(
order_id VARCHAR(8),
user_id VARCHAR(8),
order_price INT,
order_date DATE
)
ENGINE = InnoDB
DEFAULT CHARSET = utf8;
INSERT INTO
order_content (order_id,user_id,order_price,order_date)
VALUE ('o001','u001',800,'2021-06-18')
,('o002','u001',800,'2021-06-19')
,('o003','u001',1000,'2021-06-22')
,('o004','u001',1200,'2021-06-24')
,('o005','u002',400,'2021-06-25')
,('o006','u002',1500,'2021-06-26')
,('o007','u002',2100,'2021-06-28')
,('o008','u003',900,'2021-07-01')
,('o009','u003',700,'2021-07-03')
,('o010','u003',1700,'2021-07-04');
order_content表
1.FIRST_VALUE()函数
FIRST_VALUE()函数用于返回当前第一个值
SELECT *,
FIRST_VALUE(order_price) OVER (PARTITION BY user_id ORDER BY order_price) AS firstvalue
FROM order_content;
结果展示:
按照user_id字段进行分区,FIRST_VALUE()函数返回了每个分区的order_price字段的第一个值
2.LAST_VALUE()函数
LAST_VALUE()函数用于返回当前最后一个值
SELECT *,
LAST_VALUE(order_price) OVER (PARTITION BY user_id ORDER BY order_price)AS lastvalue
FROM order_content;
结果展示:
当前最后一个值即当前值,所以order_price和lastvalue字段的值相同
3.NTH_VALUE()函数
NTH_VALUE()函数用于返回有序行的第n小的值
SELECT *,
NTH_VALUE(order_price,3) OVER (PARTITION BY user_id ORDER BY order_price)AS nthvalue
FROM order_content;
结果展示:
NTH_VALUE()函数返回了每个分区第3小的数据,所以NTH_VALUE()函数通常用来求排在第n位的数据。例如,查询部门薪资排在第2位的员工信息,而返回排在第n位的数据只需要在ORDER BY之后添加DESC