Oracle常用分析函数
目录导航:
1. 分析函数语法
2. count() over():统计分区中各组的行数,partition by 可选,order by 可选
3. sum() over():统计分区中记录的总和,partition by 可选,order by 可选
4. avg() over() :统计分区中记录的平均值,partition by 可选,order by 可选
5. min() over() :统计分区中记录的最小值,partition by 可选,order by 可选
max() over() :统计分区中记录的最大值,partition by 可选,order by 可选
6. row_number() over() :连续排序,无重复值,partition by 可选,order by 必选
7. rank() over() :跳跃排序,partition by 可选,order by 必选
8. dense_rank() :连续排序,partition by 可选,order by 必选
9. ntile(n) over() :partition by 可选,order by 必选
n表示将分区内记录平均分成n份,多出的按照顺序依次分给前面的组
1、分析函数语法
function_name(<argument>,<argument>...) over(<partition_Clause><order by_Clause><windowing_Clause>);
说明:
function_name():函数名称
argument:参数
over( ):开窗函数
partition_Clause:分区子句,数据记录集分组,group by...
order by_Clause:排序子句,数据记录集排序,order by...
windowing_Clause:开窗子句,定义分析函数在操作行的集合,三种开窗方式:rows、range、Specifying
2、count() over():统计分区中各组的行数,partition by 可选,order by 可选
SELECT deptno,ename,sal,count(*) over() FROM emp --总计数
SELECT deptno,ename,sal,count(*) over(order by deptno) FROM emp --递加计数
select deptno,ename,sal,count(*) over(partition by deptno) from emp --分组计数
select deptno,ename,sal,count(*) over(partition by deptno order by ename) from emp --分组递加计数
3、sum() over():统计分区中记录的总和,partition by 可选,order by 可选
select deptno,ename,sal,sum(sal) over() from emp --总累计求和
select deptno,ename,sal,sum(sal) over(order by deptno,ename) from emp --递加累计求和
select deptno,ename,sal,sum(sal) over(partition by deptno) from emp --分组累计求和
select deptno,ename,sal,sum(sal) over(partition by deptno order by deptno,ename) from emp --分组递加累计求和
4、avg() over() :统计分区中记录的平均值,partition by 可选,order by 可选
select deptno,ename,sal,avg(sal) over() from emp --总平均值
select deptno,ename,sal,avg(sal) over(order by deptno,ename) from emp --递加求平均值
select deptno,ename,sal,avg(sal) over(partition by deptno) from emp --分组求平均值
select deptno,ename,sal,avg(sal) over(partition by deptno order by deptno,ename) from emp --分组递加求平均值
5、min() over() :统计分区中记录的最小值,partition by 可选,order by 可选
max() over() :统计分区中记录的最大值,partition by 可选,order by 可选
select deptno,ename,sal,min(sal) over() from emp --求总最小值
select deptno,ename,sal,min(sal) over(order by deptno,ename) from emp --递加求最小值
select deptno,ename,sal,min(sal) over(partition by deptno) from emp --分组求最小值
select deptno,ename,sal,min(sal) over(partition by deptno order by deptno,ename) from emp --分组递加求最小值
select deptno,ename,sal,max(sal) over() from emp --求总最大值
select deptno,ename,sal,max(sal) over(order by deptno,ename) from emp --递加求最大值
select deptno,ename,sal,max(sal) over(partition by deptno) from emp --分组求最大值
select deptno,ename,sal,max(sal) over(partition by deptno order by deptno,ename) from emp --分组递加求最大值
6、row_number() over() :连续排序,无重复值,partition by 可选,order by 必选
select deptno,ename,sal,row_number() over (partition by deptno order by deptno,sal) from emp
select deptno,ename,sal,row_number() OVER (order by sal) from emp
7、rank() over() :跳跃排序,partition by 可选,order by 必选
select deptno,ename,sal,rank() over (partition by deptno order by deptno,sal) from emp
select deptno,ename,sal,rank() OVER (order by sal) from emp
8、dense_rank() :连续排序,partition by 可选,order by 必选
select deptno,ename,sal,dense_rank() over (partition by deptno order by deptno,sal) from emp
select deptno,ename,sal,dense_rank() OVER (order by sal) from emp
9、ntile(n) over() :partition by 可选,order by 必选
n表示将分区内记录平均分成n份,多出的按照顺序依次分给前面的组
select deptno,ename,sal,ntile(3) over(order by sal desc) from emp;
select deptno,ename,sal,ntile(3) over(partition by deptno order by sal) from emp;