开窗函数over()

1,711 阅读9分钟

前言

  这个函数是笔者在如下的需求下接触到的。
  有一个表是用于记录学生进出学校信息的,属性主要包含:学生id(varchar)、进出校时间(timestamp)、进出校标志(char)。
  现在需要根据这个表生成一个学生完整的进出校数据,属性包含:学生id(varchar)、进校时间(timestamp)、出校时间(timestamp)。
  思路:先得到进出校标志(char)为0(进校)的结果集,再得到进出校标志(char)为1(出校)的结果集。排序后再进行左连接。就可以得到想要的结果集。问题是怎么把左右的结果集连接起来?

over()函数简介

  sql server对over()函数的定义:Determines the partitioning and ordering of a rowset before the associated window function is applied. That is, the OVER clause defines a window or user-specified set of rows within a query result set. A window function then computes a value for each row in the window. You can use the OVER clause with functions to compute aggregated values such as moving averages, cumulative aggregates, running totals, or a top N per group results.(在应用关联的窗口函数之前,确定行集的分区和顺序。 即,OVER子句定义查询结果集中的窗口或用户指定的行集。 然后,窗口函数将为窗口中的每一行计算一个值。 可以将OVER子句与函数一起使用,以计算聚合值,例如移动平均值,累积聚合,运行总计或每组结果的前N个。)

解决

  在这里我用的比较简单

SELECT a.stu_id, a.entry_dt, b.entry_dt
from
(SELECT ROW_NUMBER() over(ORDER BY entry_dt), stu_id, entry_daytime, entry_flg from entry_exit_hst a WHERE a.entry_flg = '0')
as a
left join
(SELECT ROW_NUMBER() over(ORDER BY entry_dt), stu_id, entry_daytime, entry_flg from entry_exit_hst a WHERE a.entry_flg = '1')
as b
on 
(a.row_number = b.row_number)

  在这里我使用了over()方法再返回数据集之前先把数据按照子查询进行了排序,然后再结合row_number()函数给每一行记录赋予编号,最后就可以用编号把左右两个结果集连接起来得到最终的结果集。

拓展

  以下是sql server的关于over()函数的语法定义   

-- Syntax for SQL Server, Azure SQL Database, and Azure SQL Data Warehouse  
  
OVER (   
       [ <PARTITION BY clause> ]  
       [ <ORDER BY clause> ]   
       [ <ROW or RANGE clause> ]  
      )  
  //根据属性分区
<PARTITION BY clause> ::=  
PARTITION BY value_expression , ... [ n ]  
  //根据属性排序
<ORDER BY clause> ::=  
ORDER BY order_by_expression  
    [ COLLATE collation_name ]   
    [ ASC | DESC ]   
    [ ,...n ]  
  //根据row|range子句缩小结果集范围
<ROW or RANGE clause> ::=  
{ ROWS | RANGE } <window frame extent>  
  
<window frame extent> ::=   
{   <window frame preceding>  
  | <window frame between>  
}  
<window frame between> ::=   
  BETWEEN <window frame bound> AND <window frame bound>  
  
<window frame bound> ::=   
{   <window frame preceding>  
  | <window frame following>  
}  
  
<window frame preceding> ::=   
{  
    UNBOUNDED PRECEDING  
  | <unsigned_value_specification> PRECEDING  
  | CURRENT ROW  
}  
  
<window frame following> ::=   
{  
    UNBOUNDED FOLLOWING  
  | <unsigned_value_specification> FOLLOWING  
  | CURRENT ROW  
}  
  
<unsigned value specification> ::=   
{  <unsigned integer literal> }  

  以下是参数的解释:

  • PARTITION BY clause

  将查询结果集划分为多个分区。 窗口函数分别应用于每个分区,并且每个分区的计算重新开始。

  • value_expression

  指定对行集进行分区的列。 value_expression只能引用FROM子句提供的列。 value_expression不能引用选择列表中的表达式或别名。 value_expression可以是列表达式,标量子查询,标量函数或用户定义的变量。

  • ORDER BY clause

  定义结果集每个分区中行的逻辑顺序。 即,它指定执行窗口函数计算的逻辑顺序。

  • order_by_expression

  指定要排序的列或表达式。 order_by_expression仅可以引用FROM子句提供的列。 不能指定整数来表示列名或别名。

  • COLLATE collation_name
      指定应根据collation_name中指定的排序规则执行ORDER BY操作。 collation_name可以是Windows归类名称或SQL归类名称。 有关更多信息,请参见排序规则和Unicode支持。 COLLATE仅适用于char,varchar,nchar和nvarchar类型的列。
  • ROWS | RANGE

  通过指定分区内的起点和终点来进一步限制分区内的行。这是通过逻辑关联或物理关联指定相对于当前行的行范围来完成的。物理关联是通过使用ROWS子句实现的。

  ROWS子句通过指定当前行之前或之后的固定行数来限制分区中的行。或者,RANGE子句通过指定相对于当前行中的值的值范围来逻辑地限制分区中的行。根据ORDER BY子句中的顺序定义前行和后行。窗口框架“ RANGE ... CURRENT ROW ...”包括ORDER BY表达式中与当前行具有相同值的所有行。例如,ROWS BETWEEN 2 PRECEDING AND CURRENT ROW表示该函数所作用的行的窗口大小为三行,从前两行开始直到当前行为止。

  /注意:ROWS或RANGE要求指定ORDER BY子句。如果ORDER BY包含多个顺序表达式,则CURRENT ROW FOR RANGE在确定当前行时会考虑ORDER BY列表中的所有列。/

局限性

  OVER子句不能与CHECKSUM聚合函数一起使用。

  RANGE不能与<无符号值规范> PRECEDING或<无符号值规范> FOLLOWING一起使用。

  根据与OVER子句一起使用的排名,汇总或分析功能,可能不支持<ORDER BY子句>和/或<ROWS和RANGE子句>。

over()函数使用例子

  1. 与row_number()一同使用

下面的示例显示将OVER子句与ROW_NUMBER函数一起使用以显示分区中每一行的行号。OVER子句中指定的ORDER BY子句按列对每个分区中的行进行排序SalesYTD。SELECT语句中的ORDER BY子句确定返回整个查询结果集的顺序。 sql:

SELECT ROW_NUMBER() OVER(PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS "Row Number",   
    p.LastName, s.SalesYTD, a.PostalCode  
FROM Sales.SalesPerson AS s   
    INNER JOIN Person.Person AS p   
        ON s.BusinessEntityID = p.BusinessEntityID  
    INNER JOIN Person.Address AS a   
        ON a.AddressID = p.BusinessEntityID  
WHERE TerritoryID IS NOT NULL   
    AND SalesYTD <> 0  
ORDER BY PostalCode;  
GO

结果集:

Row Number LastName SalesYTD PostalCode
1 Mitchell 4251368.5497 98027
2 Blythe 3763178.1787 98027
3 Carson 3189418.3662 98027
4 Reiter 2315185.611 98027
5 Vargas 1453719.4653 98027
6 Ansman-Wolfe 1352577.1325 98027
1 Pak 4116871.2277 98055
2 Varkey Chudukatil 3121616.3202 98055
3 Saraiva 2604540.7172 98055
4 Ito 2458535.6169 98055
5 Valdez 1827066.7118 98055
6 Mensa-Annan 1576562.1966 98055
7 Campbell 1573012.9383 98055
8 Tsoflias 1421810.9242 98055
  1. 与聚合函数一同使用

下面的示例OVER在查询返回的所有行上使用带有聚合函数的子句。在此示例中,使用OVER子句比使用子查询导出聚合值更有效。

sql:

SELECT SalesOrderID, ProductID, OrderQty  
    ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS Total  
    ,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Avg"  
    ,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Count"  
    ,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Min"  
    ,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Max"  
FROM Sales.SalesOrderDetail   
WHERE SalesOrderID IN(43659,43664);  
GO  

结果集:

SalesOrderID ProductID OrderQty Total Avg Count Min Max
43659 776 1 26 2 12 1 6
43659 777 3 26 2 12 1 6
43659 778 1 26 2 12 1 6
43659 771 1 26 2 12 1 6
43659 772 1 26 2 12 1 6
43659 773 2 26 2 12 1 6
43659 774 1 26 2 12 1 6
43659 714 3 26 2 12 1 6
43659 716 1 26 2 12 1 6
43659 709 6 26 2 12 1 6
43659 712 2 26 2 12 1 6
43659 711 4 26 2 12 1 6
43664 772 1 14 1 8 1 4
43664 775 4 14 1 8 1 4
43664 714 1 14 1 8 1 4
43664 716 1 14 1 8 1 4
43664 777 2 14 1 8 1 4
43664 771 3 14 1 8 1 4
43664 773 1 14 1 8 1 4
43664 778 1 14 1 8 1 4
  1. 产生移动平均值和累计总计

以下示例将AVG和SUM函数与OVER子句结合使用,以提供Sales.SalesPerson表中每个区域的移动平均数和年销售额的累计总数。数据按进行分区TerritoryID并按逻辑顺序排序SalesYTD。这意味着将根据销售年份为每个地区计算AVG函数。请注意,对于TerritoryID1,2005年销售年度有两行,分别代表当年销售的两个销售人员。计算这两行的平均销售额,然后在计算中包括代表2006年销售额的第三行。

sql:

SELECT BusinessEntityID, TerritoryID   
   ,DATEPART(yy,ModifiedDate) AS SalesYear  
   ,CONVERT(varchar(20),SalesYTD,1) AS  SalesYTD  
   ,CONVERT(varchar(20),AVG(SalesYTD) OVER (PARTITION BY TerritoryID   
                                            ORDER BY DATEPART(yy,ModifiedDate)   
                                           ),1) AS MovingAvg  
   ,CONVERT(varchar(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID   
                                            ORDER BY DATEPART(yy,ModifiedDate)   
                                            ),1) AS CumulativeTotal  
FROM Sales.SalesPerson  
WHERE TerritoryID IS NULL OR TerritoryID < 5  
ORDER BY TerritoryID,SalesYear;  

结果集:

BusinessEntityID TerritoryID SalesYear SalesYTD MovingAvg CumulativeTotal
274 NULL 2005 559,697.56 559,697.56 559,697.56
287 NULL 2006 519,905.93 539,801.75 1,079,603.50
285 NULL 2007 172,524.45 417,375.98 1,252,127.95
283 1 2005 1,573,012.94 1,462,795.04 2,925,590.07
280 1 2005 1,352,577.13 1,462,795.04 2,925,590.07
284 1 2006 1,576,562.20 1,500,717.42 4,502,152.27
275 2 2005 3,763,178.18 3,763,178.18 3,763,178.18
277 3 2005 3,189,418.37 3,189,418.37 3,189,418.37
276 4 2005 4,251,368.55 3,354,952.08 6,709,904.17
281 4 2005 2,458,535.62 3,354,952.08 6,709,904.17
  1. 指定ROWS子句

下面的示例使用ROWS子句定义一个窗口,在该窗口上将行计算为当前行,然后计算N行(此示例中为1行)。

sql:

SELECT BusinessEntityID, TerritoryID   
    ,CONVERT(varchar(20),SalesYTD,1) AS  SalesYTD  
    ,DATEPART(yy,ModifiedDate) AS SalesYear  
    ,CONVERT(varchar(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID   
                                             ORDER BY DATEPART(yy,ModifiedDate)   
                                             ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING ),1) AS CumulativeTotal  
FROM Sales.SalesPerson  
WHERE TerritoryID IS NULL OR TerritoryID < 5;  

结果集:

BusinessEntityID TerritoryID SalesYTD SalesYear CumulativeTotal
274 NULL 559,697.56 2005 1,079,603.50
287 NULL 519,905.93 2006 692,430.38
285 NULL 172,524.45 2007 172,524.45
283 1 1,573,012.94 2005 2,925,590.07
280 1 1,352,577.13 2005 2,929,139.33
284 1 1,576,562.20 2006 1,576,562.20
275 2 3,763,178.18 2005 3,763,178.18
277 3 3,189,418.37 2005 3,189,418.37
276 4 4,251,368.55 2005 6,709,904.17
281 4 2,458,535.62 2005 2,458,535.62

参考文献

docs.microsoft.com/en-us/sql/t…