Oracle 分析函数练习实例

创建表

create table emp(
   deptno varchar2(20) ,
   empno varchar2(20),
   ename varchar(20),
   sal number
);

sql演示

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

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 rows between unbounded preceding and unbounded following) max_sal
  from emp

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

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

–当前行至最后一行的汇总

select empno,
       ename,
       deptno,
       sal,
       --注意rows between current row and unbounded following 指当前行到最后一行的汇总
       sum(sal) over(partition by deptno
                     order by empno
                     rows between current row and unbounded following) sum_sal
  from 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) sum_sal
  from 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) sum_sal
  from emp;

–练习取最后一个值

select
    deptno,
    empno,
    ename,
    sal,
    last_value(sal) over(partition by deptno order by sal desc rows between unbounded preceding and unbounded following) sum_sal
from
    emp;

–练习使用first_value()

select deptno,
       empno,
       ename,
       sal,
       first_value(sal)
       over(partition by deptno
            order by sal
            rows between unbounded preceding and unbounded following) max_sal
  from emp;

本文地址：https://blog.csdn.net/JISHI412/article/details/107395566