函数专题：sum row_number count rank\dense_rank over

来源：/bbliutao/article/details/7727320

一、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') >= '-03';

FAMILY_ID PACKAGE_ID PHONE REASON OPEN_DATE RN

19811 100 ********861 ADC请求[校讯通-精装版]恢复处理 -3-6 14:16:29 1

19811 100 ********861 ADC请求[校讯通-精装版]暂停处理 -3-6 3:00:26 2

19823 100 ********667 ADC请求[精装版]暂停处理 -3-12 9:47:41 1

19823 100 ********667 ADC请求[校讯通-精装版]恢复处理 -3-7 19:03:30 2

19823 100 ********667 ADC请求[校讯通-精装版]暂停处理 -3-7 6:51:27 3

19841 100 ********328 ADC请求[精装版]恢复处理 -3-14 9:01:31 1

19841 100 ********328 ADC请求[精装版]暂停处理 -3-14 0:57:33 2

19869 100 ********172 ADC请求[校讯通-精装版]暂停处理 -3-5 2:55:17 1

19950 100 ********631 ADC请求[校讯通-精装版]恢复处理 -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') >= '-03';

FAMILY_ID PACKAGE_ID PHONE REASON OPEN_DATE RN

19811 100 ********861 ADC请求[校讯通-精装版]暂停处理 -3-6 3:00:26 1

19811 100 ********861 ADC请求[校讯通-精装版]恢复处理 -3-6 14:16:29 2

19823 100 ********667 ADC请求[精装版]暂停处理 -3-12 9:47:41 1

19823 100 ********667 ADC请求[校讯通-精装版]恢复处理 -3-7 19:03:30 2

19823 100 ********667 ADC请求[校讯通-精装版]暂停处理 -3-7 6:51:27 3

19841 100 ********328 ADC请求[精装版]暂停处理 -3-14 0:57:33 1

19841 100 ********328 ADC请求[精装版]恢复处理 -3-14 9:01:31 2

19869 100 ********172 ADC请求[校讯通-精装版]暂停处理 -3-5 2:55:17 1

19950 100 ********631 ADC请求[校讯通-精装版]恢复处理 -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') >= '-03';

FAMILY_ID PACKAGE_ID PHONE OPEN_DATE REASON RN

19811 100 ********861 -3-6 3:00:26 ADC请求[校讯通-精装版]暂停处理 2

19811 100 ********861 -3-6 14:16:29 ADC请求[校讯通-精装版]恢复处理 2

19823 100 ********667 -3-12 9:47:41 ADC请求[精装版]暂停处理 3

19823 100 ********667 -3-7 19:03:30 ADC请求[校讯通-精装版]恢复处理 3

19823 100 ********667 -3-7 6:51:27 ADC请求[校讯通-精装版]暂停处理 3

19841 100 ********328 -3-14 0:57:33 ADC请求[精装版]暂停处理 2

19841 100 ********328 -3-14 9:01:31 ADC请求[精装版]恢复处理 2

19869 100 ********172 -3-5 2:55:17 ADC请求[校讯通-精装版]暂停处理 1

19950 100 ********631 -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') >= '-03';

FAMILY_ID PACKAGE_ID PHONE OPEN_DATE REASON RN

19811 100 ********861 -3-6 3:00:26 ADC请求[校讯通-精装版]暂停处理 1

19811 100 ********861 -3-6 14:16:29 ADC请求[校讯通-精装版]恢复处理 2

19823 100 ********667 -3-7 6:51:27 ADC请求[校讯通-精装版]暂停处理 1

19823 100 ********667 -3-7 19:03:30 ADC请求[校讯通-精装版]恢复处理 2

19823 100 ********667 -3-12 9:47:41 ADC请求[精装版]暂停处理 3

19841 100 ********328 -3-14 0:57:33 ADC请求[精装版]暂停处理 1

19841 100 ********328 -3-14 9:01:31 ADC请求[精装版]恢复处理 2

19869 100 ********172 -3-5 2:55:17 ADC请求[校讯通-精装版]暂停处理 1

19950 100 ********631 -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') >= '-03';

FAMILY_ID PACKAGE_ID PHONE OPEN_DATE REASON RN

19811 100 ********861 -3-6 3:00:26 ADC请求[校讯通-精装版]暂停处理 2

19811 100 ********861 -3-6 14:16:29 ADC请求[校讯通-精装版]恢复处理 2

19823 100 ********667 -3-12 9:47:41 ADC请求[精装版]暂停处理 3

19823 100 ********667 -3-7 19:03:30 ADC请求[校讯通-精装版]恢复处理 3

19823 100 ********667 -3-7 6:51:27 ADC请求[校讯通-精装版]暂停处理 3

19841 100 ********328 -3-14 0:57:33 ADC请求[精装版]暂停处理 2

19841 100 ********328 -3-14 9:01:31 ADC请求[精装版]恢复处理 2

19869 100 ********172 -3-5 2:55:17 ADC请求[校讯通-精装版]暂停处理 1

19950 100 ********631 -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') >= '-03';

FAMILY_ID PACKAGE_ID PHONE OPEN_DATE REASON RN

19811 100 ********861 -3-6 3:00:26 ADC请求[校讯通-精装版]暂停处理 1

19811 100 ********861 -3-6 14:16:29 ADC请求[校讯通-精装版]恢复处理 2

19823 100 ********667 -3-7 6:51:27 ADC请求[校讯通-精装版]暂停处理 1

19823 100 ********667 -3-7 19:03:30 ADC请求[校讯通-精装版]恢复处理 2

19823 100 ********667 -3-12 9:47:41 ADC请求[精装版]暂停处理 3

19841 100 ********328 -3-14 0:57:33 ADC请求[精装版]暂停处理 1

19841 100 ********328 -3-14 9:01:31 ADC请求[精装版]恢复处理 2

19869 100 ********172 -3-5 2:55:17 ADC请求[校讯通-精装版]暂停处理 1

19950 100 ********631 -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') >= '-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

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