当前位置：首页 > news >正文

MariaDB Window Functions窗口函数分组取TOP N记录

news 来源：原创 2024/5/18 21:58:06

窗口函数在MariaDB10.2版本里实现，其简化了复杂SQL的撰写，提高了可读性。

在某些方面，窗口函数类似于聚集函数，但它不像聚集函数那样每组只返回一个值，窗口函数可以为每组返回多个值。

作为一种高级查询功能，解释起来并非易事。提供窗口函数介绍的最佳方法是通过示例，让我们看看窗口函数实现分组取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);

查询结果

图片.png

现在取出各班前三名

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，我们将按照班级分组，并单独计算排名行号。

图片.png

我们可以看到每个班级都有一个单独的排名顺序。

窗口函数的计算发生在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;

图片.png

通过窗口函数，非常轻松的实现分析需求，而使用传统的方法，会非常复杂，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<b.Score 
GROUP BY a.id,a.SName,a.ClsNo,a.Score HAVING COUNT(b.id)<3
ORDER BY a.ClsNo,a.Score DESC;

图片.png