`

ORACLE over函数用法

阅读更多
Oracle over函数

SQL code:

sql over的作用及用法
RANK ( ) OVER ( [query_partition_clause] order_by_clause )
DENSE_RANK ( ) OVER ( [query_partition_clause] order_by_clause )
可实现按指定的字段分组排序,对于相同分组字段的结果集进行排序,
其中PARTITION BY 为分组字段,ORDER BY 指定排序字段

over不能单独使用,要和分析函数:rank(),dense_rank(),row_number()等一起使用。
其参数:over(partition by columnname1 order by columnname2)
含义:按columname1指定的字段进行分组排序,或者说按字段columnname1的值进行分组排序。
例如:employees表中,有两个部门的记录:department_id =10和20
select department_id,rank() over(partition by department_id order by salary) from employees就是指在部门10中进行薪水的排名,在部门20中进行薪水排名。如果是partition by org_id,则是在整个公司内进行排名。



SQL code:
WITH
OBJ AS(
SELECT
name, type


目的:以oracle自带的scott模式为测试环境,主要通过试验体会分析函数的用法。

类似 sum(...) over ... 的使用


1.原表信息:

SQL> break on deptno skip 1 -- 为效果更明显,把不同部门的数据隔段显示。
SQL> select deptno,ename,sal
2 from emp
3 order by deptno;

    DEPTNO ENAME             SAL
---------- ---------- ----------
        10 CLARK            2450
           KING             5000
           MILLER           1300

        20 SMITH             800
           ADAMS            1100
           FORD             3000
           SCOTT            3000
           JONES            2975

        30 ALLEN            1600
           BLAKE            2850
           MARTIN           1250
           JAMES             950
           TURNER           1500
           WARD             1250

已选择14行。

2.先来一个简单的,注意over(...)条件的不同,
使用 sum(sal) over (order by ename)... 查询员工的薪水“连续”求和,
注意over (order by ename)如果没有order by 子句,求和就不是“连续”的,
放在一起,体会一下不同之处:

SQL> break on '' -- 取消数据分段显示
SQL> select deptno,ename,sal,
2 sum(sal) over (order by ename) 连续求和,
3 sum(sal) over () 总和,                  -- 此处sum(sal) over () 等同于sum(sal)
4 100*round(sal/sum(sal) over (),4) "份额(%)"
5 from emp
6 /

    DEPTNO ENAME             SAL   连续求和       总和    份额(%)
---------- ---------- ---------- ---------- ---------- ----------
        20 ADAMS            1100       1100      29025       3.79
        30 ALLEN            1600       2700      29025       5.51
        30 BLAKE            2850       5550      29025       9.82
        10 CLARK            2450       8000      29025       8.44
        20 FORD             3000      11000      29025      10.34
        30 JAMES             950      11950      29025       3.27
        20 JONES            2975      14925      29025      10.25
        10 KING             5000      19925      29025      17.23
        30 MARTIN           1250      21175      29025       4.31
        10 MILLER           1300      22475      29025       4.48
        20 SCOTT            3000      25475      29025      10.34
        20 SMITH             800      26275      29025       2.76
        30 TURNER           1500      27775      29025       5.17
        30 WARD             1250      29025      29025       4.31

已选择14行。


3.使用子分区查出各部门薪水连续的总和。注意按部门分区。注意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 (order by deptno,ename) 连续求和, --所有部门的薪水"连续"求和
6 sum(sal) over () 总和, -- 此处sum(sal) over () 等同于sum(sal),所有员工的薪水总和
7 100*round(sal/sum(sal) over (),4) "总份额(%)"
8 from emp
9 /

DEPTNO ENAME    SAL 部门连续求和   部门总和 部门份额(%)   连续求和   总和 总份额(%)
------ ------ ----- ------------ ---------- ----------- ---------- ------ ----------
    10 CLARK   2450         2450       8750          28       2450 29025       8.44
       KING    5000         7450       8750       57.14       7450 29025      17.23
       MILLER 1300         8750       8750       14.86       8750 29025       4.48

    20 ADAMS   1100         1100     10875       10.11       9850 29025       3.79
       FORD    3000         4100      10875       27.59      12850 29025      10.34
       JONES   2975         7075      10875       27.36      15825 29025      10.25
       SCOTT   3000        10075      10875       27.59      18825 29025      10.34
       SMITH    800        10875      10875        7.36      19625 29025       2.76

    30 ALLEN   1600         1600       9400       17.02      21225 29025       5.51
       BLAKE   2850         4450       9400       30.32      24075 29025       9.82
       JAMES    950         5400       9400       10.11      25025 29025       3.27
       MARTIN 1250         6650       9400        13.3      26275 29025       4.31
       TURNER 1500         8150       9400       15.96      27775 29025       5.17
       WARD    1250         9400       9400        13.3      29025 29025       4.31


已选择14行。



4.来一个综合的例子,求和规则有按部门分区的,有不分区的例子
SQL> select deptno,ename,sal,sum(sal) over (partition by deptno order by sal) dept_sum,
2 sum(sal) over (order by deptno,sal) sum
3 from emp;

    DEPTNO ENAME             SAL   DEPT_SUM        SUM
---------- ---------- ---------- ---------- ----------
        10 MILLER           1300       1300       1300
           CLARK            2450       3750       3750
           KING             5000       8750       8750

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

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


已选择14行。



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

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

    DEPTNO ENAME             SAL   DEPT_SUM        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
       MITH             800      10875      20275

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


已选择14行。



6.体会:在"... from emp;"后面不要加order by 子句,使用的分析函数的(partition by deptno order by sal)
里已经有排序的语句了,如果再在句尾添加排序子句,一致倒罢了,不一致,结果就令人费劲了。如:

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

    DEPTNO ENAME             SAL   DEPT_SUM        SUM
---------- ---------- ---------- ---------- ----------
        30 JAMES             950        950      20575
           WARD             1250       3450      23075
           MARTIN           1250       3450      23075
           TURNER           1500       4950      24575
           ALLEN            1600       6550      26175
           BLAKE            2850       9400      29025

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

        10 MILLER           1300       1300       1300
           CLARK            2450       3750       3750
           KING             5000       8750       8750


已选择14行


==================================================================
利用over实现的分页功能:
--假设code1,code2为用来分页的KEY,每页显示5第数据
select code1,code2,code3,
ceil(count(*) over(partition by code1,code2 order by rownum)/5),
count(*) over(partition by code1,code2)
from ma_kbn order by code1,code2

==================================================================
http://blog.sina.com.cn/u/1285703223
分享到:
评论

相关推荐

    ORACLE的OVER函数使用技巧

    ORACLE的OVER函数使用技巧ORACLE的OVER函数使用技巧ORACLE的OVER函数使用技巧

    ORACLE_OVER函数

    #### 五、OVER函数的高级用法 ##### 示例4:使用LAG()和LEAD()获取前后行数据 LAG()和LEAD()函数可以获取当前行前面或后面一行的数据。 ```sql SELECT employee_id, salary, department_id, LAG(salary, 1) OVER...

    Oracle开发的over函数

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

    oracle分析函数(用法+实例)

    Oracle 分析函数(用法+实例) Oracle 分析函数是 Oracle 8.1.6 版本中引入的高级应用,属于 Oracle 的一大亮点。分析函数可以分为四大类:排名函数、聚合函数、行比较函数和统计函数。下面将对分析函数的原理、...

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

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

    ORACLE中OVER函数的用法.doc

    ORACLE中OVER函数的用法.doc

    ORACLE分析函数

    根据提供的文件信息,我们可以深入探讨Oracle分析函数的相关知识点,特别是`SUM()`函数配合`OVER`子句的不同用法,以及`RANK()`, `DENSE_RANK()`, 和 `ROW_NUMBER()` 这三个窗口函数的应用场景。 ### Oracle分析...

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

    Oracle分析函数是数据库管理系统Oracle中的一种高级查询工具,它们用于处理行集,计算基于特定窗口内的数据的聚合值。分析函数的主要特点是返回的结果不仅仅是一行,而是多行,这与传统的聚合函数(如SUM, AVG等)...

    ORACLE 分析函数大全

    Oracle 分析函数是一种强大的SQL工具,它允许你在处理数据时执行复杂的分析操作,而不像聚合函数那样仅仅返回单行结果。分析函数在Oracle 8.1.6版本中被引入,自那时起,它们已经成为数据库查询和报表生成的重要组成...

    oracle分析函数大全

    ### Oracle分析函数详解 #### 一、概述 Oracle分析函数是一种强大的工具,它自Oracle 8.1.6版本开始引入,并在后续版本中不断完善和发展。...理解并熟练掌握这些函数的使用方法,将有助于更高效地完成数据处理任务。

    oracle分析函数及开窗函数

    通过本文的学习,我们了解了Oracle中的分析函数及其基本用法,包括常见的分析函数分类、基本语法以及实际应用场景。这些函数的强大之处在于它们能够轻松地处理复杂的数据计算需求,为数据分析提供了极大的便利。掌握...

    oracle 常用函数下载

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

    oracle 分析函数学习笔记

    在描述中提到的场景中,有几种不同的方法可以实现数据统计,包括通过程序处理、使用临时表和包、标准SQL的UNION以及Oracle特有的分析函数。每种方法都有其优缺点。例如,程序处理方法与数据库无关,但增加了开发工作...

    oracle分析函数参考手册

    通过上述介绍,我们不仅了解了分析函数的基本用法,还探讨了如何利用开窗函数来定义不同类型的计算窗口,从而实现更高级的数据分析需求。掌握了这些技巧后,您可以更加高效地进行数据探索和洞察发现。

    Orcal中over函数.doc

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

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

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

    【Oracle】LISTAGG函数的使用.pdf

    1. 作为分析函数,类似于 row_number()、rank()、dense_rank() 等函数,使用方法相似: LISTAGG(合并字段, 连接符) WITHIN GROUP(ORDER BY 合并字段的排序) OVER(PARTITION BY 分组字段) 2. 作为聚合函数,类似于 ...

    oracle开窗函数学习技巧总结

    本文将重点介绍`OVER`函数及其相关用法,并通过具体的例子来帮助读者更好地理解和掌握这些技巧。 #### 二、`OVER`函数简介 `OVER`函数是Oracle开窗函数的核心组成部分,它定义了一个计算上下文或窗口,在这个窗口内...

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

    下面我们将详细讨论`ROW_NUMBER() OVER()`的使用方法及其在不同场景下的应用。 首先,`ROW_NUMBER() OVER()`的基本语法是: ```sql ROW_NUMBER() OVER ( [PARTITION BY column1, column2, ...] ORDER BY column3...

    ORACLE分析函数.ppt

    在本文档中,我们将详细介绍 ORACLE 分析函数的使用和优化方法,并提供一些实用的示例代码和优化方案。同时,我们还将分析分析函数的优缺点,并讨论如何根据实际情况选择合适的分析函数。 1. 分析函数的优点 使用...

Global site tag (gtag.js) - Google Analytics