oracle分析函数之over() （转）

oracle分析函数之over()

 

 1、统计名次

dense_rank() : 允许并列名次，名次不间断

dense_rank() over(patition by id order by score)   --按id分组对score排名

dense_rank() over(order by score) --不分组对score排名

 

 select id,area,stu_type,score,dense_rank() over(partition by id order by score) from students;  /*按id分组对score排名*/

 

ID AREA       ST      SCORE DENSE_RANK()OVER(PARTITIONBYIDORDERBYSCORE)
---------- ---------- -- ---------- -------------------------------------------
   1 222              j       68                               1
   1 111             g      80                               2
   1 111              j      80                               2
   1 222             g      89                               3
   2 222             g      60                               1
   2 222              j      65                               2
   2 111              j      70                               3
   2 111             g      80                               4
   3 111              j      60                               1
   3 111             g      75                               2
   3 222             g      80                               3

  ID AREA       ST      SCORE DENSE_RANK()OVER(PARTITIONBYIDORDERBYSCORE)
---------- ---------- -- ---------- -------------------------------------------
   3 222              j      89                               4
   4 111              g      89                               1
   4 222              j      89                               1
   4 111              j      90                               2
   4 222              g      90                               2

 

 

row_number()  ：不允许并列名次，相同名次不重复

select id,area,stu_type,score,  row_number()over(partition by id order by score) ranking  from students;

  ID AREA       ST      SCORE    RANKING
---------- ---------- -- ---------- ----------
   1 222              j      68      1
   1 111              g      80      2
   1 111              j      80      3
   1 222              g      89      4
   2 222              g      60      1
   2 222              j      65      2
   2 111              j      70      3
   2 111              g      80      4
   3 111              j      60      1
   3 111              g      75      2
   3 222              g      80      3

  ID AREA       ST      SCORE    RANKING
---------- ---------- -- ---------- ----------
   3 222              j      89      4
   4 111              g      89      1
   4 222              j      89      2
   4 111              j      90      3
   4 222              g      90      4

 

rank():允许并列名次，名次根据重复情况自动空缺

select id,area,stu_type,score,rank() over(partition by id order by score) ranking from students;

 

cume_dist():   最大排名/总个数

 

利用cume_dist()，允许并列名次、名次自动空缺，取并列后较大名次

 

 2、分组统计－－sum(),max(),avg(),RATIO_TO_REPORT()

select id,area,sum(1) over() as 总记录数,

 sum(1) over(partition by id) as 分组记录数,

sum(score) over() as 总计 ,

 sum(score) over(partition by id) as 分组求和,

sum(score) over(order by id) as 分组连续求和,

sum(score) over(partition by id,area) as 分组ID和area求和,

sum(score) over(partition by id order by area) as 分组ID并连续按area求和,

max(score) over() as 最大值,

max(score) over(partition by id) as 分组最大值,

max(score) over(order by id) as 分组连续最大值,

max(score) over(partition by id,area) as 分组ID和area求最大值,

max(score) over(partition by id order by area) as 分组ID并连续按area求最大值,

avg(score) over() as 所有平均,

avg(score) over(partition by id) as 分组平均,

avg(score) over(order by id) as 分组连续平均,

avg(score) over(partition by id,area) as 分组ID和area平均,

avg(score) over(partition by id order by area) as 分组ID并连续按area平均,

RATIO_TO_REPORT(score) over() as "占所有%",

RATIO_TO_REPORT(score) over(partition by id) as "占分组%",

score from students;

 

3、取前后记录值

lag(score,n,x) over(order by id)：取前面记录的值

lead(score,n,x) over(order by id)：取后面记录的值

 参数：n表示移动N条记录，X表示不存在时填充值，id表示排序列

 

 4、取第一条记录和最后一条记录的值

FIRST_VALUE()：取第起始1行值

LAST_VALUE()：取第最后1行值：

first_value(score,n) over(order by id)

last_value(score,n) over(order by id)

 

源地址： http://blog.csdn.net/huangcunyin/article/details/7007060