`
sun201200204
  • 浏览: 298826 次
  • 性别: Icon_minigender_1
  • 来自: 深圳
社区版块
存档分类
最新评论

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



求个人工资占部门工资的百分比
 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)
分享到:
评论

相关推荐

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

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

    oracle开窗函数学习技巧总结

    `OVER`函数是Oracle开窗函数的核心组成部分,它定义了一个计算上下文或窗口,在这个窗口内可以执行各种类型的聚合计算。`OVER`函数的基本语法如下: ``` function_name(...) OVER (window_specification) ``` 其中...

    oracle分析函数及开窗函数

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

    Oracle开发的over函数

    ### Oracle开发中的OVER函数详解 #### 一、Oracle分析函数简介 在Oracle数据库开发中,分析函数是一类非常强大的工具,主要用于实现复杂的查询需求,尤其是在处理大量数据时,能够提供高级的数据汇总、排序和筛选...

    ORACLE_OVER函数

    #### 二、OVER函数的基本语法及概念 OVER函数通常与窗口函数(如RANK(), DENSE_RANK(), ROW_NUMBER(), SUM(), AVG()等)一起使用。其基本语法形式如下: ```sql &lt;window_function&gt; OVER ( [window_spec] ) ``` ...

    oracle 9i 分析函数参考手册.rar

    PARTITION BY用于将数据分割成独立的组,而ORDER BY则指定在每个组内如何对行进行排序。 主要的Oracle 9i 分析函数包括: 1. RANK():根据指定的排序标准对行进行排名,返回一个唯一的整数值。 2. DENSE_RANK():...

    Oracle10gR2分析函数.pdf

    #### 三、常见分析函数及用法 1. **累计函数** - **SUM()**:计算累积总和。 - **COUNT()**:计算累积计数。 - **MIN()** 和 **MAX()**:计算最小值和最大值。 2. **移动平均函数** - **AVG()**:计算在指定...

    ORACLE OLAP函数语法的总结

    ### ORACLE OLAP函数语法详解 #### 一、概述 Oracle OLAP(Online Analytical Processing)函数主要用于执行复杂的分析计算,如聚合、排名、趋势分析等。这些函数可以帮助用户更高效地处理大型数据集,特别是在数据...

    Oracle row_number()over

    row_number()over 函数的基本语法为: ``` row_number() over ([partition by col1] order by col2) as 别名 ``` 其中,`partition by col1` 用于指定分区的列,`order by col2` 用于指定排序的列。 在使用 row_...

    Orcal中over函数.doc

    ### Oracle中OVER函数详解 #### 一、概述 在Oracle数据库中,OVER函数是一个非常强大的工具,主要用于实现复杂的窗口函数操作。它可以帮助我们对数据进行分组、排序、累计等多种处理,大大增强了SQL语言的数据分析...

    oracle统计函数.pdf

    其基本语法为row_number() over ([partition by column_name] order by column_name)。在这个语法中,partition by是可选的,用于将数据集分为几个部分,然后对每个部分进行排序和编号;order by是必须的,用于指定...

    SQL开窗函数.zipSQL开窗函数.zipSQL开窗函数.zip

    SQL开窗函数是数据库查询中的一个强大工具,它允许在单个SQL语句中进行复杂的分析操作,如计算累计和、排名、移动平均等。在SQL中,窗口(或称为分区)是根据某些条件对数据集进行分组的一段连续记录。通过使用窗口...

    Oracle分析函数

    FUNCTION_NAME(&lt;参数&gt;,…) OVER (&lt;PARTITION BY 表达式,…&gt; &lt;ORDER BY 表达式 &lt;ASC DESC&gt; &lt;NULLS FIRST NULLS LAST&gt;&gt; 子句&gt;) 其中,PARTITION BY 子句用于将数据分区,ORDER BY 子句用于排序数据,而 WINDOWING ...

    oracle分析函数.doc

    #### 三、分析函数基本语法及特点 分析函数的基本语法如下: ```sql function_name(arg1, arg2, ...) OVER (&lt;partition-clause&gt;&lt;order-by-clause&gt;) ``` - **`function_name`**:指定使用的分析函数名称。 - **`...

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

    Oracle的分析函数row_number() over()是数据库查询中一种强大的工具,它用于对数据集进行分组和排序,尤其在处理重复数据或者实现排名时非常有用。在Oracle SQL中,row_number()函数会为每一行生成一个唯一的数字,...

    Oracle分析函数.pdf

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

    Oracle分析函数基本概念和语法总结及Regexp_***用法

    在Oracle中,分析函数的语法结构为: ``` analytic_function([arguments]) OVER ([partition_clause] [order_by_clause] [windowing_clause]) ``` 1. `analytic_function`:这是实际的分析函数,例如AVG, COUNT, ...

    oracle行转列聚合函数WMSYS.WM_CONCAT

    SELECT WMSYS.WM_CONCAT(column_name) OVER (PARTITION BY partition_column) FROM table_name; ``` **参数说明:** - `value1, value2, ..., valueN`:需要连接的字符串值。 - `column_name`:表中的某一列名,...

    Oracle中的分析函数详解

    Oracle数据库系统是世界上最...在实际工作中,结合PARTITION BY和OVER子句的使用,分析函数可以解决许多复杂的业务问题,提升数据处理能力。因此,对分析函数的深入理解是成为高级Oracle数据库开发人员的关键技能之一。

Global site tag (gtag.js) - Google Analytics