[数据库概论] 高级 SQL (mysql)「终于解决」

[数据库概论] 高级 SQL (mysql)「终于解决」一些复杂的查询必须借助更高级的工具来完成,内容有函数,存储过程,触发器,分窗,旋转,上卷,立方体等。

除了基本的 CRUD 之外,一些复杂的查询必须借助更复杂的工具来完成。本章主要以 MySQL 为视角介绍以下内容:

  1. 函数,存储过程,触发器。
  2. 逻辑控制与游标 ( 相当于 “T-SQL” )。
  3. 基于 WITH RECURSIVE 实现递归查询的思路。
  4. 分窗计算与旋转。
  5. 高维度聚合:上卷 ( 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

有以下几点需要注意的地方:

  1. 区分 returnsreturn 关键字,一个在声明时使用,一个在函数体内使用。
  2. 函数体以 Begin 开头,以 End 结尾。
  3. 每一行语句后面都严格以 ; 结尾。
  4. 函数参数的名字不要和内部任何一个表的属性重名,这会带来麻烦。

函数是通过以下步骤将数据传递到外界的:

  1. 首先,通过 Declare 关键字定义一个变量 i
  2. 将表查询的结果使用 Into 关键字赋值给它;
  3. 返回 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语句的限制:

  1. CONTAINS SQL 表示子程序包含 SQL 语句,但不包含读或写数据的语句;
  2. NO SQL 表示子程序中不包含 SQL 语句;
  3. READS SQL DATA 表示子程序中包含读数据的语句;
  4. MODIFIES SQL DATA 表示子程序中包含写数据的语句。
  5. 默认情况下,系统会指定为 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 不支持返回一个表,因此如果存储过程要对外部展示结果的集合就要借助其它的办法。总体而言,需要三个步骤:

  1. 创建一个存储过程,它不需要主动声明 OUT 的参数,可以根据需求设置一些 IN 参数。
  2. 将想要返回的多个返回结果 INSERT 到这个临时表中。
  3. 保证存储过程的最后一个 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 主动调用存储过程,但是触发器相当于是一个回调函数 —— 当满足特定的条件时,数据库会自行创建一个子进程来运行它。要创建一个触发器,除了命名之外,还需要以下三个主要因素:

  1. 触发时机:BEFORE ( 之前触发 ) 或 AFTER ( 之后触发 )。
  2. 触发事件:包括 INSERTUPDATEDELETE
  3. 指定触发器所绑定的表。

综上所述,创建触发器需要这样的语法:

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 来获取一个属性的 “新值” 或 “旧值”。

过渡变量的使用受触发器的触发事件,触发时机的约束:

  1. 对于 INSERT 类型的触发器,只能获取 NEW
  2. 对于 DELETE 类型的触发器,只能获取 OLD
  3. 对于 UPDATE 类型的触发器,NEWOLD 都可以获取。
  4. 对于 BEFORE 类型的触发器,INSERTUPDATE 触发器都可以直接对 NEW 进行修改。
  5. 对于 AFTER 类型的触发器,NEW 是只读的。
  6. 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 完成

对这个迭代过程稍作总结,并给出一门课程的所有先修课程的递归定义:

  1. 能直接在 prereq 搜索到的先修课程 ( 迭代的 1,2 步 );
  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 关键字实现的递归视图中,我们没有像迭代那样创建而是临时表,而是将所有查询到的新的关系直接添加到当前视图内。递归视图的标准形式就是写成两个子查询的并操作:

  1. 基查询 ( base query ):即本例子中的定义 1。
  2. 使用递归视图自身的递归查询 ( 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 动图演示了计算的需求:

sql_win.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 的实例数据如下图所示:

sales_sql.png

现在,我们希望将 ( item_name,clothes_size ) 视作是一个特定组合,并分别统计出每个组合在不同的 color 维度上的库存总量 quantity,期望得到的统计数据如下图所示:

sales_sorted_sql.png

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.png

对比两段 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 CUBE clause for GROUP BY is 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

(0)

相关推荐

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注