SQL窗口函数练习题(排名问题、topN问题）

** 笔记内容包括SQL窗口函数的介绍、练习题的解答全流程。

窗口函数语法

排名问题：每个部门按业绩来排名
topN问题：找出每个部门排名前N的员工进行奖励

窗口函数语法：

<窗口函数> over (partition by <用于分组的列名>
order by <用于排序的列名>)

<窗口函数>可以放以下两种函数：
1） 专用窗口函数，包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。
2） 聚合函数，如sum. avg, count, max, min等
专用窗口函数
rank, dense_rank, row_number这三个函数的区别如下：

对表分组
Group by VS partition by用来对表分组

练习题：双十一分析

1. 创建一个库 ：create database Exercise;
2. 查看库是否建立成功：show databases；
3. 使用这个练习库： use Exercise;
4. 查看库里的表格：show tables;
5. 创建表格

create table 双十一登陆表(
		id int,
		姓名 varchar(100),
		邮箱地址  varchar(100),
		最后登陆时间 date
		);

6. 插入信息数据：

insert into 双十一登陆表(id,姓名,邮箱地址,最后登陆时间) values(100, 'test4', 'test@yahoo.cn','2007/11/25 16:31');
insert into 双十一登陆表(id,姓名,邮箱地址,最后登陆时间) values(13,'test1','test@yahoo.cn','2007/3/22 16:27');
insert into 双十一登陆表(id,姓名,邮箱地址,最后登陆时间) values(19,'test1','test@yahoo.cn','2007/10/25 14:13');
insert into 双十一登陆表(id,姓名,邮箱地址,最后登陆时间) values(42,'test1','test@yahoo.cn','2007/10/25 14:20');
insert into 双十一登陆表(id,姓名,邮箱地址,最后登陆时间) values(45,'test2','test@yahoo.cn','2007/4/25 14:17');
insert into 双十一登陆表(id,姓名,邮箱地址,最后登陆时间) values(49,'test2','test@ahoo.cn','2007/5/25 14:22');

7. 查看表格内容：select * from 双十一登陆表;

8. 导入数据时还是出现了一些问题处理方法：删除、修改内容
   

• 删除异常值

delete from 双十一登陆表
where 最后登陆时间 is NULL;

• 修改错误值

update 双十一登陆表
set 邮箱地址='test@yahoo.cn'
where id=49;

问题1：按时间给出每个人的登录次数，登录时间最早的为1，之后的分别是2，3，4等。

因为是排名问题，所以使用分组（窗口函数partiotion by 姓名），并按最后登录时间升序排列(order by最后登录时间 asc)。

SELECT 姓名,`最后登陆时间`,row_number ( ) over (PARTITION BY 姓名 ORDER BY `最后登陆时间` ASC ) AS 登录时间排名 
 FROM `双十一登陆表`;

问题2：按天给出每个人的登陆次数，同一天多次登陆认为是同一次，最早标记为1，之后以此类推。

排名问题，要使用窗口函数。因为同一天登陆的记录排名相同，不占用下一名次排名，所以用dense_rank函数

SELECT 姓名,`最后登陆时间`,
			 dense_rank ( ) over (PARTITION BY 姓名 ORDER BY `最后登陆时间` ASC ) AS 登录时间排名 
 FROM `双十一登陆表`;

发现结果没有变化，其中test1 的登陆时间排名同一天却有两个不同排名。题目要求是登陆天数，所以需要使用date_format(date,format)来转化为时间格式：date_format(最新登陆时间,’%Y%m%d’)。

#同一天
SELECT 姓名,`最后登陆时间`,
			 dense_rank ( ) over (PARTITION BY 姓名 ORDER BY date_format(`最后登陆时间`,'%Y%m&d' ) ASC ) AS 登录时间排名 
 FROM `双十一登陆表`;

1、2答案结果汇总

SELECT 姓名,`最后登陆时间`,
			 row_number ( ) over (PARTITION BY 姓名 ORDER BY `最后登陆时间` ASC ) AS 登录时间排名,
			 dense_rank ( ) over (PARTITION BY 姓名 ORDER BY date_format(`最后登陆时间`,'%Y%m&d' ) ASC ) AS 登录天数排名 
 FROM `双十一登陆表`;

问题3：找出每个用户第一次登陆的日期。

SELECT *
FROM( 
SELECT 姓名, 最后登陆时间, row_number()over ( PARTITION BY 姓名 ORDER BY `最后登陆时间` ASC ) AS 登录时间排名
	FROM 双十一登陆表 ) AS a 
WHERE
	登录时间排名 = 1;

通俗易懂的学会：SQL窗口函数:https://zhuanlan.zhihu.com/p/92654574