函数专题:sum、row_number、count、rank\dense_rank over
来源:互联网 发布:微处理器和单片机 编辑:程序博客网 时间:2024/05/21 09:05
来源:http://blog.csdn.net/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') >= '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
感觉就是查询定位该记录的在表中位置
- 函数专题:sum、row_number、count、rank\dense_rank over
- ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD OVER()函数
- sql 排名函数 rank() , row_number() , dense_rank() over
- oracle 分组函数rank() over(),dense_rank(),row_number()
- dense_rank() over()、rank() over()、row_number() over分析函数
- dense_rank() over()、rank() over()、row_number() over分析函数
- rank() over,dense_rank() over,row_number() over函数的区别
- row_number rank dense_rank函数
- oracle over rank dense_rank row_number 等分析函数总结
- oracle中分析函数 (over rank dense_rank row_number)
- SQL中ROW_NUMBER()/RANK() /DENSE_RANK() OVER函数的基本用法
- 数据库-函数-over,row_number,dense_rank,rank,log,lead,first_value等
- 排名函数(ROW_NUMBER、RANK、DENSE_RANK)及OVER子句
- Oracle开发专题之:分析函数2(Rank, Dense_rank, row_number)
- Oracle开发专题之:分析函数2(Rank, Dense_rank, row_number)
- Oracle开发专题之:分析函数2(Rank, Dense_rank, row_number)
- Oracle开发专题之:分析函数2(Rank, Dense_rank, row_number)
- Oracle开发专题之:分析函数2(Rank, Dense_rank, row_number)
- webservice:Axis2
- poj2773 Happy 2006(用gcd函数进行的推导)
- UVA 10303 How Many Trees?
- C++ 特性和接口实现原则
- 1000以内的完数
- 函数专题:sum、row_number、count、rank\dense_rank over
- vmware linux mount U盘
- 从shp数据存入Oracle到geoserver发布地图数据
- cocos2d-x游戏开发(十二)场景切换:滑门效果
- 实践使用WinDBG从虚拟地址转换到物理地址
- Chrome 较简单修改 缓存路径 的方法
- ByteArrayOutputStream和ByteArrayInputStream详解
- 搭建struts2 + spring + hibernate(二):XML、代码篇
- ByteArrayOutputStream和ByteArrayInputStream详解