Grouping TOP N records with MariaDB Window Functions window function

The window function is implemented in the MariaDB10.2 version, which simplifies the writing of complex SQL and improves readability.

In some ways, window functions are similar to aggregate functions, but unlike aggregate functions, which return only one value per group, window functions can return multiple values for each group.

As an advanced query function, it is not easy to explain. The best way to provide an introduction to the window function is through an example, let's look at the window function implementation to group TOP N records.

Table structure

CREATE TABLE `student` (`id` int (11) NOT NULL AUTO_INCREMENT, `SName` varchar (100) DEFAULT NULL COMMENT 'name', `ClsNo` varchar (100) DEFAULT NULL COMMENT 'class', `Score`int (11) DEFAULT NULL COMMENT 'score', PRIMARY KEY (`id`) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 Insert into `student` (`id`, `SName`, `ClsNo`, `Score`) values (1) GAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA ), (11) KKKKLY), (66) (12), (13), (13), (14), (15) (15), (15) OOOOOOO, (50), (16, PPPPPQ), (17, 17, QQQ, and C3Q, 66), (18, RRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRR 76), (19), (19), (20), (20), (20), (20), (20), (20), (20), (20), (20), (20), (20), (20), (20), (20), (20), (20), (20), (20), (20), (20), (20), (20), (20), (20), (20), (20), (20), (21), (21), (21), (21), (21), (19), (19), (19), (19), (20), (20), (20), (20), (21), (21), (21), (21), (21), (21), (21), (

Query result

Now take out the top three in each class.

SELECT SName,ClsNo,Score,dense_rank () OVER (PARTITION BY ClsNo ORDER BY Score DESC) AS top3FROM student

The OVER keyword is required to use window functions. Dense_rank () is a special ranking function that can only be used as a "window function", not without an OVER clause.

The OVER clause supports a keyword called PARTITION BY, which works very similar to GROUP BY. Using PARTITION BY, we will group by class and calculate the ranking line number separately.

We can see that each class has a separate ranking order.

The calculation of the window function occurs after the completion of the WHERE,GROUP BY and HAVING clauses and before the ORDER BY. Here you need to outsource a layer of derivative tables to get the final ranking results.

SELECT * FROM (SELECT SName,ClsNo,Score, dense_rank () OVER (PARTITION BY ClsNo ORDER BY Score DESC) AS top3 FROM student) AS tmpWHERE tmp.top3