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 andcurrent row 指定计算当前行开始、当前行之前的所有值;
rowsbetween 1 preceding andcurrent row 指定计算当前行的前一行开始,其范围一直延续到当前行;
range betweencurrent row and unbounded following 指定计算从当前行开始,包括它后面的所有行;
rowsbetweencurrent 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
<a href="https://www.itdaan.com/link/aHR0cDovL3NlYXJjaC5taWNyb3NvZnQuY29tL2RlZmF1bHQuYXNwP3NvPVJFQ0NOVCZzaXRlaWQ9dXMlMkZkZXYmcD0xJm5xPU5FVyZxdT1NSU4mSW50bFNlYXJjaD0mYm9vbGVhbj1QSFJBU0UmaWc9MDEmaT0wOSZpPTk5" rel="nofollow" title="MIN">MIN</a>3 <a href="https://www.itdaan.com/link/aHR0cDovL3NlYXJjaC5taWNyb3NvZnQuY29tL2RlZmF1bHQuYXNwP3NvPVJFQ0NOVCZzaXRlaWQ9dXMlMkZkZXYmcD0xJm5xPU5FVyZxdT1NQVgmSW50bFNlYXJjaD0mYm9vbGVhbj1QSFJBU0UmaWc9MDEmaT0wOSZpPTk5" rel="nofollow" title="MAX">MAX</a>3 <a href="https://www.itdaan.com/link/aHR0cDovL3NlYXJjaC5taWNyb3NvZnQuY29tL2RlZmF1bHQuYXNwP3NvPVJFQ0NOVCZzaXRlaWQ9dXMlMkZkZXYmcD0xJm5xPU5FVyZxdT1NQVgmSW50bFNlYXJjaD0mYm9vbGVhbj1QSFJBU0UmaWc9MDEmaT0wOSZpPTk5" rel="nofollow" title="MAX">MAX</a>4
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
请仔细观察计算结果,领会子窗口的内涵;
参见:SQL CookBook
SYBASE ASA :
窗口定义:内置使用 OVER 子句和 WINDOW 子句
定义窗口的方法有三种:
-
内置(在窗口函数的 OVER 子句中)
-
在 WINDOW 子句中
-
部分内置,部分在 WINDOW 子句中
然而,有些方法存在限制,如以下各节中所述。
正在上传…重新上传取消 内置定义(在窗口函数的 OVER 子句中)
窗口定义可以放在窗口函数的 OVER 子句中。这称为以内置 方式定义窗口。
例如,以下语句在示例数据库中查询 2001 年 7 月和 8 月发运的所有产品,以及截至发运日期的累计发运量。窗口采用内置方式定义。
SELECT p.ID, p.Description, s.Quantity, s.ShipDate, SUM( s.Quantity ) OVER ( PARTITION BY s.ProductID ORDER BY s.ShipDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS Cumulative_qty FROM SalesOrderItems s JOIN Products p ON ( s.ProductID = p.ID ) WHERE s.ShipDate BETWEEN '2001-07-01' AND '2001-08-31' ORDER BY p.ID; |
此查询会返回以下结果:
ID | Description | Quantity | ShipDate | Cumulative_qty | |
---|---|---|---|---|---|
1 | 301 | V-neck | 24 | 2001-07-16 | 24 |
2 | 302 | Crew Neck | 60 | 2001-07-02 | 60 |
3 | 302 | Crew Neck | 36 | 2001-07-13 | 96 |
4 | 400 | Cotton Cap | 48 | 2001-07-05 | 48 |
5 | 400 | Cotton Cap | 24 | 2001-07-19 | 72 |
6 | 401 | Wool Cap | 48 | 2001-07-09 | 48 |
7 | 500 | Cloth Visor | 12 | 2001-07-22 | 12 |
8 | 501 | Plastic Visor | 60 | 2001-07-07 | 60 |
9 | 501 | Plastic Visor | 12 | 2001-07-12 | 72 |
10 | 501 | Plastic Visor | 12 | 2001-07-22 | 84 |
11 | 601 | Zipped Sweatshirt | 60 | 2001-07-19 | 60 |
12 | 700 | Cotton Shorts | 24 | 2001-07-26 | 24 |
在此示例中,要在连接两个表和应用查询的 WHERE 子句之后,才执行 SUM 窗口函数的计算。查询会按如下方式进行:
-
根据值 ProductID 分区(分组)输入行。
-
在每个分区内,根据 ShipDate 的值对行进行排序。
-
对于分区中的每一行,通过由各分区中(经过排序的)第一行直到当前行并包括当前行所组成的滑动窗口,使用 SUM 函数对 Quantity 中的值进行求值。
上述查询的另一种结构是,使用 WINDOW 子句在使用窗口的函数中单独指定窗口,然后在各函数的 OVER 子句内引用窗口。
在此示例中,WINDOW 子句创建名为 Cumulative、按 ProductID 对数据分区并按 ShipDate 进行排序的窗口。SUM 函数在其 OVER 子句中引用窗口,并使用 ROWS 子句定义窗口大小。
SELECT p.ID, p.Description, s.Quantity, s.ShipDate, SUM( s.Quantity ) OVER ( Cumulative ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS cumulative_qty FROM SalesOrderItems s JOIN Products p ON ( s.ProductID = p.ID ) WHERE s.ShipDate BETWEEN '2001-07-01' AND '2001-08-31' WINDOW Cumulative AS ( PARTITION BY s.ProductID ORDER BY s.ShipDate ) ORDER BY p.ID; |
当使用 WINDOW 子句语法时,以下限制将适用:
-
如果指定 PARTITION BY 子句,则必须将其置于 WINDOW 子句内。
-
如果指定 ROWS 或 RANGE 子句,则必须将其置于引用函数的 OVER 子句中。
-
如果为窗口指定 ORDER BY 子句,可将该子句置于 WINDOW 子句中,或者置于引用函数的 OVER 子句中,但不能同时置于二者之中。
-
WINDOW 子句必须位于 SELECT 语句的 ORDER BY 子句之前。
正在上传…重新上传取消 内置与 WINDOW 子句定义相结合
可以内置一部分窗口定义,然后在 WINDOW 子句中定义剩余部分。例如:
AVG() OVER ( windowA ORDER BY expression )......WINDOW windowA AS ( PARTITION BY expression ) |
使用此方式分隔窗口定义时有以下限制:
-
不能在窗口函数语法中使用 PARTITION BY 子句。
-
可以在窗口函数语法或 WINDOW 子句中使用 ORDER BY 子句,但不能在二者中同时使用。
-
不能在 WINDOW 子句中包括 RANGE 或 ROWS 子句。