Sql Server 开窗函数Over()的使用实例详解

简介

开窗函数 (Window Functions)，顾名思义即为 “在一组 rows 中开一个窗，然后计算在这个窗口中的函数”，它是近年来 SQL 中一大特性。

在 SQL Server 2005 版本推出 Window Functions。而在 2012 版本中，提供了更多的 Window Functions 函数，极大的丰富了 SQL 的聚合计算能力，例如 ROW_NUMBER(), RANK(), DENSE_RANK()等函数。

本文将对 SQL Server 开窗函数 Over() 的使用做出详细说明，提供两个具体的实例说明。

Over()函数

Over()函数是窗口函数中最重要的一个函数，用于定义该窗口中计算时的行为。

Over()函数的基本格式为：

OVER ([partition_by_clause] [order_by_clause] [row/clause）

partition_by_clause

通过该语句对数据进行分组，仅对当前分组执行分析函数。如果没有指定 partition_by_clause，则所有行视为一个分组。

order_by_clause

该语句用于对窗口中的行进行排序，如果省略该语句，则不会对窗口中的行做任何排序操作。

row/clause

该语句指定窗口中的行数范围，如果我们没有给出 row/clause，则窗口包含分组的所有行。

更多开窗函数相关语法可查看官方文档。

实例分析

在下面的实例中，我们将通过实现对下面的表进行查询。

数据表 orders:
| 订单号 | 客户ID | 订单日期 | 订单总额 |
| ---- | ---- | ---- | ---- |
| 1 | A1 | 2021-01-01 | 100 |
| 2 | A1 | 2021-01-04 | 50 |
| 3 | A2 | 2021-01-05 | 70 |
| 4 | A2 | 2021-01-06 | 200 |
| 5 | A1 | 2021-01-07 | 300 |

实例1：查询分组后每个客户的订单号及订单总额

SELECT 
  [订单号],
  [客户ID],
  [订单总额],
  SUM ([订单总额])
    OVER (PARTITION BY [客户ID])
    AS [客户总订单额]
FROM
  orders;

执行结果：
| 订单号 | 客户ID | 订单总额 | 客户总订单额 |
| ---- | ---- | ---- | ---- |
| 2 | A1 | 50 | 450 |
| 1 | A1 | 100 | 450 |
| 5 | A1 | 300 | 450 |
| 3 | A2 | 70 | 270 |
| 4 | A2 | 200 | 270 |

分析：

• OVER (PARTITION BY [客户ID]) 按客户ID进行分组，sum([订单总额]) 将当前窗口下客户ID的订单总额累加，输出到整列结果 AS [客户总订单额] 中;
• 最终输出分组后的每个订单号、订单总额和客户总订单额。

实例2：查询分组后每个客户每个订单的对比情况

SELECT 
  *,
  [订单总额]
    - SUM ([订单总额]) OVER(PARTITION BY [客户ID] ORDER BY [订单日期] ASC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
    AS [订单总额差值]
FROM orders;

执行结果：
| 订单号 | 客户ID | 订单日期 | 订单总额 | 订单总额差值 |
| ---- | ---- | ---- | ---- | ---- |
| 1 | A1 | 2021-01-01 | 100 | 0 |
| 2 | A1 | 2021-01-04 | 50 | -50 |
| 5 | A1 | 2021-01-07 | 300 | -150 |
| 3 | A2 | 2021-01-05 | 70 | 0 |
| 4 | A2 | 2021-01-06 | 200 | 130 |

分析：

• OVER(PARTITION BY [客户ID] ORDER BY [订单日期] ASC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) 分组按照客户ID和订单日期排序，[订单总额] 与窗口前一行的总额做差得到 [订单总额差值] ；
• 最终输出分组后每个订单的差值对比结果。

结论

Over()函数的使用，是开窗函数运用的核心，对于 SQL 语言的数据维度扩展，也提供了一个全新的思维视角。

除以上两个示例，OVER()函数还可以完成更多类似计算、筛选等操作，可根据实际需求进行探索和创新。