大家好，我是考100分的小小码 ，祝大家学习进步，加薪顺利呀。今天说一说[数据库概论] 高级 SQL (mysql)「终于解决」,希望您对编程的造诣更进一步.
除了基本的 CRUD 之外，一些复杂的查询必须借助更复杂的工具来完成。本章主要以 MySQL 为视角介绍以下内容：
- 函数，存储过程，触发器。
- 逻辑控制与游标 ( 相当于 “T-SQL” )。
- 基于
WITH RECURSIVE实现递归查询的思路。 - 分窗计算与旋转。
- 高维度聚合：上卷 ( ROLLUP ) 和立方体 ( CUBE )。
对于表函数，旋转，立方体等概念，MySQL 没有提供相关的支持，但我们仍有必要了解这些概念，并使用等价的 SQL 语句实现它们。
函数
无论我们学过哪门应用型语言：C，Java，或者是 Python，编写函数只需要三个核心部分 —— 函数名，参数列表，返回值，这对 SQL 来说也是一样的。而稍微不同的一点是 —— SQL 返回的可以不仅是一个值，还可以是一张表。
注意，虽然 SQL 定义了标准的语法格式，但是大部分 DBMS 厂商提供的语法都和标准格式存在少许差异。在本篇中，我们使用目前最常使用的 MySQL 数据库进行练习。在此之前，我们需要设置 MySQL 的参数：
SET GLOBAL log_bin_trust_function_creators = 1;
这个设置让 MySQL 信任函数的创建者，否则后面大部分的函数创建过程将无法继续执行，并且抛出代号为 1418 的错误。详见：自定义函数报错 Error Code: 1418. 果子-CSDN博客 首先讨论返回值的函数。比如说下面的 dept_count 函数负责按部门名称查询人数：
CREATE FUNCTION dept_count (
dname VARCHAR ( 20 )) RETURNS INTEGER BEGIN
DECLARE i INTEGER;
-- 变量本身还可以通过 SET 关键字来赋值：
-- SET i = 10;
SELECT COUNT(*) INTO i FROM instructor WHERE instructor.dept_name = dname GROUP BY dept_name;
RETURN i;
END
有以下几点需要注意的地方：
- 区分
returns和return关键字，一个在声明时使用，一个在函数体内使用。 - 函数体以
Begin开头，以End结尾。 - 每一行语句后面都严格以
;结尾。 - 函数参数的名字不要和内部任何一个表的属性重名，这会带来麻烦。
函数是通过以下步骤将数据传递到外界的：
- 首先，通过
Declare关键字定义一个变量i； - 将表查询的结果使用
Into关键字赋值给它； - 返回
i。它的数据类型和函数声明Returns的数据类型一致。
这个函数利用聚集函数和外部指定的部门名称查询到部门人数，然后返回它。函数的调用使用 SELECT 子句：
SELECT dept_count('Music')
在声明函数时还可以补充它的特征 ( Characteristic )。这一部分内容在存储过程中进行陈述，两者的特征内容相同，这里暂不做介绍。
存储过程
SQL 甚至允许返回的值是一张表，这样的函数称之为表函数 —— 然而在 MySQL 中，表函数可以被存储过程 ( 更官方的叫法是持续存储模块，Persistent Storage Module，简称 PSM ) 来代替。
CREATE PROCEDURE check_dept (IN dname VARCHAR(20),OUT result INTEGER)
READS SQL DATA
BEGIN
SELECT COUNT(*) INTO result FROM instructor WHERE instructor.dept_name = dname GROUP BY dept_name;
END
和函数相比的一个较大区别是：存储过程支持多个参数的输入输出，每个形参要标注 IN 表示入参，OUT 表示出参，INOUT 表示即可以是入参又可以充当出参。如果不加任何声明，那么该参数默认是 IN 。
而 READS SQL DATA 表明这个存储过程内部仅读取数据，而不使用 DML 修改原表的数据 ( 包括，更新，插入，删除等 )。这一部分是存储过程的特征部分。该部分有很多丰富的语义来指明，或是限定存储过程 ( 或者函数 ) 的执行权限。
函数 / 存储过程特征
[NOT] DETERMINISTIC：指明存储过程 ( 或者函数 ) 的执行结果是否是确定的。DETERMINISTIC 表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC 表示结果是非确定的，相同的输入可能得到不同的输出。默认情况下，结果是非确定的。
[CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA]：指明子程序使用SQL语句的限制：
CONTAINS SQL表示子程序包含 SQL 语句，但不包含读或写数据的语句；NO SQL表示子程序中不包含 SQL 语句；READS SQL DATA表示子程序中包含读数据的语句；MODIFIES SQL DATA表示子程序中包含写数据的语句。- 默认情况下，系统会指定为
CONTAINS SQL。
SQL SECURITY [ DEFINER | INVOKER ]：指明谁有权限来执行。DEFINER 表示只有定义者自己才能够执行；INVOKER 表示调用者可以执行。默认情况下，系统指定的权限是 DEFINER。
[ COMMENT 'string' ]：注释信息。
从存储过程中取值
在外部，存储过程的调用使用 CALL 关键字。另外，如果这个存储过程有 OUT 标识的输出参数，这里需要使用 @ 开头的临时变量 ( 又称用户变量，它的生命周期仅在这个会话内 ) 将其取出，然后才能通过 SELECT 输出到控制台或者是程序外部。
CALL check_dept('Music',@i);
SELECT @i
查看存储过程或函数
使用 SHOW STATUS 可以查看存储过程 / 和函数的状态：
SHOW [PROCEDURE | FUNCTION] STATUS LIKE 'pattern'
比如说检查刚才创建的 check_dept 存储过程：
SHOW PROCEDURE STATUS LIKE 'check_dept'
根据正则匹配的内容，这条命令会返回多个 DBMS 中的存储过程 / 函数信息。如果要查看某一个函数 / 存储过程的内部具体逻辑，则：
SHOW CREATE [PROCEDURE | FUNCTION] <`sp_name`>
在这里，sp_name 需要指定一个具体的函数或者存储过程的名字。
逻辑控制
逻辑控制是应用程序的基本内容，它在 SQL 中同样存在。SQL Server 称这些控制流程为 T-SQL ( 即加强版的 SQL )。在通用程序设计语言中，我们使用 {...} 来表示一段代码块，然而在 SQL 中可能会更麻烦一些：这里以 <关键字> ... END <关键字> 包裹控制逻辑。
逻辑控制一定程度上赋予了 DBMS 自行处理复杂逻辑业务的能力，而不需要借助外界的通用程序设计语言。下面来介绍 SQL 当中通用的逻辑控制结构。
IF 语句
IF 语句是一个基础的条件分支，不过不像其它语言一样有 else if ... 的连续句式。令初学者可能不习惯的是，条件语句必须以 END IF 来收尾。
CREATE PROCEDURE `test_if`(OUT ans VARCHAR(4))
BEGIN
DECLARE i INT;
SET i = 5;
IF i % 2 = 0 THEN SET ans = '偶数'; ELSE SET ans = '奇数';
END IF;
END
MySQL 中还有一个名为 IF() 的库函数，不要把两者混为一谈。
CASE 流程控制
这里的 CASE 流程控制是用在存储过程中的：这里的 CASE 以 END CASE 收尾，并且每一个分支都需要补齐分号。
同样，这里的 CASE 流程控制有两种形式，一是用于值判断，另一个是用于整合多个条件判断。
CREATE PROCEDURE test_case(OUT answer VARCHAR(4))
BEGIN
DECLARE i INT;
SET i = 5;
CASE (i % 2 )
WHEN 0 THEN SELECT '偶数' INTO answer;
WHEN 1 THEN SELECT '奇数' INTO answer;
END CASE;
END
上面的逻辑也可以写成：
CREATE PROCEDURE test_case(OUT answer VARCHAR(4))
BEGIN
DECLARE i INT;
SET i = 5;
CASE
WHEN (i % 2) = 0 THEN SELECT '偶数' INTO answer;
WHEN (i % 2) = 1 THEN SELECT '奇数' INTO answer;
ELSE SELECT '未知' INTO answer;
END CASE;
END
对参数赋值的另一种写法是：SET answer = '偶数'。
WHILE 语句
WHILE 语句块和其它程序语言当中的 while 没有区别：
CREATE PROCEDURE test_while()
BEGIN
declare i INTEGER;
SET i = 0;
WHILE i < 10 DO
SET i = i + 1;
END WHILE;
END
有时可以这样做：给这个 WHILE 分支起一个别称：
CREATE PROCEDURE test_while()
BEGIN
declare i INTEGER;
SET i = 0;
loop_i:
WHILE i < 10 DO
SET i = i + 1;
END WHILE loop_i;
END
这种写法有利于在循环体内安插 LEAVE ( 相当于其它编程语言的 break ) 或者是 ITERATE ( 相当于其它编程语言的 continue ) 来跳出或重复循环。这对于后文的 REPEAT 和 LOOP 循环同样适用。
LOOP 循环
LOOP 循环相当于一个无条件执行的死循环 while。这就需要我们从内部设立检查条件，以便于在查询到想要的结果时跳出循环。
CREATE PROCEDURE test_loop(OUT answer VARCHAR(4))
BEGIN
DECLARE i INT;
DECLARE sum INT;
SET i = 1;
SET sum = 0;
sum_loop: LOOP
IF
i = 11 THEN LEAVE sum_loop;
END IF;
IF
i % 2 = 0 THEN
SET i = i + 1;
ITERATE sum_loop;
END IF;
SET i = i + 1;
SET sum = sum + i;
END LOOP sum_loop;
SET answer = sum;
END
REPEAT 循环
REPEAT 的循环体至少能执行一次，并在满足 UNTIL 循环条件之后退出 ( 注意，这个循环控制结构上和其它语言的 do...while 类似，但是语义有所不同，注意甄别 )。
DROP PROCEDURE test_repeat;
CREATE PROCEDURE test_repeat(OUT ans INT)
BEGIN
DECLARE i INTEGER;
SET i = 1;
REPEAT
SET i = i + 5;
UNTIL i > 2 END REPEAT;
SET ans = i;
END
利用临时表实现返回结果集
MySQL 不支持返回一个表，因此如果存储过程要对外部展示结果的集合就要借助其它的办法。总体而言，需要三个步骤：
- 创建一个存储过程，它不需要主动声明
OUT的参数，可以根据需求设置一些IN参数。 - 将想要返回的多个返回结果
INSERT到这个临时表中。 - 保证存储过程的最后一个
SELECT语句是选中此临时表。
在这个存储过程运行完毕时，可以选择主动 DROP 掉所有临时表。在外部查询中直接 call 这个存储过程，就可以查看到返回内容了。
注意，各个会话之间的 “同名” 临时表互不影响。但是，在一个会话内不能创建多个同名的表。在 MySQL 中创建临时表非常容易，这里仅需要额外一个 TEMPORARY 关键字，由于临时表仅用于装载查询结果，且它在会话结束之后就被回收，因此一般情况下我们不需要在此表设置额外的约束。
CREATE TEMPORARY TABLE(
-- 其它内容和创建表没有区别。
)
在这个会话结束时，这个临时表会被删除。在下面的例子中，首先从原始表 instructor 当中获取数据，
CREATE PROCEDURE name_start_with_C()
BEGIN
CREATE TEMPORARY TABLE tmp(`name` VARCHAR(20),dept_name VARCHAR(20));
CREATE TEMPORARY TABLE tmp2(`name` VARCHAR(20),dept_name VARCHAR(20));
-- 演示将查询的结果插入到临时表中
INSERT INTO tmp SELECT `name`,dept_name FROM instructor;
INSERT INTO tmp2 SELECT `name`,dept_name FROM tmp WHERE name LIKE 'C%';
-- 可以在外界查看到这个结果
SELECT * FROM tmp2;
-- 主动删除临时表 tmp,tmp2
DROP TEMPORARY TABLE tmp,tmp2;
END
drop PROCEDURE check_test
call check_test()
游标
关于游标一直存在性能上的争议*。在大部分情况下，我们都不需要使用游标来处理元组 —— 除非遇到了哪些需要一行一行操作元组的情形。MySQL 的游标只能在存储过程内声明，并配合循环控制进行操作。
每个游标都存在四个 “生命周期” —— 声明，打开，使用，关闭。尤其是打开和关闭游标的逻辑需要我们主动编写。声明游标的 SQL 语句如下：
DECLARE <`cursor_name`> CURSOR FOR <`select_statement`>
游标和一个 SELECT 类型的查询语句绑定。这相当于将原本声明式的 SQL 拆解成命令式逻辑。游标相当于迭代器 Iterator ，我们将一些处理逻辑安插在循环内部，从而达到逐行检查并处理元组的目的。
在声明完游标之后，必须先打开它才可以从中取出数值，并在使用完毕后关闭它。这两个语句对应：
OPEN <`cursor_name`>
CLOSE <`cursor_name`>
如果一个游标没有被明确声明关闭，那么它会在存储过程运行结束后自行关闭。光声明游标还不够，在使用游标的过程当中，我们还需要借助一个信号量来获悉游标是否已经遍历完了。下面的存储过程列出了使用游标的前置和后置工作：
CREATE PROCEDURE test_cursor()
BEGIN
DECLARE done bool DEFAULT FALSE;
-- 声明游标;
DECLARE row_cursor CURSOR FOR SELECT ...;
-- 这行声明紧接在游标声明的后面，变量 done 将和游标的状态绑定在一起。
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN row_cursor;
-- LOOP FOR USE CURSOR
-- LOOP FOR USE CURSOR
-- LOOP FOR USE CURSOR
-- LOOP FOR USE CURSOR
-- LOOP FOR USE CURSOR
-- LOOP FOR USE CURSOR
CLOSE row_cursor;
END
使用光标的方式如下：
FETCH cursor_name INTO <`col1`> [`col2`] ...
下面做一个简单的尝试。通过游标，以命令式的逻辑实现：统计 instructor 关系中 dept_name 为历史系的教职工人数。代码块的注释部分附上了使用游标的其它注意事项。
CREATE PROCEDURE test_cursor(OUT nums INT)
BEGIN
DECLARE cur_dept VARCHAR(20);
DECLARE sum INT DEFAULT 0;
DECLARE done bool DEFAULT FALSE;
-- 注意，在所有声明中，游标需要放在最后面。
-- 通过 SQL 可知，这个游标每次只提取一个属性 dept_name。
DECLARE row_cursor CURSOR FOR SELECT dept_name FROM instructor;
-- 将游标的遍历状态绑定到 done 变量上。
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN row_cursor;
use_cursor:REPEAT
-- INTO 的变量数量取决于游标绑定的 SQL 语句选择了几列。
FETCH row_cursor INTO cur_dept;
IF cur_dept = 'history' THEN
SET sum = sum + 1;
END IF;
UNTIL done END REPEAT;
CLOSE row_cursor;
SET nums = sum;
END
-- 调用该存储函数。
CALL test_cursor(@nums);
SELECT @nums;
触发器
触发器是一个特殊的存储过程。在之前的例子中，我们需要通过 call 主动调用存储过程，但是触发器相当于是一个回调函数 —— 当满足特定的条件时，数据库会自行创建一个子进程来运行它。要创建一个触发器，除了命名之外，还需要以下三个主要因素：
- 触发时机：
BEFORE( 之前触发 ) 或AFTER( 之后触发 )。 - 触发事件：包括
INSERT，UPDATE，DELETE。 - 指定触发器所绑定的表。
综上所述，创建触发器需要这样的语法：
CREATE TRIGGER <`trigger_name`> <`trigger_time`> <`trigger_event`>
ON <`trigger_name`> FOR EACH ROW
BEGIN
#... stmts
END
比如，我们创建一个触发器，记录有多少人曾对表执行过 DELETE 操作：
-- 创建一个表，令触发器在这张表上计数。
CREATE TABLE bank_log(
item VARCHAR(20) PRIMARY KEY,
count INT
);
-- 插入初值
INSERT INTO bank_log(item,count) VALUES ('inserts',0)
-- 删除触发器语句：DROP TRIGGER test.count_insert
-- 创建触发器
CREATE TRIGGER count_insert AFTER INSERT
ON bank FOR EACH ROW
BEGIN
UPDATE bank_log SET count = count + 1 WHERE item = 'inserts';
END
-- 尝试触发 count_insert
INSERT INTO bank(`name`,balance) VALUES ('Lin Qing',4000)
-- 查看触发器是否生效。
SELECT count from bank_log WHERE item = 'inserts'
可以在触发器中捕获 INSERT 或者是 UPDATE 语句中的目标元组。我们可以拦截 “新送入的元组数据”，指代为 NEW；也可以获得 “原先的元组数据”，指代为 OLD。两者统称为触发器的过渡变量。利用这两个变量，我们可以通过 NEW.xxx 或者 OLD.xxx 来获取一个属性的 “新值” 或 “旧值”。
过渡变量的使用受触发器的触发事件，触发时机的约束：
- 对于
INSERT类型的触发器，只能获取NEW。 - 对于
DELETE类型的触发器，只能获取OLD。 - 对于
UPDATE类型的触发器，NEW和OLD都可以获取。 - 对于
BEFORE类型的触发器，INSERT和UPDATE触发器都可以直接对NEW进行修改。 - 对于
AFTER类型的触发器，NEW是只读的。 OLD无论如何都是只读的。
利用触发器，我们可以设法避免执行非法操作而产生错误，在错误元组插入到数据库之前纠正它。此外，当一个数据库模式存在外键约束时，使用触发器安全地进行级联删除也是不错的选择。
这里演示了一个简单的例子：在插入一条新的学生成绩 ( 这个元组包含了 name 和 score，使用 NEW 来指代 ) 之前，触发器可以率先在记录到数据库之前进行检查：如果这个学生的成绩小于 60，那么直接置空 null 来表示 “挂科”。
-- 假如成绩低于 60 就直接记作挂科，取消成绩。
CREATE TRIGGER check_score BEFORE INSERT
ON student_grades FOR EACH ROW
BEGIN
-- 通过 SET 直接修改变量。
IF NEW.score < 60 THEN SET NEW.score = null;
END IF;
END
触发器在创建之后是默认处于打开状态的，可以通过 ALTER 语句主动关闭：
ALTER TRIGGER <`trigger_name`> disable
尤其在进行主从备份的时候，应当保证触发器处于关闭状态，因为已经过筛选的数据不应当再重复送入触发器检查一遍。对准备接管主系统的备份复制系统，应当首先禁用触发器，等备份系统开启业务时再打开。
触发器在执行过程中可能会触发另一个触发器。在最坏的结果下，这有可能导致一个无限的触发链。因此，在编写和使用触发器时应当格外的小心。如果一个 AOP 操作可以通过存储过程来实现，那么触发器就不是必须的。
[mysql 触发器 Err] 1362 – Updating of NEW row is not allowed in after trigger 、 码由心生 (faceghost.com)
mysql触发器new和old – JimmyShan – 博客园 (cnblogs.com)
递归查询
使用递归查询之前，请确保 MySQL 版本是 8.0+。
假定下面有一张表 prereq，它记录了每一门课程以及它的先行课程：
| course_id | prereq_id |
|---|---|
| BIO-301 | BIO-101 |
| BIO-399 | BIO-101 |
| CS-190 | CS-101 |
| CS-315 | CS-190 |
| CS-319 | CS-101 |
| CS-319 | CS-315 |
| CS-347 | CS-319 |
假定现在有一个需求：对于每一门课：找出它所有直接的，或间接的先修课程。比如：CS-315 的先修课程是 CS-190，而 CS-190 的先修课程是 CS-101，因此，CS-101 也是 CS-315 的先修课程。
如果用离散数学话说，我们正在计算 prereq 的 传递闭包。有许多应用需要计算层次结构上类似的传递闭包。首先给出迭代形式的伪代码：
1.准备一个临时表 rec_prereq;
2.先将目标课程 cid 的所有先修课加到 rec_prereq 内。
3.LOOP:
IF tmp 集合发生了变化 THEN
BEGIN
寻找后加进 rec_prereq 内的课程的先修课;
END
ELSE 完成
对这个迭代过程稍作总结，并给出一门课程的所有先修课程的递归定义：
- 能直接在 prereq 搜索到的先修课程 ( 迭代的 1，2 步 )；
- 这门课所有先修课程 ( 无论是直接还是间接的 ) 先修课程 ( 迭代的循环过程 )。
下面使用 SQL 语句实现这个逻辑，这里引入了 RECURSIVE 关键字 ( 并不是所有的数据库都使用此关键字 )。
-- RECURSIVE 是 mysql 8.0 之后才提供的功能
WITH RECURSIVE rec_prereq(course_id,prereq_id) AS (
-- 相当于定义 1。
SELECT course_id,prereq_id
FROM prereq WHERE
UNION
-- 相当于定义 2.
-- 下面的一段 SQL 语句可类比离散数学中的复合操作：
-- R{<r.course_id,r.prereq_id>} ○ S{<s.course_id,s.prereq_id>} => {<r.course_id,s.prereq_id>}
-- 其中 r.prereq_id 和 s.course_id 是同一类属性。
SELECT rec_prereq.course_id,prereq.prereq_id
FROM prereq JOIN rec_prereq ON rec_prereq.prereq_id = prereq.course_id
)
SELECT * FROM rec_prereq
在这个由 WITH RECURSIVE 关键字实现的递归视图中，我们没有像迭代那样创建而是临时表，而是将所有查询到的新的关系直接添加到当前视图内。递归视图的标准形式就是写成两个子查询的并操作：
- 基查询 ( base query )：即本例子中的定义 1。
- 使用递归视图自身的递归查询 ( recursive query )，即本例子中的定义 2。
以通用编程语言实现的递归函数做类比，对递归查询最直观的理解方式是：将初值为 ∅ 的集合 T’ 传入，函数不断地从依照基查询 R 中找出隐含的传递关系集合 S，并令 T’ := T’ ∪ S。T’ 的变化可能引入了新的传递关系，因此要再次递归检查 T’，并尝试添加新的集合 S。
将 ” T’ 不再变化 ” 设为临界条件，当递归满足该条件时退出，此时的 T’ 被称之为不动点 ( fixed point )。在递归查询中，这段逻辑由数据库主动判断，我们无需显示地写 IF 分支。最终的返回结果是满足定义 1 和定义 2 的集合，即 T = T’ ∪ R，在 SQL 语句的体现便是 UNION 连接的两段子查询。
显然，rec_prereq 是原基查询 prereq 的超集：即 rec_prereq 包含了更多的信息，因为它还囊括了那些隐含的传递关系。我们加以推广，可得到这样的一个结论：递归查询一定会返回至少和以前相同的结果集，并有可能返回额外的元组。说得再简洁一些：递归查询一定是单调 ( monotonic ) 的。
相关子查询
假定现在有一个成绩表 student_score ( id，name，grade )，现在要额外输出每一名学生的排名。并且：当多名学生的成绩相同时，排名应该是相同的。比如：前三名的学生成绩相同时，这三个人的排名都是 1，而下一个排名则从 4 开始数起。
设想以下用目前学过的聚集函数应当如何实现：我们知道，如果对某个学生而言，全班有 n-1 个分数比他高，那么他的排名自然就是 n。依照这个思路给出对应的 SQL 语句，使用 COUNT(*) 实现：
SELECT ( 1 + (SELECT COUNT(*) FROM student_grades AS b WHERE b.score > a.score)) AS `rank`,`name`,score
FROM student_grades AS a
这是一个相关子查询，内部查询 ( 别名为 b 的 student_score 表 ) 使用了外部查询 ( 别名为 a 的 student_score 表 ) 的值，因为我们在 a 中的每一个元组计算其 rank 属性值时，就要对 b 进行整表扫描，且 WHERE 子句同时关联了 a，b 两表。
推广到更一般的情形，假定内表有 m 条记录，外表有 n 条记录，那么这个查询的时间复杂度将是 o( mn )。对于本例而言，时间复杂度近似是 o( n2 )。在后文，我们会用更便捷且高效的 RANK() 函数来实现。
反之，如果子查询内部没有关联外表的子句，那么这就是不相关子查询。此时的查询时间复杂度为 o( m+n )。
实现排名的其它方案
我们这里引入 RANK() 函数来实现 “相关子查询” 章节中的遗留问题，它的写法如下：
SELECT RANK() OVER (ORDER BY score DESC) as `rank`,name,score
FROM student_grades
其中，ORDER BY 被移动到了前面充当 RANK() 的谓词。RANK() 函数能够帮助我们为每一项元组补充上排名，但是不保证输出的结果也是按照排名的。这可以再补充一条 ORDER BY 来保证输出的结果：
SELECT RANK() OVER (ORDER BY score DESC) as `rank`,`name`,score
FROM student_grades
ORDER BY `rank`
如果既要保证取得同分数的人名次相同，还希望名次之间不产生空挡 ( 比如，前三名学生的成绩相同，排名均为 1，而第四名的学生排名为 2 而非 4 )，则可以使用 DENSE_RANK() 函数替代之。
SELECT DENSE_RANK() OVER (ORDER BY score DESC) as `rank`,`name`,score
FROM student_grades
ORDER BY `rank`
如果我们将这个查询结果用作一个派生表，在此基础上实现 “查询第 n 名” ，”查询第 n – m 名” 这类的需求将变得相当容易。注意，MySQL 严格要求派生表必须有一个别名：
SELECT `name` FROM (SELECT DENSE_RANK() OVER (ORDER BY score DESC) as `rank`,`name`,score FROM student_grades) as rank_table
WHERE `rank` = 2
补充一条，排名的序号从 1 开始，而非 0。
假使排名过程中遇到了 NULL 值，比如说某名学生旷考的情形，我们需要额外指定将 NULL 值的元组排到最后或最前 ( 取决于语义是怎样的 )。Oracle 数据库提供了 NULLS FIRST 或者 NULLS LAST 关键字，但是在 MySQL 中我们需要借助函数来等价实现。注意， MySQL 默认是 NULLS FIRST 的。
如果要主动实现 NULLS FIRST，则可以用 NOT ISNULL(<field>) 来代替，在 ORDER BY 子句中使用：
-- NULL 优先
SELECT *
FROM student_grades
ORDER BY NOT ISNULL(score)
如果要实现 NULLS LAST，则可以用 ISNULL(<field>) 来代替，在 ORDER BY 子句中使用：
SELECT *
FROM student_grades
ORDER BY ISNULL(score)
参考 ：[mysql实现排序null记录放在最后（实现oracle的nulls first|last])_打不死的小强lee的博客-CSDN博客
RANK() 的其它形式
Percent_rank() 函数可以显示当前元组在整体中的排名百分比 p。显然，第一名的 p = 0.00，而最后一名的 p = 1.00。在需要反馈 “打败了 xx.xx% 用户” 这类应用需求时，只需简单计算 1 – p 的值就可以了。
SELECT PERCENT_RANK() OVER (ORDER BY score DESC) as `rank`,`name`,score
FROM student_grades as rank_table
Row_number() 是一种不考虑并列情况的 Rank()，因此保证了每个元组的序号是唯一的。当遇到名次相同的元组时，数据库直接以一种不稳定的方式对它们进行先后排序。
SELECT ROW_NUMBER() OVER (ORDER BY score DESC) as `rank`,`name`,score
FROM student_grades as rank_table
ntile(n) 将排序好的元组进行平均分桶 ( 分区 )，桶囊括了一定区间的名次，对于单独的元组而言，它只被标识桶序号。
SELECT NTILE(4) OVER (ORDER BY score DESC) as `rank`,`name`,score
FROM student_grades as rank_table
当不能完全平均分桶时，那么每个桶之间的元组数目相差最多一个。处于 “两桶边界” 的元组，即使两者的名次相同，但也有可能被分配到不同的两个组中。该函数对于构造基于百分比的直方图时会特别有用。
假设现在的成绩还分专业 dept_name ，且希望每个专业内部能单独排列出名次，那现在只用 ORDER BY 就不太够用了，我们还需要使用 PARTITION 子句进行分区：
SELECT RANK() OVER (PARTITION BY `dept_name` ORDER BY score DESC) AS `rank`,`dept_name`,`name`,`score`
FROM student_grades
当然，如果我们仅对某一个特定的专业名次感兴趣，那么没必要使用 PARTITION BY 子句，因为它可以被如下查询代替：
SELECT RANK() OVER (ORDER BY score DESC) AS `rank`,`dept_name`,`name`,`score`
FROM student_grades
WHERE `dept_name` = 'history'
LIMIT
回归到更普通一点的排序问题。当我们的关注点不在于 “每个学生具体排第几名” 时，那么诸如 “前 n 名”，”查询第 n – m 名” 这类的需求可以使用 LIMIT 关键字解决。
-- 取成绩最高的前 3 名
SELECT * FROM student_grades ORDER BY score DESC
LIMIT 3
-- 取成绩最高的第 3 - 5 名
SELECT * FROM student_grades ORDER BY score DESC
LIMIT 3,5
LIMIT 关键字常用于一般情况下的分页查询。但是当指定的偏移量十分庞大时，基于 LIMIT 的数据查询效率就会变得十分低下。解决方案见：实战！聊聊如何解决MySQL深分页问题 – 掘金 (juejin.cn)
分窗
假设有这样一张表：stock ( price,year ) 记录了某支股票从 2010 ~ 2020 年的价格，每年只有一条数据。
CREATE TABLE stock(
price DOUBLE,
`year` CHAR(4) PRIMARY KEY
)
现在要求，每一年和前两年的股价进行一次均值计算，并返回一个新的表。比如：13 年和 12，11 年求均值，14 年和 13，12 年求均值，依此类推。特殊的，对于最早的年份 2010，由于没有 2009，2008 年的数据，因此均值就是当年的价格。而对 2011 年来说，由于没有 2009 年的数据，因此均值只考虑到 2010 年。下面的 gif 动图演示了计算的需求：
如果是求固定三年的均值，那么使用基础的 AVG 函数可以轻松解决，但是现在要对每三年进行一个均值计算，问题就变得稍微棘手了。从动图来看，计算的过程好像在推动一个 “滑动的窗口”。对于这样的计算，SQL 提供了窗口函数。分窗计算常见于股票市场的趋势分析，在商务和投资网站上可以找到各种各样的 “移动平均线”。
-- ROWS 3 PRECEDING 表示 '每一行和它前面两行' ( 一共三行 )
-- ORDER BY 'year' 保证数据是按年份有序排列的，默认是 ASC ，因此不需要额外的关键字。
SELECT `year`,AVG(price) OVER (ORDER BY `year`ROWS 3 PRECEDING) AS `avg`
FROM stock
分窗的形式多种多样。比如说：以每一年和后两年作一个均值计算：
-- ROWS 3 FOLLOWING 表示 '每一行和它后面两行' ( 一共三行 )
SELECT `year`,AVG(price) OVER (ORDER BY `year`ROWS 3 FOLLOWING) AS `avg`
FROM stock
以前 3 行和后 3 行 ( 算上 “当前行”，最多将有 7 行元组计入计算 ) 作为一个窗口进行计算：
-- BETWEEN N PRECEDING AND M FOLLOWING 最多可包含 M + N + 1 行，额外的 1 指 "当前行"。
SELECT `year`,AVG(price) OVER (ORDER BY `year`ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) AS `avg`
FROM stock
如果希望窗口的某一侧边界是无范围的，那么使用 UNBOUNDED 来替换具体的数字。下面列举了不同的情形：
-- 忽略掉 SQL 语句的其它部分。
-- 计算当前行和前面所有行
ROWS UNBOUNDED PRECEDING
-- 计算当前行和后面所有行
ROWS UNBOUNDED FOLLOWING
-- 计算前面所有行，当前行，以及后三行。
ROWS BETWEEN UNBOUNDED AND 3 FOLLOWING
-- 计算前三行，当前行，以及后面所有行。
ROWS BETWEEN 3 AND UNBOUNDED FOLLOWING
此外，计算 “前两年和今年的股票均值” 还有另外一种表达：
-- 两者表达的意思相同。
ROWS BETWEEN 3 AND CURRENT ROW
ROWS 3 PRECEDING
分窗函数不止可以按照行 ( rows ) 进行分窗计算，还可以按照 ORDER BY 的属性值的邻域 ( range ) 分窗并计算。比如：
-- 假设当前行的 price 值为 c，那么窗口选择 price 在 [c-0.5,c] 区间的所有行。
SELECT AVG(price) OVER (ORDER BY price RANGE BETWEEN 0.5 PRECEDING AND CURRENT ROW) AS avg,`year` FROM stock;
SELECT AVG(price) OVER (ORDER BY price RANGE 0.5 PRECEDING) AS avg,`year` FROM stock;
-- 假设当前行的 price 值为 c，那么窗口选择 price 在 [c-0.5,c+0.6] 区间的所有行。
SELECT AVG(price) OVER (ORDER BY price RANGE BETWEEN 0.5 PRECEDING AND 0.6 FOLLOWING) AS avg,`year` FROM stock;
额外注意，如果使用 RANGE 进行分窗，那么 ORDER BY 的属性必须是可计算的 numeric，或者是两个时间的差 interval。
SQL 窗口函数是什么？涨见识了！ – Java技术栈 – 博客园 (cnblogs.com)
mysql窗口函数中的滑动窗口weixin_46338676的博客-CSDN博客mysql 滑动窗口
旋转 PIOVT 在 MySQL 的等价实现
“旋转” 的概念舶来自 Oracle，SQL Server 数据库。MySQL 没有直接提供 pivot 关键字处理，但是我们仍然可以使用基本 SQL 语句来实现这个概念。
假定有这样的表 sales( item_name，color，clothes_size，quantity )，它的一些属性已经使用 CHECK 进行了约束，以表明它们的值域是有限集合。quantity 属性表示这个商品的库存。
CREATE TABLE sales(
item_name VARCHAR(20),
color VARCHAR(10),
clothes_size CHAR(1),
quantity INT(5),
PRIMARY KEY(item_name,color,clothes_size),
CHECK(item_name IN ('dress','pants','shirt','skirt')),
CHECK(color IN ('black','white','pastel')),
CHECK(clothes_size IN ('S','M','L'))
)
表 sales 的实例数据如下图所示：
现在，我们希望将 ( item_name，clothes_size ) 视作是一个特定组合，并分别统计出每个组合在不同的 color 维度上的库存总量 quantity，期望得到的统计数据如下图所示：
color 的值域 ( ‘dark’，‘pastel’，’white’ ) 在这个表中被旋转成为了属性。上述表是原 sales 表的另一种数据呈现形式，又被称作是交叉表，或者是数据透视表。在 Oracle 等数据库中可以直接使用 piovt 子句实现这个抽象的查询：
SELECT * FROM sales
PIVOT(
SUM(quantity)
FOR color IN ('dark','pastel','white')
)
由于 MySQL 不提供此关键字，因此通过组合基本函数的方式给出等价实现。首先，使用 GROUP BY 子句将 item_name 和 clothes_size 一同作为商品的分组，其语义为：” M 码的裙子”，” S 码的短袖 ” … 等等。被展开并旋转的 dark，white 等属性均代表着 “该组商品在各个颜色维度的库存”。那么分组内的每个商品符合哪种颜色，就将自身的 quantity 汇总到哪个 color 列上，而对剩下的两个无关列贡献 0 值即可。这个逻辑使用 IF() 函数来实现。
CREATE PROCEDURE pivot()
READS SQL DATA
BEGIN
SELECT item_name,clothes_size,
SUM(IF(color = 'dark',quantity,0)) as dark,
SUM(IF(color = 'pastel',quantity,0)) as pastel,
SUM(IF(color = 'white',quantity,0)) as white
FROM sales
GROUP BY item_name,clothes_size;
END
多维度 GROUP BY
有时，数据分析师希望能够得到以多种形式聚合统计起来的数据。下面介绍了两种高纬度分组的概念：上卷 ( ROLLUP ) 和立方体 ( CUBE ) 。
ROLLUP
继续用上一个 sales 表为例子：首先对商品按照 ( item_name，clothes_size ) 小类分组并统计库存，然后再对商品按照 item_name 进行大类分组并进行一个小计，最后对所有商品的库存再做一个总计。
如果仅使用基本的聚集函数，那么实现上述三个需求要分别使用三个 SQL 语句来实现：
-- 统计小类
SELECT item_name,color,SUM(quantity)
FROM sales
GROUP BY item_name,color;
-- 统计大类
SELECT item_name,SUM(quantity)
FROM sales
GROUP BY item_name;
-- 对所有库存做一个总计
SELECT SUM(quantity) as `total`
FROM sales;
而 SQL 提出的上卷等效于一次做完上述的三个计算：
-- 笔者在这里使用 ROW_NUMBER() 打印行数，以方便说明。
-- 不同的 DBMS，其 ROLLUP 语法会有差异，这里仅展示 mysql 的。
SELECT ROW_NUMBER() OVER () line,item_name,color,SUM(quantity)
FROM sales
GROUP BY item_name,color with ROLLUP
查询结果如下所示，其中途中没有被彩色标注的行都是小类的库存统计。而第 3，7，10 行则是每一 item_name 大类的库存小计，而第 11 行数据是所有商品库存的总计。为了能够将不同维度的统计数据整合到一张表内，数据库对部分属性值置为 NULL 值。
对比两段 SQL 代码，ROLLUP 相当于对表一次性进行了三种 GROUP BY 分组：
1. GROUP BY(item_name,color)
2. GROUP BY(item_name)
3. GROUP BY()
其中，GROUP BY() 表示不分组，它的意思是对表内所有元组求一个总计。这些分组实际上是 ROLLUP 选中属性集的所有 “前缀” 构成的集合。比如，求属性集 ( item_name，color，clothes_size ) 的上卷，那么会存在以下分组：
1. GROUP BY(item_name,color,clothes_size)
2. GROUP BY(item_name,color)
3. GROUP BY(item_name)
4. GROUP BY()
显然如果 ROLLUP 子句选中了 n 个属性，那么会划分出 n+1 个分组。注意，在 ROLLUP 选择属性的顺序会影响计算的结果。也就是说求 ROLLUP( item_name，color ) 不和 ROLLUP ( color，item_name ) 等价。如果 SQL 是这样的：
SELECT item_name,color,SUM(quantity)
FROM sales
GROUP BY color,item_name with ROLLUP
那么 ROLLUP 的分组为：
1. GROUP BY(color,item_name)
2. GROUP BY(color)
3. GROUP BY()
特别注意，如果分组的列包含 NULL，那么上卷的结果可能不正确。因为在分组统计时，NULL 具有特殊意义。因此在进行上卷计算时可以先将 NULL 转换成一个不可能存在的值，或者没有特别含义的值，比如：IFNULL(xxx,0) 。参考：MySQL ROLLUP和CUBE问题_ITPUB博客
CUBE
MySQL 似乎在 8.0.1 版本之后彻底取消掉了 CUBE 关键字 ( 且在低版本的 MySQL 中也没有实际提供对 CUBE 的支持 )。见 MySQL 官方文档的叙述：MySQL :: MySQL 8.0 Release Notes :: Changes in MySQL 8.0.1 (2017-04-10, Development Milestone)：The unimplemented and nonstandard
WITH CUBEclause forGROUP BYis no longer supported.
CUBE 子句是更高维度的划分，它会生成更多的分组，这些分组由 CUBE 子句中列出的属性的所有子集组成。比如：
SELECT item_name,color,SUM(quantity)
FROM sales
GROUP BY (item_name,color,clothes_size) WITH CUBE
这相当于划分出了以下 GROUP BY 分组：
1. GROUP BY(item_name,color,clothes_size)
2. GROUP BY(item_name,color)
3. GROUP BY(item_name,clothes_size)
4. GROUP BY(color,clothes_size)
5. GROUP BY(item_name)
6. GROUP BY(color)
7. GROUP BY(clothes_size)
8. GROUP BY()
显然，如果 CUBE 子句选中了 n 个属性，那么会划分出 2n 个分组。由于 MySQL 不再支持 CUBE 关键字，因此我们直接执行上述的 SQL 语句会报语法错误。不过，CUBE 可以看作是多个 ROLLUP 的并集。因此我们仍然可以通过组合 ROLLUP 的形式来等价实现 CUBE。
1.ROLLUP(item_name,color,clothes_size)
- GROUP BY(item_name,color,clothes_size)
- GROUP BY(item_name,color)
- GROUP BY(item_name)
- GROUP BY()
UNION
2.ROLLUP(color,clothes_size)
- GROUP BY(color,clothes_size)
- GROUP BY(color)
- GROUP BY()
UNION
3.ROLLUP(clothes_size)
- GROUP BY(clothes_size)
- GROUP BY()
我们由此可知，CUBE 是比 ROLLUP 更高维度的分组方式。
SELECT item_name,color,clothes_size,SUM(quantity) FROM sales
GROUP BY item_name,color,clothes_size WITH ROLLUP
UNION
-- 为了保证三个表的交集以一个统一的表输出，因此缺时的列使用 NULL 凑齐。
SELECT item_name,color,NULL AS clothes_size,SUM(quantity) FROM sales
GROUP BY item_name,color WITH ROLLUP
UNION
SELECT item_name,NULL AS color,NULL AS clothes_size,SUM(quantity) FROM sales
GROUP BY item_name WITH ROLLUP
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
转载请注明出处: https://daima100.com/13738.html
