Oracle统计分析函数集,over(partition by..) 的运用（转）

yelr_j

浏览: 68174 次
性别:
来自: 成都

最近访客更多访客>>

cefei2010

liuzhiqiang19890403

fxj123

521冰521

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

数据库相关

Oracle SQL C C++C#

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)

sum(nid) over(partition by v1 order by nid) (2007-05-16 16:22:48)

分类：sql语句

SQL> select n1,v1,nid,sum(nid) over(order by nid) as sum
2 from t1;

        N1 V1                NID        SUM
---------- ---------- ---------- ----------
         1 aa                 61         61
         2 aa                 62        123
         3 aa                 63        186
         4 aa                 64        250

取nid列的累积和， 即下面以emp表为例的按部门“连续”求总和

==================================================================

按v1分组取nid的和

SQL> select v1,sum(nid) over (partition by v1 order by v1) as sum_nid from t1;

V1            SUM_NID
---------- ----------
aa                187
aa                187
aa                187
bb                 83

按v1分组取nid的和，并重复行只显示一行

SQL> select distinct * from (select v1,sum(nid) over (partition by v1) as sum_nid from t1);

V1            SUM_NID
---------- ----------
aa                187
bb                 83

==================================================================

再以emp为例

使用子分区查出各部门薪水连续的总和。注意按部门分区 over(...)条件的不同
sum(sal) over (partition by deptno order by ename) 按部门“连续”求总和
sum(sal) over (partition by deptno) 按部门求总和
sum(sal) over (order by deptno，ename) 不按部门“连续”求总和
sum(sal) over () 不按部门，求所有员工总和，效果等同于sum(sal)。

sql> break on deptno skip 1 -- 为效果更明显，把不同部门的数据隔段显示。

SQL> select deptno,ename,sal,
2      sum(sal) over (partition by deptno order by ename) 部门连续求和,
3      sum(sal) over (partition by deptno) 部门总和,
4      100*round(sal/sum(sal) over (partition by deptno),4) 部门份额,
5      sum(sal) over () 总和,
6      sum(sal) over (order by deptno,ename) 连续求和,
7      100*round(sal/sum(sal) over (),4) 总份额
8 from emp;

DEPTNO ENAME SAL 部门连续求和部门总和部门份额总和连续求和总份额

---------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- ----------

10 CLARK 2450 2450 8750 28 29025 2450 8.44

KING 5000 7450 8750 57.14 29025 7450 17.23

MILLER 1300 8750 8750 14.86 29025 8750 4.48

20 ADAMS 1100 1100 10875 10.11 29025 9850 3.79

FORD 3000 4100 10875 27.59 29025 12850 10.34

JONES 2975 7075 10875 27.36 29025 15825 10.25

SCOTT 3000 10075 10875 27.59 29025 18825 10.34

SMITH 800 10875 10875 7.36 29025 19625 2.76

30 ALLEN 1600 1600 9400 17.02 29025 21225 5.51

BLAKE 2850 4450 9400 30.32 29025 24075 9.82

JAMES 950 5400 9400 10.11 29025 25025 3.27

MARTIN 1250 6650 9400 13.3 29025 26275 4.31

TURNER 1500 8150 9400 15.96 29025 27775 5.17

WARD 1250 9400 9400 13.3 29025 29025 4.31

已选择14行。

综合的例子，求和规则有按部门分区的，有不分区的例子

SQL> select deptno,ename,sum(sal) over(partition by deptno order by sal) as sum_sal,
2 sum(sal) over(order by deptno,sal) as sum_dept_sal
3 from emp;

    DEPTNO ENAME         SUM_SAL SUM_DEPT_SAL
---------- ---------- ---------- ------------
        10 MILLER           1300         1300
           CLARK            3750         3750
           KING             8750         8750

        20 SMITH             800         9550
           ADAMS            1900        10650
           JONES            4875        13625
           SCOTT           10875        19625
           FORD            10875        19625

        30 JAMES             950        20575
           WARD             3450        23075
           MARTIN           3450        23075
           TURNER           4950        24575
           ALLEN            6550        26175
           BLAKE            9400        29025

已选择14行。

来一个逆序的，即部门从大到小排列，部门里各员工的薪水从高到低排列，累计和的规则不变。

SQL> select deptno,ename,sal,
2 sum(sal) over (partition by deptno order by deptno desc,sal desc) as sum_sal_order,
3 sum(sal) over (order by deptno desc,sal desc) as sum
4 from emp;

    DEPTNO ENAME             SAL SUM_SAL_ORDER        SUM
---------- ---------- ---------- ------------- ----------
        30 BLAKE            2850          2850       2850
           ALLEN            1600          4450       4450
           TURNER           1500          5950       5950
           WARD             1250          8450       8450
           MARTIN           1250          8450       8450
           JAMES             950          9400       9400

        20 SCOTT            3000          6000      15400
           FORD             3000          6000      15400
           JONES            2975          8975      18375
           ADAMS            1100         10075      19475
           SMITH             800         10875      20275

        10 KING             5000          5000      25275
           CLARK            2450          7450      27725
           MILLER           1300          8750      29025

已选择14行。

分享到：

Oracle创建删除用户、角色、表空间、导入导 ... | sql 学习

2010-08-19 17:01
浏览 1099
评论(0)
分类:数据库
查看更多

发表评论

您还没有登录,请您登录后再发表评论

最近访客更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论