OVER Clause是个好东西，常和ROW_NUMBER()、Sum、AVG、Count、Min、Max配合使用

最新推荐文章于 2023-03-14 15:48:02 发布

原创最新推荐文章于 2023-03-14 15:48:02 发布 · 772 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql

SQL 专栏收录该内容

6 篇文章

订阅专栏

本文深入探讨了SQL中窗口函数的应用，通过实例展示了如何使用ROW_NUMBER()和窗口函数进行数据排序、分组聚合以及计算统计数据，如总和、平均值、计数、最小值和最大值，特别关注于邮政编码分组和销售额排序场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

根据SQL官方帮助的实例：

USE AdventureWorks2012;
GO
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() 以PARTITION BY PostalCode分隔，
按照ORDER BY SalesYTD DESC排序

实际效果类似

官方其他例子：
USE AdventureWorks2012;
GO
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