MoocherMan
- 浏览: 33264 次
- 性别:
- 来自: 北京
-
文章分类
最新评论
========================================================================================
转自：
http://www.diybl.com/course/7_databases/oracle/oraclejs/20091005/177878.html
http://www.cnblogs.com/umen/archive/2011/04/11/2012136.html
sql over的作用及用法
RANK ( ) OVER ( [query_partition_clause] order_by_clause )
DENSE_RANK ( ) OVER ( [query_partition_clause] order_by_clause )
可实现按指定的字段分组排序，对于相同分组字段的结果集进行排序,
其中PARTITION BY 为分组字段，ORDER BY 指定排序字段
over不能单独使用，要和分析函数：rank(),dense_rank(),row_number()等一起使用。
其参数：over（partition by columnname1 order by columnname2）
含义：按columname1指定的字段进行分组排序，或者说按字段columnname1的值进行分组排序。
例如：employees表中，有两个部门的记录：department_id ＝10和20
select department_id，rank（） over（partition by department_id order by salary) from employees就是指在部门10中进行薪水的排名，在部门20中进行薪水排名。如果是partition by org_id，则是在整个公司内进行排名。
目的：以oracle自带的scott模式为测试环境，主要通过试验体会分析函数的用法。
类似 sum(...) over ... 的使用
1.原表信息：
****************************************************************************************
插播几个分析函数：
rownumber
denserank
rank
这三个函数的作用都是给数据生成从1到N的自然数，也就是给数据排名。
区别在于对 相同数据的排名 如有多个学生的得分都是60分 那么根据考分给各位同学排名时：
rownumber：相同考分的同学排名不一样[按考分不一样处理]，按顺序分列第一第二第三。。。
denserank:相同考分的同学排名一样 如都是第一名，下一个不同考分的名次就是第二名。
rank : 相同考分的同学排名都是一样 如都是第一名，下一个名次就不会出现第二名了 会顺位向下计算 如 有三个同学并列第一 那么下一个名次就是 第四名。
partition by 与 group by 的区别[分析函数 与 聚合函数的区别]：
前者返回的结果集可以是多条记录，后者返回的结果集只能为单条记录
如 partition by 部门 按部门分区显示数据
group by 部门 将每个部门的信息汇总处理后，返回各部门的汇总信息（汇总包括 sum count 等）
===========================================================================================
转自：
http://www.diybl.com/course/7_databases/oracle/oraclejs/20091005/177878.html
http://www.cnblogs.com/umen/archive/2011/04/11/2012136.html
sql over的作用及用法
RANK ( ) OVER ( [query_partition_clause] order_by_clause )
DENSE_RANK ( ) OVER ( [query_partition_clause] order_by_clause )
可实现按指定的字段分组排序，对于相同分组字段的结果集进行排序,
其中PARTITION BY 为分组字段，ORDER BY 指定排序字段
over不能单独使用，要和分析函数：rank(),dense_rank(),row_number()等一起使用。
其参数：over（partition by columnname1 order by columnname2）
含义：按columname1指定的字段进行分组排序，或者说按字段columnname1的值进行分组排序。
例如：employees表中，有两个部门的记录：department_id ＝10和20
select department_id，rank（） over（partition by department_id order by salary) from employees就是指在部门10中进行薪水的排名，在部门20中进行薪水排名。如果是partition by org_id，则是在整个公司内进行排名。
WITH OBJ AS( SELECT name, type
目的：以oracle自带的scott模式为测试环境，主要通过试验体会分析函数的用法。
类似 sum(...) over ... 的使用
1.原表信息：
SQL> break on deptno skip 1 -- 为效果更明显，把不同部门的数据隔段显示。
SQL> select deptno,ename,sal
2 from emp
3 order by deptno;
DEPTNO ENAME SAL
---------- ---------- ----------
10 CLARK 2450
KING 5000
MILLER 1300
20 SMITH 800
ADAMS 1100
FORD 3000
SCOTT 3000
JONES 2975
30 ALLEN 1600
BLAKE 2850
MARTIN 1250
JAMES 950
TURNER 1500
WARD 1250
已选择14行。
2.先来一个简单的，注意over(...)条件的不同，
使用 sum(sal) over (order by ename)... 查询员工的薪水“连续”求和,
注意over (order by ename)如果没有order by 子句，求和就不是“连续”的，
放在一起，体会一下不同之处：
SQL> break on '' -- 取消数据分段显示
SQL> select deptno,ename,sal,
2 sum(sal) over (order by ename) 连续求和,
3 sum(sal) over () 总和, -- 此处sum(sal) over () 等同于sum(sal)
4 100*round(sal/sum(sal) over (),4) "份额(%)"
5 from emp
6 /
DEPTNO ENAME SAL 连续求和 总和 份额(%)
---------- ---------- ---------- ---------- ---------- ----------
20 ADAMS 1100 1100 29025 3.79
30 ALLEN 1600 2700 29025 5.51
30 BLAKE 2850 5550 29025 9.82
10 CLARK 2450 8000 29025 8.44
20 FORD 3000 11000 29025 10.34
30 JAMES 950 11950 29025 3.27
20 JONES 2975 14925 29025 10.25
10 KING 5000 19925 29025 17.23
30 MARTIN 1250 21175 29025 4.31
10 MILLER 1300 22475 29025 4.48
20 SCOTT 3000 25475 29025 10.34
20 SMITH 800 26275 29025 2.76
30 TURNER 1500 27775 29025 5.17
30 WARD 1250 29025 29025 4.31
已选择14行。
3.使用子分区查出各部门薪水连续的总和。注意按部门分区。注意over(...)条件的不同，
sum(sal) over (partition by deptno order by ename) 按部门“连续”求总和
sum(sal) over (partition by deptno) 按部门求总和
sum(sal) over (order by deptno，ename) 不按部门“连续”求总和
sum(sal) over () 不按部门，求所有员工总和，效果等同于sum(sal)。
SQL> break on deptno skip 1 -- 为效果更明显，把不同部门的数据隔段显示。
SQL> select deptno,ename,sal,
2 sum(sal) over (partition by deptno order by ename) 部门连续求和,--各部门的薪水"连续"求和
3 sum(sal) over (partition by deptno) 部门总和, -- 部门统计的总和，同一部门总和不变
4 100*round(sal/sum(sal) over (partition by deptno),4) "部门份额(%)",
5 sum(sal) over (order by deptno,ename) 连续求和, --所有部门的薪水"连续"求和
6 sum(sal) over () 总和, -- 此处sum(sal) over () 等同于sum(sal)，所有员工的薪水总和
7 100*round(sal/sum(sal) over (),4) "总份额(%)"
8 from emp
9 /
DEPTNO ENAME SAL 部门连续求和 部门总和 部门份额(%) 连续求和 总和 总份额(%)
------ ------ ----- ------------ ---------- ----------- ---------- ------ ----------
10 CLARK 2450 2450 8750 28 2450 29025 8.44
KING 5000 7450 8750 57.14 7450 29025 17.23
MILLER 1300 8750 8750 14.86 8750 29025 4.48
20 ADAMS 1100 1100 10875 10.11 9850 29025 3.79
FORD 3000 4100 10875 27.59 12850 29025 10.34
JONES 2975 7075 10875 27.36 15825 29025 10.25
SCOTT 3000 10075 10875 27.59 18825 29025 10.34
SMITH 800 10875 10875 7.36 19625 29025 2.76
30 ALLEN 1600 1600 9400 17.02 21225 29025 5.51
BLAKE 2850 4450 9400 30.32 24075 29025 9.82
JAMES 950 5400 9400 10.11 25025 29025 3.27
MARTIN 1250 6650 9400 13.3 26275 29025 4.31
TURNER 1500 8150 9400 15.96 27775 29025 5.17
WARD 1250 9400 9400 13.3 29025 29025 4.31
已选择14行。
4.来一个综合的例子，求和规则有按部门分区的，有不分区的例子
SQL> select deptno,ename,sal,sum(sal) over (partition by deptno order by sal) dept_sum,
2 sum(sal) over (order by deptno,sal) sum
3 from emp;
DEPTNO ENAME SAL DEPT_SUM SUM
---------- ---------- ---------- ---------- ----------
10 MILLER 1300 1300 1300
CLARK 2450 3750 3750
KING 5000 8750 8750
20 SMITH 800 800 9550
ADAMS 1100 1900 10650
JONES 2975 4875 13625
SCOTT 3000 10875 19625
FORD 3000 10875 19625
30 JAMES 950 950 20575
WARD 1250 3450 23075
MARTIN 1250 3450 23075
TURNER 1500 4950 24575
ALLEN 1600 6550 26175
BLAKE 2850 9400 29025
已选择14行。
5.来一个逆序的，即部门从大到小排列，部门里各员工的薪水从高到低排列，累计和的规则不变。
SQL> select deptno,ename,sal,
2 sum(sal) over (partition by deptno order by deptno desc,sal desc) dept_sum,
3 sum(sal) over (order by deptno desc,sal desc) sum
4 from emp;
DEPTNO ENAME SAL DEPT_SUM SUM
---------- ---------- ---------- ---------- ----------
30 BLAKE 2850 2850 2850
ALLEN 1600 4450 4450
TURNER 1500 5950 5950
WARD 1250 8450 8450
MARTIN 1250 8450 8450
JAMES 950 9400 9400
20 SCOTT 3000 6000 15400
FORD 3000 6000 15400
JONES 2975 8975 18375
ADAMS 1100 10075 19475
MITH 800 10875 20275
10 KING 5000 5000 25275
CLARK 2450 7450 27725
MILLER 1300 8750 29025
已选择14行。
6.体会：在"... from emp;"后面不要加order by 子句，使用的分析函数的(partition by deptno order by sal)
里已经有排序的语句了，如果再在句尾添加排序子句，一致倒罢了，不一致，结果就令人费劲了。如：
SQL> select deptno,ename,sal,sum(sal) over (partition by deptno order by sal) dept_sum,
2 sum(sal) over (order by deptno,sal) sum
3 from emp
4 order by deptno desc;
DEPTNO ENAME SAL DEPT_SUM SUM
---------- ---------- ---------- ---------- ----------
30 JAMES 950 950 20575
WARD 1250 3450 23075
MARTIN 1250 3450 23075
TURNER 1500 4950 24575
ALLEN 1600 6550 26175
BLAKE 2850 9400 29025
20 SMITH 800 800 9550
ADAMS 1100 1900 10650
JONES 2975 4875 13625
SCOTT 3000 10875 19625
FORD 3000 10875 19625
10 MILLER 1300 1300 1300
CLARK 2450 3750 3750
KING 5000 8750 8750
已选择14行
==================================================================
利用over实现的分页功能：
--假设code1,code2为用来分页的KEY，每页显示5条数据
select code1,code2,code3,
ceil(count(*) over(partition by code1,code2 order by rownum)/5), --总页数
count(*) over(partition by code1,code2)
from ma_kbn order by code1,code2
==================================================================
****************************************************************************************
插播几个分析函数：
rownumber
denserank
rank
这三个函数的作用都是给数据生成从1到N的自然数，也就是给数据排名。
区别在于对 相同数据的排名 如有多个学生的得分都是60分 那么根据考分给各位同学排名时：
rownumber：相同考分的同学排名不一样[按考分不一样处理]，按顺序分列第一第二第三。。。
denserank:相同考分的同学排名一样 如都是第一名，下一个不同考分的名次就是第二名。
rank : 相同考分的同学排名都是一样 如都是第一名，下一个名次就不会出现第二名了 会顺位向下计算 如 有三个同学并列第一 那么下一个名次就是 第四名。
partition by 与 group by 的区别[分析函数 与 聚合函数的区别]：
前者返回的结果集可以是多条记录，后者返回的结果集只能为单条记录
如 partition by 部门 按部门分区显示数据
group by 部门 将每个部门的信息汇总处理后，返回各部门的汇总信息（汇总包括 sum count 等）
===========================================================================================
分享到：
发表评论
-
ORACLE 表空间相关[补充中]
2013-02-25 16:58 883一.查询表空间信息 SELECT B.FILE_NAME ... -
ORACLE 多表INSERT
2013-02-21 16:21 857ORACLE9i开始，可以通过 [INSERT ALL/INS ... -
ORACLE PARTITION表分区 [转载]
2013-02-18 08:57 636此文从以下几个方面来整理关于分区表的概念及操作: ... -
ORACLE 中的 正则表达式
2012-12-07 15:59 14711. REGEXP_SUBSTR REGEXP_S ... -
ORACLE 免客户端 PLSQLDeveloper(64位OS)
2012-12-06 12:59 13531.下载附件instantclient_10_2.zip,解压 ... -
ORACLE 常用函数[转]
2012-11-16 15:25 5941、set linesize 100; 设置长度 ... -
ORACLE 存储过程实例
2012-11-16 13:23 839环境： oracle数据库 PL/SQL Developer ... -
ORACLE 分析函数实例[处理冗余数据]
2012-11-14 15:56 735一.创建数据库表结构 create table test1 ... -
ORACLE常用的SQL函数
2012-11-08 14:51 8ORACLE常用函数实例 1、set linesize ... -
ORACLE 处理冗余数据[转]
2012-09-27 12:07 859比如现在有一人员表 (表名：peosons) 若想将姓 ... -
ORACLE SQL语句笔试题(转)
2011-12-02 15:30 773（1）表名：购物信息 ... -
数据库优化收集
2011-12-01 20:18 512三、不可优化的where子句 1.例：下列SQL条件语句中的 ...
相关推荐
oracle分析函数，oracle分析函数over_及开窗函数用法。
oracle的分析函数over 及开窗函数
1 Oracle开发专题之：分析函数 OVER 2 Oracle开发专题之：分析函数 Rank Dense rank row number 3 Oracle开发专题之：分析函数3 Top Bottom N First Last NTile 4 Oracle开发专题之：窗口函数 5 Oracle开发专题...
主要包括分析函数(OVER);分析函数2(Rank, Dense_rank, row_number);分析函数3(Top/Bottom N、First/Last、NTile);窗口函数;报表函数;分析函数总结;26个分析函数;PLSQL开发笔记和小结;分析函数简述 ROW_NUMBER () ...
oracle分析函数，窗口函数，报表函数 分析函数(OVER) 分析函数2(Rank, Dense_rank, row_number) 分析函数3(Top/Bottom N、First/Last、NTile)
oracle分析函数row_number() over()使用，很好的ORACLE分析函数
Oracle分析函数RANK(),ROW_NUMBER(),LAG()等的使用方法 Oracle分析函数参考手册 开窗函数(over)详解
oracle分析函数及开窗函数的使用，包括over等分析函数
目录 Oracle开发专题之：分析函数(OVER) Oracle开发专题之：分析函数2(Rank, Dense_rank, row_number) Oracle开发专题之：分析函数3(Top/Bottom N、First/Last、NTile) Oracle开发专题之：窗口函数 ...
介绍ORACLE分析函数的一些例子，对开发报表非常有帮助
oracle数据仓库分析函数汇总：over oracle 分析函数 数据仓库:带了测试数据，由浅到深圳分析数据仓库的分析函数，是BI入门的好帮手
分析函数应用： 1、 结构：聚合函数（）over（ partition by 字段1,字段2,字段3 order by 字段 desc/asc range between 数值/date preceding/flowing and 数值/date preceding/flowing） 聚合函数可以是：sum,count,...
本文通过实例代码给大家介绍了oracle百分比分析函数RATIO_TO_REPORT() OVER(),代码简单易懂，非常不错，具有一定的参考借鉴价值，需要的朋友可以参考下
第六篇 著名函数之分析函数 1、AVG([DISTINCT|ALL] expr) OVER(analytic_clause) 计算平均值。 例如： --聚合函数 SELECT col, AVG(value) FROM tmp1 GROUP BY col ORDER BY col; --分析函数 SELECT col, AVG(value)...
oracle分析函数 row_number() over
根据网上文章自己粘贴成一篇文档，赚几个分好下文档
分析函数(OVER) 分析函数2(Rank, Dense_rank, row_number) 分析函数3(Top/Bottom N、First/Last、NTile) 窗口函数 报表函数 分析函数总结 26个分析函数 PLSQL开发笔记和小结 分析函数简述