SQL中的窗口函数OVER窗口函数

若♡

已于 2022-09-27 09:32:20 修改

阅读量591

点赞数

文章标签： sql 数据库 oracle

于 2022-09-15 09:38:56 首次发布

本文链接：https://blog.csdn.net/weixin_67271870/article/details/126841142

版权

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

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

参见：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 子句定义

上述查询的另一种结构是，使用 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 子句。

若♡

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
SQL中的窗口函数OVER窗口函数

使用Partiton by字句定义行的分区或组，可以用paritition by对定义的行组计算聚集（当遇到新组的时候复位），并返回每个值（每个组中的每个成员），而不是一个用一个组表示表中的这个值的所有实例。对于分区中的每一行，通过由各分区中（经过排序的）第一行直到当前行并包括当前行所组成的滑动窗口，使用 SUM 函数对 Quantity 中的值进行求值。窗口采用内置方式定义。上述查询的另一种结构是，使用 WINDOW 子句在使用窗口的函数中单独指定窗口，然后在各函数的 OVER 子句内引用窗口。
复制链接

扫一扫