超级SQL——在SQL中累加

最新推荐文章于 2024-08-25 17:39:15 发布

l0979365428

最新推荐文章于 2024-08-25 17:39:15 发布

阅读量1k

点赞数

分类专栏：学习心得文章标签： sql class c oracle date null

学习心得专栏收录该内容

18 篇文章 0 订阅

订阅专栏

此为转贴，但是从连个帖子中收集而来

下面来一起看看论坛里的一个oracle方面的问题：

====================Question =========================

jmbdat   dayt   y              mon
27-9月-07    2033.2      2007 200709
28-9月-07    2750.28    2007     200709
29-9月-07    2885.68    2007     200709
30-9月-07    2556.68    2007     200709
01-10月-07 2903.04    2007     200710
02-10月-07 1002.96     2007     200710
03-10月-07 1038.24     2007     200710

对上边的表用sql处理下变成下面的

jmbdat           dayt      y       mon       mont
27-9月-07     2033.2   2007 200709    2033.2
28-9月-07     2750.28 2007 200709    4783.28
29-9月-07     2885.68 2007 200709    7669.16
30-9月-07     2556.68 2007 200709    20225.84
01-10月-07   2903.04 2007 200710    2903.04
02-10月-07   1002.96 2007 200710    3906
03-10月-07   1038.24 2007 200710    4944.24

该怎么做啊？

==================Solution =========================

SQL :

select tt. * ,
       sum (tt.dayt) over (partition by tt.mon order by tt.jmbdat,tt.y,tt.mon) as sum_dayt
from tablename tt;

Result :

JMBDAT            DAYT         Y      MON      SUM_DAYT
----------- ---------- ---------- ------ ----------
2007-9-27       2033.2       2007 200709     2033.2
2007-9-28      2750.28       2007 200709    4783.48
2007-9-29      2885.68       2007 200709    7669.16
2007-9-30      2556.68       2007 200709   10225.84
2007-10-1      2903.04       2007 200710    2903.04
2007-10-2      1002.96       2007 200710       3906
2007-10-7      1038.24       2007 200710    4944.24

7 rows selected

一：分析函数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)