MariaDB Window Functions窗口函數分組取TOP N記錄

窗口函數在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);

查詢結果

現在取出各班前三名

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<b.Score 
GROUP BY a.id,a.SName,a.ClsNo,a.Score HAVING COUNT(b.id)<3
ORDER BY a.ClsNo,a.Score DESC;

參考：

https://mariadb.com/kb/en/library/window-functions-overview/

https://blog.csdn.net/acmain_chm/article/details/4126306