oracle的分析函数over 及开窗函数[转]

一：分析函数over
 Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是
对于每个组返回多行，而聚合函数对于每个组只返回一行。
下面通过几个例子来说明其应用。                                      

1：统计某商店的营业额。       

    date      sale
    1          20
    2          15
    3          14
    4          18
    5          30
   规则：按天统计：每天都统计前面几天的总额
   得到的结果：
   DATE  SALE      SUM
   ----- -------- ------
   1     20       20          --1天          
   2     15       35          --1天＋2天          
   3     14       49          --1天＋2天＋3天          
   4     18       67           .         
   5     30       97           .
    
2:统计各班成绩第一名的同学信息

   NAME  CLASS S                        
   ----- ----- ----------------------
   fda   1     80                    
   ffd   1     78                    
   dss   1     95                    
   cfe   2     74                    
   gds   2     92                    
   gf    3     99                    
   ddd   3     99                    
   adf   3     45                    
   asdf  3     55                    
   3dd   3     78             
  
   通过：  
   --
   select * from                                                                      
   (                                                                           
   select name,class,s,rank()over(partition by class order by s desc) mm from t2
   )                                                                           
   where mm=1
   --
   得到结果：
   NAME  CLASS S                      MM                                                                                       
   ----- ----- ---------------------- ----------------------
   dss   1     95                     1                     
   gds   2     92                     1                     
   gf    3     99                     1                     
   ddd   3     99                     1         
  
   注意：
   1.在求第一名成绩的时候，不能用row_number()，因为如果同班有两个并列第一，row_number()只返回一个结果         
   2.rank()和dense_rank()的区别是：
     --rank()是跳跃排序，有两个第二名时接下来就是第四名
     --dense_rank()l是连续排序，有两个第二名时仍然跟着第三名
    
    
3.分类统计 (并显示信息)

   A  B  C                     
   -- -- ----------------------
   m  a  2                     
   n  a  3                     
   m  a  2                     
   n  b  2                     
   n  b  1                     
   x  b  3                     
   x  b  2                     
   x  b  4                     
   h  b  3

  select a,c,sum(c)over(partition by a) from t2               
  得到结果：
  A  B  C       SUM(C)OVER(PARTITIONBYA)     
  -- -- ------- ------------------------
  h  b  3       3                       
  m  a  2       4                       
  m  a  2       4                       
  n  a  3       6                       
  n  b  2       6                       
  n  b  1       6                       
  x  b  3       9                       
  x  b  2       9                       
  x  b  4       9                       
 
  如果用sum，group by 则只能得到
  A  SUM(C)                           
  -- ----------------------
  h  3                     
  m  4                     
  n  6                     
  x  9                     
  无法得到B列值      
 
＝＝＝＝＝

二：开窗函数          
     开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：
1：    
   over（order by salary） 按照salary排序进行累计，order by是个默认的开窗函数
   over（partition by deptno）按照部门分区
2：
  over（order by salary range between 5 preceding and 5 following）
  每行对应的数据窗口是之前行幅度值不超过5，之后行幅度值不超过5
  例如:对于以下列
    aa
    1
    2
    2
    2
    3
    4
    5
    6
    7
    9
  
  sum(aa)over（order by aa range between 2 preceding and 2 following）
  得出的结果是
           AA                      SUM
           ---------------------- -------------------------------------------------------
           1                      10                                                     
           2                      14                                                     
           2                      14                                                     
           2                      14                                                     
           3                      18                                                     
           4                      18                                                     
           5                      22                                                     
           6                      18                                                               
           7                      22                                                               
           9                      9                                                                
            
  就是说，对于aa=5的一行 ，sum为  5-1<=aa<=5+2 的和
  对于aa=2来说 ，sum=1+2+2+2+3+4=14    ；
  又如 对于aa=9 ，9-1<=aa<=9+2 只有9一个数，所以sum=9   ；
             
3：其它：
     over（order by salary rows between 2 preceding and 4 following）
         每行对应的数据窗口是之前2行，之后4行
4：下面三条语句等效：          
     over（order by salary rows between unbounded preceding and unbounded following）
         每行对应的数据窗口是从第一行到最后一行，等效：
     over（order by salary range between unbounded preceding and unbounded following）
          等效
     over(partition by null)

oracle中over()开窗函数的理解

关键字: 分析函数 analytic query functions

开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：
over（order by salary） 按照salary排序进行累计，order by是个默认的开窗函数
over（partition by deptno）按照部门分区
over（order by salary range between 50 preceding and 150 following）
每行对应的数据窗口是之前行幅度值不超过50，之后行幅度值不超过150
over（order by salary rows between 50 preceding and 150 following）
每行对应的数据窗口是之前50行，之后150行
over（order by salary rows between unbounded preceding and unbounded following）
每行对应的数据窗口是从第一行到最后一行，等效：
over（order by salary range between unbounded preceding and unbounded following）

eg.

select * from
(
select p.deal_no,p.step_id,p.charge_id,p.dr_acc_no,
sum(p.dr_origin_amt)
over (partition by p.deal_no,p.step_id,p.dr_acc_no order by p.charge_id) as a,
dense_rank()
over (partition by p.deal_no,p.step_id,p.dr_acc_no order by p.charge_id) as b
  from ixqdbaci p
where p.entity='002' and p.charge_id not like '99%'
and p.deal_no like '00779010010612%'
) where b=1
sum中的over()加上order by后会变成递增累加，而不是分组的所有值sum，所以在这条sql中应该把a列的oder by p.charge_id删除，b列的dense_rank是为了区分同一组的不同行，并且order by是不可少的。

decode()

  1。decode()不仅仅针对固定值

    (case when (b.aggregateno is null or b.aggregateno='') then customerid else b.aggregateno end)
    也可以写成
    decode(aggregateno,null,customerid,'',customerid,aggregateno)
    decode里面放的是字段，而不是固定值，case when的语法实在难以写正确。而sql又没有发现IDE，程序员离开IDE还有点难办，不过做IDE的人却不挣钱，Borland都要卖掉他们的IDE 部门了,不过今天公司竟然收到了Borland的邮件，说我们有人使用非正版的Jbuilder,唉....竟然还有人喜欢用Jbuilder，不是说他不好，而是他的商业版本也太没性价比了。

    2。取前10条记录放入临时表的写法

    本来这是一个简单问题，用select first 10 * from table1 into temp temptable 就解决了，但是into temp table或者insert into 的时候都不能使用first，Image。这种设计不知道出于什么考虑

    /*先从所有记录中sum出总和，并且按中和排序放入临时表中，这样插入的记录rowid在非异常情况下是连续的，并且按sum值排序*/
    select T1.aggregateno aggregateno, sum(T1.balance) sumbalance
        from aggr_n T1
         group by T1.aggregateno
        order by 2 desc, 1 asc
        into temp temptop1
        /*取前10 的记录放入临时表，条件是rowid小于min(rowid)+10*/
    select aggregateno,sumbalance from temptop1
     where rowid < (select min(rowid)+10 from temptop1)
     order by 2 desc into temp temptop10