欢迎来到爱书不爱输的程序猿的博客, 本博客致力于知识分享，与更多的人进行学习交流

本文收录于SQL应知应会专栏,本专栏主要用于记录对于数据库的一些学习，有基础也有进阶，有MySQL也有Oracle

分析函数的点点滴滴

  • 1.什么是分析函数：
    • 1.1统计分析函数略解
    • 1.2.排序分析函数
    • 1.3 开窗函数 ROW 与 RANGE
    • 1.4 统计分析函数详解
    • 1.5 不使用order by时
    • 1.6 开窗函数与聚合函数
  • 2. 偏移分析函数 lag()与lead()用法
  • 3. mysql低版本怎么实现分组排序:row_number()为例
    • 3.1 原因：mysql8 版本才支持 over partition by 函数
    • 3.2 解决方法：

本篇文章依然是讲SQL的分析函数的知识点，在前面的两篇文章中已经讲解了分析函数是什么、统计分析函数和排序分析函数也进行了详细的讲解和代码演示、开窗函数，这篇文章，将重心放在开窗函数与聚合函数的联合使用，并讲解了偏移分析函数lag()和lead()的用法和代码演示，在文章的末尾，对row_number()进行了扩展，因为MySQL5.7中是没有这个函数的，mysql8 版本才支持 over partition by 函数的，那么对于低版本的MySQL我们应该如何应对呢？那就快一起来学习这篇文章吧。
如果有不去的地方，大家可以评论区或者私信我，十分感谢

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)

在MySQL和Oracle中,还可以写成

select deptno,count(empno) cnt from emp group by deptno order by cnt# 因为order by的执行顺序在select后

但是如果是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)

  • 代码效果及重点标注如下图所示：

  • lead() 的使用示例

select e.*,lead(sal,2,null) over() from emp e;
  • 代码效果及重点标注如下图所示：
select e.*,lead(sal,1,null) over(partition by deptno) from emp e;# 加了分区(分组),所以lead会在组内偏移
  • 代码效果及重点标注如下图所示：
  • MySQL可以在原始数据上还用lag()和lead()，Oracle需要在over()中加入内容,如partition by、order by
Oracle：select e.*,lead(sal,1,null) over(order by empno) from emp e;

Oracle：select e.*,lead(sal,1,null) over(partition by job order by sal) from emp e;

3. mysql低版本怎么实现分组排序:row_number()为例

3.1 原因：mysql8 版本才支持 over partition by 函数

3.2 解决方法：

set @rownum = 0; -- @rownum自增参数,初始化参数为0set @cid = ''; -- 初始化动态参数cid为空select 'sid','cid',scorefrom(select 'sid', 'cid',score, case when @cid = 'cid' then @rownum := @rownum + 1else @rownum := 1-- 因为@cid初始化为空,所以第一次循环,@cid ≠ 'cid',所以不执行then,执行else子句end rn,@cid := 'cid', -- 第一轮循环时将cid的值赋给@cidfrom scorder by 'cid',score desc -- cid默认升序,score通过desc降序) awhere rn <= 2;
  • 表内容下图左半部分，代码运行解析下图右半部分