向[hebo2005]提问：关于rank() row_number() 以及 over()等后面连接 partition by... order by...

首先看到有over 的就知道是分析函数
分析函数用法，以前写了点，你先看一下五、分析函数
Oracle提供一个强大的函数系列――分析函数，上面写到的row_number()  over()就是其中之一，特征就在于over()
Over()里面三类参数
Partition  by  相当于group by 后面跟相应的字段名
Order by  排序条件
Rows between 窗口函数主要用来计算一定的记录范围内、一定的值域内、或一段时间内的累积和及移动平均值等。之所以叫“窗口”因为处理结果中使用了一个滑动的查询结果集范围。
一般常用的参数就是partition by 和order by
Over() 有些时候里面可以不写参数，默认就是对于所有数据，例如sum(字段)　over()　就是对于所有纪录范围求和，而有些时候order by是必须的，如row_number() over() 就是的分析函数的优点在于，不受语句里的group by 的限制(注,经研究,SUM,AVG等的结果还是受GROUP影响) 这里顺便说几个关于SUM的
sum(字段) 这样写,除非是求所有纪录的某字段的和,并且select只显示sum字段,否则需要和group一起用 sum(字段) over(),也是对所有纪录集范围内字段求和,不需要GROUP,可以和其它字段同行显示比如说你可以写这样的 SQL codeselect a.*,sum(b) over()
      from asum(字段)over(partition by 分组字段)按分组字段求和,不需要group by sum(字段1) over (order by 字段2) 按排序顺序求和到当前行比如表a
字段1  字段2
1      1
1      2
2      3
2      4
4      5
5      6
用 SQL codeselect a.*
sum(字段1) over (order by 字段2)  c
from a出来结果
字段1  字段2  c
1      1      1
1      2      2
2      3      4
2      4      6
4      5      10
5      6      15
大家知道写group 　by时，select里的字段除聚合函数（如sum,avg,count之类）外，其余都要和group by 里对应，也不能写*
比如说上面row_number() over() 里的例子，要求知道每天最后张订单的所有信息，传统的作法就需要按订单日期每天分组，然后找最大的订单时间，然后再和原表关联查询，也就是说要对同张表，做两次扫描查询
而用row_number() over(partition by trunc(order_date) order by order_date desc)一次可以产生分组编号，最后只要取结果为1的就行了常用的分析函数
Row_number() over 按分组按排序编号
Rank()      over 按分组按排序编号，有并列的，编号相同，并跳过相同数目下几个编号
Dense_rank()  over按分组按排序编号，有并列的，编号相同，不跳号
Lag(字段名，偏移量，默认值) over 按分组排序，取当前纪录字段的往上+偏移量条纪录的同一字段的值，取不到的话，显示默认值
lead(字段名，偏移量，默认值) over 基本同上，差别在于往后取纪录
first_value(字段) over(　窗口函数)　取窗口函数范围内的第一条纪录，需要写开窗函数
last_value 同上，只不过是取结果集中最后条纪录
ratio_to_report(字段) over() 字段所占 over结果集的百分比，像统计当天各地区销量的占比，传统做法要写两个语句关联，一个统计各地区的的销量和，一个统计所有销量和，然后再关联计算各地方的百分比，而用这个分析函数，一条语句就够了，如 SQL codeSELECT   br_area_gb, SUM (syslast) qty,
         ratio_to_report (SUM (syslast)) OVER () ratio
    FROM torderdetail a
   WHERE syslast > 0
GROUP BY br_area_gb
http://topic.csdn.net/u/20080627/20/cee43126-c167-489a-86c9-a3d140daacc8.html

假如一个表EMP,有
员工号,工资,部门号
EMPID  SALARY  DEPID
001    100   1
002    200   1
003    200   1
004    150   1
005    100   2
006    200   2
007    120   2rank() over (order by ...)和row_number() over (order by ...) 都是加上一个伪列，内容都是排序后的序号。区别:RANK排序后可以是相同的序号，row_number()一定是不同的序号SQL> SELECT EMP.*, rank() over (order by SALARY)
  2  FROM EMP;     EMPID     SALARY      DEPID RANK()OVER(ORDERBYSALARY)
---------- ---------- ---------- -------------------------
         1        100          1                         1
         5        100          2                         1
         7        120          2                         3
         4        150          1                         4
         2        200          1                         5
         6        200          2                         5
         3        200          1                         5已选择7行。SQL> SELECT EMP.*, ROW_NUMBER() over (order by SALARY)
  2  FROM EMP;     EMPID     SALARY      DEPID ROW_NUMBER()OVER(ORDERBYSALARY)
---------- ---------- ---------- -------------------------------
         1        100          1                               1
         5        100          2                               2
         7        120          2                               3
         4        150          1                               4
         2        200          1                               5
         6        200          2                               6
         3        200          1                               7已选择7行。上面的排序都是对表全部内容排序,
如果使用partition by,就是分部分排序,partition by DEPID就是分部门排序SQL> SELECT EMP.*, RANK() over (PARTITION BY DEPID order by SALARY)
  2  FROM EMP;     EMPID     SALARY      DEPID RANK()OVER(PARTITIONBYDEPIDORDERBYSALARY)
---------- ---------- ---------- -----------------------------------------
         1        100          1                                         1
         4        150          1                                         2
         3        200          1                                         3
         2        200          1                                         3
         5        100          2                                         1
         7        120          2                                         2
         6        200          2                                         3已选择7行。
SQL> SELECT EMP.*, ROW_NUMBER() over (PARTITION BY DEPID order by SALARY)
  2  FROM EMP;     EMPID     SALARY      DEPID ROW_NUMBER()OVER(PARTITIONBYDEPIDORDERBYSALARY)
---------- ---------- ---------- -----------------------------------------------
         1        100          1                                               1
         4        150          1                                               2
         3        200          1                                               3
         2        200          1                                               4
         5        100          2                                               1
         7        120          2                                               2
         6        200          2                                               3已选择7行。

不同的函数，over后面不同的partition 或order
具体参加：
http://blog.csdn.net/suncrafted/archive/2008/07/29/2731419.aspx

rank()、row_number()是两个不同的函数
over()里面可以有Partition by 和order by
不知道楼主想问啥

good

从室友那里获取的oracle分析函数：
oracle分析函数--SQL*PLUS环境
--1、GROUP BY子句 --CREATE TEST TABLE AND INSERT TEST DATA.
create table students
(id number(15,0),
area varchar2(10),
stu_type varchar2(2),
score number(20,2));insert into students values(1, '111', 'g', 80 );
insert into students values(1, '111', 'j', 80 );
insert into students values(1, '222', 'g', 89 );
insert into students values(1, '222', 'g', 68 );
insert into students values(2, '111', 'g', 80 );
insert into students values(2, '111', 'j', 70 );
insert into students values(2, '222', 'g', 60 );
insert into students values(2, '222', 'j', 65 );
insert into students values(3, '111', 'g', 75 );
insert into students values(3, '111', 'j', 58 );
insert into students values(3, '222', 'g', 58 );
insert into students values(3, '222', 'j', 90 );
insert into students values(4, '111', 'g', 89 );
insert into students values(4, '111', 'j', 90 );
insert into students values(4, '222', 'g', 90 );
insert into students values(4, '222', 'j', 89 );
commit;col score format 999999999999.99--A、GROUPING SETSselect id,area,stu_type,sum(score) score
from students
group by grouping sets((id,area,stu_type),(id,area),id)
order by id,area,stu_type;/*--------理解grouping sets
select a, b, c, sum( d ) from t
group by grouping sets ( a, b, c )等效于select * from (
select a, null, null, sum( d ) from t group by a
union all
select null, b, null, sum( d ) from t group by b
union all
select null, null, c, sum( d ) from t group by c
)
*/--B、ROLLUPselect id,area,stu_type,sum(score) score
from students
group by rollup(id,area,stu_type)
order by id,area,stu_type;/*--------理解rollup
select a, b, c, sum( d )
from t
group by rollup(a, b, c);等效于select * from (
select a, b, c, sum( d ) from t group by a, b, c
union all
select a, b, null, sum( d ) from t group by a, b
union all
select a, null, null, sum( d ) from t group by a
union all
select null, null, null, sum( d ) from t
)
*/--C、CUBEselect id,area,stu_type,sum(score) score
from students
group by cube(id,area,stu_type)
order by id,area,stu_type;/*--------理解cube
select a, b, c, sum( d ) from t
group by cube( a, b, c)等效于select a, b, c, sum( d ) from t
group by grouping sets(
( a, b, c ),
( a, b ), ( a ), ( b, c ),
( b ), ( a, c ), ( c ),
() )
*/--D、GROUPING/*从上面的结果中我们很容易发现,每个统计数据所对应的行都会出现null,
如何来区分到底是根据那个字段做的汇总呢,grouping函数判断是否合计列!*/select decode(grouping(id),1,'all id',id) id,
decode(grouping(area),1,'all area',to_char(area)) area,
decode(grouping(stu_type),1,'all_stu_type',stu_type) stu_type,
sum(score) score
from students
group by cube(id,area,stu_type)
order by id,area,stu_type; --2、OVER()函数的使用
--1、RANK()、DENSE_RANK() 的、ROW_NUMBER()、CUME_DIST()、MAX()、AVG()break on id skip 1
select id,area,score from students order by id,area,score desc;select id,rank() over(partition by id order by score desc) rk,score from students;--允许并列名次、名次不间断
select id,dense_rank() over(partition by id order by score desc) rk,score from students;--即使SCORE相同，ROW_NUMBER()结果也是不同
select id,row_number() over(partition by ID order by SCORE desc) rn,score from students;select cume_dist() over(order by id) a, --该组最大row_number/所有记录row_number
row_number() over (order by id) rn,id,area,score from students;select id,max(score) over(partition by id order by score desc) as mx,score from students;select id,area,avg(score) over(partition by id order by area) as avg,score from students; --注意有无order by的区别--按照ID求AVG
select id,avg(score) over(partition by id order by score desc rows between unbounded preceding
and unbounded following ) as ag,score from students;
--2、SUM()select id,area,score from students order by id,area,score desc;select id,area,score,
sum(score) over (order by id,area) 连续求和, --按照OVER后边内容汇总求和
sum(score) over () 总和, -- 此处sum(score) over () 等同于sum(score)
100*round(score/sum(score) over (),4) "份额(%)"
from students;select id,area,score,
sum(score) over (partition by id order by area ) 连id续求和, --按照id内容汇总求和
sum(score) over (partition by id) id总和, --各id的分数总和
100*round(score/sum(score) over (partition by id),4) "id份额(%)",
sum(score) over () 总和, -- 此处sum(score) over () 等同于sum(score)
100*round(score/sum(score) over (),4) "份额(%)"
from students;--4、LAG(COL,n,default)、LEAD(OL,n,default) --取前后边N条数据select id,lag(score,1,0) over(order by id) lg,score from students;select id,lead(score,1,0) over(order by id) lg,score from students;--5、FIRST_VALUE()、LAST_VALUE()select id,first_value(score) over(order by id) fv,score from students;select id,last_value(score) over(order by id) fv,score from students;

调试易

向[hebo2005]提问：关于rank() row_number() 以及 over()等后面连接 partition by... order by...

解决方案 »