/*Row_number() over(partition by…order by…)
Rank() over(partition by...order by...)
Dense_rank() over(partition by...order by...)
count() over(partition by...order by...)
max() over(partition by...order by...)
min() over(partition by...order by...)
sum() over(partition by...order by...)
avg() over(partition by...order by...)
first_value() over(partition by...order by...)
last_value() over(partition by...order by...)
lag() over(partition by...order by...)
lead() over(partition by...order by...)*/
--例子:
select empno,sal,deptno,
row_number()over(partition by deptno order by sal desc) as rn,
rank()over(partition by deptno order by sal desc) as rk,
dense_rank()over(partition by deptno order by sal desc) as drk,
count(sal)over(partition by deptno order by sal desc) as ct,
max(sal)over(partition by deptno order by sal desc) as mx,
min(sal)over(partition by deptno order by sal desc) as mn,
sum(sal)over(partition by deptno order by sal desc) as sm,
avg(sal)over(partition by deptno order by sal desc) as ag,
first_value(sal)over(partition by deptno order by sal desc) as fv,
last_value(sal)over(partition by deptno order by sal desc) as lv,
lag(sal)over(partition by deptno order by sal desc) as lg,
lead(sal)over(partition by deptno order by sal desc) as ld
from emp;