sql - SQL OVER() 子句 - 何时以及为何有用？

Question

    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有什么作用？ 分区依据有什么作用？为什么我无法通过编写 Group By SalesOrderID 进行查询？

Answer 1

您可以使用GROUP BY SalesOrderID。不同之处在于，使用 GROUP BY，您只能获得未包含在 GROUP BY 中的列的聚合值。

相反，使用窗口聚合函数而不是 GROUP BY，您可以检索聚合值和非聚合值。也就是说，尽管您没有在示例查询中执行此操作，但您可以检索相同 SalesOrderID 组中的各个 OrderQty 值及其总和、计数、平均值等.

下面是一个实际示例，说明窗口聚合为何如此出色。假设您需要计算每个值占总数的百分比。如果没有窗口聚合，您必须首先派生聚合值列表，然后将其连接回原始行集，即像这样:

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

更容易、更干净，不是吗？