MySQL开窗函数

Mysql 开窗函数在Mysql8.0+ 中可以得以使用，实在且好用。

• row number() over
• rank() over
• dense rank()
• ntile() 

我们先上测试数据，是不同姓名，不同课程的分数表；

/*测试数据*/
CREATE TABLE `school_score` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(1) DEFAULT NULL,
    `course` char(10) DEFAULT NULL,
  `score`  int (2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ;

INSERT INTO `test`.`school_score`(`id`, `name`,`course`,`score`) VALUES (1, 'A','Chinese',80);
INSERT INTO `test`.`school_score`(`id`, `name`,`course`,`score`) VALUES (2, 'B','Chinese',90);
INSERT INTO `test`.`school_score`(`id`, `name`,`course`,`score`) VALUES (3, 'C','Chinese',70);
INSERT INTO `test`.`school_score`(`id`, `name`,`course`,`score`) VALUES (4, 'A','Math',70);
INSERT INTO `test`.`school_score`(`id`, `name`,`course`,`score`) VALUES (5, 'B','Math',100);
INSERT INTO `test`.`school_score`(`id`, `name`,`course`,`score`) VALUES (6, 'C','Math',80);
INSERT INTO `test`.`school_score`(`id`, `name`,`course`,`score`) VALUES (7, 'A','English',90);
INSERT INTO `test`.`school_score`(`id`, `name`,`course`,`score`) VALUES (8, 'B','English',85);
INSERT INTO `test`.`school_score` (`id`, `name`,`course`,`score`) VALUES (9, 'C','English',99);

 

1. row number() over

/*开窗函数和排名类函数结合，看每个课程的排名*/

SELECT
    `name`,
    `course`,
    `score`,
    row_number ( ) over ( PARTITION BY `course` ORDER BY score DESC ) AS score_rank 
FROM
    `test`.`school_score`;

结果👇：

 

    /*使用开窗函数计算每个课程分数最高的一个*/
    
SELECT
    * 
FROM
    ( SELECT `name`, `course`, `score`, row_number ( ) over ( PARTITION BY `course` ORDER BY score DESC ) AS score_rank FROM `test`.`school_score` ) AS a 
WHERE
    a.score_rank = 1;

结果👇：

 

 

 

 

 

    /*第二部分：开窗函数和SUM() ，AVG() 等聚合函数结合*/
    
SELECT
    `name`,
    `course`,
    `score`,
    SUM( score ) over ( PARTITION BY `course` ) AS course_score_total ,
    round(AVG(score) over (PARTITION BY `course`),2)  as  course_score_avg
FROM
    `test`.`school_score`;

结果👇：

/* SUM(score) over (PARTITION BY `course` ORDER BY score ASC)   如果执行这个语句，就是在每个
课程对分数进行累加*/

SELECT
    `name`,
    `course`,
    `score`,
    SUM(score) over (PARTITION BY `course` ORDER BY score ASC ) as course_score_total
FROM
`test`.`school_score`;

 

 

 

思考🤔： 有order by ,按照排序连续累加；无order by ,计算partition by 后的和；over() 中没有partition by ,计算所有数据总和

同时，order by 的asc 和 desc 的排序不同，有order by 的结果也不一样。

 

2. row number() over , rank() over ,dense rank() 三者对比。

create table students_score(
    id int(4)  auto_increment primary key,
    name varchar(50) not null, 
    score int(4) not null
    );
    
    insert into students_score(name,score) values
    ('A', 300),
    ('B', 240),
    ('C', 250), 
    ('D', 280), 
    ('E', 240), 
    ('F', 200);
    

 

执行👇语句：

SELECT
    `id`,
    `name`,
    rank ( ) over ( ORDER BY score DESC ) AS r,
    DENSE_RANK ( ) OVER ( ORDER BY score DESC ) AS dense_r,
    row_number ( ) OVER ( ORDER BY score DESC ) AS row_r 
FROM
    students_score;

 

 👆 看图🤔区别，就可以知道三者的排名的区别了，如果我是校长，我希望可以按照 DENSE_RANK() 的排序，公平且可以激励着一代代莘莘学子。

 

3.ntile()分组

SELECT
    `id`,
    `name`,
 score,
 ntile(3) over (order by score desc)  as n
FROM
    students_score;