MySQL8 CTE和窗口函数的用法

MySQL 阅读量

普通统计函数在每个分组内只能返回一条记录。而新增加的窗口函数和CTE函数可以一个分组返回多条函数。

窗口函数&CTE实操

  • 一张表结构如下
1
2
3
4
5
6
7
8
9
10
mysql> desc mytest;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| id     | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| name   | varchar(255)     | NO   |     | NULL    |                |
| course | varchar(255)     | NO   |     | NULL    |                |
| score  | decimal(10,0)    | NO   |     | NULL    |                |
+--------+------------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
  • 它有以下数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> select * from mytest;
+----+--------+--------+-------+
| id | name   | course | 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 | 可可 | 物理 | 75    |
+----+--------+--------+-------+
15 rows in set (0.01 sec)

问题: 要查询出每一科最高分学生的学号，姓名，成绩和科目。在MySQL8之前主要是通过下面的方式来实现。

答案1: MySql 8之前的方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
   SELECT x.id,x.NAME,x.course,x.score FROM
(
SELECT
	t.*,
IF
	( @_course = t.course, @rn := @rn + 1, @rn := 1 ) AS rn,
	@_course := t.course AS _course 
FROM
	( SELECT t.* FROM mytest t ORDER BY course, score DESC ) t,
	( SELECT @rn := 0 rn, @_course := '' ) b 
) x 
   WHERE rn = 1 ORDER BY course;
   
   +----+--------+--------+-------+
   | id | NAME   | course | score |
   +----+--------+--------+-------+
   |  4 | 汤姆   | 化学   |   100 |
   |  1 | 汤姆   | 数学   |   100 |
   |  5 | 汤姆   | 物理   |    99 |
   | 13 | 可可   | 英语   |    93 |
   |  7 | 杰克   | 语文   |   100 |
   +----+--------+--------+-------+
   5 rows in set, 5 warnings (0.00 sec)

这个sql虽然高效，但是可读性不强。接下来我们来看看窗口函数怎么实现

答案2: 使用窗口函数ROW_NUMBER

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT
 id,name,course,score 
FROM
    ( SELECT *,row_number() over ( PARTITION BY course ORDER BY score DESC ) AS rn FROM mytest ) t 
WHERE
    rn = 1;
    
+----+--------+--------+-------+
| id | name   | course | score |
+----+--------+--------+-------+
|  4 | 汤姆   | 化学   |   100 |
|  1 | 汤姆   | 数学   |   100 |
|  5 | 汤姆   | 物理   |    99 |
| 13 | 可可   | 英语   |    93 |
|  7 | 杰克   | 语文   |   100 |
+----+--------+--------+-------+
5 rows in set (0.01 sec)

答案3: 使用CTE方式

1
2
3
4
5
6
7
8
9
10
11
12
13
WITH cte AS ( SELECT *,row_number() over ( PARTITION BY course ORDER BY score DESC ) AS rn FROM mytest ) 
SELECT id,name,course,score FROM cte WHERE rn = 1;

+----+--------+--------+-------+
| id | name   | course | score |
+----+--------+--------+-------+
|  4 | 汤姆   | 化学   |   100 |
|  1 | 汤姆   | 数学   |   100 |
|  5 | 汤姆   | 物理   |    99 |
| 13 | 可可   | 英语   |    93 |
|  7 | 杰克   | 语文   |   100 |
+----+--------+--------+-------+
5 rows in set (0.01 sec)

通过对比上面的三种答案，发现CTE和窗口函数极大简化了我们的sql语句。我们的sql更容易读懂。

通用表表达式（Common Table Expressions）

复杂的查询会使用嵌入式表，例如：

1
2
3
   SELECT t1.*,t2.* FROM
( SELECT col1 FROM table1 ) t1,
( SELECT col2 FROM table2 ) t2;

而有了 CTE，我们可以这样写：

1
2
3
4
WITH
t1 AS (SELECT col1 FROM table1),
t2 AS (SELECT col2 FROM table2)
SELECT t1.*, t2.* FROM t1, t2;

这样看上去层次和区域都更加分明，改起来也更清晰的知道要改哪一部分。

窗口函数

什么是窗口函数

窗口函数引入的其实不只是几个函数，而是一套完整的语法，窗口函数是此语法中的一部分。

窗口函数 over 窗口表达式。

over是窗口函数语法的关键字。

窗口函数

    1. 序号函数: row_number(), rank(), dense_rank()
    1. 分布函数: percent_rank(), cume_dist()
    1. 前后函数: lead(), lag()
    1. 头尾函数: fist_val(), last_val()
    1. 其他函数: nth_value(), nfile()

窗口函数的两种写法

  1. over关键词后直接定义窗口
1
2
3
4
select
  row_number() over (partition by user_no order by amount desc),
  order_id
from order_tab;
  1. over关键字后使用窗口别名：
1
2
3
4
5
select 
    row_number() over w,
    order_id
from order_tab
WINDOW w AS (partition by user_no order by amount desc);

窗口表达式语法

PARTITION BY子句

PARTITION BY子句是用来分组的，从字面上来看，应该叫分区。理解上可以类比group by。

某行记录所在的分区就是这行对应的窗口。一个窗口可以包含多行，就像group by分的组一样。

PARTITION BY <expression>[{,<expression>…}]

其中的expression表达式，可以是列名，也可以是某个计算结果。如果是列名，就代表简单的按照列的值分区。
可以支持按照多个表达式或列来分区。
根据每行记录的值，可以确定每行属于哪个分区，也就是哪个窗口。

ORDER BY子句

order by子句是排序用的，决定了属于同一个窗口的行记录的排序方式。语法很简单：

ORDER BY <expression> [ASC|DESC] [{,<expression>…}]

和MySQL中原有的order by关键字意思差不多。原有的order by关键字是所有查询结果一起排序，窗口函数的order by子句是对某个窗口中的行记录进行排序。

frame子句

frame子句定义了窗口中的一个子窗口，子集。这个稍微麻烦点，语法是：

frame_unit {<frame_start>|<frame_between>}

  • frame_unit有两种选择，ROWS和RANGE，ROWS代表按起止位置标识子集，RANGE代表按起止值标识子集。
  • frame_start标识子窗口的开始位置，结束位置默认是当前行。
  • frame_between是用BETWEEN AND关键字来标识子窗口的起止位置。

窗口函数介绍

序号函数: row_number(), rank(), dense_rank()

序号函数的作用是显示分区中每行的行号。

  • row_number()：排序字段相同时，行号随机排，下一行行号正常加1。显然此函数得到的行号是连续的。
  • rank()：排序字段相同时，行号相同，下一行行号按照排名确定。这个逻辑比较像是现实中的排名规则，当出现并列冠军时，第三个人排名第三，没有亚军。显然此函数得到的行号是不连续的。
  • dense_rank()：排序字段相同时，行号相同，下一行行号顺序加1。也就是出现冠军时，第三个人排名是第二。显然此函数得到的行号是连续的。

分布函数: percent_rank(), cume_dist()

分布函数得到的是一个百分比，计算公式用到rank()函数。

  • percent_rank(): 实际计算公式：(rank()-1)/(rows-1)
  • cume_dist(): 实际计算公式：rank()/rows

前后函数: lead(), lag()

  • lead(expr,n): 代表当前行前面第n行记录的expr表达式（或字段值）。
  • lag(expr,n): 代表当前行后面的第n行记录的expr表达式（或字段值）。

比如lead(add_time,1)就代表当前行前面一行的add_time字段。

头尾函数: fist_val(), last_val()

  • first_val(expr): 代表分区第一行记录的expr表达式（或字段值）。
  • last_val(expr): 代表分区最后一行记录的expr表达式（或字段值）。

其他函数: nth_value(), nfile()

  • nth_value(expr,n): 代表区间第n条记录的expr表达式（或字段值）。
    比如：nth_value(amount,2)代表区间第二行的amount字段。
  • nfile(n): 代表把区间列分为n个组，返回组号。

文档

Powered by Hexo and Hexo-theme-hiker

Copyright © 2017 - 2023 Keep It Simple And Stupid All Rights Reserved.

访客数 : | 访问量 :