mysql  第四天 2020.4.30


-- 开窗函数
/*
开窗函数: 是在满足某种条件的记录集合上执行的特殊函数，对于每条记录都要在此窗口内执行函数。
静态窗口：有的函数随着记录不同，窗口大小都是固定的
动态窗口：有的函数则相反，不同的记录对应着不同的窗口
开窗函数的本质还是聚合运算，只不过它更具灵活性，它对数据的每一行，都使用与该行相关的行进行计算并返回计算结果

语法：开窗函数名([<字段名>]) over([partition by <分组字段>] [order by <排序字段> [desc]] [<细分窗口>])

当前行：当前行属于某个窗口，窗口由over关键字来指定函数执行的窗口范围，

over() 函数内相关参数的解析：
1.over() 不指定参数则不分，意味着整个满足条件的表数据即为一个分区，开窗函数基于所有行进行计算
2.partition by <分组字段> : 根据什么字段进行分组，类似于group by 的功能, 如果不指定则不分组。 按照指定字段进行分区，两个分区由边界分隔，开窗函数在不同的分区内分别执行，在跨越分区边界时重新初始化

3.order by <排序字段>： 对当前分区内的数据排序，即对分组内的数据进行排序，按照指定字段进行排序后，开窗函数将按照排序后的记录顺序进行编号 order by 分区内的数据排序后，如果不指定滑动窗口范围，默认计算的是分区内的第一行到当前行（便于计算累计求和）
4.<细分窗口> ：分区的一个子集，通常用来作为滑动窗口使用。对于滑动窗口的范围指定，通常使用between frame_start and frame_end 语法来表示行范围。
frame_start和frame_end可以支持如下关键字，来确定不同的动态行记录：
current row 边界是当前行，一般和其他范围关键字一起使用
unbounded preceding 边界是分区中的第一行
unbounded following 边界是分区中的最后一行
expr preceding 边界是当前行减去expr的值
expr following 边界是当前行加上expr的值

细分窗口子句语法：比如 rows between 1 preceding and 1 following


# 开窗函数，实际中的应用：移动平均 和 移动求和，累计求和  ，环比和同比计算，连续登陆天数

*/

-- 聚合函数和开窗函数的区别
-- 聚合函数是将多条记录聚合为一条；而开窗函数是每条记录都会执行，有几条记录执行完还是几条。聚合函数也可以用于开窗函数中。

-- 普通聚合函数
-- 1. 查询所有员工的平均工资
select avg(salary) from emp; -- return 1条记录
select t.deptid,avg(salary) from emp t group by t.deptid; -- return 3条记录

-- 2.聚合函数用于开窗函数
select *, avg(salary) over () from emp; -- return n 条记录
select avg(salary) over(partition by t.deptid) from emp t ; -- return n条记录，n条记录分为3组

-- 3. 计算累计值时，开窗函数很有用，会返回每一行的累计值
-- 问题：各部门按照入职日期计算部门累计工资
-- 分区内的数据排序后，如果不指定滑动窗口范围，默认计算的是分区内的第一行到当前行（便于计算累计和）
select *,sum(salary) over(partition by deptid order by hiredate) as 累计工资 from emp;

-- 4.指定滑动窗口：计算分区内的当前行的前一行和后一行
-- rows 基于行的滑动
select *,avg(salary) over(partition by deptid order by hiredate rows between 1 preceding and 1 following) as 移动平均工资 from emp;


-- 序号函数(开窗函数中的一种)
/*
row_number() 显示分区中不重复不间断的序号--针对静态窗口中，所有数据，所以不需要指定滑动窗口范围
dense_rank() 显示分区中重复不间断的序号-- 取值相同时可以并列显示序号，并列2两个人，下一个就是3
rank() 显示分区中重复间断的序号-- 可以显示并列序号，并列2两个，下一个就是4
*/

-- 查询所有员工的工资排名
select *,row_number() over( order by salary desc) as 工资排名 from emp; -- 就是加了一个排名序号，不会显示并列情况

-- 各个部门员工的工资排名

select *,
row_number() over(partition by deptid order by salary desc) 无并列1 ,
dense_rank() over(partition by deptid order by salary desc) 有并列连续2 ,
rank() over(partition by deptid order by salary desc) 有并列不连续3 from emp;


/*
按照函数功能不同，MySQL支持的开窗函数分为如下几类：
序号函数：row_number() / rank() / dense_rank()
分布函数：percent_rank() / cume_dist()
前后函数：lag() / lead()
头尾函数：first_value() / last_value()
其他函数：nth_value() / nfile()
用的时候查一下用法，而且可以查询其是否为静态窗口还是滑动窗口函数。
*/



2.能用多表查询，就不要用子查询，因为子查询在执行时会建立临时存储空间，在执行结束后会收回这块儿空间，所以多表查询效率更高些。


3.二维表的相关实现方式

-- 35 查询所有学生的课程及分数情况 （二维表）

select s_id,
sum(if(c_id = \'01\',score,0)) \'01\',
sum(if(c_id = \'02\',score,0)) \'02\',
sum(if(c_id = \'03\',score,0)) \'03\'
from sc
group by s_id ;


select s_id,
sum(case when c_id = \'01\' then score  else 0 end )\'01\',
sum(case when c_id = \'02\' then score  else 0 end) \'02\',
sum(case when c_id = \'03\' then score  else 0 end) \'03\'
from sc
group by s_id;


-- 最常用的方法,表达式法

select s_id,
sum((c_id = \'01\') * score) \'01\',
sum((c_id = \'02\') * score) \'02\' ,
sum((c_id = \'03\') * score) \'03\'
from sc
group by s_id;


4.表达式在聚合函数中的应用

/*18 、查询各科成绩最高分、最低分和平均分：
以如下形式显示：
课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
*/


select
co.c_id,
co.c_name,
max(score) 最高分,
min(score) 最低分,
avg(score) 平均分,
/*sum(score>=60)/count(score>=60) 及格率,
sum(score>=70 and score<80)/count(score>=70 and score<80) 中等率,
sum(score>=80 and score<90)/count(score>=80 and score<90) 优良率,
sum(score>=90)/count(score>=90) 优秀率*/
avg(score>=60) 及格率,
avg(score>=70 and score<80) 中等率,
avg(score>=80 and score<90)  优良率,
avg(score>=90)  优秀率
from co left join sc on co.c_id = sc.c_id
group by c_id ;

-- -- 做成百分比
select
co.c_id,
co.c_name,
max(score) 最高分,
min(score) 最低分,
avg(score) 平均分,
concat(avg(score>=60)*100,\'%\') 及格率,
concat(avg(score>=70 and score<80)*100,\'%\') 中等率,
concat(avg(score>=80 and score<90)*100,\'%\')  优良率,
concat(avg(score>=90)*100,\'%\')  优秀率
from co left join sc on co.c_id = sc.c_id
group by c_id ;  

-- 25 查询各科成绩前三名的记录 (用开窗函数排名后的结果必须通过from表查询才能使用，因为字段别名不能用于where条件中)
select * from (
select *,
rank() over( partition by c_id order by score desc )排名
from sc) t
where t.排名 <=3
;