分析函数-over(partition by ... order by ...)

不会叫的狼

已于 2022-09-15 16:25:50 修改

阅读量536

点赞数

分类专栏： Java InterView oracle mysql 文章标签： sql

于 2022-06-06 15:37:43 首次发布

本文链接：https://blog.csdn.net/weixin_40017062/article/details/125146217

版权

Java InterView 同时被 3 个专栏收录

26 篇文章 0 订阅

订阅专栏

mysql

15 篇文章 0 订阅

订阅专栏

oracle

8 篇文章 0 订阅

订阅专栏

概述

分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。

分析函数和聚合函数很多是同名的，意思也一样，只是聚合函数用group by分组，每个分组返回一个统计值，而分析函数采用partition by分组，并且每组每行都可以返回一个统计值。

简单的说就是 聚合函数返回统计结果，分析函数返回明细加统计结果。

分析函数语法

FUNCTION_NAME(<argument>,<argument>...) OVER (<partition-by-Clause> <order-by-Clause> <Windowing Clause>)

例子：

select ename,sum(sal) over (partition by deptno order by ename) new_alias from emp;

sum：函数名（FUNCTION_NAME）
(sal)：分析函数的参数，每个函数有0~3个参数，参数可以是表达式，例如：sum(sal+comm)
over ：关键字，用于标识分析函数，否则查询分析器不能区别 sum() 聚集函数和 sum() 分析函数
partition by deptno：（按相应的值(deptno)进行分组统计）是可选的分区子句，如果不存在任何分区子句，则全部的结果集可看作一个单一的大区
order by ename：是可选的 order by 子句，对新查询得到的数据进行重新排序编号。

即：分析函数带有一个开窗函数over()，包含三个分析子句:

分组(partition by)
排序(order by)
窗口(rows)

FUNCTION_NAME子句

SUM：该函数计算组中表达式的累积和
MIN：在一个组中的数据窗口中查找表达式的最小值
MAX：在一个组中的数据窗口中查找表达式的最大值
AVG：用于计算一个组和数据窗口内表达式的平均值。
COUNT：对一组内发生的事情进行累积计数
RANK：根据 ORDER BY 子句中表达式的值，从查询返回的每一行，计算它们与其它行的相对位置
DENSE_RANK：根据 ORDER BY 子句中表达式的值，从查询返回的每一行，计算它们与其它行的相对位置
FIRST：从 DENSE_RANK 返回的集合中取出排在最前面的一个值的行
LAST：从 DENSE_RANK 返回的集合中取出排在最后面的一个值的行
ROW_NUMBER：返回有序组中一行的偏移量，从而可用于按特定标准排序的行号

应用示例

1、新建表EMP

CREATE TABLE EMP
(
"EMPNO" VARCHAR(10),
"ENAME" VARCHAR(64),
"JOB" VARCHAR(30),
"SALARY" VARCHAR(30),
"DEPTNO" VARCHAR(10)
);

insert into emp values ('1001','lili','IT','10000','01');
insert into emp values ('1002','jack','IT','12000','01');
insert into emp values ('1003','shwen','SAL','15000','02');
insert into emp values ('1004','luky','SAL','13000','02');
insert into emp values ('1005','bulc','OPE','12000','01');
insert into emp values ('1006','tom','OPE','16000','01');
insert into emp values ('1007','bob','PM','17000','03');
insert into emp values ('1008','yahan','OA','13000','04');

2、测试over函数

查询各部门人员数量

SELECT empno,ename,job,deptno,  --查询基础字段
    COUNT(*) over(PARTITION BY deptno) cnt_dept_man   --查询部门人员数量 （等同于按部门deptno进行分组查询）
FROM emp;

查询各部门人员数量，并按员工号排序

SELECT empno,ename,job,deptno,  --查询基础字段
    COUNT(*) over (PARTITION BY deptno ORDER BY empno) AS sum_dept_add  --按部门deptno进行分组查询，组内再按照empno排序
FROM emp;

查询各岗位的的人员数量

SELECT empno,ename,job,deptno,  --查询基础字段
    COUNT(*) over(PARTITION BY  job) cnt_job_man      --查询岗位的的人员数量   （等同于按岗位job进行分组查询）
FROM emp;

查询各岗位的的人员数量，并按员工号排序

SELECT empno,ename,job,deptno,  --查询基础字段
    COUNT(*) over (PARTITION BY job ORDER BY empno) AS sum_job_add      --按岗位job进行分组查询，组内再按照empno排序
FROM emp;

汇总

SELECT empno,ename,job,deptno,  ----查询基础字段
    COUNT(*) over(PARTITION BY deptno) cnt_dept_man,      --查询部门人员数量 （等同于按部门deptno进行分组查询）
    COUNT(*) over (PARTITION BY deptno ORDER BY empno) AS sum_dept_add,   --按部门deptno进行分组查询，组内再按照empno排序
    COUNT(*) over(PARTITION BY  job) cnt_job_man,         --查询岗位的的人员数量   （等同于按岗位job进行分组查询）
    COUNT(*) over (PARTITION BY job ORDER BY empno) AS sum_job_add        --按岗位job进行分组查询，组内再按照empno排序
FROM emp;

查询每个部门工资最高的员工信息

一般的写法：

SELECT E.ENAME, E.JOB, E.SAL MAXSAL, E.DEPTNO
FROM SCOTT.EMP E, 
    (SELECT E.DEPTNO,
         MAX(E.SAL) SAL
    FROM SCOTT.EMP E
    GROUP BY  E.DEPTNO) ME
WHERE E.DEPTNO = ME.DEPTNO
    AND E.SAL = ME.SAL;

使用over函数思路：
1、先获取各部门的最大薪资

select empno,ename,job,deptno,  --查询基础字段
   MAX(SALARY) over(partition by DEPTNO) as max_SALARY
from emp;

2、只获取部门字段和最高薪资，并且去重。

select distinct deptno,
   MAX(SALARY) over(partition by DEPTNO) as max_SALARY
from emp;

3、和emp表连接，获取员工信息

SELECT *
FROM emp e, 
    (SELECT DISTINCT deptno,
         MAX(SALARY) over(partition by DEPTNO) AS max_SALARY
    FROM emp) a
WHERE e.DEPTNO = a.DEPTNO
  AND e.SALARY = a.max_SALARY;

其他over函数使用：

使用count函数用order by将相应数据分组，获取分组编号

-- 使用count函数用order by将相应数据分组，获取分组编号
SELECT empno,
       ename,
       job,
       deptno,
       max_SALARY
FROM 
    (SELECT empno,
            ename,
            job,
            deptno,
            MAX(SALARY) OVER (PARTITION BY DEPTNO) AS max_SALARY, 
            COUNT(*) OVER (PARTITION BY DEPTNO ORDER BY  SALARY DESC) AS NUM
    FROM EMP)
WHERE NUM = 1;

其实上述 sql 把 MAX 去掉也可以，因为已经排序了，选第一个就行。修改如下：

SELECT empno,ename,job,deptno,SALARY
FROM 
    (SELECT empno,ename,job,deptno,SALARY,
            COUNT(*) OVER (PARTITION BY DEPTNO ORDER BY  SALARY DESC) AS NUM
    FROM EMP)
WHERE NUM = 1;

使用rank、dense_rank函数用order by将相应数据分组，获取分组编号

-- 使用rank函数用order by将相应数据分组，获取分组编号
SELECT empno,
       ename,
       job,
       deptno,
       SALARY
FROM 
    (SELECT empno,
            ename,
            job,
            deptno,
            SALARY,
            RANK() OVER (PARTITION BY DEPTNO ORDER BY  SALARY DESC) RANK
    FROM EMP) E
WHERE E.RANK = 1
      AND deptno IS NOT NULL;

使用row_number函数用order by将相应数据分组，获取分组编号

-- 使用row_number函数用order by将相应数据分组，获取分组编号 
SELECT empno,
       ename,
       job,
       deptno,
       SALARY
FROM 
    (SELECT empno,
            ename,
            job,
            deptno,
            SALARY,
            row_number() OVER (PARTITION BY DEPTNO ORDER BY  SALARY DESC) RANK
    FROM EMP) E
WHERE E.RANK = 1
      AND deptno IS NOT NULL;

count、rank、dense_rank、row_number区别

如果排序字段存在相同的数值，比如上述例子中jack、bulc的薪资都是12000，count、rank、dense_rank获取的序号是一样的，而row_number是不一样的。

查询各科前三名

create table score(student_no varchar(3), Subject_no varchar(20), Score int(11));
insert into score values('001','语文',70);
insert into score values('001','数学',60);
insert into score values('001','英语',90);
insert into score values('002','语文',78);
insert into score values('002','数学',67);
insert into score values('002','英语',80);
insert into score values('003','语文',89);
insert into score values('003','数学',60);
insert into score values('003','英语',97);
insert into score values('004','语文',50);
insert into score values('004','数学',67);
insert into score values('004','英语',70);
insert into score values('005','语文',79);
insert into score values('005','数学',65);
insert into score values('005','英语',79);
insert into score values('006','语文',78);
insert into score values('006','数学',56);
insert into score values('006','英语',87);

一般聚合做法一：

SELECT T1.*
FROM score T1
WHERE 
    (SELECT COUNT(1)
    FROM score
    WHERE Subject_no = T1.Subject_no
            AND score > T1.Score) < 3
ORDER BY  Subject_no,score DESC

一般做法二：

SELECT		T1.*
FROM 		score T1
LEFT JOIN	(SELECT DISTINCT Subject_no, score FROM score) T2
ON 			T1.Subject_no = T2.Subject_no
AND 		T1.score < T2.score
GROUP BY 	student_no,Subject_no,score
HAVING		COUNT(1) < 3
ORDER BY 	Subject_no,score DESC

先将T2表数据按科目和分数，去重，再查找满足比它分数高的记录数 < 3 的数据即可

分析函数：

SELECT student_no,
       Subject_no,
       Score
FROM 
    (SELECT student_no,
            Subject_no,
            Score,
            rank() OVER (partition by Subject_no ORDER BY  Score DESC) rank
    FROM score)
WHERE rank <= 3;

结果可见，语文成绩78的并列第三名也筛选出来了，如果使用 row_number() 则不会刷选出来。

SELECT student_no,
       Subject_no,
       Score
FROM 
    (SELECT student_no,
            Subject_no,
            Score,
            row_number() OVER (partition by Subject_no ORDER BY  Score DESC) rank
    FROM score)
WHERE rank <= 3;

查每个科目的平均分

SELECT t.*,
       AVG(score) over(PARTITION by t.Subject_no )
FROM score t

去重：

SELECT DISTINCT
       t.Subject_no,
       AVG(score) over(PARTITION by t.Subject_no) as avg_score
FROM score t

查每个学生的平均分

SELECT DISTINCT
       t.student_no,
       AVG(score) over(PARTITION by t.student_no) as avg_score
FROM score t

注意事项：

一般写法与分析函数的主要区别在于：使用分析函数可以提升sql的执行效率，一般写法是通过两个或多个表关联来进行查询（存在笛卡尔积），而用分析函数则所有的查询都在一个表中实现，大大提升了sql的查询效率（主要体现于自身表的关联查询）。

row_number的用途非常广泛，排序最好用它，它会为查询出来的每一行记录生成一个序号，依次排序且不会重复，注意使用row_number函数时必须要用over子句选择对某一列进行排序才能生成序号。

rank函数用于返回结果集的分区内每行的排名，行的排名是相关行之前的排名数加一。简单来说rank函数就是对查询出来的记录进行排名，与row_number函数不同的是，rank函数考虑到了over子句中排序字段值相同的情况，如果使用rank函数来生成序号，over子句中排序字段值相同的序号是一样的，后面字段值不相同的序号将跳过相同的排名号排下一个，也就是相关行之前的排名数加一，可以理解为根据当前的记录数生成序号，后面的记录依此类推。

参考文档：
分析函数 over （partition by …order by …）

不会叫的狼

关注

0
点赞
踩
6

收藏

觉得还不错? 一键收藏
打赏
0
评论
分析函数-over(partition by ... order by ...)

1、分析函数可以返回明细加统计结果。2、分析函数主要体现于自身表的关联查询，大大提升了sql的查询效率。3、row_number的用途非常广泛，排序最好用它，它会为查询出来的每一行记录生成一个序号，依次排序且不会重复。4、rank、dense_rank排序字段值相同的情况，如果使用rank函数来生成序号，over子句中排序字段值相同的序号是一样的。
复制链接

扫一扫