窗口函数在MariaDB10.2版本里实现,其简化了复杂SQL的撰写,提高了可读性。
成都创新互联公司公司2013年成立,是专业互联网技术服务公司,拥有项目网站设计、网站建设网站策划,项目实施与项目整合能力。我们以让每一个梦想脱颖而出为使命,1280元遵化做网站,已为上家服务,为遵化各地企业和个人服务,联系电话:13518219792
在某些方面,窗口函数类似于聚集函数, 但它不像聚集函数那样每组只返回一个值,窗口函数可以为每组返回多个值。
作为一种高级查询功能,解释起来并非易事。提供窗口函数介绍的最佳方法是通过示例,让我们看看窗口函数实现分组取TOP N记录。
表结构
CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO_INCREMENT, `SName` varchar(100) DEFAULT NULL COMMENT '姓名', `ClsNo` varchar(100) DEFAULT NULL COMMENT '班级', `Score` int(11) DEFAULT NULL COMMENT '分数', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
insert into `student`(`id`,`SName`,`ClsNo`,`Score`) values (1,'AAAA','C1',67),(2,'BBBB','C1',55),(3,'CCCC','C1',67),(4,'DDDD','C1',65), (5,'EEEE','C1',95),(6,'FFFF','C2',57),(7,'GGGG','C2',87),(8,'HHHH','C2',74), (9,'IIII','C2',52),(10,'JJJJ','C2',81),(11,'KKKK','C2',67),(12,'LLLL','C2',66), (13,'MMMM','C2',63),(14,'NNNN','C3',99),(15,'OOOO','C3',50),(16,'PPPP','C3',59), (17,'QQQQ','C3',66),(18,'RRRR','C3',76),(19,'SSSS','C3',50),(20,'TTTT','C3',50), (21,'UUUU','C3',64),(22,'VVVV','C3',74);
查询结果
现在取出各班前三名
SELECT SName,ClsNo,Score, dense_rank() OVER (PARTITION BY ClsNo ORDER BY Score DESC) AS top3 FROM student;
使用窗口函数需要OVER关键字。 dense_rank()是一个特殊的排名函数,只能作为“窗口函数”使用,不能在没有OVER子句的情况下使用。
OVER子句支持一个名为PARTITION BY的关键字,它与GROUP BY的工作方式非常相似。 使用PARTITION BY,我们将按照班级分组,并单独计算排名行号。
我们可以看到每个班级都有一个单独的排名顺序。
窗口函数的计算发生在WHERE,GROUP BY和HAVING子句完成之后,在ORDER BY之前。固这里需要外包一层派生表得到最终排名结果。
SELECT * FROM (SELECT SName,ClsNo,Score, dense_rank() OVER (PARTITION BY ClsNo ORDER BY Score DESC) AS top3 FROM student) AS tmp WHERE tmp.top3 <=3 ORDER BY tmp.ClsNO ASC,tmp.Score DESC;
通过窗口函数,非常轻松的实现分析需求,而使用传统的方法,会非常复杂,SQL理解起来也很困难。
例:
SELECT a.id,a.SName,a.ClsNo,a.Score FROM student a LEFT JOIN student b ON a.ClsNo=b.ClsNo AND a.Score参考:
https://mariadb.com/kb/en/library/window-functions-overview/
https://blog.csdn.net/acmain_chm/article/details/4126306
本文标题:MariaDBWindowFunctions窗口函数分组取TOPN记录
当前地址:http://www.cdkjz.cn/article/jjggsi.html