一、分析函数
Oracle从8.1.6开始提供分析函数，专门用于解决复杂报表统计需求的功能强大的函数，它可以在数据中进行分组然后计算基于组的某种统计值，并且每一组的每一行都可以返回一个统计值。分析函数用于计算基于组的某种聚合值。
它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。普通的聚合函数用group by分组，每个分组返回一个统计值；而分析函数采用partition by分组，并且每组每行都可以返回一个统计值。
1、分析函数的形式：
分析函数带有一个开窗函数over()，在窗口函数中包含三个分析子句:分组(partition by), 排序(order by), 窗口(rows) ，他们的使用形式如下：over(partition by xxx order by yyy rows between zzz)。
注：窗口子句在这里我只说rows方式的窗口,range方式和滑动窗口也不提。
例如：统计函数+over()、排序函数+over()、数据分布函数+over()、统计分析函数+over()。
2、开窗函数：
开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。例如over函数
二、开窗函数
窗口函数中常用的子句有：分区（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 | SQL> select * from t_over; |
2、分区（partition by col）：
按照col进行分区统计1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16SQL> 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
16SQL> 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
16SQL> 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
14SELECT E.DEPTNO,
E.EMPNO,
E.ENAME,
E.SAL,
LAST_VALUE(E.SAL)
OVER(PARTITION BY E.DEPTNO
ORDER BY E.SAL
ROWS
--unbounded preceding and unbouned following针对当前所有记录的前一条、后一条记录，也就是表中的所有记录
--unbounded：不受控制的，无限的
--preceding：在...之前
--following：在...之后
BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) MAX_SAL
FROM EMP E;
6、下面三条语句等效：
1 | over（order by salary rows between unbounded preceding and unbounded following） |
三、常见的分析函数
常见的分析函数如下：
- 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
36SQL> 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.
注意这里有3条记录的订单总额是一样的。假如我们现在需要筛选排名前12位的客户，如果使用rownum会有什么样的后果呢？
1 | SQL> select rownum, t.* |
针对上面的情况，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
17SQL> 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 | select deptno,sal,ename, |
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 | SQL> select ename,job,sal ,lag(sal) over(order by sal) last_sal from emp; |
设置了default 值之后 第一行对应的值 为5001
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20SQL> 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、按天统计，每天统计前面几天的总和：
1 | SQL> select * from t_over1; |
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
25NAME 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
注意：
1.在求第一名成绩的时候，不能用row_number()，因为如果同班有两个并列第一，row_number()只返回一个结果
2.rank()和dense_rank()的区别是：
–rank()是跳跃排序，有两个第二名时接下来就是第四名
–dense_rank()l是连续排序，有两个第二名时仍然跟着第三名