MySQL 开窗函数

码农私塾 2021-06-24

2651

什么是开窗函数

MySQL 在8.0版本以后才引入了开窗函数这个功能,而其他数据库 SQL Server Oracle 等早已引用这个功能也叫分析函数

开窗函数怎么用

语法:

开窗函数名([<字段名>]) over([partition by <分组字段>] [order by <排序字段> [desc]] [<窗口分区>])
栗子: first_value(amount) over(partition by user_id order by order time desc)
# first_value 这个函数是返回第一个值 这里就是 根据用户id分组 然后根据时间排序 返回满足条件的金额

开窗函数分类:

也可以分为如下几类

序号函数：row_number() rank() dense_rank()
分布函数：percent_rank() cume_dist()
前后函数：lag() lead()
头尾函数：first_value() last_value()
其他函数：nth_value() nfile()

使用场景

用户表和订单表,结构和数据如下:

drop table if EXISTS user_test;
CREATE table user_test (
id int PRIMARY key  AUTO_INCREMENT,
user_account varchar(32),
user_name varchar(32),
user_password varchar(32),
user_age int(3)
);


drop table if EXISTS order_test;
CREATE table order_test (
order_id int PRIMARY key  AUTO_INCREMENT,
user_id  int not null ,
order_time datetime not null,
order_amount NUMERIC(11,2)
);


INSERT INTO `user_test` VALUES (1, 'test1', '张三', '123', 17);
INSERT INTO `user_test` VALUES (2, 'test2', '李四', '123', 18);
INSERT INTO `user_test` VALUES (3, 'test3', '王五', '123', 22);


INSERT INTO `order_test` VALUES (1, 1, '2021-06-18 17:00:57', 2200.00);
INSERT INTO `order_test` VALUES (2, 1, '2021-06-20 17:03:22', 1345.00);
INSERT INTO `order_test` VALUES (3, 3, '2021-06-23 20:04:05', 666.19);

查询需求: 查询累计消费金额大于1000块钱的用户信息

如果按照传统的写法那么结果如下

SELECT
  A.*,
  B.sum_amount 
FROM
  user_test A
  INNER JOIN ( SELECT user_id, sum( order_amount ) AS sum_amount FROM order_test GROUP BY user_id HAVING sum( order_amount )> 1000 ) AS B ON A.id = B.user_id

采用开窗函数方式来处理:

  SELECT * from (  SELECT DISTINCT  A.*,sum(B.order_amount) over(PARTITION by B.user_id) as sum_amount  from 
  user_test A INNER JOIN order_test B 
  on A.id=B.user_id ) as  T
  WHERE sum_amount>1000

总结

其实使用开窗函数的场景,不使用也能解决,如果是对某个字段进行排序然后,新增一列排序编号,那么用这个开窗函数很方便比如这样

  SELECT *,ROW_NUMBER()over(order by user_age desc ) as age_sort_no from user_test

mysql

文章转载自码农私塾，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL 开窗函数

评论