USE AdventureWorks2008R2;
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);
我读到了该条款,但不明白为什么我需要它。
作用是什么Over
做?什么是Partition By
做?
为什么我不能用书写方式进行查询Group By SalesOrderID
?
You can use GROUP BY SalesOrderID
。不同之处在于,使用 GROUP BY,您只能获得未包含在 GROUP BY 中的列的聚合值。
相反,使用窗口聚合函数而不是 GROUP BY,您可以检索聚合值和非聚合值。也就是说,尽管您在示例查询中没有这样做,但您可以检索两个单独的OrderQty
相同组的值及其总和、计数、平均值等SalesOrderID
s.
下面是一个实际示例,说明窗口聚合为何如此出色。假设您需要计算每个值占总数的百分比。如果没有窗口聚合,您必须首先派生聚合值列表,然后将其连接回原始行集,即像这样:
SELECT
orig.[Partition],
orig.Value,
orig.Value * 100.0 / agg.TotalValue AS ValuePercent
FROM OriginalRowset orig
INNER JOIN (
SELECT
[Partition],
SUM(Value) AS TotalValue
FROM OriginalRowset
GROUP BY [Partition]
) agg ON orig.[Partition] = agg.[Partition]
现在看看如何使用窗口聚合执行相同的操作:
SELECT
[Partition],
Value,
Value * 100.0 / SUM(Value) OVER (PARTITION BY [Partition]) AS ValuePercent
FROM OriginalRowset orig
更容易、更干净,不是吗?
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)