函数专题：分析函数 over

一、sum over
sum over主要用来对某个字段值进行逐步累加，连续求和

SELECT Name, Salary, SUM (Salary) OVER (ORDER BY Salary, Name) SubSal
FROM emp
ORDER BY Salary;

Name, Salary, Age
A，900， 20
B，900， 21
C，1000， 18
D，950， 20

执行后格式

Name, Salary, SubSal
A， 900， 900
B， 900， 1800
D， 950， 2750
C， 1000， 3750

sum(sal) over (partition by deptno) --按照不同的部门分组，对部门自身的员工工资连续求和
partition by 是起着分组的作用


二、row_number over
row_number() over ([partition by col1] order by col2) ) as 别名

表示根据col1分组，在分组内部根据 col2排序
而这个“别名”的值就表示每组内部排序后的顺序编号（组内连续的唯一的）,[partition by col1] 可省略

row_number() over 按照partion by 分组，然后在每组内部按照 open_date 排序 得到返回的是内部排序的顺序编号

SELECT t.family_id,
t.package_id,
t.phone,
t.reason,
t.open_date,
ROW_NUMBER ()
OVER (PARTITION BY family_id, package_id, phone
ORDER BY open_date DESC)
rn
FROM zj_transaction_log t
WHERE TO_CHAR (t.open_date, 'yyyy-mm') >= '2011-03';

FAMILY_ID PACKAGE_ID PHONE REASON OPEN_DATE RN
19811 100 ********861 ADC请求[校讯通-精装版]恢复处理 2011-3-6 14:16:29 1
19811 100 ********861 ADC请求[校讯通-精装版]暂停处理 2011-3-6 3:00:26 2
19823 100 ********667 ADC请求[精装版]暂停处理 2011-3-12 9:47:41 1
19823 100 ********667 ADC请求[校讯通-精装版]恢复处理 2011-3-7 19:03:30 2
19823 100 ********667 ADC请求[校讯通-精装版]暂停处理 2011-3-7 6:51:27 3
19841 100 ********328 ADC请求[精装版]恢复处理 2011-3-14 9:01:31 1
19841 100 ********328 ADC请求[精装版]暂停处理 2011-3-14 0:57:33 2
19869 100 ********172 ADC请求[校讯通-精装版]暂停处理 2011-3-5 2:55:17 1
19950 100 ********631 ADC请求[校讯通-精装版]恢复处理 2011-3-2 14:14:51 1

SELECT t.family_id,
t.package_id,
t.phone,
t.reason,
t.open_date,
ROW_NUMBER ()
OVER (PARTITION BY family_id, package_id, phone
ORDER BY package_id DESC)
rn
FROM zj_transaction_log t
WHERE TO_CHAR (t.open_date, 'yyyy-mm') >= '2011-03';

FAMILY_ID PACKAGE_ID PHONE REASON OPEN_DATE RN
19811 100 ********861 ADC请求[校讯通-精装版]暂停处理 2011-3-6 3:00:26 1
19811 100 ********861 ADC请求[校讯通-精装版]恢复处理 2011-3-6 14:16:29 2
19823 100 ********667 ADC请求[精装版]暂停处理 2011-3-12 9:47:41 1
19823 100 ********667 ADC请求[校讯通-精装版]恢复处理 2011-3-7 19:03:30 2
19823 100 ********667 ADC请求[校讯通-精装版]暂停处理 2011-3-7 6:51:27 3
19841 100 ********328 ADC请求[精装版]暂停处理 2011-3-14 0:57:33 1
19841 100 ********328 ADC请求[精装版]恢复处理 2011-3-14 9:01:31 2
19869 100 ********172 ADC请求[校讯通-精装版]暂停处理 2011-3-5 2:55:17 1
19950 100 ********631 ADC请求[校讯通-精装版]恢复处理 2011-3-2 14:14:51 1


三、count over

SELECT t.family_id,
t.package_id,
t.phone,
t.open_date,
t.reason,
COUNT( * )
OVER (PARTITION BY family_id, package_id, phone)
rn
FROM zj_transaction_log t
WHERE TO_CHAR (t.open_date, 'yyyy-mm') >= '2011-03';

FAMILY_ID PACKAGE_ID PHONE OPEN_DATE REASON RN
19811 100 ********861 2011-3-6 3:00:26 ADC请求[校讯通-精装版]暂停处理 2
19811 100 ********861 2011-3-6 14:16:29 ADC请求[校讯通-精装版]恢复处理 2
19823 100 ********667 2011-3-12 9:47:41 ADC请求[精装版]暂停处理 3
19823 100 ********667 2011-3-7 19:03:30 ADC请求[校讯通-精装版]恢复处理 3
19823 100 ********667 2011-3-7 6:51:27 ADC请求[校讯通-精装版]暂停处理 3
19841 100 ********328 2011-3-14 0:57:33 ADC请求[精装版]暂停处理 2
19841 100 ********328 2011-3-14 9:01:31 ADC请求[精装版]恢复处理 2
19869 100 ********172 2011-3-5 2:55:17 ADC请求[校讯通-精装版]暂停处理 1
19950 100 ********631 2011-3-2 14:14:51 ADC请求[校讯通-精装版]恢复处理 1

SELECT t.family_id,
t.package_id,
t.phone,
t.open_date,
t.reason,
COUNT( * )
OVER (PARTITION BY family_id, package_id, phone
order by open_date)
rn
FROM zj_transaction_log t
WHERE TO_CHAR (t.open_date, 'yyyy-mm') >= '2011-03';

FAMILY_ID PACKAGE_ID PHONE OPEN_DATE REASON RN
19811 100 ********861 2011-3-6 3:00:26 ADC请求[校讯通-精装版]暂停处理 1
19811 100 ********861 2011-3-6 14:16:29 ADC请求[校讯通-精装版]恢复处理 2
19823 100 ********667 2011-3-7 6:51:27 ADC请求[校讯通-精装版]暂停处理 1
19823 100 ********667 2011-3-7 19:03:30 ADC请求[校讯通-精装版]恢复处理 2
19823 100 ********667 2011-3-12 9:47:41 ADC请求[精装版]暂停处理 3
19841 100 ********328 2011-3-14 0:57:33 ADC请求[精装版]暂停处理 1
19841 100 ********328 2011-3-14 9:01:31 ADC请求[精装版]恢复处理 2
19869 100 ********172 2011-3-5 2:55:17 ADC请求[校讯通-精装版]暂停处理 1
19950 100 ********631 2011-3-2 14:14:51 ADC请求[校讯通-精装版]恢复处理 1

SELECT t.family_id,
t.package_id,
t.phone,
t.open_date,
t.reason,
COUNT( * )
OVER (PARTITION BY family_id, package_id, phone
order by package_id)
rn
FROM zj_transaction_log t
WHERE TO_CHAR (t.open_date, 'yyyy-mm') >= '2011-03';

FAMILY_ID PACKAGE_ID PHONE OPEN_DATE REASON RN
19811 100 ********861 2011-3-6 3:00:26 ADC请求[校讯通-精装版]暂停处理 2
19811 100 ********861 2011-3-6 14:16:29 ADC请求[校讯通-精装版]恢复处理 2
19823 100 ********667 2011-3-12 9:47:41 ADC请求[精装版]暂停处理 3
19823 100 ********667 2011-3-7 19:03:30 ADC请求[校讯通-精装版]恢复处理 3
19823 100 ********667 2011-3-7 6:51:27 ADC请求[校讯通-精装版]暂停处理 3
19841 100 ********328 2011-3-14 0:57:33 ADC请求[精装版]暂停处理 2
19841 100 ********328 2011-3-14 9:01:31 ADC请求[精装版]恢复处理 2
19869 100 ********172 2011-3-5 2:55:17 ADC请求[校讯通-精装版]暂停处理 1
19950 100 ********631 2011-3-2 14:14:51 ADC请求[校讯通-精装版]恢复处理 1

SELECT t.family_id,
t.package_id,
t.phone,
t.reason,
COUNT( * )
OVER (PARTITION BY family_id, package_id, phone
ORDER BY open_date DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
rn
FROM zj_transaction_log t
WHERE TO_CHAR (t.open_date, 'yyyy-mm') >= '2011-03';

FAMILY_ID PACKAGE_ID PHONE OPEN_DATE REASON RN
19811 100 ********861 2011-3-6 3:00:26 ADC请求[校讯通-精装版]暂停处理 1
19811 100 ********861 2011-3-6 14:16:29 ADC请求[校讯通-精装版]恢复处理 2
19823 100 ********667 2011-3-7 6:51:27 ADC请求[校讯通-精装版]暂停处理 1
19823 100 ********667 2011-3-7 19:03:30 ADC请求[校讯通-精装版]恢复处理 2
19823 100 ********667 2011-3-12 9:47:41 ADC请求[精装版]暂停处理 3
19841 100 ********328 2011-3-14 0:57:33 ADC请求[精装版]暂停处理 1
19841 100 ********328 2011-3-14 9:01:31 ADC请求[精装版]恢复处理 2
19869 100 ********172 2011-3-5 2:55:17 ADC请求[校讯通-精装版]暂停处理 1
19950 100 ********631 2011-3-2 14:14:51 ADC请求[校讯通-精装版]恢复处理 1

SELECT t.family_id,
t.package_id,
t.phone,
t.reason,
COUNT( * )
OVER (PARTITION BY family_id, package_id, phone
ORDER BY package_id DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
rn
FROM zj_transaction_log t
WHERE TO_CHAR (t.open_date, 'yyyy-mm') >= '2011-03';

FAMILY_ID PACKAGE_ID PHONE REASON RN
19811 100 ********861 ADC请求[校讯通-精装版]暂停处理 1
19811 100 ********861 ADC请求[校讯通-精装版]恢复处理 2
19823 100 ********667 ADC请求[精装版]暂停处理 1
19823 100 ********667 ADC请求[校讯通-精装版]恢复处理 2
19823 100 ********667 ADC请求[校讯通-精装版]暂停处理 3
19841 100 ********328 ADC请求[精装版]暂停处理 1
19841 100 ********328 ADC请求[精装版]恢复处理 2
19869 100 ********172 ADC请求[校讯通-精装版]暂停处理 1
19950 100 ********631 ADC请求[校讯通-精装版]恢复处理 1


四、rank\dense_rank over

rank 的分析功能语法：
RANK ( ) OVER ( [query_partition_clause] order_by_clause )

rank 的合计功能语法：
RANK ( expr [, expr]... ) WITHIN GROUP
( ORDER BY
expr [ DESC | ASC ] [NULLS { FIRST | LAST }]
[, expr [ DESC | ASC ] [NULLS { FIRST | LAST }]]...
)

对于分析功能，
SELECT a.*,RANK() OVER(PARTITION BY col2 ORDER BY col1) "Rank" FROM table a;
已col2分组col1排序，rank与dense_rank用法相当，区别如下：
　　
例如：当rank时为：
SELECT f.phone, f.student_name,
RANK () OVER (PARTITION BY f.phone ORDER BY f.student_name) cnt
FROM zs_family f
WHERE NVL (f.is_test, 0) <> 1
AND LENGTH (f.phone) = 11
AND f.phone NOT LIKE '%0%'
　　
PHONE STUDENT_NAME CNT
********188 陈雅琳 1
********188 陈雅琳 1
********188 陈镇豪 3

而如果用dense_rank时为：
SELECT f.phone, f.student_name,
dense_RANK () OVER (PARTITION BY f.phone ORDER BY f.student_name) cnt
FROM zs_family f
WHERE NVL (f.is_test, 0) <> 1
AND LENGTH (f.phone) = 11
AND f.phone NOT LIKE '%0%'
　　
PHONE STUDENT_NAME CNT
********188 陈雅琳 1
********188 陈雅琳 1
********188 陈镇豪 2

从上可知，都是对数据分组排序，差别在于以a分组，b排序的时候，dence_rank在并列关系时，相关等级不会跳过。rank则跳过（常用在排名）。

over 分析函数系列都十分类似。

对于合计功能：

SELECT RANK ('********188','陈雅琳') within group (ORDER BY f.phone,f.student_name) cnt
FROM zs_family f
WHERE NVL (f.is_test, 0) <> 1
AND LENGTH (f.phone) = 11
AND f.phone NOT LIKE '%0%';

cnt
258

感觉就是查询定位该记录的在表中位置


五、max\min over
功能：生成2列获得字段最大值、最小值
例如：
查询员工信息与其中工资最高最低员工 select ename,sal,max(sal) over(), min(sal) over() from emp;


六、lag\lead over
功能：
得到当前行上一行或者下一行的数据
例子：
要求用一个SQL语句，统计每个月及上月和下月的订单量
要求列表输出为
月份 当月订单量 上月订单量 下月订单量
select to_char(d.update_time, 'yyyymm'),
count(code),
lag(count(code)) over(order by count(code)) 上月订单量,
lead(count(code)) over(order by count(code)) 下有订单量
from V_NWRZ_dwxx_TEST2 d
group by to_char(d.update_time, 'yyyymm');