MySQL中如何使用开窗函数
（1）开窗函数的定义开窗函数也叫OLAP函数（OnlineAnalyticalProcessing，联机分析处理），主要用来实时分析处理数据。MySQL之前的版本是不支持开窗函数的，从8.0版本之后开始支持开窗函数。#开窗函数语法func_name()OVER([PARTITIONBY][ORDERBYASC|DESC])开窗函数语句解析：函数分为两部分，一部分是函数名称，开窗函数的数量比较少，总共才11个开窗函数+聚合函数（所有的聚合函数都可以用作开窗函数）。根据函数的性质，有的需要写参数，有的不需要写
（1）开窗函数的定义
开窗函数也叫OLAP函数（Online Analytical Processing，联机分析处理），主要用来实时分析处理数据。MySQL之前的版本是不支持开窗函数的，从8.0版本之后开始支持开窗函数。
# 开窗函数语法 func_name() OVER([PARTITION BY ] [ORDER BY 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
详解Navicat导入数据库的标准步骤，涵盖SQL脚本执行与CSV数据导入两种核心场景。针对导入过程中常见的字符集乱码、主键冲突及外键约束错误，提供具体的设置方法与修复建议，确保数据迁移准确无误。
深入理解SQL PRIMARY KEY与UNIQUE约束的区别。文章从行标识与重复限制的目标出发，详解主键的非空特性、外键引用角色以及多数据库对NULL值的处理差异。结合users表实战示例，指导开发者如何根据字段稳定性与业务语义合理选择约束类型，避免设计误区。
刚开始设计关系型数据库？了解PRIMARY KEY、FOREIGN KEY、UNIQUE、NOT NULL、CHECK、DEFAULT等约束的作用、语法和排错方法。本文以PostgreSQL语法为主，结合用户订单场景，清晰展示如何防止脏数据并处理常见约束冲突。
深入解析SQL游标导致的死锁与资源未释放问题。通过PostgreSQL与MySQL语法对比，展示如何识别长事务持锁、分析锁等待链，并提供短事务提交、异常清理及批量处理的最佳实践，确保数据库连接与锁资源及时释放。
后端开发者在维护数据库时，常需对现有表添加主键、唯一或外键约束。若直接执行ADD CONSTRAINT，往往因存量数据不合规导致报错。本文详解操作前的数据清洗步骤、标准SQL语法及MySQL等特殊写法，帮助你在生产环境安全完成结构变更。
针对 SQL 初学者的分组统计实战指南。深入解析 GROUP BY 分组机制、WHERE 与 HAVING 的执行时机差异，以及 COUNT 函数对 NULL 值的处理逻辑。通过具体代码示例演示如何正确筛选聚合结果，并排查因 JOIN 连接导致的统计数据异常，提升报表准确性。
本文详细对比SQL Server和MySQL中游标的基本用法。从DECLARE声明到FETCH遍历，再到CLOSE关闭与DEALLOCATE/Handler清理，解析两者在语法结构、变量接收及异常处理上的核心差异。通过标准代码示例展示如何安全使用游标，避免资源泄漏，适用于需要进行数据库存储过程开发或跨平台迁移的开发者。
深入解析SQL中INNER JOIN与LEFT JOIN的核心区别。通过users和orders表实例，展示匹配行保留与NULL补位机制，重点讲解ON与WHERE子句中放置右表过滤条件的不同后果，提供防止LEFT JOIN退化的实战技巧及行数异常排查步骤。
深入理解 SQL CHECK 约束在 PostgreSQL、MySQL 等数据库中的实现差异。文章通过订单业务场景，讲解如何定义单列范围与多列逻辑约束，分析 NULL 值对布尔条件的影响，并提供修改已有表结构时的安全操作建议，帮助开发者构建更健壮的数据完整性防线。
后端开发中，如何正确使用 SQL 外键约束来维护订单与明细表的关系？本文通过 PostgreSQL 示例，深入讲解外键的创建语法、插入顺序要求，以及 ON DELETE CASCADE、RESTRICT、SET NULL 等策略的实际影响与风险控制，助你构建健壮的数据库结构。
火山引擎是字节跳动旗下企业级云与AI服务平台。聚焦大模型核心能力，依托字节自研豆包多模态大模型，凭借火山方舟一站式平台，提供模型精调、推理部署、智能应用搭建等全链路服务，结合高性能AI算力，助力各行业快速落地大模型数字化与智能化应用。
