欢迎来到Doc100.Net免费学习资源知识分享平台!
您的位置:首页 > 程序异常 >

oracle sql特点使用

更新时间: 2014-01-05 02:07:16 责任编辑: Author_N1

 

oracle SQL特性使用
oracle分析函数
统计记录中类似1/222这样的记录
select rownum||'/'||count(1) over(partition by 1)  from user_tables

通过以上一个小例子,学习一下开窗函数,如下:
oracle的分析函数over 及开窗函数,网上有些不正确,本人修正了

eg:  相关解析:
表t_pi_part
字段  id  code   name
value 1  222     a
value 2  222     b
value 3 333     c
给code相同的part code 添加行标,根据id 排序

select p.* ,row_number()over(partition by p.code order by a.id desc) as row_index from t_pi_part p;

一:分析函数over
Oracle从8.1.6开始提供分析函数,分析函数用于计算基于组的某种聚合值,它和聚合函数的不同之处是
对于每个组返回多行,而聚合函数对于每个组只返回一行。
下面通过几个例子来说明其应用。                                      
1:统计某商店的营业额。       
     date       sale
     1           20
     2           15
     3           14
     4           18
     5           30
    规则:按天统计:每天都统计前面几天的总额
   select p.*,sum(sale) over (order by "date") from sale_test p;
   
    得到的结果:
    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()是连续排序,有两个第二名时仍然跟着第三名
    
    
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列值      
 
=====

select * from test

数据:
A B C
1 1 1
1 2 2
1 3 3
2 2 5
3 4 6


---将B栏位值相同的对应的C 栏位值加总
select a,b,c, SUM(C) OVER (PARTITION BY B) C_Sum
from test

A B C C_SUM
1 1 1 1
1 2 2 7
2 2 5 7
1 3 3 3
3 4 6 6



---如果不需要已某个栏位的值分割,那就要用 null

eg: 就是将C的栏位值summary 放在每行后面

select a,b,c, SUM(C) OVER (PARTITION BY null) C_Sum
from test

A B C C_SUM
1 1 1 17
1 2 2 17
1 3 3 17
2 2 5 17
3 4 6 17

求个人工资占部门工资的百分比

SQL> select * from salary;

NAME DEPT SAL
---------- ---- -----
a 10 2000
b 10 3000
c 10 5000
d 20 4000

SQL> select name,dept,sal,sal*100/sum(sal) over(partition by dept) percent from salary;

NAME DEPT SAL PERCENT
---------- ---- ----- ----------
a 10 2000 20
b 10 3000 30
c 10 5000 50
d 20 4000 100
二:开窗函数          
      开窗函数指定了分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变化而变化,举例如下:
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)
上一篇:上一篇
下一篇:下一篇

 

随机推荐程序问答结果

 

 

如对文章有任何疑问请提交到问题反馈,或者您对内容不满意,请您反馈给我们DOC100.NET论坛发贴求解。
DOC100.NET资源网,机器学习分类整理更新日期::2014-01-05 02:07:16
如需转载,请注明文章出处和来源网址:http://www.doc100.net/bugs/t/5848/
本文WWW.DOC100.NET DOC100.NET版权所有。