`

over partition

阅读更多
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)
分享到:
评论
2 楼 datawarehouse 2012-06-29  
很好啊啊啊
1 楼 hezhou_0521 2010-09-19  
不错,实际应用性很强。

相关推荐

    Oracle查询中OVER (PARTITION BY ..)用法

    Oracle查询中的`OVER (PARTITION BY ..)`是一个窗口函数,它允许我们在数据集上执行计算,但不是在整个结果集上,而是针对每个分区。这部分功能非常强大,可以用于复杂的分析和排序任务,尤其是在处理分组数据时。在...

    SqlServer的over partition by开窗语句

    SqlServer的over partition by开窗语句

    Oracle中rank,over partition函数的使用方法

    在Oracle数据库中,`RANK()`, `OVER()`, `PARTITION BY` 是窗口函数的重要组成部分,它们在数据处理和分析中发挥着至关重要的作用。本文将深入探讨这些函数的用法,通过实例来帮助理解它们的功能和应用场景。 首先...

    row_number,根据多个字段过滤,partition by

    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY name || idNumber || TO_CHAR(date, 'YYYYMMDD') ORDER BY id) AS rn FROM teacher t ) subquery WHERE rn = 1; ``` - **PARTITION BY** 子句中的`name || idNumber...

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

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

    Mssql 增加排序列,分组排序与普通排序 ROW_NUMBER.docx

    `ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) AS Seq` - `PARTITION BY` 子句用于将数据集分割成多个分区,每个分区内的行具有相同的值。 - `ORDER BY` 子句则定义了在每个分区内部如何对行进行排序。 2...

    只满足某个条件的查询效率分析

    介绍了 1.NOT EXISTS, 2.GROUP BY ... [HAVING ...] 3.count(*) over(partition by ...) 做查询的效率,以及各自的使用方法

    SQLServer中Partition By及row_number 函数使用详解

    在 `OVER(PARTITION BY ... ORDER BY ...)` 中,`PARTITION BY` 定义了分区的依据,而 `ORDER BY` 规定了行号的排序规则。例如: ```sql SELECT *, ROW_NUMBER() OVER(PARTITION BY corp_name ORDER BY submit_time...

    partition by 使用说明

    ROW_NUMBER() OVER (PARTITION BY 列名 ORDER BY 列名) AS NUM FROM 表名; ``` 其中,`列名` 是要进行分区的列名,`表名` 是要查询的表名。 Partition By 函数的使用示例 --------------------------- 下面是...

    Oracle row_number()over

    row_number() over ([partition by col1] order by col2) as 别名 ``` 其中,`partition by col1` 用于指定分区的列,`order by col2` 用于指定排序的列。 在使用 row_number()over 函数时,需要注意以下几点: * ...

    A20_sys_partition.fexv1.3.zip_A20_partition_sys_partition.fex

    4. **更新策略**:定义了如何处理系统更新,如OTA(Over-The-Air)更新时的数据备份和恢复机制。 在v1.3版本中,可能包含了一些新特性或修复了之前版本的bug,以提升系统的稳定性和性能。这通常涉及到分区大小的...

    pl/sql及常见实用oracle语句学习

    7. **OVER PARTITION BY**:此窗口函数允许你在数据集上进行分组计算,如计算每个组的平均值、累计和等。例如: ``` SELECT column1, AVG(column2) OVER (PARTITION BY column3) FROM table_name; ``` 8. **书籍...

    postgresql 最新版本15.0源代码

    - **窗口函数增强**:PostgreSQL 15.0 引入了新的窗口函数,如 `OVER PARTITION BY ... ORDER BY ... ROWS BETWEEN`,使得复杂的数据分析更加便捷。 - **JSONB 函数扩展**:对 JSONB 数据类型的支持进一步加强,新增...

    Orcal中over函数.doc

    - **部门连续求和**:`SUM(sal) OVER (PARTITION BY deptno ORDER BY ename)`表示按照部门进行分组后,再按`ename`排序对薪资进行连续累加。 - **部门总和**:`SUM(sal) OVER (PARTITION BY deptno)`表示计算每个...

    over函数的使用

    例如,在一个包含多个部门员工薪资信息的表中,我们可能希望分别计算每个部门的最高薪资,这时就可以使用`over(partition by deptno)`来实现这一功能。 #### 示例代码解析 ```sql SELECT * FROM ( SELECT name, ...

    ORACLE_OVER函数

    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num FROM employees; ``` 这个查询将为每个部门的员工按工资降序排列,并为每个员工分配一个行号。 ##### 示例2:使用SUM()计算...

    oracle分析函数row_number() over()使用

    这里,`SUM(sal) OVER (PARTITION BY deptno)`计算每个部门的总薪水,而`SUM(sal) OVER (PARTITION BY deptno ORDER BY ename)`则在每个部门内按照员工姓名排序并累加薪水。 此外,我们还可以通过改变`ORDER BY`...

    RANK OVER()和ROW_NUMBER()OVER的使用

    介绍rank() over()分析函数和 row_number() over 的使用实例及其异同点

Global site tag (gtag.js) - Google Analytics