您当前的位置: 首页 » MySQL/MSSQL编程 » 探讨SQL Server 2005窗口函数
探讨SQL Server 2005窗口函数
论坛链接
SQL Server 2005中的窗口函数（window function）与微软Windows无关；相反，它们建立数据窗口。窗口函数帮助你迅速查看不同级别的聚合，通过它可以非常方便地恢复累计总数、移动平均值、以及执行其它计算。
一个SQL Server窗口是对函数应用的行的分区。你使用OVER(…)子句指定一个窗口，你可以对任何一个聚合函数应用这个子句。通常来说，你把数据分成几个组，但OVER（）的自变量可选。
列表A说明如何一次搜集几个聚集，甚至根据聚合进行其它计算。这样就可得到列表B中的结果集。我把结果集限定为一个单独的OrderID，那么查询将只产生一个窗口。你可以用各种方式修改子句，如使用一个IN（）子句列出一组特殊的顺序，或使用一个BETWEEN子句推导一个顺序范围内的统计值。最后你指定的每个OrderID都得到一个窗口。
窗口函数功能非常强大，建立起来也十分容易。你可以使用这个技巧立即搜索到大量统计值。这个教程中的例子指出，你可以在单独一个查询中搜索任何统计值集合。
列表A
USE AdventureWorks2;
GO
SELECT SalesOrderID, ProductID, OrderQty AS 'Item Qty' ,
SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Total Qty' ,
AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Average Qty' ,
COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Total Count' ,
MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Minimum Count' ,
MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Maximum Count' ,
LineTotal ,
AVG(LineTotal) OVER(PARTITION BY SalesOrderID) AS 'Average Amount' ,
SUM(LineTotal) OVER(PARTITION BY SalesOrderID) AS 'Total Amount' ,
(LineTotal/SUM(LineTotal) OVER(PARTITION BY SalesOrderID)) * 100 AS 'Percent'FROM Sales.SalesOrderDetail WHERE SalesOrderID = 43664O
RDER BY ProductID
GO
列表B
43664 714 1 14 1 8 1 4 28.840400 3054.07610024432.608800 0.118000
43664 716 1 14 1 8 1 4 28.840400 3054.07610024432.608800 0.118000
43664 771 3 14 1 8 1 4 6119.9820003054.07610024432.608800 25.048400
43664 772 1 14 1 8 1 4 2039.9940003054.07610024432.608800 8.349400
43664 773 1 14 1 8 1 4 2039.9940003054.07610024432.608800 8.349400
43664 775 4 14 1 8 1 4 8099.9760003054.07610024432.608800 33.152300
43664 777 2 14 1 8 1 4 4049.9880003054.07610024432.608800 16.576100
43664 778 1 14 1 8 1 4 2024.9940003054.07610024432.608800 8.288000
一个SQL Server窗口是对函数应用的行的分区。你使用OVER(…)子句指定一个窗口，你可以对任何一个聚合函数应用这个子句。通常来说，你把数据分成几个组，但OVER（）的自变量可选。
列表A说明如何一次搜集几个聚集，甚至根据聚合进行其它计算。这样就可得到列表B中的结果集。我把结果集限定为一个单独的OrderID，那么查询将只产生一个窗口。你可以用各种方式修改子句，如使用一个IN（）子句列出一组特殊的顺序，或使用一个BETWEEN子句推导一个顺序范围内的统计值。最后你指定的每个OrderID都得到一个窗口。
窗口函数功能非常强大，建立起来也十分容易。你可以使用这个技巧立即搜索到大量统计值。这个教程中的例子指出，你可以在单独一个查询中搜索任何统计值集合。
列表A
USE AdventureWorks2;
GO
SELECT SalesOrderID, ProductID, OrderQty AS 'Item Qty' ,
SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Total Qty' ,
AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Average Qty' ,
COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Total Count' ,
MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Minimum Count' ,
MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Maximum Count' ,
LineTotal ,
AVG(LineTotal) OVER(PARTITION BY SalesOrderID) AS 'Average Amount' ,
SUM(LineTotal) OVER(PARTITION BY SalesOrderID) AS 'Total Amount' ,
(LineTotal/SUM(LineTotal) OVER(PARTITION BY SalesOrderID)) * 100 AS 'Percent'FROM Sales.SalesOrderDetail WHERE SalesOrderID = 43664O
RDER BY ProductID
GO
列表B
43664 714 1 14 1 8 1 4 28.840400 3054.07610024432.608800 0.118000
43664 716 1 14 1 8 1 4 28.840400 3054.07610024432.608800 0.118000
43664 771 3 14 1 8 1 4 6119.9820003054.07610024432.608800 25.048400
43664 772 1 14 1 8 1 4 2039.9940003054.07610024432.608800 8.349400
43664 773 1 14 1 8 1 4 2039.9940003054.07610024432.608800 8.349400
43664 775 4 14 1 8 1 4 8099.9760003054.07610024432.608800 33.152300
43664 777 2 14 1 8 1 4 4049.9880003054.07610024432.608800 16.576100
43664 778 1 14 1 8 1 4 2024.9940003054.07610024432.608800 8.288000