12.分析函数与分区表——Oracle数据库学习日记

分析函数与分区表

1、分析函数

# 统计分区中各组行数
count() over(partition by ... order by...)
列出各部门的人数以员工姓名、部门编号及工资
select ename,sal,deptno,count(*) over(partition by deptno)
from emp;

# 统计分区中各组总和
sum() over(partition by ... order by ...)
列出各部门员工姓名、工资、部门编号和各部门工资总和，各部门内按工资排序
select ename,sal,deptno,
        sum(*) over(partition by deptno order by sal)
 from emp;

max() over(partition by ... order by ...)
min() over(partition by ... order by ...)
avg() over(partition by ... order by ...)

#rank() over()跳跃排序
#dense_rank() over()连续排序
rank() over(partition by ... order by ...)
dense_rank() over(partition by ... order by ...)
列出全公司的员工信息，按照各部门员工工资排序，并列出序号
select empno,ename,job,hiredate,sal,deptno,
    rank() over(partition by deptno order by sal desc) r
  from emp;

#lead() over()前一个
#lag() over()后一个
lag() over(partition by .. order by ...)
lead() over(partition by ... order by ...