1.什么是分析函数：
👉:传送门💖分析函数💖
1.1统计分析函数略解
👉:传送门💖统计分析函数💖
1.2.排序分析函数
👉:传送门💖排序分析函数💖
1.3 开窗函数 ROW 与 RANGE
👉:传送门💖开窗函数 ROW 与 RANGE💖
1.4 统计分析函数详解
👉:传送门💖统计分析函数💖
1.5 不使用order by时
👉:传送门💖不使用order by时💖
1.6 开窗函数与聚合函数
先进行聚合函数,再进行开窗函数
select deptno,
sum(count(empno)) over(order by count(empno)
rows between unbounded preceding and current row)
from emp t group by deptno;
1.会先进行聚合函数
select deptno,count(empno) from emp group by deptno order by count(empno)
1
在MySQL和Oracle中,还可以写成
select deptno,count(empno) cnt from emp group by deptno order by cnt
# 因为order by的执行顺序在select后
1
2
但是如果是having,则在Oracle中是不可以的
# MySQL：可以
select deptno,count(empno) cnt from emp group by deptno having cnt > 1; -- select先执行,having后执行
# Oracle：不可以
select deptno,count(empno) cnt from emp group by deptno having cnt > 1; -- having先执行,select后执行
# Oracle：可以
select deptno,count(empno) cnt from emp group by deptno having count(empno) > 1; -- oracle的having后面只能跟函数
# ChatGPT：
在 Oracle 中，HAVING 用于对 GROUP BY 结果进行筛选过滤，只有满足筛选条件的组才被返回。
通常情况下，HAVING 后面都是需要对分组后的结果进行聚合统计的函数，例如 SUM()、COUNT()、MAX()、
MIN()、AVG() 等函数，因为这些函数能够对每个分组内的数据进行计算，并返回分组后的统计结果。但是，
HAVING 后面也可以跟普通的表达式和逻辑运算符组成的条件，这时需要将这些条件中所涉及的列都包含在
GROUP BY 子句中。但是在这种情况下，需要注意你的查询结果是否符合你的预期，因为这种方法可能会
导致某些行被排除在分组结果之外。
2.再进行开窗函数
下图中的结果就是在上图中的结果的基础上进行计算，3,3+6=9,9+6=15
# 开窗函数里面的内容,需要在聚合函数得到的结果的基础上进行
# 如聚合函数中只有deptno和count(empno)
select deptno,
sum(count(empno)) over(order by count(empno)
rows between unbounded preceding and current row)
from emp t group by deptno;
2. 偏移分析函数 lag()与lead()用法
lag()与lead()函数是跟偏移量相关的两个分析函数，通过这两个函数可以在一次查询中取出同一字段的**前N行的数据(lag)和后N行的数据(lead)**作为独立的列,从而更方便地进行进行数据过滤。
over()表示 lag()与lead()操作的数据都在over()的范围内，他里面可以使用partition by 语句（用于分组） order by 语句（用于排序）
lead(field, num, defaultvalue) ：field需要查找的字段，num往后查找的num行的数据，defaultvalue没有符合条件的默认值
lag() 的使用示例
select e.*,lag(sal) over() from emp e;
# 由下图,显然lag(sal)中有缺省值,为lag(sal,1,null)
1
2
代码效果及重点标注如下图所示：
lead() 的使用示例
select e.*,lead(sal,2,null) over() from emp e;
1
代码效果及重点标注如下图所示：
select e.*,lead(sal,1,null) over(partition by deptno) from emp e;
# 加了分区(分组),所以lead会在组内偏移
1
2
代码效果及重点标注如下图所示：
MySQL可以在原始数据上还用lag()和lead()，Oracle需要在over()中加入内容,如partition by、order by
Oracle：select e.*,lead(sal,1,null) over(order by empno) from emp e;
1
Oracle：select e.*,lead(sal,1,null) over(partition by job order by sal) from emp e;
1
3. mysql低版本怎么实现分组排序:row_number()为例
3.1 原因：mysql8 版本才支持 over partition by 函数
3.2 解决方法：
set @rownum = 0; -- @rownum自增参数,初始化参数为0
set @cid = ''; -- 初始化动态参数cid为空
select 'sid','cid',score
from(
select 'sid',
'cid',
score,
case when @cid = 'cid' then @rownum := @rownum + 1
else @rownum := 1 -- 因为@cid初始化为空,所以第一次循环,@cid ≠ 'cid',所以不执行then,执行else子句
end rn,
@cid := 'cid', -- 第一轮循环时将cid的值赋给@cid
from sc
order by 'cid',score desc -- cid默认升序,score通过desc降序
) a
where rn <= 2;
表内容下图左半部分，代码运行解析下图右半部分
