MySQL 8.0窗口函数--row_number over..应用

EG：取出没门课程的第一名：

CREATE TABLE window_test
  (id int, 
  name text, 
  subject text, 
  score numeric
  );
  
INSERT INTO window_test VALUES (1,'小黄','数学',99.5), (2,'小黄','语文',89.5),(3,'小黄','英语',79.5), (4,'小黄','物理',99.5), (5,'小黄','化学',98.5), (6,'小红','数学',89.5), (7,'小红','语文',99.5), (8,'小红','英语',79.5), (9,'小红','物理',89.5), (10,'小红','化学',69.5),(11,'小绿','数学',89.5), (12,'小绿','语文',91.5), (13,'小绿','英语',92.5),(14,'小绿','物理',93.5), (15,'小绿','化学',94.5);

root@my3308.sock-8.0.11>[employees]>select * from window_test;
+------+--------+---------+-------+
| id   | name   | subject | score |
+------+--------+---------+-------+
|    1 | 小黄   | 数学    |   100 |
|    2 | 小黄   | 语文    |    90 |
|    3 | 小黄   | 英语    |    80 |
|    4 | 小黄   | 物理    |   100 |
|    5 | 小黄   | 化学    |    99 |
|    6 | 小红   | 数学    |    90 |
|    7 | 小红   | 语文    |   100 |
|    8 | 小红   | 英语    |    80 |
|    9 | 小红   | 物理    |    90 |
|   10 | 小红   | 化学    |    70 |
|   11 | 小绿   | 数学    |    90 |
|   12 | 小绿   | 语文    |    92 |
|   13 | 小绿   | 英语    |    93 |
|   14 | 小绿   | 物理    |    94 |
|   15 | 小绿   | 化学    |    95 |
+------+--------+---------+-------+
15 rows in set (0.00 sec)

直接写成如下形式：发现结果并不对。如何解决呢？

root@my3308.sock-8.0.11>[employees]>select id,name,subject,max(score) from window_test group by subject;
+------+--------+---------+------------+
| id   | name   | subject | max(score) |
+------+--------+---------+------------+
|    1 | 小黄   | 数学    |        100 |
|    2 | 小黄   | 语文    |        100 |
|    3 | 小黄   | 英语    |         93 |
|    4 | 小黄   | 物理    |        100 |
|    5 | 小黄   | 化学    |         99 |
+------+--------+---------+------------+
5 rows in set (0.00 sec)

1.当没有窗口函数之前我们使用哪几种方法：
方法一：使用jion 形式
a.按照subject 分组，取出score 的最大值

root@my3308.sock-8.0.11>[employees]>select subject,max(score) from window_test group by subject;
+---------+------------+
| subject | max(score) |
+---------+------------+
| 数学    |        100 |
| 语文    |        100 |
| 英语    |         93 |
| 物理    |        100 |
| 化学    |         99 |
+---------+------------+
5 rows in set (0.00 sec)

b.根据subject,score 的值再去关键Window_test表取出其他几列的值

root@my3308.sock-8.0.11>[employees]>select b.* from
 (select subject,max(score) as score from window_test group by subject
) a     
 join window_test  b on  a.score = b.score and a.subject = b.subject;
+------+--------+---------+-------+
| id   | name   | subject | score |
+------+--------+---------+-------+
|    1 | 小黄   | 数学    |   100 |
|    4 | 小黄   | 物理    |   100 |
|    5 | 小黄   | 化学    |    99 |
|    7 | 小红   | 语文    |   100 |
|   13 | 小绿   | 英语    |    93 |
+------+--------+---------+-------+
5 rows in set (0.00 sec)
此时的结果就是证券的了。

方法二：例如group by 特性来写
a.window_test 按照subject asc,score desc 来排序

root@my3308.sock-8.0.11>[employees]>select * from window_test order by subject, score desc;
+------+--------+---------+-------+
| id   | name   | subject | score |
+------+--------+---------+-------+
|    5 | 小黄   | 化学    |    99 |
|   15 | 小绿   | 化学    |    95 |
|   10 | 小红   | 化学    |    70 |
|    1 | 小黄   | 数学    |   100 |
|    6 | 小红   | 数学    |    90 |
|   11 | 小绿   | 数学    |    90 |
|    4 | 小黄   | 物理    |   100 |
|   14 | 小绿   | 物理    |    94 |
|    9 | 小红   | 物理    |    90 |
|   13 | 小绿   | 英语    |    93 |
|    3 | 小黄   | 英语    |    80 |
|    8 | 小红   | 英语    |    80 |
|    7 | 小红   | 语文    |   100 |
|   12 | 小绿   | 语文    |    92 |
|    2 | 小黄   | 语文    |    90 |
+------+--------+---------+-------+
15 rows in set (0.00 sec)

b.进行对此结果进行group by

root@my3308.sock-8.0.11>[employees]>select * from (select * from window_test order by subject, score desc) a group by a.subject;
+------+--------+---------+-------+
| id   | name   | subject | score |
+------+--------+---------+-------+
|    1 | 小黄   | 数学    |   100 |
|    2 | 小黄   | 语文    |    90 |
|    3 | 小黄   | 英语    |    80 |
|    4 | 小黄   | 物理    |   100 |
|    5 | 小黄   | 化学    |    99 |
+------+--------+---------+-------+
5 rows in set (0.00 sec)

有没有注意到此时的结果是不正确的，那么我们来看下具体的执行计划。哪里出错了呢？

root@my3308.sock-8.0.11>[employees]>explain select * from (select * from window_test order by subject, score desc) a group by a.subject;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: window_test
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 15
     filtered: 100.00
        Extra: Using temporary
1 row in set, 1 warning (0.00 sec)
从执行计划中我们得到了什么？有没有发现，视图进行了合并了。explain select_type = simple 。那么我们其实可以通过show warnings\G来查看具体的mysql 改写的语句

root@my3308.sock-8.0.11>[employees]>show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */
 select `employees`.`window_test`.`id` AS `id`,
`employees`.`window_test`.`name` AS `name`,
`employees`.`window_test`.`subject` AS `subject`,
`employees`.`window_test`.`score` AS `score`
 from `employees`.`window_test` group by `employees`.`window_test`.`subject`
1 row in set (0.00 sec)
可以直接从show warnings很直观的看到mysql 优化器给改写的SQL。

而在5.6上没有进行合并，5.7 8.0上进行了视图合并，我们应该如何避免呢？

1.5.7优化器新添加了什么功能造成视图合并？
2.如进行避免视图合并有哪几种方法：
方法一：

root@my3308.sock-8.0.11>[employees]>set optimizer_switch ="derived_merge=off";
Query OK, 0 rows affected (0.00 sec)

直接将derived_merge 进行关闭了。这种方法太暴力了。。。。非常不推荐，那么我们应该使用什么方法来解决此问题呢？大家自己思考下

root@my3308.sock-8.0.11>[employees]>set optimizer_switch ="derived_merge=off";
Query OK, 0 rows affected (0.00 sec)

root@my3308.sock-8.0.11>[employees]>select * from (select * from window_test order by subject, score desc) a group by a.subject;
+------+--------+---------+-------+
| id   | name   | subject | score |
+------+--------+---------+-------+
|    5 | 小黄   | 化学    |    99 |
|    1 | 小黄   | 数学    |   100 |
|    4 | 小黄   | 物理    |   100 |
|   13 | 小绿   | 英语    |    93 |
|    7 | 小红   | 语文    |   100 |
+------+--------+---------+-------+
5 rows in set (0.00 sec)

root@my3308.sock-8.0.11>[employees]>explain select * from (select * from window_test order by subject, score desc) a group by a.subject;
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | PRIMARY     | <derived2>  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   15 |   100.00 | Using temporary |
|  2 | DERIVED     | window_test | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   15 |   100.00 | Using filesort  |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
2 rows in set, 1 warning (0.00 sec)

此时结果就对了

方法三：使用if(@_subject=t.subject ,@rn:=@rn+1,@rn:=1) 形式来模拟
a.使用if(@_subject=t.subject ,@rn:=@rn+1,@rn:=1) 来进行subject 分组，以及显示各个row_num

root@my3308.sock-8.0.11>[employees]>select t.*,if(@_subject=t.subject ,@rn:=@rn+1,@rn:=1) as rn ,@_subject:=t.subject as _subject
    ->  from(select t.* from window_test t order by subject,score  desc  ) t , (select @rn:=0 rn ,@_subject:='') b;
+------+--------+---------+-------+------+----------+
| id   | name   | subject | score | rn   | _subject |
+------+--------+---------+-------+------+----------+
|    5 | 小黄   | 化学    |    99 |    1 | 化学     |
|   15 | 小绿   | 化学    |    95 |    2 | 化学     |
|   10 | 小红   | 化学    |    70 |    3 | 化学     |
|    1 | 小黄   | 数学    |   100 |    1 | 数学     |
|    6 | 小红   | 数学    |    90 |    2 | 数学     |
|   11 | 小绿   | 数学    |    90 |    3 | 数学     |
|    4 | 小黄   | 物理    |   100 |    1 | 物理     |
|   14 | 小绿   | 物理    |    94 |    2 | 物理     |
|    9 | 小红   | 物理    |    90 |    3 | 物理     |
|   13 | 小绿   | 英语    |    93 |    1 | 英语     |
|    3 | 小黄   | 英语    |    80 |    2 | 英语     |
|    8 | 小红   | 英语    |    80 |    3 | 英语     |
|    7 | 小红   | 语文    |   100 |    1 | 语文     |
|   12 | 小绿   | 语文    |    92 |    2 | 语文     |
|    2 | 小黄   | 语文    |    90 |    3 | 语文     |
+------+--------+---------+-------+------+----------+
15 rows in set (0.00 sec)

b.进行取出rn = 1 的结果

root@my3308.sock-8.0.11>[employees]>select  x.id,x.name,x.subject,x.score
    -> from (
    -> select t.*,if(@_subject=t.subject ,@rn:=@rn+1,@rn:=1) as rn ,@_subject:=t.subject as _subject
    ->  from(select t.* from window_test t order by subject,score  desc  ) t , (select @rn:=0 rn ,@_subject:='') b
    ->  )x  WHERE rn=1 ORDER BY SUBJECT;
+------+--------+---------+-------+
| id   | name   | subject | score |
+------+--------+---------+-------+
|    5 | 小黄   | 化学    |    99 |
|    1 | 小黄   | 数学    |   100 |
|    4 | 小黄   | 物理    |   100 |
|   13 | 小绿   | 英语    |    93 |
|    7 | 小红   | 语文    |   100 |
+------+--------+---------+-------+
5 rows in set (0.00 sec)

方法四：使用8.0引入的窗口函数row_number over..

root@my3308.sock-8.0.11>[employees]>select id,name,subject,score from  (
    ->     select row_number()over(partition by subject order by score desc) as rn,
    ->     id,name,subject,score
    ->     from window_test )t
    -> where rn=1;
+------+--------+---------+-------+
| id   | name   | subject | score |
+------+--------+---------+-------+
|    5 | 小黄   | 化学    |    99 |
|    1 | 小黄   | 数学    |   100 |
|    4 | 小黄   | 物理    |   100 |
|   13 | 小绿   | 英语    |    93 |
|    7 | 小红   | 语文    |   100 |
+------+--------+---------+-------+
5 rows in set (0.00 sec)
是不是很方便。

root@my3308.sock-8.0.11>[employees]>with cte as  (
    ->     select row_number()over(partition by subject order by score desc) as rn,
    ->     id,name,subject,score
    ->     from window_test
    ->     )select id,name,subject,score  from  cte where rn = 1;
+------+--------+---------+-------+
| id   | name   | subject | score |
+------+--------+---------+-------+
|    5 | 小黄   | 化学    |    99 |
|    1 | 小黄   | 数学    |   100 |
|    4 | 小黄   | 物理    |   100 |
|   13 | 小绿   | 英语    |    93 |
|    7 | 小红   | 语文    |   100 |
+------+--------+---------+-------+
5 rows in set (0.00 sec)