Oracle 分析函数 over(partition by ... order by ...)

今天在看别人写的视图的时候发现了这样一句sql语句 row_number() OVER( PARTITION BY xxx ORDER BY xxx) as id 发现自己以前好像没有涉及过这样的东西 然后就去网上查了一下 发现还挺好用 这里记录一下
以下内容参考自：
一、Oracle分析函数入门
oracle分析函数汇总
oracle最全面的分析函数over(Partition by…)及开窗函数详解

• 分析函数是什么？ 分析函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数，它可以在数据中进行分组然后计算基于组的某种统计值，并且每一组的每一行都可以返回一个统计值。

• 分析函数和聚合函数的区别？ 普通的聚合函数每个分组返回一个统计值，而分析函数采用partition by分组，并且每组每行都可以返回一个统计值。

• 分析函数的形式 分析函数带有一个开窗函数over()，包含三个分析子句:分组(partition by), 排序(order by), 窗口(rows) ，他们的使用形式如下：over(partition by xxx order by yyy rows)

语法

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

注：使用开窗子句时一定要有排序子句！！！

---

常用的分析函数：

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 ...)

row_number() over() ：排序，无重复值，partition by 可选，order by 必选

select ename,eage,row_number() over(order by eage) from emp; --查询结果依据eage排序
select ename,eage,row_number() over(partition by job order by eage) from emp; --查询结果根据job分组后 依据eage排序

ROW_NUMBER() OVER()函数用法详解

rank() over() ：跳跃排序，partition by 可选，order by 必选

select ename,eage,rank() over(order by eage) from emp;
select ename,eage,rank() over(partition by job order by eage) from emp;

dense_rank() ：连续排序，partition by 可选，order by 必选

select ename,eage,dense_rank() over(partition by job order by eage) from emp;
select ename,eage,dense_rank() over(order by eage) from emp;

count() over() ：统计分区中各组的行数，partition by 可选，order by 可选

select ename,esex,eage,count(*) over() from emp; --总计数
select ename,esex,eage,count(*) over(order by eage) from emp; --递加计数
select ename,esex,eage,count(*) over(partition by esex) from emp; --分组计数
select ename,esex,eage,count(*) over(partition by esex order by eage) from emp;--分组递加计数

min()/max() over() ：统计分区中记录的最小值/最大值，partition by 可选，order by 可选

select ename,esex,eage,salary,min(salary) over() from emp; --求总最小值
select ename,esex,eage,salary,min(salary) over(order by eage) from emp; --递加求最小值
select ename,esex,eage,salary,min(salary) over(partition by esex) from emp; --分组求最小值
select ename,esex,eage,salary,min(salary) over(partition by esex order by eage) from emp; --分组递加求最小值

sum() over() ：统计分区中记录的总和，partition by 可选，order by 可选

select ename,esex,eage,sum(salary) over() from emp; --总累计求和
select ename,esex,eage,sum(salary) over(order by eage) from emp; --递加累计求和
select ename,esex,eage,sum(salary) over(partition by esex) from emp; --分组累计求和
select ename,esex,eage,sum(salary) over(partition by esex order by eage) from emp; --分组递加累计求和

avg() over() ：统计分区中记录的平均值，partition by 可选，order by 可选

select ename,esex,eage,avg(salary) over() from emp; --总平均值
select ename,esex,eage,avg(salary) over(order by eage) from emp; --递加求平均值
select ename,esex,eage,avg(salary) over(partition by esex) from emp; --分组求平均值
select ename,esex,eage,avg(salary) over(partition by esex order by eage) from emp; --分组递加求平均值

first ：从DENSE_RANK返回的集合中取出排在最前面的一个值的行
last ：从DENSE_RANK返回的集合中取出排在最后面的一个值的行

select job,max(salary) keep(dense_rank first order by salary desc),
max(salary) keep(dense_rank last order by salary desc) from emp
group by job;

lag() over() ：取出前n行数据，partition by 可选，order by 必选
lead() over() ：取出后n行数据，partition by 可选，order by 必选

select ename,eage,lag(eage,1,0) over(order by salary), 
lead(eage,1,0) over(order by salary) from emp;
 
select ename,eage,lag(eage,1) over(partition by esex order by salary),
lead(eage,1) over(partition by esex order by salary) from emp;