Oracle 分析函数

--每一个值占总数的百分比
SELECT x, y, z,round(z/sum(z) over()*100,2)||'%' propn , sum(z) over() sum FROM t1;
--每一个值占分组的百分比
SELECT x, y, z,round(z/sum(z) over(partition by x)*100,2)||'%' propn , sum(z) over(partition by x) sum FROM t1;
--以x分区，按y排序累计取和
SELECT x, y, z, sum(z) over(partition by x order by y desc) sum FROM t1;
--以x分区，按z降序，每个分区取前两个
select *
from (select x,
y,
z,
s,
dense_rank() over(partition by x order by z desc) r1,
rank() over(partition by x order by z desc) r2,
count(*) over(partition by x order by z desc, y range unbounded preceding) r3 from (SELECT x, y, z, sum(z) over(partition by x) s
FROM t1
order by 4 desc, z desc))
where r3 < 3
order by z desc, x
/*
语法：
function_name(<argument>,<argument>,...)
over(<partition-clause><order-by-clause><windowing-clause>)
函数名(参数)
over关键字( ：over关键字用于区分普通聚集函数和分析函数，必选
partition子句：将结果集分区分组，当分区变化时重新计数
ORDER BY子句：数据在分区内是如何存储的，会直接影响一些分析函数
windowing子句：一个定义变化或固定的数据窗口方法，用于分析函数计数
range窗口：根据where条件将行集中到一起，如range 5 preceding，产生一个滑动窗口，在分区内拥有所有当前行以前的5行集合，只能用于数值和日期，order by只能有一列
order by sal range 1000 preceding
row窗口：是物理单元，包括在窗口中的行的物理数
order by row 5 preceding
包含6行，当前行以及前面的5行，“前面”是指order by后的
)
*/
select deptno,
empno,
sum(sal) over(partition by deptno) dept_sum, --部门工资总和
sum(sal) over() all_sum, --全部工资总和
sum(sal) over(partition by deptno order by sal) running_sum, --按工资排序累加
row_number() over(partition by deptno order by sal) dept_seq --部门内工资排名
from emp;
select deptno,
ename,
sal,
--开窗子句，必须有order by，可以通过范围range、与当前行的偏移量来确定开窗sum(sal) over(partition by deptno order by ename rows 2 preceding) sliding_total,
--默认的开窗范围是一个固定范围，从分区第一行到当前行
sum(sal) over(partition by deptno order by ename range unbounded preceding) sliding_total
from emp
order by deptno, ename;
--range开窗子句
select ename,
sal,
hiredate,
hiredate - 100 windowtop,
first_value(ename) over(order by hiredate asc range 100 preceding) ename_prec,
first_value(hiredate) over(order by hiredate asc range 100 preceding) hiredate_prec from emp
order by hiredate asc;
--row开窗
select ename,
sal,
hiredate,
first_value(ename) over(order by hiredate asc rows 5 preceding) ename_prec,
first_value(hiredate) over(order by hiredate asc rows 5 preceding) hiredate_prec
from emp
order by hiredate asc;
--分析函数
--cume_dist：返回一行在组的位置，大于0，小于等于1，1/3,2/3/,3/3，必须order by
--percent_rank：与cume_dist相似，对一组内特定行来说，计算行号时先减1，然后除以n-1，n为组内所有行数，如果排序重复则序号相同
select deptno,
ename,
sal,
cume_dist() over(partition by deptno order by sal),
percent_rank() over(partition by deptno order by sal)
--dense_rank：根据order by子句排序后，计算当前行与其他行的相对位置，从1开始累加，排名有并列
--rank：和dense_rank相似，但rank有跳跃，相同的值序号相同
select deptno,
ename,
sal,
dense_rank() over(partition by deptno order by sal) drank,
rank() over(partition by deptno order by sal) r
from emp t;
--first_value、last_value：返回组中第一个、最后一个值，加了order by开窗和不加差别很大select deptno,
ename,
sal,
first_value(ename) over(partition by deptno order by sal) fname,
last_value(ename) over(partition by deptno order by sal) lname
from emp t;
--row_number：返回一组中的一行的偏移量，产生按特定标准排序的行号
select deptno,
ename,
sal,
row_number() over(partition by deptno order by sal desc) rn
from emp t;
--ntile：将组内分成表达式的散列值，ntile(5)，将组内数据平均分成5份，并从1开始编号，如果不能平分，则按递减顺序分配
select deptno,
ename,
sal,
ntile(2) over(partition by deptno order by sal desc) rn
from emp t;
--lag、lead：lag可以访问组中其他行而不用自连接；lead可以访问组中当前行之后的所有行而不用自连接
select deptno,
ename,
sal,
lag(ename,1) over(partition by deptno order by sal desc) la,
lead(ename,1) over(partition by deptno order by sal desc) le
from emp t;
--sum、avg、count、max、min和普通的聚集函数用法相同
--其他
--1、分析函数目前还无法直接在plsql中使用，plsql的sql引擎至今无法理解分析函数，但可以使用动态游标来搞定
--。