扫码关注官方微信

扫码下载APP
返回顶部
首页 > 资讯 > 数据库 >MySQL 开窗函数
  • 692

0
分享到

MySQL 开窗函数

2024-04-02 19:04:59 692人浏览 八月长安
摘要

目录（1）开窗函数的定义（2）开窗函数的实际应用场景结合order by关键词和limit关键词是可以解决很多的topN问题，比如从二手房数据集中查询出某个地区的最贵的10套房，从电

结合order by关键词和limit关键词是可以解决很多的topN问题，比如从二手房数据集中查询出某个地区的最贵的10套房，从电商交易数据集中查询出实付金额最高的5笔交易，从学员信息表中查询出年龄最小的3个学员等。但是，如果需求变成从二手房数据集中查询出各个地区最贵的10套房，从电商数据集中查询出每月实付金额最高的5笔交易，从学员信息表中查询出各个科系下年龄最小的3个学员，该如何解决呢？

其实这类问题的核心就是，筛选出组内的topN，而不是从全部数据集中挑选出topN。遇到这种既需要分组也需要排序的问题，直接上开窗函数就能解决了。

（1）开窗函数的定义

开窗函数也叫OLAP函数（Online Analytical Processing，联机分析处理），主要用来实时分析处理数据。Mysql之前的版本是不支持开窗函数的，从8.0版本之后开始支持开窗函数。

# 开窗函数语法 
func_name(<parameter>) 
OVER([PARTITioN BY <part_by_condition>] 
[ORDER BY <order_by_list> ASC|DESC])

开窗函数语句解析：
函数分为两部分，一部分是函数名称，开窗函数的数量比较少，总共才11个开窗函数+聚合函数（所有的聚合函数都可以用作开窗函数）。根据函数的性质，有的需要写参数，有的不需要写参数。

另一部分为over语句，over()是必须要写的，里面的参数都是非必须参数，可以根据需求有选择地使用：

  • 第一个参数是partition by + 字段，含义是根据此字段将数据集分为多份
  • 第二个参数是order by + 字段，每个窗口的数据依据此字段进行升序或降序排列

在这里插入图片描述

开窗函数与分组聚合函数比较相似，都是通过指定字段将数据分成多份，区别在于：

  • sql 标准允许将所有聚合函数用作开窗函数，用OVER 关键字区分开窗函数和聚合函数。
  • 聚合函数每组只返回一个值，开窗函数每组可返回多个值。

在这11个开窗函数中，实际工作中用的最多的当属ROW_NUMBER()、RANK()、DENSE_RANK()这三个排序函数了。下面我们通过一个简单的数据集学习一下这三个开窗函数。

# 首先创建虚拟的业务员销售数据 
CREATE TABLE Sales
( 
idate date, 
iname char(2), 
sales int
); 
# 向表中插入数据 
INSERT INTO Sales VALUES 
('2021/1/1', '丁一', 200), 
('2021/2/1', '丁一', 180), 
('2021/2/1', '李四', 100), 
('2021/3/1', '李四', 150), 
('2021/2/1', '刘猛', 180), 
('2021/3/1', '刘猛', 150), 
('2021/1/1', '王二', 200), 
('2021/2/1', '王二', 180), 
('2021/3/1', '王二', 300), 
('2021/1/1', '张三', 300), 
('2021/2/1', '张三', 280), 
('2021/3/1', '张三', 280); 
# 数据查询 
SELECT * FROM Sales; 
# 查询各月中销售业绩最差的业务员
SELECT month(idate),iname,sales, 
	ROW_NUMBER() 
	OVER(PARTITION BY month(idate) 
			 ORDER BY sales) as sales_order 
FROM Sales;

SELECT * FROM 
(SELECT month(idate),iname,sales, 
	 ROW_NUMBER() 
	 OVER(PARTITION BY month(idate) 
   ORDER BY sales) as sales_order FROM Sales) as t
WHERE sales_order=1;

在这里插入图片描述

# ROW_NUMBER()、RANK()、DENSE_RANK()的区别 
SELECT * FROM 
(SELECT month(idate) as imonth,iname,sales, 
ROW_NUMBER() 
OVER(PARTITION BY month(idate) ORDER BY sales) as row_order,
RANK() 
OVER(PARTITION BY month(idate) ORDER BY sales) as rank_order, 
DENSE_RANK() 
OVER(PARTITION BY month(idate) ORDER BY sales) as dense_order 
FROM Sales) as t;

在这里插入图片描述

ROW_NUMBER()：顺序排序——1、2、3
RANK()：并列排序，跳过重复序号——1、1、3
DENSE_RANK()：并列排序，不跳过重复序号——1、1、2

（2）开窗函数的实际应用场景

在实际工作或者面试中，可能会遇到求用户连续登录天数、连续签到天数等问题。下面就提供一个用开窗函数解决此类问题的思路。

# 首先创建虚拟的用户登录表，并插入数据 
create table user_login
( 
user_id varchar(100), 
login_time datetime
); 

insert into user_login values 
(1,'2020-11-25 13:21:12'), 
(1,'2020-11-24 13:15:22'), 
(1,'2020-11-24 10:30:15'), 
(1,'2020-11-24 09:18:27'), 
(1,'2020-11-23 07:43:54'), 
(1,'2020-11-10 09:48:36'), 
(1,'2020-11-09 03:30:22'), 
(1,'2020-11-01 15:28:29'), 
(1,'2020-10-31 09:37:45'), 
(2,'2020-11-25 13:54:40'), 
(2,'2020-11-24 13:22:32'), 
(2,'2020-11-23 10:55:52'), 
(2,'2020-11-22 06:30:09'), 
(2,'2020-11-21 08:33:15'), 
(2,'2020-11-20 05:38:18'), 
(2,'2020-11-19 09:21:42'), 
(2,'2020-11-02 00:19:38'), 
(2,'2020-11-01 09:03:11'), 
(2,'2020-10-31 07:44:55'), 
(2,'2020-10-30 08:56:33'), 
(2,'2020-10-29 09:30:28'); 
# 查看数据 
SELECT * FROM user_login;

计算连续登录天数通常会有以下三种情况：

  • 查看每位用户连续登录的情况
  • 查看每位用户最大连续登录的天数
  • 查看在某个时间段里连续登录天数超过N天的用户

针对第一种情况：查看每位用户连续登录的情况
根据实际经验，我们知道在一段时间内，用户可能出现多次连续登录，这些信息我们都要输出，所以最后结果输出的字段可以是用户ID、首次登录日期、结束登录日期、连续登录天数这四个。

# 数据预处理：由于统计的窗口期是天数，所以可以对登录时间字段进行格式转换，将其变成日期格式然后再去重（去掉用户同一天内多次登录的情况） 
# 为方便后续代码查看，将处理结果放置新表中，一步一步操作 
create table user_login_date(
select distinct user_id, date(login_time) login_date from user_login);
# 处理后的数据如下： 
select * from user_login_date;

# 第一种情况：查看每位用户连续登陆的情况 
# 对用户登录数据进行排序 
create table user_login_date_1( 
select *,
rank() over(partition by user_id order by login_date) irank 
from user_login_date); 
#查看结果 
select * from user_login_date_1;
 
# 增加辅助列，帮助判断用户是否连续登录 
create table user_login_date_2( 
select *,
date_sub(login_date, interval irank DAY) idate  #data_sub从指定的日期减去指定的时间间隔
from user_login_date_1); 
# 查看结果 
select * from user_login_date_2; 

# 计算每位用户连续登录天数 
select user_id, 
min(login_date) as start_date, 
max(login_date) as end_date, 
count(login_date) as days 
from user_login_date_2 
group by user_id,idate;

# ===============【整合代码，解决用户连续登录问题】=================== 
select user_id, 
       min(login_date) start_date, 
       max(login_date) end_date, 
       count(login_date) days 
from (select *,date_sub(login_date, interval irank day) idate 
from (select *,rank() over(partition by user_id order by login_date) irank 
from (select distinct user_id, date(login_time) login_date from user_login) as a) as b) as c 
group by user_id,idate;

针对第二种情况：查看每位用户最大连续登录的天数

# 计算每个用户最大连续登录天数 
select user_id,max(days) from 
(select user_id, 
			 min(login_date) start_date, 
			 max(login_date) end_date, 
			 count(login_date) days 
from (select *,date_sub(login_date, interval irank day) idate 
from (select *,rank() over(partition by user_id order by login_date) irank 
from (select distinct user_id, date(login_time) login_date from user_login) as a) as b) as c 
group by user_id,idate) as d 
group by user_id;

针对第三种情况：查看在某个时间段里连续登录天数超过N天的用户

假如说，我们的需求是查看10/29-11/25在这段时间内连续登录天数≥5天的用户。这个需求也可以用第一种情况查询的结果进行筛选。

# 查看在这段时间内连续登录天数≥5天的用户 
select distinct user_id from 
(select user_id, 
		min(login_date) start_date, 
		max(login_date) end_date, 
		count(login_date) days 
from (select *,date_sub(login_date, interval irank day) idate 
from (select *,rank() over(partition by user_id order by login_date) irank 
from (select distinct user_id, date(login_time) login_date from user_login) as a) as b) as c 
group by user_id,idate 
having days>=5
) as d;

这种写法是可以得出结果，但是针对这个问题来说有点麻烦了，下面介绍一个简单的方法：引用一个新的静态窗口函数lead()

select *, 
lead(login_date,4) over(partition by user_id order by login_date) as idate5 
from user_login_date;

lead函数有三个参数，第一个参数是指定的列（这里用登陆日期），第二个参数是当前行向后几行的值，这里用的是4，也就是第五次登录的日期，第三个参数是如果返回的空值可以用指定值替代，这里没有使用第三个参数。 over语句里面是针对user_id分窗，每个窗口针对登录日期升序。

用第五次登录日期 - login_date+1，如果等于5，说明是连续登录五天的，如果得到空值或者大于5，说明没有连续登录五天，代码和结果如下：

# 计算第5次登录日期与当天的差值 
select *,datediff(idate5,login_date)+1 days 
from (select *,lead(login_date,4) over(partition by user_id order by login_date) idate5
from user_login_date) as a; 
# 找出相差天数为5的记录 
select distinct user_id 
from (select *,datediff(idate5,login_date)+1 as days 
from (select *,lead(login_date,4) over(partition by user_id order by login_date) idate5 
from user_logrin_date) as a)as b 
where days = 5;

【练习】美团外卖平台数据分析面试题——SQL
现有交易数据表user_Goods_table如下：

在这里插入图片描述

现在老板想知道每个用户购买的外卖品类偏好分布，并找出每个用户购买最多的外卖品类是哪个。

# 分析题目：要求输出字段为用户名user_name，该用户购买最多的外卖品类goods_kind 
# 解题思路：这是一个分组排序的问题，可以考虑窗口函数 
# 第一步：使用窗口函数row_number()，对每个用户购买的外卖品类进行分组统计与排名
select user_name,goods_kind,count(goods_kind),
rank() over (partition by user_name order by count(goods_kind) desc) as irank
from user_goods_table
group by user_name,goods_kind;

# 第二步：筛选出每个用户排名第一的外卖品类
select user_id,goods_kind from 
(select user_name,goods_kind,count(goods_kind),
rank() over (partition by user_name order by count(goods_kind) desc) as irank
from user_goods_table
group by user_name,goods_kind) as a 
where irank=1

到此这篇关于mysql 开窗函数的文章就介绍到这了,更多相关MySQL 开窗函数内容请搜索编程网以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程网！

您可能感兴趣的文档:

--结束END--

本文标题: MySQL 开窗函数

本文链接: https://lsjlt.com/news/139181.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com    QQ/279061341

猜你喜欢
  • MySQL 开窗函数
    目录（1）开窗函数的定义（2）开窗函数的实际应用场景结合order by关键词和limit关键词是可以解决很多的topN问题，比如从二手房数据集中查询出某个地区的最贵的10套房，从电...
    99+
    2024-04-02
  • MySQL：开窗函数
    当查询条件需要用到复杂子查询时，聚合函数操作起来非常麻烦，因此使用开窗函数能够轻松实现。 注意：在Oracle中称为分析函数。            在MySQL中称为开窗函数，使用于MySQL8.0以上版本，sql sever、hive、...
    99+
    2023-09-03
    mysql 数据库
  • SQL中的开窗函数(窗口函数)
    目录窗口函数1.1 排序窗口函数rank1.2 rank(), dense_rank(), row_number()区别1.3 排序截取数据lag(),lead(),ntile(),cume_dist()1.4 聚合函数...
    99+
    2024-04-02
  • MySQL中如何使用开窗函数
    这篇文章主要介绍了MySQL中如何使用开窗函数，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。（1）开窗函数的定义开窗函数也叫OLAP函数（Online Analytical ...
    99+
    2023-06-29
  • 【Mysql系列】LAG与LEAD开窗函数
    💝💝💝欢迎来到我的博客，很高兴能够在这里和您见面！希望您在这里可以感受到一份轻松愉快的氛围，不仅可以获得有趣的内容和知识，也可以畅所欲言、分享您的想法和见解。 推荐:kwan 的首页,...
    99+
    2023-12-22
    mysql 数据库
  • Mysql 窗口函数
    一, MySQl 8.0 窗口函数 窗口函数适用场景: 对分组统计结果中的每一条记录进行计算的场景下, 使用窗口函数更好, 注意, 是每一条!! 因为MySQL的普通聚合函数的结果(如 group...
    99+
    2023-09-17
    scala spark java
  • oracle的开窗函数
    原创select *  from (select province,               ...
    99+
    2024-04-02
  • MySQL8.0数据库开窗函数
    简介       数据库开窗函数是一种在SQL中使用的函数，它可以用来对结果集中的数据进行分组和排序，以便更好地分析和处理数据。开窗函数与聚合函数不同，它不会将多行数据聚合成一行，而是保留每一行数据，并对其进行分组和排序。 常见的开窗函...
    99+
    2023-09-13
    数据库 mysql
  • ORACLE数据库 开窗函数
    开窗函数 2.1开窗函数的定义及语法 开窗函数（又名：分析函数，窗口函数，OLAP函数） 聚合函数：将数据按照一定的规则分组，统一分析各组的某项情况，每个分组返回一行结果 开窗函数：将数据按照一定的规则分组，统一分析各组的某项情况，每行数据...
    99+
    2023-09-02
    数据库 oracle python
  • MySQL窗口函数（MySQL Window Functions）
           MySQL从8.0开始支持窗口函数（Window Functions），因其经常被用在数据分析的场景，窗口函数也被称为分析函数（Analytic Functions）。 目录 一、窗口函数概念 二、基础语法 ...
    99+
    2023-09-01
    数据库
  • MYSQL窗口函数（Rows & Range）——滑动窗口函数用法
    语法介绍 窗口函数语法： over (partition by order by rows/range子句 ) 可以放以下两种函数： 1） 专用窗口函数，包括后面要讲到的rank, den...
    99+
    2023-09-03
    mysql 数据库
  • MySQL窗口函数 PARTITION BY()函数介绍
    前期数据准备 # 创建数据库create database if not exists shopping charset utf8;# 选择数据库use shopping;# 创建产品表create ...
    99+
    2023-09-12
    mysql sql 数据库
  • mysql窗口函数有哪些
    本篇文章为大家展示了mysql窗口函数有哪些，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。1、说明可以用来实现一些新的查询方式。窗口函数像SUM()、COUNT()这样的集合函数，但不是将多行查询结...
    99+
    2023-06-15
  • Oracle分析函数之开窗函数over()详解
    分析函数是什么？ 分析函数是Oracle专门用于 解决复杂报表统计需求 的功能强大的函数， 它可以在数据中进行分组然后计算基于组的某种统计值 ，并且每一组的每一行都可以返回一个统计值。 ...
    99+
    2024-04-02
  • mysql如何使用窗口函数
    使用 mysql 窗口函数的步骤：1. 定义窗口（over 关键字）、2. 选择窗口函数（sum()、avg()、rank()）、3. 应用窗口函数。窗口函数类型有分组、排序、行、累计。...
    99+
    2024-06-14
    mysql
  • SQL中的开窗函数是什么
    本篇内容主要讲解“SQL中的开窗函数是什么”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“SQL中的开窗函数是什么”吧!OVER的定义OVER用于为行定义一个窗口...
    99+
    2024-04-02
  • MySQL 8.0窗口函数怎么运行
    今天就跟大家聊聊有关MySQL 8.0窗口函数怎么运行，可能很多人都不太了解，为了让大家更加了解，小编给大家总结了以下内容，希望大家根据这篇文章可以有所收获。# ROW_NUMBER() ...
    99+
    2024-04-02
  • MySQL窗口函数的具体使用
    目录一、什么是窗口函数1、怎么理解窗口?2、什么是窗口函数二、窗口函数用法1、序号函数：row_number() / rank() / dense_rank()2、分布函数：...
    99+
    2024-04-02
  • 窗口函数OVER(PARTITION BY)详细用法——语法+函数+开窗范围ROWS和RANGE
    目录 一、函数写法 二、开窗的窗口范围ROWS与RANGE 1.范围限定用法 2.ROWS和RANGE的区别 (1) ROWS按行数限定 (2) RANGE按数据范围限定         order by 数字               ...
    99+
    2023-09-02
    数据库 mysql sql
  • SparkSQL开窗函数分析使用示例
    目录聚合函数和开窗函数开窗函数聚合开窗函数排序开窗函数开窗函数能在每行的最后一行都显示聚合函数的结果，所以聚合函数可以用作开窗函数 聚合函数和开窗函数 聚合函数是将多行变成一行，如果...
    99+
    2023-01-28
    SparkSQL开窗函数 SparkSQL窗口函数
软考高级职称资格查询
热门wiki
近期文章
推荐阅读
热门问答
热门标签
编程网，编程工程师的家园，是目前国内优秀的开源技术社区之一，形成了由开源软件库、代码分享、资讯、协作翻译、讨论区和博客等几大频道内容，为IT开发者提供了一个发现、使用、并交流开源技术的平台。

  • 官方手机版

  • 微信公众号

  • 商务合作