与聚集函数一样,窗口函数也针对定义的行集(组)执行聚集,但它不像聚集函数那样每组之返回一个值,窗口函数可以为每组返回多个值。实际上,DB2中称这种函数为联机分析处理OLAP函数,而Oracle把它们称为解析函数,但ISO SQL标准把它们称为窗口函数。窗口函数一般在OLAP分析、制作报表过程中会使用到。
窗口函数:
聚合函数 over()
聚合函数 over(partition by 字段)—分区
聚合函数 over(order by 字段)--框架字句
本文以Oracle11g中HR模式下的Employees表为例子来试着了解窗口函数,
Employees表结构如下:
计算部门号位20的员工总数:
SQL> edit
1 select first_name,department_id,count(*) over()
2 from employees
3* where department_id=20
SQL> /
FIRST_NAME DEPARTMENT_ID COUNT(*)OVER()
-------------------- ------------- --------------
Michael 20 2
Pat 20 2
窗口 ,函数 count(*) over() 对于查询返回的每一行,它返回了表中所有行的计数。
在深入研究Over字句之前,一定要注意:在SQL处理中,窗口函数都是最后一步执行,而且仅位于Order by字句之前。
- 分区
使用Partiton by字句定义行的分区或组,可以用paritition by对定义的行组计算聚集(当遇到新组的时候复位),并返回每个值(每个组中的每个成员),而不是一个用一个组表示表中的这个值的所有实例。如:
SQL> edit
1 select first_name,department_id,count(*) over(partition by department_id) as cnt
2 from employees
3* order by 2
SQL> /
FIRST_NAME DEPARTMENT_ID CNT
-------------------- ------------- ----------
Jennifer 10 1
Michael 20 2
Pat 20 2
Den 30 6
Alexander 30 6
Shelli 30 6
Sigal 30 6
Guy 30 6
Karen 30 6
Susan 40 1
Matthew 50 45
。。。。。。。。。。
如上结果所示:对于同一个部门(同一个分区)的每个员工的cnt值相同,这是由于在遇到新部门之前不会重置聚集。
另外partition by字句的优点是:在同一个select语句中,一个窗口函数的计算独立于按其他列分区的其他窗口函数的计算。例如下面的查询,返回每个员工、他的部门、他的部门中的员工数、他的职位以及跟他相同职位的员工数:
1 select first_name,department_id,count(*) over (partition by department_id) as dept_cnt,
2 job_id,
3 count(*) over(partition by job_id) as job_cnt
4 from employees
5* order by 2
SQL> /
FIRST_NAME DEPARTMENT_ID DEPT_CNT JOB_ID JOB_CNT
-------------------- ------------- ---------- ---------- ----------
Jennifer 10 1 AD_ASST 1
Michael 20 2 MK_MAN 1
Pat 20 2 MK_REP 1
Sigal 30 6 PU_CLERK 5
Alexander 30 6 PU_CLERK 5
Shelli 30 6 PU_CLERK 5
Karen 30 6 PU_CLERK 5
Den 30 6 PU_MAN 1
Guy 30 6 PU_CLERK 5
Susan 40 1 HR_REP 1
Donald 50 45 SH_CLERK 20
- 框架字句:
当在窗口函数over字句中使用order by 字句时,就指定了两件事:
1、分区中的行如何排序
2、在计算中包含哪些行
请看下面的查询,它计算了30号员工的工资的累计和
1 select department_id,first_name,hire_date,salary,
2 sum(salary) over(partition by department_id) as total1,
3 sum(salary) over() as total2,
4 sum(salary) over(order by hire_date) as running_total
5 from employees
6* where department_id=30
SQL> /
DEPARTMENT_ID FIRST_NAME HIRE_DATE SALARY TOTAL1
------------- -------------------- -------------- ---------- ----------
TOTAL2 RUNNING_TOTAL
---------- -------------
30 Den 07-12月-02 11000 24900
24900 11000
30 Alexander 18-5月 -03 3100 24900
24900 14100
30 Sigal 24-7月 -05 2800 24900
24900 16900
DEPARTMENT_ID FIRST_NAME HIRE_DATE SALARY TOTAL1
------------- -------------------- -------------- ---------- ----------
TOTAL2 RUNNING_TOTAL
---------- -------------
30 Shelli 24-12月-05 2900 24900
24900 19800
30 Guy 15-11月-06 2600 24900
24900 22400
30 Karen 10-8月 -07 2500 24900
24900 24900
已选择6行。
上面的查询语句相当于:
也就说默认情况下会告诉查询:计算所有行的和,即从当前行开始、包括它前面的所有行。对从当前行开始、包括它前面的所有行进行求和,就可以得到累计和效果了。
通过,框架字句允许定义数据的不同“子窗口”,以便在计算中使用,有很多方式可以指定这样的子窗口。如:
1 select department_id,first_name,salary,
2 sum(salary) over (order by hire_date range between unbounded preceding and current row) as run_total1,
3 sum(salary) over(order by hire_date rows between 1 preceding and current row) as run_total2,
4 sum(salary) over(order by hire_date range between current row and unbounded following) as run_total3,
5 sum(salary) over(order by hire_date rows between current row and 1 following) as run_total4
6 from employees
7* where department_id=30
SQL> /
DEPARTMENT_ID FIRST_NAME SALARY RUN_TOTAL1 RUN_TOTAL2 RUN_TOTAL3
------------- -------------------- ---------- ---------- ---------- ----------
RUN_TOTAL4
----------
30 Den 11000 11000 11000 24900
14100
30 Alexander 3100 14100 14100 13900
5900
30 Sigal 2800 16900 5900 10800
5700
DEPARTMENT_ID FIRST_NAME SALARY RUN_TOTAL1 RUN_TOTAL2 RUN_TOTAL3
------------- -------------------- ---------- ---------- ---------- ----------
RUN_TOTAL4
----------
30 Shelli 2900 19800 5700 8000
5500
30 Guy 2600 22400 5500 5100
5100
30 Karen 2500 24900 5100 2500
2500
已选择6行。
其中:
rangebetweenunbounded
precedingandcurrentrow
指定计算当前行开始、当前行之前的所有值;
rowsbetween1
precedingandcurrentrow
指定计算当前行的前一行开始,其范围一直延续到当前行;
rangebetweencurrentrowandunbounded
following 指定计算从当前行开始,包括它后面的所有行;
rowsbetweencurrentrowand1
following 指定计算当前行和它后面的一行;
最后一个例子,展示 了框架字句对查询输出的影响,请看下面查询:
分享到:
相关推荐
oracle分析函数,窗口函数,报表函数 分析函数(OVER) 分析函数2(Rank, Dense_rank, row_number) 分析函数3(Top/Bottom N、First/Last、NTile)
4 Oracle开发专题之:窗口函数 5 Oracle开发专题之:报表函数 6 Oracle开发专题之:分析函数总结 7 Oracle开发专题之:26个分析函数 8 分析函数简述">1 Oracle开发专题之:分析函数 OVER 2 Oracle开发专题之:...
窗口函数;报表函数;分析函数总结;26个分析函数;PLSQL开发笔记和小结;分析函数简述 ROW_NUMBER () OVER([partition_clause] order_by_clause) dense_rank在做排序时如果遇到列有重复值,则重复值所在行的序列值...
目录 Oracle开发专题之:分析函数(OVER) Oracle开发专题之:分析函数2(Rank, Dense_rank, row_number) Oracle开发专题之:分析函数3(Top/Bottom N、First/Last、NTile) Oracle开发专题之:窗口函数 ...
包含Oracle常用的高级函数,比如取前N名,每个分组的前N名等。 详细介绍Oracle分析函数(OVER、Rank、Dense_rank、row_number、Top/Bottom N、First/Last、NTile) ,窗口函数,报表函数
Over不能单独使用,用来制定数据窗口大小 Partition by表示分类数据集合,在此集合上的运算 Order by 跟排序字段,range时只能按一个字段排序,使用rows是可以跟多个字段排序 Range 可以使用range 100 preceding 也...
分析函数(OVER) 分析函数2(Rank, Dense_rank, row_number) 分析函数3(Top/Bottom N、First/Last、NTile) ...窗口函数 报表函数 分析函数总结 26个分析函数 PLSQL开发笔记和小结 分析函数简述
一、回顾一下前面《Oracle开发之窗口函数》中关于全统计一节,我们使用了Oracle提供的: 代码如下:sum(sum(tot_sales)) over (order by month rows between unbounded preceding and unbounded following) 来统计...
row_number()over(partition by col1 order by col2)表示根据col1分组,在分组内部根据col2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的)。 与rownum的区别在于:使用rownum进行排序的...
TOAD提供语法标识、错误标识和其他很多易于使用的功能,如在弹出窗口显示表名、列名和Oracle函数。和其他的 PL/SQL 编辑工具不同,TOAD 允许在一个文件中操作多个数据库对象,可以编译一个对象、编译多个对象、编译...
集成调试器(要求Oracle 7.3.4或更高)——该调试器提供您所需要的全部特性:跳入(Step In)、跳过(Step Over)、跳出(Step Out)、异常时停止运行、断点、观察和设置变量、观察全部堆栈等。基本能够调试任何...
该调试器(要求Oracle 7.3.4或更高)提供您所需要的全部特性:跳入(Step In)、跳过(Step Over)、跳出(Step Out)、异常时停止运行、断点、观察和设置变量、观察全部堆栈等。基本能够调试任何程序单元(包括...
1 SQL基础 1.1 基本概念 结构化查询语言(Structured Query ...select后面出现的列,如果没有使用集合函数,必须出现在group by 中。 select sno,sname,sum(grade) from student group by sno,sname; //合法写法 select...
NTKO OFFICE文档控件能够在浏览器窗口中直接编辑Word,Excel,Wps等Office文档并保存到任意Web服务器。实现文档和电子表格的统一管理。同时支持强制痕迹保留,手写签名,电子印章,版本控制,附件上传等办公自动化系统...
内含各种例子(vc下各种控件的使用方法、标题栏与菜单栏、工具栏与状态栏、图标与光标、程序窗口、程序控制、进程与线程、字符串、文件读写操作、文件与文件夹属性操作、文件与文件夹系统操作、系统控制操作、程序...
内含各种例子(vc下各种控件的使用方法、标题栏与菜单栏、工具栏与状态栏、图标与光标、程序窗口、程序控制、进程与线程、字符串、文件读写操作、文件与文件夹属性操作、文件与文件夹系统操作、系统控制操作、程序...
内含各种例子(vc下各种控件的使用方法、标题栏与菜单栏、工具栏与状态栏、图标与光标、程序窗口、程序控制、进程与线程、字符串、文件读写操作、文件与文件夹属性操作、文件与文件夹系统操作、系统控制操作、程序...
内含各种例子(vc下各种控件的使用方法、标题栏与菜单栏、工具栏与状态栏、图标与光标、程序窗口、程序控制、进程与线程、字符串、文件读写操作、文件与文件夹属性操作、文件与文件夹系统操作、系统控制操作、程序...
内含各种例子(vc下各种控件的使用方法、标题栏与菜单栏、工具栏与状态栏、图标与光标、程序窗口、程序控制、进程与线程、字符串、文件读写操作、文件与文件夹属性操作、文件与文件夹系统操作、系统控制操作、程序...
内含各种例子(vc下各种控件的使用方法、标题栏与菜单栏、工具栏与状态栏、图标与光标、程序窗口、程序控制、进程与线程、字符串、文件读写操作、文件与文件夹属性操作、文件与文件夹系统操作、系统控制操作、程序...