sql中的分析函数

来源：互联网发布：李连杰国际影响力知乎编辑：程序博客网时间：2024/05/17 03:36

--21、查询各科成绩前三名的记录:(不考虑成绩并列情况)
select a.cno,score
from course a
left join sc b
on a.cno=b.cno
order by cno,score desc;

select * from sc

select * from
  (
   select * ,
   row_number()
   over
   (
    partition by cno
    order by score desc
   )
   as pm
   from sc
  )x
  where x.pm<=3;
select * from (select * ,rank()over(partition by cno order by score desc)as pm from sc)x where x.pm<=3;
select * from (select * ,dense_rank()over(partition by cno order by score desc)as pm from sc)x where x.pm<=3;

总结：1、partition by用于给结果集分组，如果没有指定那么它把整个结果集作为一个分组。

2.rank是在每个分组内部进行排名的

函数的区别

1
rank() over是的作用是查出指定条件后进行一个排名，但是有一个特点。假如是对学生排名，那么实用这个函数，成绩相同的两名是并列，例如下图1 2 2 4。
步骤阅读
2
dense_rank()的作用和rank()很像，唯一的一点区别就是，领命学生的成绩并列以后，下一位同学并不空出并列所占的名次，例如下图1 2 2 3。
步骤阅读
3
row_number()就不一样了，它和上面两种的区别就很明显了，这个函数不需要考虑是否并列，哪怕根据条件查询出来的数值相同也会进行连续排名，如下图

一、Oracle分析函数入门

分析函数是什么？
分析函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数，它可以在数据中进行分组然后计算基于组的某种统计值，并且每一组的每一行都可以返回一个统计值。

分析函数和聚合函数的不同之处是什么？
普通的聚合函数用group by分组，每个分组返回一个统计值，而分析函数采用partition by分组，并且每组每行都可以返回一个统计值。

分析函数的形式
分析函数带有一个开窗函数over()，包含三个分析子句:分组(partition by), 排序(order by), 窗口(rows) ，他们的使用形式如下：over(partition by xxx order by yyy rows between zzz)。
注：窗口子句在这里我只说rows方式的窗口,range方式和滑动窗口也不提

分析函数例子(在scott用户下模拟)

示例目的：显示各部门员工的工资，并附带显示该部分的最高工资。

--显示各部门员工的工资，并附带显示该部分的最高工资。SELECT E.DEPTNO,       E.EMPNO,       E.ENAME,       E.SAL,       LAST_VALUE(E.SAL)        OVER(PARTITION BY E.DEPTNO             ORDER BY E.SAL ROWS             --unbounded preceding and unbouned following针对当前所有记录的前一条、后一条记录，也就是表中的所有记录            --unbounded：不受控制的，无限的            --preceding：在...之前            --following：在...之后            BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) MAX_SAL  FROM EMP E;

运行结果：

示例目的：按照deptno分组，然后计算每组值的总和

SELECT EMPNO,       ENAME,       DEPTNO,       SAL,       SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY ENAME) max_sal  FROM SCOTT.EMP;

运行结果：

示例目的：对各部门进行分组，并附带显示第一行至当前行的汇总

SELECT EMPNO,       ENAME,       DEPTNO,       SAL,       --注意ROWS BETWEEN unbounded preceding AND current row  是指第一行至当前行的汇总       SUM(SAL) OVER(PARTITION BY DEPTNO                      ORDER BY ENAME                      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) max_sal  FROM SCOTT.EMP;

运行结果：

示例目标：当前行至最后一行的汇总

SELECT EMPNO,       ENAME,       DEPTNO,       SAL,       --注意ROWS BETWEEN current row AND unbounded following 指当前行到最后一行的汇总       SUM(SAL) OVER(PARTITION BY DEPTNO                      ORDER BY ENAME                      ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) max_sal  FROM SCOTT.EMP;

运行结果：

示例目标：当前行的上一行(rownum-1)到当前行的汇总

SELECT EMPNO,       ENAME,       DEPTNO,       SAL,       --注意ROWS BETWEEN 1 preceding AND current row 是指当前行的上一行(rownum-1)到当前行的汇总        SUM(SAL) OVER(PARTITION BY DEPTNO                      ORDER BY ENAME ROWS                      BETWEEN 1 PRECEDING AND CURRENT ROW) max_sal  FROM SCOTT.EMP;

运行结果：

示例目标：当前行的上一行(rownum-1)到当前行的下辆行(rownum+2)的汇总

SELECT EMPNO,       ENAME,       DEPTNO,       SAL,       --注意ROWS BETWEEN 1 preceding AND 1 following 是指当前行的上一行(rownum-1)到当前行的下辆行(rownum+2)的汇总       SUM(SAL) OVER(PARTITION BY DEPTNO                      ORDER BY ENAME                      ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) max_sal  FROM SCOTT.EMP;

运行结果：

0 0