与聚集函数一样，窗口函数也针对定义的行集（组）执行聚集，但它不像聚集函数那样每组之返回一个值，窗口函数可以为每组返回多个值。实际上，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> edit1  select first_name,department_id,count(*) over()2  from employees3* where department_id=20
SQL> /FIRST_NAME           DEPARTMENT_ID COUNT(*)OVER()                               
-------------------- ------------- --------------                               
Michael                         20              2                               
Pat                             20              2         

 

窗口 ，函数 count(*) over() 对于查询返回的每一行，它返回了表中所有行的计数。

在深入研究Over字句之前，一定要注意：在SQL处理中，窗口函数都是最后一步执行，而且仅位于Order by字句之前。

 

1. 分区

使用Partiton by字句定义行的分区或组，可以用paritition by对定义的行组计算聚集（当遇到新组的时候复位），并返回每个值（每个组中的每个成员），而不是一个用一个组表示表中的这个值的所有实例。如：

SQL> edit1  select first_name,department_id,count(*) over(partition by department_id) as cnt2  from employees3* 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_cnt4  from employees5* 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     

 

 

1. 框架字句：

当在窗口函数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_total5  from employees6* 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_total5  from employees6* 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_total46  from employees7* 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 betweenunbounded preceding and currentrow 指定计算当前行开始、当前行之前的所有值；

rows between1 preceding and currentrow 指定计算当前行的前一行开始，其范围一直延续到当前行；

range between currentrow andunbounded following 指定计算从当前行开始，包括它后面的所有行；

rows between currentrow and1 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) max48* 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      

请仔细观察计算结果，领会子窗口的内涵；

参见：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.ProductIDORDER BY s.ShipDateROWS BETWEEN UNBOUNDED PRECEDINGAND 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	CrewNeck	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	CottonCap	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	PlasticVisor	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	ZippedSweatshirt	60	2001-07-19	60
12	700	Cotton Shorts	24	2001-07-26	24

在此示例中，要在连接两个表和应用查询的 WHERE 子句之后，才执行 SUM 窗口函数的计算。查询会按如下方式进行：

1. 根据值 ProductID 分区（分组）输入行。

2. 在每个分区内，根据 ShipDate 的值对行进行排序。

3. 对于分区中的每一行，通过由各分区中（经过排序的）第一行直到当前行并包括当前行所组成的滑动窗口，使用 SUM 函数对 Quantity 中的值进行求值。

 WINDOW 子句定义

上述查询的另一种结构是，使用 WINDOW 子句在使用窗口的函数中单独指定窗口，然后在各函数的 OVER 子句内引用窗口。

在此示例中，WINDOW 子句创建名为 Cumulative、按 ProductID 对数据分区并按 ShipDate 进行排序的窗口。SUM 函数在其 OVER 子句中引用窗口，并使用 ROWS 子句定义窗口大小。

SELECT p.ID, p.Description, s.Quantity, s.ShipDate,SUM( s.Quantity ) OVER ( CumulativeROWS BETWEEN UNBOUNDED PRECEDINGAND 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 ( windowAORDER BY expression )... ... WINDOW windowA AS ( PARTITION BY expression )

使用此方式分隔窗口定义时有以下限制：

• 不能在窗口函数语法中使用 PARTITION BY 子句。

• 可以在窗口函数语法或 WINDOW 子句中使用 ORDER BY 子句，但不能在二者中同时使用。

• 不能在 WINDOW 子句中包括 RANGE 或 ROWS 子句。