SQL Wizardry 第六部分 - 窗口函数

Christian Graus

4.70/5 (9投票s)

2014年1月12日

CPOL

11分钟阅读

27170

241

讨论窗口函数，从 sum 到 row_number()，以及 SS2012 中的新函数。

下载 SQL_Wizardry_06.zip - 3.1 KB

引言

今天我想向大家介绍窗口函数。你可能已经熟悉一些了，所以我们从这些开始。sum、avg 和 count 都能够根据列的内容获取总数。例如，在 AdventureWorks 中，以下代码将显示销售表中订单的总和、平均值和计数：

select sum(unitprice * orderqty) as orderSum,  avg(unitprice*orderqty)as orderAvg, count(unitprice * orderqty) as orderCount from  sales.salesOrderDetail

如果你尝试添加 productid，你会得到一个错误，因为你有一些聚合列和一些单一数据列。

select productId, sum(unitprice * orderqty) as orderSum,  avg(unitprice*orderqty)as orderAvg, count(unitprice * orderqty) as orderCount from  sales.salesOrderDetail

最常见的解决方法是使用“group by”。

select productId, sum(unitprice * orderqty) as orderSum,  avg(unitprice*orderqty)as orderAvg, count(unitprice * orderqty) as orderCount from  sales.salesOrderDetail group by productid

正如你可能知道的，“group by”告诉聚合函数什么构成了一组需要被考虑用于单个结果的数据。我们可以使用“group by”而不指定单个值（如产品 ID），但当然，数据会难以阅读，如果我们添加产品 ID，我们确实需要按它分组才能使 SQL 工作。

还有另一种方法可以做到这一点，如下所示：

select distinct productId, sum(unitprice * orderqty) over (partition by productid) as orderSum ,  avg(unitprice*orderqty) over (partition by productid) as orderAvg, count(unitprice * orderqty) over (partition by productid) as orderCount from  sales.salesOrderDetail

我使用 distinct 而不是“group by”来说明在这种情况下不再需要“group by”。“over”语句允许我们告诉 SQL 如何对数据进行分区。这比我们的第一个例子更冗长，而且没有明确的用途。但是，如果我们需要不同的列使用不同的分组呢？

select distinct salesorderid, productId, avg(unitprice * orderqty) over (partition by salesOrderId) as avgByOrder ,  avg(unitprice*orderqty) over (partition by productid) as avgByProduct from  sales.salesOrderDetail order by salesorderid, productid

这为我们提供了这些项目的平均订单价格和平均产品价格。我不确定这是一个非常有用的例子，但它确实表明我们能够定义两个组，而无需使用 CTE，并相应地获取数据。

其他窗口函数

还有许多其他可用的窗口函数，它们的功能远比计数或聚合值强大。它们之所以都被称为窗口函数，是因为它们对“数据窗口”进行操作，即使一个计算总和的简单语句对整个数据集进行操作，那仍然是一个窗口，并且窗口可以通过我所示的两种方式缩小。

我将向您展示的第一个函数自 SS 2005 以来就存在。最后，我将向您展示 SS2012 中的一些新功能。

对于其中一些示例，我们将创建一个旨在记录网络访问的表，它包含用户 ID、登录日期时间以及可以为 null 的注销日期时间。

create table trackAccess
(
  userId int not null,
  login datetime not null,
  logout datetime
)

这周我们会在一些地方跳来跳去，所以最简单的方法可能是在 AdventureWorks 中创建它并在最后删除它。

ROW_NUMBER()

第一个函数很简单。它只是创建一个带有行号的列。

首先运行这个

select userId, row_number() over (partition by userid order by login) as login from trackAccess

你可以看到，它为每个用户 ID 创建一个数字序列，为每一行分配一个数字。

with logins as
(
  select userId, row_number() over (partition by userid order by login) as login from trackAccess
)

select userId, max(login) as logins from logins group by userId

这将获取最大数字，然后就是某人登录的次数。

当然，你不需要使用 row_number 来完成此操作，你可以这样做：

select userid, count(login) as logins from trackAccess group by userId

甚至可以使用我上面向您展示的语法

select distinct userId, count(login) over (partition by userId) as loginCount from trackAccess

请再次注意，我在这里使用 distinct 是为了明确使用此语法不需要 group by，但在实际情况中，我可能会使用 group by。

但是，如果我想找出最早的登录日期和时间，那么这是一种方法：

;with logins as
(
  select userId, login, row_number() over (partition by userid order by login) as loginOrder from trackAccess
)

select userId, login from logins where loginOrder = 1;

这是使用 row_number 的另一个更有用的功能，它按所需顺序在整个表中分配数字，这样我就可以一次获取一页数据：

declare @pageSize int = 20
declare @page int = 5

;with data
as
(
select row_number() over (order by modifieddate) as rowNo, salesOrderId, UnitPrice, OrderQty from Sales.salesorderdetail
)

select * from data where rowNo >= ((@page - 1) * @pageSize) and rowNo < (@page * @pageSize);

这种方法可以用于任何可以指定顺序但没有数字序列的数据分页。

请注意，如果您有一个数字主键 ID，这并不意味着没有孤岛（由删除引起），所以这可能是在几乎所有情况下进行分页的好方法。

删除重复记录

Row_number() 可用于删除重复记录。首先，我们将创建一个包含大量重复记录的表。

create table duplicates
(
  id int
)

insert into duplicates values (1),(1),(1),(1),(1),(1),(2),(2),(2),(2),(2),(2),(2),(3),(3),(3),(3),(3),(3),(3)

现在我们将构建一个查询来识别它们。请注意，如果您有多个列，则必须按所有要识别为重复的列进行分区，这就是 partition by 的作用，它根据您识别的组创建序列。

with dups
as
(
  select id, row_number() over (partition by id order by id) as row from duplicates 
)
select * from dups

这向我们展示了将用于执行删除的数据。我们现在所做的就是更改最后一条语句：

-- delete duplicates
with dups
as
(
  select id, row_number() over (partition by id order by id) as row from duplicates 
)
delete from dups where row > 1

而现在这个

select * from duplicates

将只返回三行，每行对应一个 ID 值。

在序列中寻找孤岛

row number 的一个优点是，它可以在数据序列中查找孤岛。这意味着，如果您有一个像 1,2,3,4,5... 这样的序列，可以找到序列中的空白。使用提供的 SQL 创建孤岛表。

然后运行这个

select id, row_number() over(order by id) as sort from islands

正如你所预料的，我们的序列与 ID 失去了同步，因为它里面没有孤岛。

现在，这是诀窍

select id, id - row_number() over(order by id) as sort from islands

正如你所看到的，id - row_number 意味着一个值上升，另一个值下降。一旦出现孤岛，就会出现一个一次性的间隙，导致计算出的数字发生变化，然后它保持不变。我们可以将这个值用作 group by 来创建一个简单的查询，显示我们的孤岛：

;with island as
(
	select id, id - row_number() over(order by id) as sort from islands
)

select min(id) as start, max(id) as [end] from island group by sort;

CTE 的主要原因是我的 group by 的排序是命名的。这非常优雅，在我知道这个技巧之前，我不得不查找数据中的孤岛，代码要长得多，可读性也差得多。

RANK() 和 DENSE_RANK()

rank 函数做类似的事情，但它为 order by 列中具有相同值的行赋予相同的值。

所以这个

SELECT i.ProductID, p.Name, i.LocationID, i.Quantity
    ,RANK() OVER 
    (PARTITION BY i.LocationID ORDER BY i.Quantity DESC) AS Rank
FROM Production.ProductInventory AS i 
INNER JOIN Production.Product AS p 
    ON i.ProductID = p.ProductID
WHERE i.LocationID BETWEEN 3 AND 4
ORDER BY i.LocationID;

创建的序列如下：

ProductID	Name	LocationID	Quantity	Rank
494	Paint - Silver	3	49	1
495	Paint - Blue	3	49	1
493	Paint - Red	3	41	3
496	Paint - Yellow	3	30	4
492	Paint - Black	3	17	5
495	Paint - Blue	4	35	1
496	Paint - Yellow	4	25	2
493	Paint - Red	4	24	3
492	Paint - Black	4	14	4
494	Paint - Silver	4	12	5

请注意，当 locationid（我们的分区值）改变时，它会从 1 重新开始，并且它会从 1 跳到 3，因为有两列“1”，它们都具有相同的数量。

DENSE_RANK 的作用相同，但是，即使有两个相同的值，它也不会跳过数字。

SELECT i.ProductID, p.Name, i.LocationID, i.Quantity
    ,DENSE_RANK() OVER 
    (PARTITION BY i.LocationID ORDER BY i.Quantity DESC) AS Rank
FROM Production.ProductInventory AS i 
INNER JOIN Production.Product AS p 
    ON i.ProductID = p.ProductID
WHERE i.LocationID BETWEEN 3 AND 4
ORDER BY i.LocationID;

NTILE

NTILE 用于将数据分割成 n 个分区，使其大小尽可能接近相等。

select userId, login, ntile(5) over (order by login) as loginOrder from trackAccess

会将我们所有的登录信息分成 5 个大小相等、不考虑用户 ID 的组。

select userId, login, ntile(5) over (partition by userid order by login) as loginOrder from trackAccess

将尝试创建 5 个分区，但它无法做到，因此最终会按用户 ID 进行 row_number() 分割。

select userId, login, ntile(5) over (order by userid) as loginOrder from trackAccess

按用户 ID 排序，但在这里你可以非常清楚地看到，它与 ID 不匹配，它只是使用该顺序来创建 5 个相等的组。

从现在开始，如果你没有 SS2012，这些例子将无法工作，但我仍然建议继续阅读，以了解这些新函数。

聚合函数的“Order by”

我已经向你展示了如何使用“OVER”来分区 SUM、AVG 或 COUNT。然而，你也可以这样做：

select distinct userId, count(login) over (partition by userId order by login) as loginCount from trackAccess

看起来不那么有趣，因为它做了什么不清楚。试试这个：

select top 100 unitprice from Sales.SalesOrderDetail order by salesOrderId, productId

现在尝试使用分区

select top 100 SalesOrderId, sum(UnitPrice) over (partition by SalesOrderId) from Sales.SalesOrderDetail order by SalesOrderId

这现在向我们显示了每个订单的总价格，这就是为什么相同的价值每行重复一次。

当我们添加“order by”时会发生什么？

select top 100 SalesOrderId, sum(UnitPrice) over (partition by SalesOrderId order by productId) from Sales.SalesOrderDetail order by SalesOrderId

Order by 做了一件非常有趣的事情，同时（最初）也违反直觉。它给了我们一个运行总计。数字不断攀升，直到达到我们之前看到的总计，因为它们与它们之前的所有值一起被聚合。

LEAD 和 LAG

Lead 和 Lag 允许我们告诉 SQL Server 从当前行向前或向后查找 X 行。例如，这里是使用 Lag 的代码，用于显示每次登录的前一次登录时间：

select userId, login, lag(login, 1, null) over (partition by userid order by login) as lastLogin from trackAccess

下一个查询使用 lead 查找下一次登录，然后从其中减去当前注销时间，这样我们就得到了两次登录之间的时间量：

select userid, cast( (lead(login, 1, null) over (partition by userid order by login) - logout) as time) as timeNotLoggedIn from trackAccess

请注意，在这种情况下，“null”表示“无限”，此用户没有记录进一步的登录。

FIRST_VALUE 和 LAST_VALUE

您可以直接跳转到窗口中的第一个或最后一个值，而不是按特定数字向前或向后。这里是第一次尝试：

select userId, first_value(login) over (partition by userId order by login) as firstLogin, LAST_VALUE(login) over (partition by userId order by login) as lastLogin from trackAccess

这将返回以下内容：

userId	firstLogin	lastLogin
1	2012-01-01 10:00:00.000	2012-01-01 10:00:00.000
1	2012-01-01 10:00:00.000	2012-01-02 09:32:12.000
1	2012-01-01 10:00:00.000	2012-01-03 10:04:53.000
2	2012-01-01 09:08:32.000	2012-01-01 09:08:32.000
2	2012-01-01 09:08:32.000	2012-01-02 09:12:32.000
2	2012-01-01 09:08:32.000	2012-01-03 10:12:41.000
2	2012-01-01 09:08:32.000	2012-01-03 14:27:21.000
3	2012-01-01 08:40:27.000	2012-01-01 08:40:27.000
3	2012-01-01 08:40:27.000	2012-01-02 11:12:13.000
3	2012-01-01 08:40:27.000	2012-01-04 15:27:43.000

看起来总是找到第一个值，但会为数据库中的每个可能的行返回一个最后一个值。

发生了什么？

还记得我们上面看到的 SUM 或 AVG 上的窗口函数现在可以有 order by 吗？这里的语法是相同的，我们得到的结果也是相同的。您可能还记得，使用 order by 会导致 SQL Server 返回累积总和？这是因为您可以指定窗口操作的范围。您可以用 ROWS 或 RANGE 来指定。区别在于 RANGE 指定逻辑关联，而 ROW 指定数据集中物理位置。我承认我不太确定这到底意味着什么，我总是使用 ROWS。

以下是一些示例值：

ROWS x PRECEDING - 这意味着向后查看 x 行（RANGE 不允许）ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW - 所有之前的行和当前行 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING - 所有行 ROWS x FOLLOWING - 此行之后的 x 行（如果存在）（RANGE 不允许）

此值的默认值为 BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。这就是为什么使用 orderby 会给我们一个累积总和，因为默认情况下，它会从数据窗口的开始到当前行对值进行求和或求平均值。这也是为什么我们得到这么多值，因为默认情况下，它返回了所有可能的最后一行，而没有检查当前行之后的行。

现在运行此

select distinct userId, first_value(login) over (partition by userId order by login) as firstLogin, LAST_VALUE(login) over (partition by userId order by login ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as lastLogin from trackAccess

我们需要 DISTINCT，否则它将为数据库中的每一行返回一行，但现在我们查找窗口末尾范围内的最后一行，我们得到所需的结果，没有其他。

OFFSET 和 FETCH NEXT

在 SS2012 中，有新的函数使分页比使用 row_number() 更容易。它们仅在您的 select 语句包含 order by 时才有效（因为否则顺序不确定，并且行无法具有特定的、一致的位置）。您可以指定 OFFSET 跳过一定数量的行，并使用 FETCH NEXT 指定要选择的行数。您不能在没有 OFFSET 的情况下使用 FETCH NEXT，但可以在没有 FETCH 语句的情况下使用 OFFSET。这是一个示例：

declare @pageSize int = 20
declare @page int = 5

SELECT
	TransactionID
	, ProductID
	, TransactionDate
	, Quantity
	, ActualCost
	FROM
	Production.TransactionHistory
	ORDER BY TransactionDate DESC
	OFFSET (@page - 1) * @pagesize ROWS
	FETCH NEXT @pagesize ROWS ONLY;

FETCH 可以使用 FIRST 或 NEXT，但它们都表示相同的意思。我推测这是游标中 FETCH 语法变化的原因。你也可以使用 ROW 或 ROWS。

显然，TOP 不能与 OFFSET 和 FETCH 一起使用。

统计函数

有四个新的统计函数。

CUME_DIST

计算 SQL Server 2012 中一组值中某个值的累积分布。也就是说，CUME_DIST 计算指定值在一组值中的相对位置。对于一行 r，假设升序排序，r 的 CUME_DIST 是值小于或等于 r 的行数，除以分区或查询结果集中评估的行数。CUME_DIST 类似于 PERCENT_RANK 函数。

PERCENTILE_CONT

计算 SQL Server 2012 中列值连续分布的百分位数。结果是插值的，可能不等于列中的任何特定值。

PERCENTILE_DISC

在 SQL Server 2012 中，计算整个行集或行集的不同分区中排序值的特定百分位数。对于给定的百分位数 P，PERCENTILE_DISC 对 ORDER BY 子句中的表达式值进行排序，并返回 CUME_DIST 值（相对于相同的排序规范）最小且大于或等于 P 的值。例如，PERCENTILE_DISC (0.5) 将计算表达式的第 50 个百分位数（即中位数）。

PERCENTILE_DISC 根据列值的离散分布计算百分位数；结果等于列中的特定值。PERCENTILE_CONT 插入适当的值，无论它是否存在于数据集中，而 PERCENTILE_DISC 始终返回集合中的实际值，因此，这两个函数之间的值可能不同。

PERCENT_RANK

计算 SQL Server 2012 中一行在一组行中的相对排名。使用 PERCENT_RANK 评估值在查询结果集或分区中的相对位置。PERCENT_RANK 类似于 CUME_DIST 函数。

Percent rank 使用百分比（即 0 到 1 的范围）来应用排名。percent_rank 的公式是：

percent_Rank = (rank-1)/(totalRow - 1)。

我承认上面的定义是从 MSDN 复制的，我对这些函数的用法并不十分熟悉，但我已将所有这些函数的使用示例包含在可下载的 SQL 文件中，作为您深入研究的起点。我的示例也来自网络（主要是 MSDN），因为，正如我所说，我对这些函数的用法没有深入的理解。

结论

希望本文能让您了解窗口函数的一些功能，它们远远超出了简单地聚合列中的值。它们是 SQL 中一个非常强大的功能，您最好熟练使用和理解它们。