`

oracle的分析函数over (partition by)及开窗函数

阅读更多
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




求个人工资占部门工资的百分比


二:开窗函数          
      开窗函数指定了分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变化而变化,举例如下:
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)
分享到:
评论

相关推荐

    oracle分析函数over_及开窗函数.txt

    ### Oracle分析函数OVER及开窗函数详解 #### 一、概述 在Oracle数据库中,分析函数(Analytic Functions)是一种非常强大的工具,用于处理复杂的查询需求。这些函数可以在一组相关的行上执行计算,并且每行返回一个...

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

    ### Oracle 语法之 OVER (PARTITION BY ..) 及开窗函数详解 #### 一、OVER (PARTITION BY ..) 概述 在Oracle数据库中,`OVER (PARTITION BY ...)` 是一种非常强大的功能,它允许用户在数据集上进行窗口操作。这在...

    oracle分析函数及开窗函数

    ### Oracle分析函数及开窗函数详解 #### 一、Oracle分析函数概述 Oracle自8.1.6版本开始引入了分析函数,这类函数主要用于计算基于组的聚合值,并且与传统的聚合函数不同的是,分析函数可以针对每个组返回多行结果...

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

    ### Oracle的分析函数OVER及开窗函数 #### 一、分析函数OVER ##### 1. 概念介绍 从Oracle 8.1.6版本开始,Oracle引入了分析函数,这些函数可以对分组的数据执行复杂的操作,如计算累积总和、排名等。与聚合函数...

    ORACLE分析函数教程

    3. **制表(Reporting)函数**:与开窗函数同名,但作用于整个分区或结果集中的所有行,不依赖于特定的顺序。常见的制表函数有`SUM()`, `COUNT()`, `AVG()`, `MIN()`, `MAX()`等。 ```sql -- 示例:计算所有员工的...

    oracle开窗函数学习技巧总结

    在Oracle数据库中,开窗函数是一种非常强大的功能,它能够帮助我们对数据进行更复杂的分析和处理。开窗函数允许我们在查询结果集的一个窗口内执行聚合操作,而无需对数据进行物理排序或分组。本文将重点介绍`OVER`...

    oracle分析函数文档

    ### Oracle分析函数详解 #### 一、概述 Oracle分析函数是一种强大的工具,它允许用户对分组数据执行复杂的计算,并且结果可以根据特定条件进行动态调整。这种灵活性使得Oracle分析函数在处理复杂的数据集时非常...

    oracle分析函数大全

    1. **`OVER (ORDER BY salary)`**:此函数按薪资排序进行累计计算,其中`ORDER BY`可以被视为一种默认的开窗函数。 2. **`OVER (PARTITION BY deptno)`**:根据部门进行分组(分区),即在每个部门内部执行相应的...

    Oracle开发的over函数

    - **制表(reporting)函数**:与开窗函数同名,但作用于一个分区或一组上的所有列,不依赖于排序。 - **LAG、LEAD函数**:这类函数允许在结果集中向前或向后检索值,避免数据的自连接。 - **VAR_POP、VAR_SAMP、...

    oracle分析函数参考手册

    ### Oracle分析函数详解 #### 一、概述 Oracle分析函数是一种强大的工具,它允许用户对分组数据执行复杂的计算,并且能够返回多个结果行。这与传统的聚合函数(如`SUM`、`COUNT`等)形成鲜明对比,后者通常只针对...

    Oracle分析函数.pdf

    3. 制表(Reporting)函数:作用于一个分区或一组上的所有列,主要用于报告目的,与开窗函数类似,但`OVER`子句中缺少`ORDER BY`子句。 4. LAG, LEAD函数:允许在结果集中向前或向后检索数据值,常用于在没有自连接...

    ORACLE常用分析函数说明

    开窗函数是分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变化而变化。开窗函数可以指定数据窗口的大小和范围,例如按照 salary 排序进行累计、按照部门分区等。 下面是 Oracle 中一些常用的分析函数...

    oracle 分析函数

    #### 四、Oracle分析函数分类及示例 根据功能的不同,Oracle分析函数可以分为以下几类: 1. **等级(Ranking)函数**:用于确定数据在特定集合中的位置,如`RANK()`、`DENSE_RANK()`、`ROW_NUMBER()`等。 - 示例...

    oracle分析函数的用法

    Oracle分析函数是数据库查询中的强大工具,它允许用户在数据集上执行复杂的分析操作,而不仅仅是简单的聚合。分析函数的特点在于它们能够对数据进行排序、筛选,并且可以在不同的分区、窗口和排序规则下运行,这使得...

    Oracle 分析函数

    - **报表(Reporting)函数**: 与开窗函数类似名称,但作用于整个分区或一组数据,而不是一个特定的窗口。例如,`SUM`或`COUNT`函数不指定`ORDER BY`子句时即为报表函数。 - **LAG`, `LEAD`函数**: 这些函数允许向前...

    oracle 常用函数下载

    以下是一些常见的Oracle分析函数及其用法: 1. **开窗函数(Over)**: 开窗函数允许在一组行上执行计算,这组行被称为“窗口”,窗口可以基于特定的排序或分区条件。例如: - `OVER (ORDER BY salary)` 按薪资排序...

    ORACLE分析函数

    2. **按区域查找上一年度订单总额占区域订单总额20%以上的客户**:使用`SUM`开窗函数结合`OVER`子句和条件筛选完成。 3. **查找上一年度销售最差的部门所在的区域**:通过`MIN`或`RANK`函数结合`OVER`子句找到最低...

    oracle分析函数

    ### Oracle分析函数详解 #### 一、Oracle分析函数概述 Oracle分析函数是在Oracle数据库中用于处理复杂的查询需求的一组强大工具。与传统的SQL聚合函数(如`SUM`, `AVG`, `COUNT`等)不同,分析函数能够在同一查询...

    Oracle 9i 分析函数参考手册

    开窗函数主要包括 `OVER` 子句以及其中的 `PARTITION BY` 和 `ORDER BY` 子句。 #### `OVER` 子句 `OVER` 子句用于定义一个分析函数的操作窗口,它可以包含 `PARTITION BY` 和/或 `ORDER BY` 子句。 ##### `...

Global site tag (gtag.js) - Google Analytics