Oracle over(partition by）用法

1.开窗函数

• 开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：
  • over（order by salary） 按照salary排序进行累计，order by是个默认的开窗函数
  • over（partition by deptno）按照部门分区

2.row_number()获取行号

• 表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 order by a.id desc) as row_index from t_pi_part p;
    

  • 执行结果
    value 1 222 a 1
    value 2 222 b 2
    value 3 333 c 3

3.rank()获取分组级别(级数)

• 统计各班成绩第一名的同学信息

  • 班级信息
    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是连续排序，有两个第二名时仍然跟着第三名

4.SUM()累计计数

• 将B栏位值相同的对应的C 栏位值加总

  • 表结构:
    A B C
    1 1 1
    1 2 2
    1 3 3
    2 2 5
    3 4 6

  • 通过

    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

  • 通过

    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

5.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 ；
• 其它：
  • over（order by salary rows between 2 preceding and 4 following）
    • 每行对应的数据窗口是之前2行，之后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)

6.ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW窗口区间累加

• 统计某商店的营业额。
  aa sale
  1 20
  2 15
  3 14
  4 18
  5 30
  • 规则：按天统计：每天都统计前面几天的总额
  • 通过

  sum(sale) OVER
  (ORDER BY aa ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
  AS SUM
  

  • 得到的结果：
    DATE SALE SUM
    1 20 20 --1天
    2 15 35 --1天＋2天
    3 14 49 --1天＋2天＋3天
    4 18 67 .
    5 30 97 .