分析函数：first_value,last_value用法

分析函数：first_value,last_value用法  

2009-07-03 11:09:38|  分类： 技术类|举报|字号 订阅

    

  下载LOFTER我的照片书  |

原始数据：
SQL>select * from a;
NO      NAME SCOTT 
-- ---------- ---------- 
1         LIFEN         86     
2         HONGGE       76      
3         AIKAI         90       
4         LIFEN         73        
5         KUJUHD       69        
6         LIFEN         93        
7         KOU        80  
8        HONGGE       83        
......................
select T.*, first_value(scoot) over (partition by name order by scott) F,last_value(scoot) over (partition by name order by scott) L from a T order by no ;
NO      NAME SCOTT F  L
-- ---------- ----------   -----   ----- 
1         LIFEN         86   73       86   
2         HONGGE       76   76   76
3         AIKAI         90       90  90
4         LIFEN         73        73  73
5         KUJUHD       69       69   69
6         LIFEN         93        73  93
7         KOU        80      80  80
8        HONGGE       83    76   83
可以看出 能得到姓名相同中的最小分数 却不能得到最大分数 也就是last_value這函數里存在order和partition會得不到預期的結果﹐
可以將order by 放在最后面
找出最高分数 （如果有重复姓名）和最低分数的NO
SQL>SELECT T.*,
  2  first_value(scott) OVER (PARTITION BY NAME) lowest_scott,
  3  last_value(scott) OVER (PARTITION BY NAME) highest_scott
  4  FROM a T
  5  ORDER BY scott;

第二种方法 就是
select T.*, first_value(scoot) over (partition by name order by scott) F,
last_value(scoot) over (partition by name order by scott rows between unbounded preceding and unbounded following)  L from a T order by no ;
ps:分析函数分析的时候是在一个分析窗口中进行的，默认的窗口是当前组的第一行到当前行，如果让窗口是整个组，需要指定窗口子句

删除姓名重复的 分数最高的 保留下来
SQL>delete from a where no not in (select first_value(no) over (partition by name order by scott desc ) no  from a );