`
西门吹牛
  • 浏览: 226184 次
  • 性别: Icon_minigender_1
  • 来自: 天津
社区版块
存档分类
最新评论

分析及开窗函数

OracleSQLBlog 
阅读更多
转自http://hi.baidu.com/edgar108/blog/item/e24c7fd66b0817d7a144dfc8.html

我以oracle中的emp 和dept表为例，讲一下开窗函数。

假如，现在有这样的要求：查出所有的员工的名字ename,薪水sal 以及他的薪水占说有员工薪水的比例。
一开始，我们的思路可能是这样：
select ename ,sal ,sal/sum(sal) from emp;
但是这样写是不对的，sum()是一个单行统计函数，只返回一个值，不能和其他字段同时出现。
解决办法就是使用开窗函数over()
select ename ,sal ,sal/sum(sal) over() as percent from emp;
查询结果：
ENAME             SAL    PERCENT
---------- ---------- ----------
SMITH             800 .027562446
ALLEN            1600 .055124892
WARD             1250 .043066322
JONES            2975 .102497847
MARTIN           1250 .043066322
BLAKE            2850 .098191214
CLARK            2450 .084409991
SCOTT            3000 .103359173
KING             5000 .172265289
TURNER           1500 .051679587
ADAMS            1100 .037898363

ENAME             SAL    PERCENT
---------- ---------- ----------
JAMES             950 .032730405
FORD             3000 .103359173
MILLER           1300 .044788975

已选择14行。

上面的over是指把前面的函数(本例中是sum())当成开窗函数而不是统计函数，SQL标准允许讲所有的统计函数
用作开窗函数，使用over关键字来区分这两种用法。

上面的“sum(sal) over()”的意思是，对于每一条记录，都去计算一次sal的和。如果over关键字后的括号中的选项为空，

把上面的sql改进一下：
select ename ,sal ,'0'||round(sal/sum(sal) over(),3) as percent from emp;
查询结果：

ENAME             SAL PERCENT
---------- ---------- -----------------------------------------
SMITH             800 0.028
ALLEN            1600 0.055
WARD             1250 0.043
JONES            2975 0.102
MARTIN           1250 0.043
BLAKE            2850 0.098
CLARK            2450 0.084
SCOTT            3000 0.103
KING             5000 0.172
TURNER           1500 0.052
ADAMS            1100 0.038

ENAME             SAL PERCENT
---------- ---------- -----------------------------------------
JAMES             950 0.033
FORD             3000 0.103
MILLER           1300 0.045

已选择14行。

如果现在像查询每个员工的姓名ename，工资sal，以及他的工资占他所在部门的比例，按照上面的思路，这次要这样写：

select ename,deptno,sal,'0'|| round(sal/sum(sal) over(partition by deptno),3) from emp;

如果需要对sal排序，再partition by deptno 后面 再加上order by sal:

select ename,deptno,sal,'0'|| round(sal/sum(sal) over(partition by deptno order by sal),3) from emp;

ORDER BY 的完整语法为 ORDER BY 字段名 RANGE|ROWS BETWEEN 边界规则1 AND 边界规则2
RANGE 表示 按照值的范围进行范围的定义，而 ROWS 表示按照行的范围进行范围的定义

边界规则的取值见下表：

可取值                                                说明                                   示例

CURRENT ROW                              当前行

N PRECEDING                                 前N行                                    2 PRECEDING

UNBOUNDED PRECEDING          一直到第一条记录     

N FOLLOWING                                 后N行                                  2 FOLLOWING

UNBOUNDED FOLLOWING          一直到最后一条记录


但是，如果这样写，会报错：
select ename,deptno,sal,'0'|| round(sal/sum(sal) over(order by sal partition by deptno ),3) from emp;可能 order by不能写在partition by的前面。


如果现在按照员工的姓名排序，并计算工资的累加和：

select ename ,sal ,sum(sal) over(order by sal rows between unbounded preceding and current row) as result from emp;

order by sal rows between unbounded preceding and current row 的意思是： 按照sal进行排序，然后计算从第一行(unbounded preceding)到当前行
(current row)的和，这样的结果就是按照工资进行排序的工作值的累加和。

因为ROWS 表示按照行的范围进行范围的定义，所以计算从第一行到当前行的累加和。

如果把ROWS换成 RANGE :

select ename ,sal ,sum(sal) over(order by sal range between unbounded preceding and current row) as result from emp;

ENAME             SAL     RESULT
---------- ---------- ----------
SMITH             800        800
JAMES             950       1750 (800+950)
ADAMS            1100       2850 (800+950+1100)
WARD             1250       5350
MARTIN           1250       5350
MILLER           1300       6650
TURNER           1500       8150
ALLEN            1600       9750
CLARK            2450      12200
BLAKE            2850      15050
JONES            2975      18025

ENAME             SAL     RESULT
---------- ---------- ----------
SCOTT            3000      24025
FORD             3000      24025
KING             5000      29025

已选择14行。


RANGE 表示 按照值的范围进行范围的定义 ，在计算累加和的过程中，如果遇到相同的值(本例中为sal)，则计算所有的相同值同时累加
(本例中SCOTT,FORD的sal全是3000，所以值是 18025+3000+3000=24025)

select ename ,sal ,sum(sal) over(order by sal rows between 2 preceding and 2 following) as result from emp;

ENAME             SAL     RESULT
---------- ---------- ----------
SMITH             800       2850 (800+950+1100)
JAMES             950       4100
ADAMS            1100       5350
WARD             1250       5850
MARTIN           1250       6400
MILLER           1300       6900
TURNER           1500       8100 (1250+1300+1500+1600+2450)
ALLEN            1600       9700
CLARK            2450      11375
BLAKE            2850      12875
JONES            2975      14275 (2450+2850+2975+3000+3000)

ENAME             SAL     RESULT
---------- ---------- ----------
SCOTT            3000      16825
FORD             3000      13975
KING             5000      11000 (3000+3000+5000)

已选择14行。

sum(sal) over(order by sal rows between 2 preceding and 2 following)
按照sal进行排序，然后计算从当前行前两行(2 preceding) 到 当前行后两行(2 following)的累加和

对于第1行到第2行(n=2)，“前2行”是不存在或不完整的，所以按照前两行不存在或不完整来计算，最后2行类似。

select ename ,sal ,sum(sal) over(order by sal rows between 1 following and 3 following) as result from emp;

ENAME             SAL     RESULT
---------- ---------- ----------
SMITH             800       3300 (950+1100+1250)
JAMES             950       3600
ADAMS            1100       3800
WARD             1250       4050
MARTIN           1250       4400
MILLER           1300       5550
TURNER           1500       6900
ALLEN            1600       8275
CLARK            2450       8825
BLAKE            2850       8975
JONES            2975      11000

ENAME             SAL     RESULT
---------- ---------- ----------
SCOTT            3000       8000
FORD             3000       5000
KING             5000                 (后面没有数据了，所以是NULL)

已选择14行。

计算的某一列后1行到后3行的值

select ename ,sal ,sum(sal) over(order by sal range between unbounded preceding and current row) as result from emp;
与
select ename ,sal ,sum(sal) over(order by sal) as result from emp;
是等价的。
也就是说 range between unbounded preceding and current row 是默认的定位方式。

select ename ,sal ,count(*) over(order by sal desc rows between unbounded preceding and current row) as result from emp;

ENAME             SAL     RESULT
---------- ---------- ----------
KING             5000          1
FORD             3000          2
SCOTT            3000          3
JONES            2975          4
BLAKE            2850          5
CLARK            2450          6
ALLEN            1600          7
TURNER           1500          8
MILLER           1300          9
WARD             1250         10
MARTIN           1250         11

ENAME             SAL     RESULT
---------- ---------- ----------
ADAMS            1100         12
JAMES             950         13
SMITH             800         14

已选择14行。
order by sal desc rows between unbounded preceding and current row 表示按照sal的降序排列，计算从第一行到当前行的个数，所以这个可以看作员工工资的排名。
分享到：
| oracle行转列
评论
发表评论

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

相关推荐

    oracle分析函数及开窗函数

    oracle分析函数及开窗函数的使用，包括over等分析函数

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

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

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

    oracle分析函数，oracle分析函数over_及开窗函数用法。

    SQL开窗函数的基本概念、用法以及一些常见的开窗函数

    开窗函数为分析型查询提供了强大的工具，使您能够执行诸如计算行号、累计总和、移动平均值等复杂操作。 下面将详细解释SQL开窗函数的基本概念、用法以及一些常见的开窗函数。SQL开窗函数（Window Functions）是SQL...

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

    Oracle 语法之 OVER (PARTITION BY ..) 及开窗函数（转载）,这个命令很实用，对于分析人员经常用到。

    SQL开窗函数介绍以及示例

    SQL开窗函数（Window Functions）是一种用于在查询结果集中执行聚合、排序和分析操作的强大工具。它们可以在不破坏查询结果集的情况下，对每一行数据进行计算，比如计算行的排名、累计和、移动平均等。以下是SQL开窗...

    敲黑板啦！开窗函数你学会了吗

    特征分析与偏移分析什么是开窗函数？学习目标：1、累计计算窗口函数（1）sum(…) over(……)（2）avg(…) over(……)（3）语法总结：2、分区排序窗口函数3、分组排序窗口函数4、偏移分析窗口函数练习总结： 什么是开...

    SQL开窗函数详解.pdf

    SQL开窗函数（也称为分析函数或OLAP函数）是一种高级功能，允许用户执行复杂的计算和聚合操作，同时保持与原始数据行的关联。这些函数在SQL查询中提供了更多的灵活性和强大的分析能力，使得用户能够更深入地洞察数据...

    oracle分析函数

    常用oracle分析函数 开窗函数 强大

    ORACLE分析函数

    Oracle分析函数RANK(),ROW_NUMBER(),LAG()等的使用方法 Oracle分析函数参考手册 开窗函数(over)详解

    Oracle分析函数中文+英文【英文非中文的配套】

    1、oracle分析函数 中文： 主要含： rank() 和 dense_rank() first_value()和last_value() row_number() LAG() range开窗函数 2、oracle分析函数 英文： 比较详细

    oracle

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

    大数据Oracle理论+SQL+面试问题汇总+BI理论

    汇总了Oracle在开发中常用到的各种函数如分析函数、开窗函数、数字函数、字符串函数、时间函数、转换函数、空值转换函数等。从SQL句子的讲解到函数、游标、存储过程、序列等进行了例子讲解。如果你现在从事的行业有

    DB2中OLAP函数

    DB2中OLAP函数。电子文档里面有详细介绍！对学习开窗函数有很好的帮助。联机分析处理OLAP是一种软件技术，它使分析人员能够迅速、一致、交互地从各个方面观察信息，以达到深入理解数据的目的。

    《程序员的SQL金典》

    第2部分为进阶篇，讲解了函数、子查询、表连接、不同DBMS中的SQL语法差异、SQL调优、NULL值处理、事务、开窗函数等高级技术；第3部分为案例篇，对前两部分的知识进行了综合运用。 中的SQL语法差异、SQL调优、NULL值...

    程序员SQL金典，第一本专门为程序员编写的数据库图书。

    主要介绍SQL 的语法规则及在实际开发中的应用，并且对SQL 在MySQL、 ...的SQL 语法差异、SQL 调优、NULL 值处理、事务、开窗函数等高级技术；通过对实际案例 开发过程的详细分析，使读者掌握SQL的综合应用技巧。

    程序员的SQL金典完整版

    的 SQL 语法差异、SQL 调优、NULL 值处理、事务、开窗函数等高级技术；通过对实际案例 开发过程的详细分析，使读者掌握 SQL的综合应用技巧。 内容提要 本书主要介绍SQL以及在实际开发中的应用，并且对SQL在MYSQL、...

    程序员的SQL经典

    本书特色：主要介绍 SQL 的...透彻分析函数、子查询、表连接、不同 DBMS 中 的 SQL 语法差异、SQL 调优、NULL 值处理、事务、开窗函数等高级技术；通过对实际案例 开发过程的详细分析，使读者掌握 SQL 的综合应用技巧。

    程序员的SQL经典程序

    本书特色：主要介绍SQL 的语法规则及在实际开发中的应用，并且对...的SQL 语法差异、SQL 调优、NULL 值处理、事务、开窗函数等高级技术；通过对实际案例 开发过程的详细分析，使读者掌握SQL的综合应用技巧。 内容提要

    mysql面试题-电商零售数据分析

    （3）编写SQL，分别求出2021年各平台销售额最高的商品（使用开窗函数）。 （4）开放思维，对各平台、各商品的销售提出自己的建议，并通过数据说明你提出该建议的原因。 2. 小贴士： 该资源为utf-8格式的csv文件，...

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