MySQL开窗函数

• MySql在8.0的版本增加了对开窗函数的支持，终于可以在MySql使用开窗函数了。开窗函数又称OLAP函数（Online Analytical Processing）.

• 开窗函数的语法结构：

  #Key word :Partiton by & order by
  <开窗函数> over ([PARTITION by <列清单>]
                       Order by <排序用列清单>）
  

  1
  2
  3
  

• 开窗函数大体分为两种：

  • a. 能够作为开窗函数的聚合函数：（sum,avg,count,max,min）
  • b. 专用开窗函数：(Rank,Dense_Rank,Row_Number)

# 0. 导入数据

/*测试数据*/
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

• sql

  /*开窗函数和排名类函数结合，看每个课程的排名*/
  
  SELECT
      `name`,
      `course`,
      `score`,
      row_number ( ) over ( PARTITION BY `course` ORDER BY score DESC ) AS score_rank 
  FROM
      `test`.`school_score`;
      
  /* 重要
  如果是 row_number ( ) over ( ORDER BY score DESC ) 会根据score排序, 然后为每一行添加一个序号1,2,3,4,5,6...
  */
  

  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  

• 输出

  

​

# 2. 开窗函数和sum,avg等函数结合

• sql

  /*第二部分：开窗函数和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`;
  

  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  

• res

  

# 3. rank, dense_rank, row_number区别

• rank() over：成绩相同的两名是并列，下一个成绩的排名空出所占的名次
  • 1 1 3 4 4 6
• dense_rank() over：成绩相同的两名是并列，下一位同学并不空出所占的名次
  • 1 1 2 3 3 4
• row_num() over：不需要考虑是否并列，哪怕根据条件查询出来的数值相同也会进行连续排名。
  • 1 2 3 4 5 6