SQL/OR
与聚集函数一样,窗口函数也针对定义的行集(组)执行聚集,但它不像聚集函数那样每组之返回一个值,窗口函数可以为每组返回多个值。实际上,DB2中称这种函数为联机分析处理OLAP函数,而Oracle把它们称为解析函数,但ISO SQL标准把它们称为窗口函数。窗口函数一般在OLAP分析、制作报表过程中会使用到。
窗口函数:
聚合函数 over()
聚合函数 over(partition by 字段)—分区
聚合函数 over(order by 字段)--框架字句
本文以Oracle11g中HR模式下的Employees表为例子来试着了解窗口函数,
Employees表结构如下:
SQL> desc employees
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
计算部门号位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,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 range between unbounded preceding and current row) as running_total
5 from employees
6* where department_id=30
也就说默认情况下会告诉查询:计算所有行的和,即从当前行开始、包括它前面的所有行。对从当前行开始、包括它前面的所有行进行求和,就可以得到累计和效果了。
通过,框架字句允许定义数据的不同“子窗口”,以便在计算中使用,有很多方式可以指定这样的子窗口。如:
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行。
其中:
range between unbounded preceding and current row 指定计算当前行开始、当前行之前的所有值;
rows between 1 preceding and current row 指定计算当前行的前一行开始,其范围一直延续到当前行;
range between current row and unbounded following 指定计算从当前行开始,包括它后面的所有行;
rows between current row and 1 following 指定计算当前行和它后面的一行;
最后一个例子,展示 了框架字句对查询输出的影响,请看下面查询:
1 select first_name,salary,min(salary) over(order by salary) min1,
2 max(salary) over(order by salary) max1,
3 min(salary) over(order by salary range between unbounded preceding and unbounded following) min2,
4 max(salary) over(order by salary range between unbounded preceding and unbounded following) max2,
5 min(salary) over(order by salary range between current row and current row) min3,
6 max(salary) over(order by salary range between current row and current row) max3,
7 max(salary) over(order by salary rows between 3 preceding and 3 following) max4
8* from employees
SQL> /
FIRST_NAME SALARY MIN1 MAX1 MIN2 MAX2
-------------------- ---------- ---------- ---------- ---------- ----------
MIN3 MAX3 MAX4
---------- ---------- ----------
TJ 2100 2100 2100 2100 24000
2100 2100 2400
Steven 2200 2100 2200 2100 24000
2200 2200 2400
Hazel 2200 2100 2200 2100 24000
2200 2200 2500
请仔细观察计算结果,领会子窗口的内涵;
---------------------
作者:mfkpie
来源:CSDN
原文:https://blog.csdn.net/mfkpie/article/details/16364513
版权声明:本文为博主原创文章,转载请附上博文链接!