`
周禄康
  • 浏览: 161036 次
  • 性别: Icon_minigender_1
  • 来自: 石家庄
社区版块
存档分类
最新评论

Oracle统计分析函数集,over(partition by..) 的运用

OracleSQLCC++C# 
阅读更多

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)

sum(nid) over(partition by v1 order by nid)(2007-05-16 16:22:48)
  分类：sql语句
 

SQL> select n1,v1,nid,sum(nid) over(order by nid) as sum
  2  from t1;

        N1 V1                NID        SUM
---------- ---------- ---------- ----------
         1 aa                 61         61
         2 aa                 62        123
         3 aa                 63        186
         4 aa                 64        250

取nid列的累积和，即下面以emp表为例的按部门“连续”求总和

 ==================================================================

按v1分组取nid的和

SQL> select v1,sum(nid) over (partition by v1 order by v1) as sum_nid from t1;

V1            SUM_NID
---------- ----------
aa                187
aa                187
aa                187
bb                 83

按v1分组取nid的和，并重复行只显示一行

SQL> select distinct * from (select v1,sum(nid) over (partition by v1) as sum_nid from t1);

V1            SUM_NID
---------- ----------
aa                187
bb                 83

==================================================================

再以emp为例

使用子分区查出各部门薪水连续的总和。注意按部门分区 over(...)条件的不同
sum(sal) over (partition by deptno order by ename) 按部门“连续”求总和
sum(sal) over (partition by deptno) 按部门求总和
sum(sal) over (order by deptno，ename) 不按部门“连续”求总和
sum(sal) over () 不按部门，求所有员工总和，效果等同于sum(sal)。

sql> break on deptno skip 1  -- 为效果更明显，把不同部门的数据隔段显示。

SQL> select deptno,ename,sal,
  2      sum(sal) over (partition by deptno order by ename) 部门连续求和,
  3      sum(sal) over (partition by deptno) 部门总和,
  4      100*round(sal/sum(sal) over (partition by deptno),4) 部门份额,
  5      sum(sal) over () 总和,
  6      sum(sal) over (order by deptno,ename) 连续求和,
  7      100*round(sal/sum(sal) over (),4) 总份额
  8  from emp;

    DEPTNO ENAME             SAL 部门连续求和   部门总和   部门份额       总和   连续求和     总份额

---------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- ----------

        10 CLARK            2450         2450       8750         28      29025       2450       8.44

           KING             5000         7450       8750      57.14      29025       7450      17.23

           MILLER           1300         8750       8750      14.86      29025       8750       4.48


        20 ADAMS            1100         1100      10875      10.11      29025       9850       3.79

           FORD             3000         4100      10875      27.59      29025      12850      10.34

           JONES            2975         7075      10875      27.36      29025      15825      10.25

           SCOTT            3000        10075      10875      27.59      29025      18825      10.34

           SMITH             800        10875      10875       7.36      29025      19625       2.76


        30 ALLEN            1600         1600       9400      17.02      29025      21225       5.51

           BLAKE            2850         4450       9400      30.32      29025      24075       9.82

           JAMES             950         5400       9400      10.11      29025      25025       3.27

           MARTIN           1250         6650       9400       13.3      29025      26275       4.31

           TURNER           1500         8150       9400      15.96      29025      27775       5.17

           WARD             1250         9400       9400       13.3      29025      29025       4.31

已选择14行。

综合的例子，求和规则有按部门分区的，有不分区的例子

SQL> select deptno,ename,sum(sal) over(partition by deptno order by sal) as sum_sal,
  2         sum(sal) over(order by deptno,sal) as sum_dept_sal
  3  from emp;

    DEPTNO ENAME         SUM_SAL SUM_DEPT_SAL
---------- ---------- ---------- ------------
        10 MILLER           1300         1300
           CLARK            3750         3750
           KING             8750         8750

        20 SMITH             800         9550
           ADAMS            1900        10650
           JONES            4875        13625
           SCOTT           10875        19625
           FORD            10875        19625

        30 JAMES             950        20575
           WARD             3450        23075
           MARTIN           3450        23075
           TURNER           4950        24575
           ALLEN            6550        26175
           BLAKE            9400        29025


已选择14行。

来一个逆序的，即部门从大到小排列，部门里各员工的薪水从高到低排列，累计和的规则不变。

SQL> select deptno,ename,sal,
  2         sum(sal) over (partition by deptno order by deptno desc,sal desc) as sum_sal_order,
  3         sum(sal) over (order by deptno desc,sal desc) as sum
  4  from emp;

    DEPTNO ENAME             SAL SUM_SAL_ORDER        SUM
---------- ---------- ---------- ------------- ----------
        30 BLAKE            2850          2850       2850
           ALLEN            1600          4450       4450
           TURNER           1500          5950       5950
           WARD             1250          8450       8450
           MARTIN           1250          8450       8450
           JAMES             950          9400       9400

        20 SCOTT            3000          6000      15400
           FORD             3000          6000      15400
           JONES            2975          8975      18375
           ADAMS            1100         10075      19475
           SMITH             800         10875      20275

        10 KING             5000          5000      25275
           CLARK            2450          7450      27725
           MILLER           1300          8750      29025


已选择14行。
 

分享到：
| javascript获取FCKeditor内容
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

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

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

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

    `OVER (PARTITION BY ...)` 和开窗函数在Oracle数据库中提供了极其强大的数据处理能力，特别是在需要进行复杂的数据分析或报表制作时。掌握这些技术对于提高数据库查询效率和数据处理能力至关重要。

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

    在Oracle数据库系统中，分析函数为数据分析师和数据库管理员提供了深入洞察数据的能力，尤其在处理排序数据集时，它们能提供非常灵活的功能。 分析函数的基本概念： 1. 分析函数与聚合函数（如SUM, COUNT, AVG等）...

    ORACLE分析函数大全[收集].pdf

    Oracle分析函数是数据库管理系统Oracle中的一个重要特性，它们在SQL查询中用于执行复杂的数据分析操作，尤其是在处理分组数据时。自Oracle 8.1.6版本开始引入分析函数，其核心区别于传统的聚合函数（如SUM、COUNT、...

    Oracle中的分析函数详解

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

    Oracle之分析函数讲解及PPT资源.zip

    Oracle数据库中的分析函数是SQL查询中的重要工具，用于在数据集上执行聚合操作，并且能够对每个分组或行提供上下文相关的计算。这些函数在处理复杂的数据统计和报表展示时非常有用，能够帮助我们高效地获取所需信息...

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

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

    Oracle分析函数.doc

    例如，`function_name(arg1,arg2,...) over (partition by column1, column2 order by column3)`会根据`column1`和`column2`分组，然后在每个组内按`column3`排序来应用函数。 Oracle分析函数在数据分析、报表生成...

    oracle分析函数.doc

    3. **分析函数OVER解析** `OVER`子句是分析函数的关键部分，它可以接受两个主要部分：`PARTITION BY`和`ORDER BY`。 - `PARTITION BY`：将数据集划分为多个分区，每个分区内的分析函数独立计算。 - `ORDER BY`：...

    Oracle开发的over函数

    #### 三、分析函数OVER解析 1. **分析函数语法**: 分析函数的基本语法结构如下： ```sql FUNCTION_NAME(,) OVER ( &lt;Partition-Clause&gt; &lt;Order-by-Clause&gt; ) ``` - `FUNCTION_NAME` 是分析函数的名称...

    oracle 常用函数下载

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

    ORACLE 分析函数大全

    数据窗口定义了分析函数作用的行集，这个行集可以根据特定的排序条件（如`ORDER BY`子句）或者分区条件（如`PARTITION BY`子句）动态改变。例如，`OVER (ORDER BY salary)`会按照薪水对数据进行排序并进行计算，而`...

    ORACLE分析函数教程

    ### Oracle分析函数详解 #### 一、Oracle分析函数概述 Oracle分析函数是在处理大量数据时极为有用的一套工具，主要用于在线分析处理（OLAP）场景。这类函数可以在多个级别上进行数据聚合，并支持复杂的排序、分组...

    oracle函数介绍(6) 著名函数之分析函数.doc

    Oracle函数在数据库查询和数据分析中扮演着至关重要的角色，尤其是一些著名的分析函数，它们能够帮助我们进行复杂的统计计算和数据处理。在本篇中，我们将深入探讨几个关键的分析函数，包括AVG、SUM、COUNT、FIRST_...

    ORACLE 常用分析函数

     ROW_NUMBER () OVER([partition_clause] order_by_clause) dense_rank在做排序时如果遇到列有重复值，则重复值所在行的序列值相同，而其后的序列值依旧递增，rank则是重复值所在行的序列值相同，但其后的序列值从...

    ORACLE常用分析函数说明

    ORACLE 常用分析函数说明 Oracle 分析函数从 8.1.6 版本开始提供，是一种计算基于组的聚合值的函数。它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。 开窗函数是分析函数工作的...

    Oracle分析函数实践 - blog.docx

    在Oracle中，分析函数的核心组成部分是`OVER()`子句，它包括三个主要部分：`PARTITION BY`、`ORDER BY`和`WINDOW`。`PARTITION BY`子句按照指定的表达式将数据逻辑上分为多个分区；`ORDER BY`子句定义了分区内部的...

    Oracle_分析函数

    分析函数的基本结构包括函数名和OVER子句，OVER子句中可以包含PARTITION BY、ORDER BY以及窗口子句。通过这些子句，可以对数据进行分组（分区）、排序、定义窗口大小等操作，从而得到每个分区内的累计值、排名、...

    ORACLE分析函数.pdf

    Oracle分析函数是数据库管理系统Oracle中的一种高级SQL特性，它允许用户在单个查询中对一组行进行计算，而无需使用子查询或自连接。这些函数极大地增强了数据分析和报告的能力，提高了查询性能。以下是对Oracle分析...

Magicbox
Global site tag (gtag.js) - Google Analytics