Oracle 语法之 OVER (PARTITION BY ..) 及开窗函数 转载alibaba - 凯发娱乐

Oracle 语法之 OVER (PARTITION BY ..) 及开窗函数 转载alibaba

2019-02-08 07:16:34 | 作者: 怜南 | 标签: 函数,开窗,成果 | 浏览: 937

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列值 
 
=====
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)
版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表凯发娱乐立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章

阅读排行

  • 1
  • 2
  • 3
  • 4

    union联合查询guojizaixian

    成果,数据,排序
  • 5

    TIME快报

    效劳,状况,修正
  • 6

    SQL试题环球

    用户,时刻,信息
  • 7
  • 8
  • 9

    asdfasdfITeye头条

    用户,权限,指令
  • 10

    SQL一招ITeyesohu

    字符串,标题,检索