oracle的分析函数over 及开窗函数

最新推荐文章于 2022-10-04 12:12:20 发布

kj110

最新推荐文章于 2022-10-04 12:12:20 发布

阅读量311

点赞数

分类专栏：数据库相关文章标签： oracle class date c null 工作

数据库相关专栏收录该内容

10 篇文章 0 订阅

订阅专栏

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