MYSQL8.0新特性

最新推荐文章于 2024-06-12 11:17:59 发布

weixin_46091713

最新推荐文章于 2024-06-12 11:17:59 发布

阅读量849

点赞数 18

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/weixin_46091713/article/details/136401495

版权

mysql 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

窗口函数具体语法：
window_function_name(expression)
OVER(
[partition_defintion]
[order_definition]
[frame_definition]
)
即窗口函数名（[<字段名>])over([partition by <分组字段>][order by <排序字段>[desc]][<窗口分区>])
窗口函数的一个概念时当前行，当前行属于某个窗口，窗口由over关键字来指定函数执行的窗口范围，如果后面括号中什么都不写，则意味着窗口包含满足where条件的所有行，窗口函数基于所有行计算；如果不为空，则有三个参数来设置窗口，具体参数的含义如下：
partition by 子句：按照指定字段进行分区，两个分区由边界分隔，窗口函数在不同分区内分别执行，在跨越分区边界时重新初始化。
order by 子句：按照指定字段进行排序，开窗函数将按照排序后的记录顺序进行编号。可以和partition by 子句配合使用，也可以单独使用。
frame子句：当前分区的一个子集，用来定义子集的规则，通常用来作为滑动窗口使用。
按照功能分为：
序号函数：ROW_NUMBER(),RANK(),DENSE_RANK()
分布函数：PERCENT_RANK(),CUME_DIST()
前后函数：LAG(),LEAD()
头尾函数：FIRST_VALUE(),LAST_VALUE()
其他函数：NTH_VALUE(),NTILE()

导出mysql中的数据

mysqldump  -uroot -proot hr --tables employees departments>/root/hr.sql
--把导出的文件送到其他服务器上
// scp 原文件路径（直接路径/间接路径）	目标主机ip:/文件到目标端的位置
scp /root/hr.sql	192.168.100.80:/root
--把表恢复到mysql8.0的hr数据库下
mysql -uroot -p -Dhr < hr.sql

序号函数

窗口函数之序号函数：ROW_NUMBER(),RANK(),DENSE_RANK()
用途：显示分区中的当前行，对查询结果进行排序。
ROW_NUMBER():顺序排序-1，2，3
RANK():并列排序，跳过重复序号-当出现并列的时候，会跳过并列值所占用的数字，直接到下一位数字。1-1-3，1-1-1-4
DENSE_RANK():并列排序，不跳过重复序号-出现并列的时候，会跳过并列值所占用的数字。1-1-2

SELECT stu_id,name,subject,score,
ROW_NUMBER() OVER(PARTITION BY subject ORDER BY score DESC) AS ROW_NUM,
DENSE_RANK() OVER(PARTITION BY subjet ORDER BY score DESC) AS DENSE_RK,
RANK() OVER(PARTITION BY subject ORDER BY score DESC) AS RANK_RK
FROM tb_score;
结果：
stu_id		name	subject		score	ROW_NUM		DENSE_RK		RANK_RK
	1		张三		数学			 100	 1			 1				 1
	7		小强		数学			 100	 2			 1				 1
    3		王五		数学			 99		 3			 2				 3
    5		小明		数学			 99		 4			 2				 3
    6		小月		数学			 97		 5			 3				 5
    4		赵六		数学			 86		 6			 4				 6
    2		李四		数学			 55		 7			 5				 7
	5		小明		英语			 94		 1			 1				 1
	2		李四		英语			 85		 2			 2				 2
	6		小月		英语			 83		 3			 3				 3
	7		小强		英语			 83		 4			 3				 3
	3		王五		英语			 82		 5			 4				 5
	4		赵六		英语			 79		 6			 5				 6
	1		张三		英语			 78		 7			 6				 7
	7		小强		语文			 95		 1			 1				 1
	2		李四		语文			 93		 2			 2				 2
	1		张三		语文			 88		 3			 3				 3
	3		王五		英语			 86		 4			 4				 4
	4		赵六		语文			 64		 5			 5				 5
	6		小月		语文			 59		 6			 6				 6
	5		小明		语文			 52		 7			 7				 7

ROW_NUMBER() OVER(PARTITION BY subject ORDER BY score desc)
语法分析：按照subject分区（分组），分区（分组）以后按照score降序排序
窗口函数总结：
ROW_NUMBER() --使用的窗口函数/窗口函数名
OVER(
PARTITION BY subject 窗口分区/子窗口等效于group by subject

ORDER BY score DESC 窗口分区后，将窗口分区内的数据进行排序
) 窗口/打开窗口
主要的点：彼此相邻的两个窗口分区是彼此独立的，进一步来说，每一个窗口分区也都是对立的，而我们oder by将会对每个独立的窗口分区内的数据进行排序。
窗口函数，要作用在每一个独立的窗口分区（子窗口）上

分布函数

窗口函数之分布函数：PERCENT_RANK(),CUME_DIST()
PERCENT_RANK():等级值百分比非常不常用取值范围0-1
用途：每行按照公式（rank-1）/(rows-1)进行计算，其中，rank为RANK（）函数产生的排名序号，rows为当前窗口
分区内的记录总行数

SELECT
RANK() OVER(PARTITION BY stu_id ORDER BY SCORE ) AS rk,
PERCENT_RANK() OVER (PARTITION BY stu_id ORDER BY score) AS prk,
stu_id,name,score
FROM tb_score
where stu_id >= 1;
结果：
rk 		prk		stu_id		name		score
1		0			1		张三			78
2		0.5			1		张三			88	
3		1			1		张三			100
...     ...        ....      ....      ....

百分比计算：
第一行：（1-1）/（3-1）= 0/2 = 0
第二行： （2-1）/（3-1）= 1/2 = 0.5
第三行：  （3-1）/（3-1） = 2/2 = 1

给窗口指定别名：WINDOW w AS (PARTITION BY stu_id ORDER BY score)

SELECT 
RANK() OVER w AS rk,
PERCENT_RANK() OVER w AS prk,
stu_id,name,score
from tb_score
where stu_id >= 1
WINDOW w AS (PARTITION BY stu_id ORDER BY score);
等价于
SELECT
RANK() OVER(PARTITION BY stu_id ORDER BY SCORE ) AS rk,
PERCENT_RANK() OVER (PARTITION BY stu_id ORDER BY score) AS prk,
stu_id,name,score
FROM tb_score
where stu_id >= 1;
这个执行效率更好

窗口别名增加了程序的嵌套性和可读性。但是论执行效率来说，我觉得反倒是直接写分区执行效率更高。
但是不代表所有情况都适用，当你的sql文本中，如果出现了大量的相同窗口，或者说出现了大量的 over(…) ,那么还是建议使用窗口指定别名的方式去做，这样可以缩短sql语法解析时间，增加sql的解析速度。但是如果只出现一两个相同的窗口或者over(…)的话，那还是直接写吧，不用窗口别名的方式，因为减少了窗口别名变量赋值的时间。

CUME_DIST()：累积分布值函数
如果按升序排列，则统计：小于等于当前值的行数和总行数的比例
如果降序排列，则统计：大于等于当前值的行数和总行数的比例
取值范围 0-1
计算公式：累计分布比例 = 当前条件所对应的行上的 / 分组（未分组）的总行数
示例：
统计小于等于当前工资的人数占总人数的比例

SELECT 
department_id,
last_name,
salary,
cume_dist() over( order by salary) as rn1,
cume_dist() over( partition by department_id order by salary) as rn2
from employees;
结果：
department_id	last_name		salary		rn1							rn2
60				lorentz			4200		0.42990654205607476			0.2
60				Austin			4800		0.45794392523334486			0.6
60				Pataballa		4800		0.45794392523334486			0.6
60				Ernst			6000		0.48598130481121495			0.8
60				Hunold			9000		0.78504672897196261			1
...							....				.....
第一行代表的意思：lorentz的工资是4200，
rn1-在所有人的工资中小于等于4200的人占到了总人数的42.9%，
rn2-在按照department_id进行分区后，在当前窗口分区（在60号部门中）下，工资小于等于4200的人比例为20%
参照公式：1（工资小于等于4200的人）/5（分区窗口下的总人数）

统计大于等于当前工资的人数占总人数（包括分区和未分区的）的比例

select 
department_id,
last_name,
salary,
cume_dist() over(order by salary desc) as rn1,
cume_dist() over (partition by department_id order by salary desc) as rn2
from employees;
结果
department_id	last_name		salary		rn1							rn2
60				Hunold			9000		0.78504672897196261			0.2
60				Ernst			6000		0.48598130481121495			0.4
60				Pataballa		4800		0.45794392523334486			0.8
60				Austin			4800		0.45794392523334486			0.8
60				lorentz			4200		0.42990654205607476			1
...							....				.....

LAG()和LEAD()
LAG和 LEAD窗口函数可以在同一次查询中取出同一字段的前N行数据（lag）和后N行的数据（lead）作为独立的列。
在实际应用中，若要用到取今天和昨天的某字段差值时，lag和lead函数的应用就显得尤为重要。当然，这种操作可以用表的自连接实现，但是lag和lead与left join,right join 等自连接相比，效率更高，sql更简洁。
函数语法如下：
lag(exp_str,offset,defval) over( PARTITION BY … ORDER BY …)
lead(exp_str,offset,defval) over(PARTITION BY … ORDER BY …)
参数说明：
exp_str是当前字段所在行（要取的列上的数据）
offset是偏移量，即是前一个或者前N个的值，也可以时后1个或者后N个的值，假设当前行在表中排第10行，若offset为3，则表示我们所要找的数据行就是表中的第7行（即 10 -3 = 7）或者表中的第13行（10+3=13）。
defval默认值，当使用lag()和lead（）函数取表中从当前行位置向前数N行或者向后数N行且已经超出了表的范围时，lag（）或lead（）函数将defval这个参数值作为函数的返回值，若没有指定默认值，则返回null，那么在数学运算中，总要给一个默认值才不会出错。

select 
order_id,username,create_date,cost,
LAG(cost,1,0) OVER(PARTITION BY username ORDER BY create_date) cost_lag,
LEAD(cost,0,1) OVER (PARTITION BY username ORDER BY create_date) cost_lead
from tb_cs tcs;
结果：
order_id		username		create_date 	cost		cost_lag		cost_lead
	1			Jack			2022-01-01		10				0				46
	5			Jack			2022-01-05		46				10				55
	8			Jack			2022-01-08		55				46				23
	3			Jack			2022-02-03		23				55				42
	6			Jack			2022-04-06		42				23				0
	...					.....				.....
	分组中第一行中没有前一行，所以 cost_lag 为0，
	分组中第二行中数据的前一行是10，数据的后一行55

语句解析：
LAG(cost,1,0) OVER(PARTITION BY username ORDER BY create_date) cost_lag,
取当前位置上行数据所对应的前一行的数据，如果没有渠道数据就用0代表
LEAD(cost,0,1) OVER (PARTITION BY username ORDER BY create_date) cost_lead
取当前位置上行数据所对应的后一行的数据，如果没有渠道数据就用0代表
例子：
按照部门分区，工资排序，找到员工按照工资排序后比“我”工资低两位和高两位的人都是谁？

SELECT
employee_id,last_name,department_id,salary,
LAG(salary,2,0) OVER(PARTITION BY department_id ORDER BY salary ) cost_lag,
LEAD(salary,2,0) OVER(PARTITION BY department_id ORDER BY salary) cost_lead
from employees;

窗口函数之头尾函数：FIRST_VALUE(expr),LAST_VALUE(expr)
用途：返回第一个或者最后一个expr的值
应用场景：截止到当前的cost，按照日期排序查询第一个和最后一个cost的值。
执行如下sql，代码运行结果如下：

select order_id,username,create_date,cost,
FIRST_VALUE(cost) over(partition by username order by create_date desc) first_cost,
LAST_VALUE(cost) over(partition by username order by create_date desc) last_cost
from tb_cs; 
结果：
order_id	username		create_date		cost		first_cost  	last_cost
6			Jack			2022-04-06		42				42				0
3			Jack			2022-02-03		23				42				23
8			Jack			2022-01-08		55				42				55
5			Jack			2022-01-05		46				42				46
1			Jack			2022-01-01		10				42				10
由上面可以知道 first_cost显示正确，但是last_cost显示不正确

所以
select order_id,username,create_date,cost,
FIRST_VALUE(cost) over(partition by username order by create_date desc) first_cost,
LAST_VALUE(cost) over(partition by username order by create_date desc 
rows between unbounded preceding and unbounded following
) last_cost
from tb_cs; 
结果：
order_id	username		create_date		cost		first_cost  	last_cost
6			Jack			2022-04-06		42				42				10
3			Jack			2022-02-03		23				42				10
8			Jack			2022-01-08		55				42				10
5			Jack			2022-01-05		46				42				10
1			Jack			2022-01-01		10				42				10

FIRST_VALUE()：直接在结果的所有行记录中输出同一个满足条件的首个记录，窗口分区的第一个值。
LAST_VALUE():默认统计范围是 rows between unbound preceding and current row,也就是取当前行数据与当前行之前的数据的比较。
那么如果我们直接在每行数据中显示最后的那个数据，需在order by 条件的后面加上语句： rows between unbounded preceding and unbounded following,也就是前面无界和后面无界之间的行比较。

窗口函数之NTH_VALUE(expr,n)
含义：取排序中的第n个值进行输出
其中NTH_VALUE（expr,n）中的第二个参数n是指这个函数取排序第n的记录进行输出，expr可以是表达式，也可以是列名。
执行sql如下

select order_id,username,create_date,cost,
NTH_VALUE(cost,3) over(partition by username asc) nth_cost
from tb_cs;
结果：
order_id		username		create_date 	cost		nth_cost		
	1			Jack			2022-01-01		10				46				
	3			Jack			2022-02-03		23				46				
	5			Jack			2022-01-05		46				46	  --取这个列的cost进行输出			
	6			Jack			2022-04-06		42				46				
	8			Jack			2022-01-08		55				46				
	9			King			2022-04-08		62				46				
	10			King			2022-04-09		68				46				
	...					.....				.....

select order_id,username,create_date,cost,
NTH_VALUE(cost,3) over(order by  cost asc) nth_cost
from tb_cs;
结果：
order_id		username		create_date 	cost		nth_cost	
   1			Jack			2022-01-01		10				NULL
   11			Paul			2022-05-10		12				NULL
   2			Tony			2022-01-02		15				15	 ---取这行的cost值为nth_val
   3			Jack			2022-02-03		23				15
   ...					....				...
由上面可知：当nth_value中你的取值列，和窗口中排序所有的列为同一列时，只输出大于等于现在排序位的值
上面同一列cost，第三行cost为15，但是 第1行和第2行的cost小于15，所以nth_value 为null。

窗口函数之NTILE(n)函数
用途：分桶函数，将窗口中处理的数据分成n个桶
应用场景：将每个顾客按cost分到4个桶
NTILE（n）函数在数据分析中应用比较多，比如由于数据量大，需要将数据平均分配到n个并行的进程分别计算，此时就可以用NTILE（n）对数据惊醒分桶（由于记录不一定被n整除，所以数据不一定完全平均），然后将不同的桶号的数据再分配。

SELECT 
NTILE(4) OVER(PARTITION BY username ORDER BY cost) as ntl,
order_id,username,cost
from tb_cs;
结果：
ntl			order_id		username		cost
1			  1					Jack		10
1			  3					Jack		23
2			  6					Jack		42
3			  5					Jack		46
4			  8					Jack		55
....				....		.....

聚合函数和窗口联用
在窗口中每条记录动态应用聚合函数（sum/avg/max/min/count）,可以动态计算在指定的窗口内的各种聚合函数值。

SELECT   employee_id,last_name,department_id,salary,
max(salary) over(partition by department_id) as max,
min(salary) over(partition by department_id) as min,
sum(salary) over(partition by department_id) as sum,
avg(salary) over(partition by department_id) as avg,
count(salary) over(partition by department_id) as count
from  employees;
结果：
employee_id		last_name	department_id	salary	max		min		sum		avg			  count
	114			A			30				11000	11000	2500	24900	4150.0000		6
	115			b			30				3100	11000	2500	24900	4150.0000		6
	116			c			30				2900	11000	2500	24900	4150.0000		6
	117			d			30				2800	11000	2500	24900	4150.0000		6
	118			e			30				2600	11000	2500	24900	4150.0000		6
	119			f			30				2500	11000	2500	24900	4150.0000		6

--按照入职时间先后，去计算每个部门工资的总和：
SELECT employee_id,last_name,department_id,hire_date,salary,
sum(salary) over(PARTITION BY department_id ORDER BY hire_date ) as sum
from employees;
结果：
employee_id		last_name	department_id	hire_date		salary		sum
103					a			60			1990-01-03		9000		9000
104					b			60			1991-01-03		6000		15000
105					c			60			1997-01-03		4800		19800
106					d			60			1998-01-03		4800		24600
...			....						....			....
那么这么写的优点是：可以更好的描述数据的变化。
包括在统计某个部门 某个员工月薪的变化时，也经常使用这种写法。

weixin_46091713

关注

18
点赞
踩
19

收藏

觉得还不错? 一键收藏
1
评论
MYSQL8.0新特性

窗口函数具体语法：OVER(即窗口函数名（[<字段名>])over([partition by <分组字段>][order by <排序字段>[desc]][<窗口分区>])窗口函数的一个概念时当前行，当前行属于某个窗口，窗口由over关键字来指定函数执行的窗口范围，如果后面括号中什么都不写，则意味着窗口包含满足where条件的所有行，窗口函数基于所有行计算；
复制链接

扫一扫