oracle————————数据统计函数

 

函数名称

描述

01

sum([distinct|all]表达式)

计算分区（分组）中的数据累加和

02

min([distinct|all]表达式)

查找分区（分组）中的最小值

03

max([distinct|all]表达式)

查找分区（分组）中的最大值

04

avg([distinct|all]表达式)

计算分区（分组）中的数据平均值

05

count(*|[distinct|all]表达式)

计算分区（分组）中的数据量

范例：

select * 
from (
select e.empno,e.ename,e.deptno,e.job,e.sal,
count(e.empno) over (partition by e.deptno) count,
round(avg(e.sal) over (partition by e.deptno),2) avg,
max(e.sal) over (partition by e.deptno) max,
min(e.sal) over (partition by e.deptno) min,
sum(e.sal) over (partition by e.deptno) sum
from emp e
) e1
where e1.empno = 7369;

select e.empno,e.ename,e.sal,d.dname,d.loc,
round(avg(e.sal) over (partition by e.deptno order by e.sal),2) count,
max(e.sal) over (partition by e.deptno) max,
min(e.sal) over (partition by e.deptno) min
from emp e 
left join dept d on d.deptno = e.deptno;