`
pengfeicao521
  • 浏览: 144562 次
  • 性别: Icon_minigender_1
  • 来自: 合肥
社区版块
存档分类
最新评论

关于oracle 开窗函数--1

阅读更多
一.OVER
1.SQL> SELECT deptno,ename,sal FROM emp ORDER BY deptno,ename;


    DEPTNO ENAME             SAL
---------- ---------- ----------
        10 CLARK            2450
        10 KING             5000
        10 MILLER           1300
        20 FORD             3000
        20 JONES            2975
        20 SMITH             800
        30 ALLEN            1600
        30 BLAKE            2850
        30 JAMES             950
        30 MARTIN           1250
        30 TURNER           1500
        30 WARD             1250


已选择12行。


2.SQL> SELECT deptno,ename,sal,
  2  SUM(sal) OVER(ORDER BY ename) 连续求和,
  3  SUM(sal) OVER() 总和, --等效于SUM(sal)
  4  100*ROUND(sal/SUM(sal) OVER(),4) "份额(%)"
  5  FROM emp;


    DEPTNO ENAME             SAL   连续求和       总和    份额(%)
---------- ---------- ---------- ---------- ---------- ----------
        30 ALLEN            1600       1600      24925       6.42
        30 BLAKE            2850       4450      24925      11.43
        10 CLARK            2450       6900      24925       9.83
        20 FORD             3000       9900      24925      12.04
        30 JAMES             950      10850      24925       3.81
        20 JONES            2975      13825      24925      11.94
        10 KING             5000      18825      24925      20.06
        30 MARTIN           1250      20075      24925       5.02
        10 MILLER           1300      21375      24925       5.22
        20 SMITH             800      22175      24925       3.21
        30 TURNER           1500      23675      24925       6.02
        30 WARD             1250      24925      24925       5.02


已选择12行。


3.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  FROM emp;


    DEPTNO ENAME             SAL 部门连续求和   部门总和 部门份额(%)
---------- ---------- ---------- ------------ ---------- -----------
        10 CLARK            2450         2450       8750          28
        10 KING             5000         7450       8750       57.14
        10 MILLER           1300         8750       8750       14.86
        20 FORD             3000         3000       6775       44.28
        20 JONES            2975         5975       6775       43.91
        20 SMITH             800         6775       6775       11.81
        30 ALLEN            1600         1600       9400       17.02
        30 BLAKE            2850         4450       9400       30.32
        30 JAMES             950         5400       9400       10.11
        30 MARTIN           1250         6650       9400        13.3
        30 TURNER           1500         8150       9400       15.96
        30 WARD             1250         9400       9400        13.3


已选择12行。


4.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
        30 ALLEN            1600       4450       4450
        30 TURNER           1500       5950       5950
        30 MARTIN           1250       8450       8450
        30 WARD             1250       8450       8450
        30 JAMES             950       9400       9400
        20 FORD             3000       3000      12400
        20 JONES            2975       5975      15375
        20 SMITH             800       6775      16175
        10 KING             5000       5000      21175
        10 CLARK            2450       7450      23625
        10 MILLER           1300       8750      24925


已选择12行。


5.SQL> SELECT deptno,ename,sal
  2  FROM (SELECT emp.deptno,emp.ename,emp.sal,row_number() over(PARTITION BY de
ptno order by sal desc) rn FROM emp) --部门分组、薪水降序
  3  WHERE rn<3; --部门薪水最高的两个人


    DEPTNO ENAME             SAL
---------- ---------- ----------
        10 KING             5000
        10 CLARK            2450
        20 FORD             3000
        20 JONES            2975
        30 BLAKE            2850
        30 ALLEN            1600


已选择3行。
SQL> SELECT deptno,ename,sal --部门薪水最高的人,可能有多个
  2  FROM emp t
  3  WHERE NOT EXISTS(SELECT 1 FROM emp WHERE deptno=t.deptno AND sal>t.sal);


    DEPTNO ENAME             SAL
---------- ---------- ----------
        20 FORD             3000
        10 KING             5000
        30 BLAKE            2850


已选择3行。
SQL> SELECT deptno,ename,sal --查询每个部门薪水最高的两个人
  2  FROM emp t
  3  WHERE (SELECT COUNT(1) FROM emp WHERE deptno=t.deptno AND sal>t.sal)<=1;


    DEPTNO ENAME             SAL
---------- ---------- ----------
        30 ALLEN            1600
        20 FORD             3000
        10 KING             5000
        20 JONES            2975
        30 BLAKE            2850
        10 CLARK            2450


已选择6行。


6.其它
SUM(sal) OVER(ORDER BY sal RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING)


SUM(sal) OVER(ORDER BY sal ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)


SUM(sal) OVER(ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)


SUM(sal) OVER(ORDER BY sal ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
分享到:
评论

相关推荐

    oracle开窗函数学习技巧总结

    ### Oracle开窗函数学习技巧总结 #### 一、概述 在Oracle数据库中,开窗函数是一种非常强大的功能,它能够帮助我们对数据进行更复杂的分析和处理。开窗函数允许我们在查询结果集的一个窗口内执行聚合操作,而无需对...

    oracle分析函数及开窗函数

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

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

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

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

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

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

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

    ORACLE常用分析函数说明

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

    Oracle数据库中SQL开窗函数的使用

    Oracle、SQL Server、DB2等主流数据库系统都实现了开窗函数,但MySQL暂时还不支持。 开窗函数与聚合函数相似,都是对一组行进行计算,但不同之处在于,开窗函数可以在每组内返回多个结果,而不是像聚合函数那样只...

    Oracle 9i 分析函数参考手册

    ### 开窗函数及其应用场景 #### 开窗函数定义 开窗函数是分析函数中用于定义数据窗口范围的关键组成部分。它允许用户指定一个动态或静态的窗口范围,从而可以对窗口内的数据进行计算。开窗函数主要包括 `OVER` 子句...

    oracle分析函数参考手册

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

    ORACLE分析函数教程

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

    oracle分析函数文档

    开窗函数定义了分析函数作用的数据范围,通常包括以下几种类型: 1. **ORDER BY**:根据指定列的值对数据进行排序。 2. **PARTITION BY**:将数据划分成不同的分区,每个分区独立进行计算。 3. **RANGE BETWEEN** ...

    oracle分析函数大全

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

    Mysql 集合运算,伪列,开窗函数及示例代码

    MySql只支持Union(并集)集合运算;但是对于交集Intersect、差集Except,就没有实现了。一般的解决方案用in和not in来解决,小量数据还可以,但数据...伪列:实现类似Oracle,MySqlServer的Over() partition by 开窗函数

    SQL开窗函数的具体实现详解

    目前在 MSSQLServer、Oracle、DB2 等主流数据库中都提供了对开窗函数的支持,不过非常遗憾的是 MYSQL 暂时还未对开窗函数给予支持。 开窗函数简介:与聚合函数一样,开窗函数也是对行集组进行聚合计算,但是它不像...

    Oracle开发的over函数

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

    oracle 常用函数下载

    1. **开窗函数(Over)**: 开窗函数允许在一组行上执行计算,这组行被称为“窗口”,窗口可以基于特定的排序或分区条件。例如: - `OVER (ORDER BY salary)` 按薪资排序进行累计。 - `OVER (PARTITION BY deptno)`...

    oracle学习之函数存储过程

    ### Oracle学习之函数与存储过程详解 #### 一、函数的定义及应用 函数在Oracle中主要用于执行特定的计算或逻辑处理,并返回一个结果。它们是数据库编程中的重要组成部分,可以提高代码的重用性和可维护性。 #####...

    oracle 调优经验总结(转发)

    1. 应用设计人员需确保数据流清晰,利用Oracle特性提高性能,并在设计阶段设定性能期望。 2. 应用开发人员需选择高效的实现策略,并识别可能导致性能问题的SQL语句。 3. 数据库管理员则负责监控系统活动,提供性能...

    oracle 函数使用大总结

    - `SIGN(x)`:返回x的符号,1表示正数,-1表示负数,0表示零。 - `SIN(x)`:返回x的正弦值,x是以弧度表示的角度。 - `SINH(x)`:返回x的双曲正弦值。 - `SQRT(x)`:返回x的平方根,x必须大于或等于0。 - `TAN...

Global site tag (gtag.js) - Google Analytics