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
		);
  1. 插入信息数据:
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');
  1. 查看表格内容:select * from 双十一登陆表;

  2. 导入数据时还是出现了一些问题处理方法:删除、修改内容
    在这里插入图片描述

  • 删除异常值
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

SQL是高级的非过程化编程语言,是沟通数据库服务器和客户端的重要工具,允许用户在高层数据结构上工作。它不要求用户指定对数据的存放方法,也不需要用户了解具体的数据存放方式,所以,具有完全不同底层结构的不同数据库系统,可以使用相同的SQL语言作为数据输入与管理的SQL接口。 它以记录集合作为操作对象,所有SQL语句接受集合作为输入,返回集合作为输出,这种集合特性允许一条SQL语句的输出作为另一条SQL语句的输入,所以SQL语句可以嵌套,这使它具有极大的灵活性和强大的功能,在多数情况下,在其他语言中需要一大段程序实现的功能只需要一个SQL语句就可以达到目的,这也意味着用SQL语言可以写出非常复杂的语句。    结构化查询语言(Structured Query Language)最早是IBM的圣约瑟研究实验室为其关系数据库管理系统SYSTEM R开发的一种查询语言,它的前身是SQUARE语言。SQL语言结构简洁,功能强大,简单易学,所以自从IBM公司1981年推出以来,SQL语言得到了广泛的应用。如今无论是像Oracle、Sybase、DB2、Informix、SQL Server这些大型的数据库管理系统,还是像Visual Foxpro、PowerBuilder这些PC上常用的数据库开发系统,都支持SQL语言作为查询语言。    美国国家标准局(ANSI)与国际标准化组织(ISO)已经制定了SQL标准。ANSI是一个美国工业和商业集团组织,负责开发美国的商务和通讯标准。ANSI同时也是ISO和International Electrotechnical Commission(IEC)的成员之一。ANSI 发布与国际标准组织相应的美国标准。1992年,ISO和IEC发布了SQL国际标准,称为SQL-92。ANSI随之发布的相应标准是ANSI SQL-92。ANSI SQL-92有时被称为ANSI SQL。尽管不同的关系数据库使用的SQL版本有一些差异,但大多数都遵循 ANSI SQL 标准。SQL Server使用ANSI SQL-92的扩展集,称为T-SQL,其遵循ANSI制定的 SQL-92标准。    SQL语言包含4个部分:    数据定义语言(DDL),例如:CREATE、DROP、ALTER等语句。    数据操作语言(DML),例如:INSERT(插入)、UPDATE(修改)、DELETE(删除)语句。    数据查询语言(DQL),例如:SELECT语句。    数据控制语言(DCL),例如:GRANT、REVOKE、COMMIT、ROLLBACK等语句。    SQL语言包括三种主要程序设计语言类别的语句:数据定义语言(DDL),数据操作语言(DML)及数据控制语言(DCL)。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值