一、分析函数
Oracle从8.1.6开始提供分析函数，专门用于解决复杂报表统计需求的功能强大的函数，它可以在数据中进行分组然后计算基于组的某种统计值，并且每一组的每一行都可以返回一个统计值。分析函数用于计算基于组的某种聚合值。
它和聚合函数的不同之处是：
对于每个组返回多行，而聚合函数对于每个组只返回一行。
普通的聚合函数用group by分组，每个分组返回一个统计值；而分析函数采用partition by分组，并且每组每行都可以返回一个统计值。
1、分析函数的形式：
例如：统计函数+over()、排序函数+over()、数据分布函数+over()、统计分析函数+over()。
2、开窗函数：
二、开窗函数
窗口函数中常用的子句有：分区（partition by）、排序（order by）、范围（rows between或range between），以及她们的混合方式。形式如下：
over(partition by xxx order by yyy rows between zzz)
1、over(order by col) ：
可以理解为按照col列排序进行
累计
，order by是个默认的开窗函数
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33
SQL> select * from t_over; A ---------- 1 2 2 2 3 4 5 6 7 9 10 rows selected. SQL> select a,avg(a)over(order by a),sum(a)over(order by a) from t_over; A AVG(A)OVER(ORDERBYA) SUM(A)OVER(ORDERBYA) ---------- -------------------- -------------------- 1 1 1 2 1.75 7 2 1.75 7 2 1.75 7 3 2 10 4 2.33333333 14 5 2.71428571 19 6 3.125 25 7 3.55555556 32 9 4.1 41 10 rows selected.
2、分区（partition by col）：
按照col进行分区统计
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
SQL> select a,sum(a)over(partition by a) from t_over; A SUM(A)OVER(PARTITIONBYA) ---------- ------------------------ 1 1 2 6 2 6 2 6 3 3 4 4 5 5 6 6 7 7 9 9 10 rows selected.
3、范围over（order by salary range between 5 preceding and 5 following）：
窗口范围为当前行数据幅度减5加5后的范围内的。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
SQL> select a,sum(a)over(order by a range between 2 preceding and 2 following) from t_over; A SUM(A)OVER(ORDERBYARANGEBETWEEN2PRECEDINGAND2FOLLOWING) ---------- ------------------------------------------------------- 1 10 -- 1减2加2范围是-1到3，a在这个范围内有1、2、2、2、3，所以是10 2 14 2 14 2 14 3 19 4 24 5 25 -- 5减2加2范围是3到7，a在这个范围内有3、4、5、6、7，所以是25 6 22 7 27 9 16 -- 9减2加2范围是7到11，a在这个范围内有7、9，所以是16 10 rows selected.
4、范围over（order by salary rows between 5 preceding and 5 following）：
窗口范围为当前行前后各移动5行。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
SQL> select a,sum(a)over(order by a rows between 2 preceding and 2 following) from t_over; A SUM(A)OVER(ORDERBYAROWSBETWEEN2PRECEDINGAND2FOLLOWING) ---------- ------------------------------------------------------ 1 5 2 7 2 10 2 13 3 16 4 20 5 25 6 31 7 27 9 22 10 rows selected.
5、可以混合使用：
以上几中可以混合使用，如下
1 2 3 4 5 6 7 8 9 10 11 12 13 14
SELECT E .DEPTNO, E .EMPNO, E .ENAME, E .SAL, LAST_VALUE (E .SAL) OVER (PARTITION BY E .DEPTNO ORDER BY E .SAL ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) MAX_SAL FROM EMP E ;
6、下面三条语句等效：
1 2 3 4 5
over（order by salary rows between unbounded preceding and unbounded following） 每行对应的数据窗口是从第一行到最后一行，等效： over（order by salary range between unbounded preceding and unbounded following） 等效 over(partition by null)
三、常见的分析函数
常见的分析函数如下：
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 …)
1、row_number()、rank()、dense_rank()：
举一个销售的例子：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36
SQL> select * from user_order order by customer_sales; REGION_ID CUSTOMER_ID CUSTOMER_SALES ---------- ----------- -------------- 5 1 151162 10 29 903383 6 7 971585 10 28 986964 9 21 1020541 9 22 1036146 8 16 1068467 6 8 1141638 5 3 1161286 5 5 1169926 8 19 1174421 7 12 1182275 7 11 1190421 6 10 1196748 6 9 1208959 10 30 1216858 5 2 1224992 9 24 1224992 9 23 1224992 8 18 1253840 7 15 1255591 7 13 1310434 10 27 1322747 8 20 1413722 6 6 1788836 10 26 1808949 5 4 1878275 7 14 1929774 8 17 1944281 9 25 2232703 30 rows selected.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
SQL> select rownum, t.* 2 from (select * 3 from user_order 4 order by customer_sales desc) t 5 where rownum <= 12 6 order by customer_sales desc; ROWNUM REGION_ID CUSTOMER_ID CUSTOMER_SALES ---------- ---------- ----------- -------------- 1 9 25 2232703 2 8 17 1944281 3 7 14 1929774 4 5 4 1878275 5 10 26 1808949 6 6 6 1788836 7 8 20 1413722 8 10 27 1322747 9 7 13 1310434 10 7 15 1255591 11 8 18 1253840 12 5 2 1224992 12 rows selected.
很明显假如只是简单地按rownum进行排序的话，我们漏掉了另外两条记录(参考上面的结果)。
针对上面的情况，Oracle从8i开始就提供了3个分析函数：rand，dense_rank，row_number来解决诸如此类的问题，下面我们来看看这3个分析函数的作用以及彼此之间的区别：
Row_number
函数返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。 例如：row_number()over(partition by col1 order by col2)表示根据col1分组，在分组内部根据col2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的）。
与rownum的区别在于：使用rownum进行排序的时候是先对结果集加入伪列rownum然后再进行排序，而此函数在包含排序从句后是先排序再计算行号码。 row_number()和rownum差不多，功能更强一点（可以在各个分组内从1开始排序）。
Dense_rank
函数返回一个唯一的值，除非当碰到相同数据时，此时所有相同数据的排名都是一样的。
Rank
函数返回一个唯一的值，除非遇到相同的数据时，此时所有相同数据的排名是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。
举一个例子：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
SQL> select region_id, customer_id, sum(customer_sales) total, 2 rank() over(order by sum(customer_sales) desc) rank, 3 dense_rank() over(order by sum(customer_sales) desc) dense_rank, 4 row_number() over(order by sum(customer_sales) desc) row_number 5 from user_order 6 group by region_id, customer_id; REGION_ID CUSTOMER_ID TOTAL RANK DENSE_RANK ROW_NUMBER ---------- ----------- ---------- ---------- ---------- ---------- 8 18 1253840 11 11 11 5 2 1224992 12 12 12 9 23 1224992 12 12 13 9 24 1224992 12 12 14 10 30 1216858 15 13 15 30 rows selected.
当出现第二条相同的记录时，Rank和Dense_rank依然给出同样的排名12；而row_number则顺延递增为13，依次类推至第三条相同的记录；
当排名进行到下一条不同的记录时，可以看到Rank函数在12和15之间空出了13,14的排名，因为这2个排名实际上已经被第二、三条相同的记录占了。而Dense_rank则顺序递增。row_number函数也是顺序递增。
2、first_value() 和last_value()：
FIRST_VALUE 返回组中数据窗口的第一个值 ；
LAST_VALUE 返回组中数据窗口的最后一个值。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
select deptno,sal,ename,first_value (ename) over (partition by deptno order by sal) first_ename,last_value (ename) over (partition by deptno order by sal) last_ename1,last_value (ename) over (partition by deptno) last_ename2 5 from emp; DEPTNO SAL ENAME FIRST_ENAM LAST_ENAME LAST_ENAME 10 1300 MILLER MILLER MILLER KING 10 2450 CLARK MILLER CLARK KING 10 5000 KING MILLER KING KING 20 800 SMITH SMITH SMITH SCOTT 20 1100 ADAMS SMITH ADAMS SCOTT 20 2975 JONES SMITH JONES SCOTT 20 3000 FORD SMITH SCOTT SCOTT 20 3000 SCOTT SMITH SCOTT SCOTT 30 950 JAMES JAMES JAMES BLAKE 30 1250 MARTIN JAMES WARD BLAKE 30 1250 WARD JAMES WARD BLAKE 30 1500 TURNER JAMES TURNER BLAKE 30 1600 ALLEN JAMES ALLEN BLAKE 30 2850 BLAKE JAMES BLAKE BLAKE 14 rows selected.
3、lag()和lead（）：
Lag和Lead函数可以在一次查询中取出同一字段的前N行的数据和后N行的值。语法：lag(exp_str,offset,defval) over()
exp_str 是要做对比的字段；
offset 是exp_str字段的偏移量 比如说 offset 为2 则 拿exp_str的第一行和第三行对比，第二行和第四行，依次类推，offset的默认值为1！
defval是当该函数无值可用的情况下返回的值。Lead函数的用法类似。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
SQL> select ename,job,sal ,lag(sal) over(order by sal) last_sal from emp; ENAME JOB SAL LAST_SAL ---------- --------- ---------- ---------- SMITH CLERK 800 JAMES CLERK 950 800 ADAMS CLERK 1100 950 WARD SALESMAN 1250 1100 MARTIN SALESMAN 1250 1250 MILLER CLERK 1300 1250 TURNER SALESMAN 1500 1300 ALLEN SALESMAN 1600 1500 CLARK MANAGER 2450 1600 BLAKE MANAGER 2850 2450 JONES MANAGER 2975 2850 SCOTT ANALYST 3000 2975 FORD ANALYST 3000 3000 KING PRESIDENT 5000 3000 14 rows selected.
设置了default 值之后 第一行对应的值 为500
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
SQL> select ename,job,sal ,lag(sal,1,500) over(order by sal) last_sal from emp; ENAME JOB SAL LAST_SAL ---------- --------- ---------- ---------- SMITH CLERK 800 500 JAMES CLERK 950 800 ADAMS CLERK 1100 950 WARD SALESMAN 1250 1100 MARTIN SALESMAN 1250 1250 MILLER CLERK 1300 1250 TURNER SALESMAN 1500 1300 ALLEN SALESMAN 1600 1500 CLARK MANAGER 2450 1600 BLAKE MANAGER 2850 2450 JONES MANAGER 2975 2850 SCOTT ANALYST 3000 2975 FORD ANALYST 3000 3000 KING PRESIDENT 5000 3000 14 rows selected.
【实例】
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
SQL> select * from t_over1; DA SA ---------- ---------- 1 20 2 15 3 14 4 18 5 30 SQL> select da,sum(sa)over(order by da) from t_over1; DA SUM(SA)OVER(ORDERBYDA) ---------- ---------------------- 1 20 2 35 3 49 4 67 5 97
2、统计各班成绩第一名的同学信息：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
NAME CLASS S fda 1 80 ffd 1 78 dss 1 95 cfe 2 74 gds 2 92 gf 3 99 ddd 3 99 adf 3 45 asdf 3 55 3dd 3 78 使用分析函数： select * from ( select name ,class ,s,rank ()over (partition by class order by s desc ) mm from t2 ) where mm=1 得到结果： NAME CLASS S MM dss 1 95 1 gds 2 92 1 gf 3 99 1 ddd 3 99 1
