SQL OVER()子句-何时以及为何有用? [英] The SQL OVER() clause - when and why is it useful?

问题描述

    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有什么作用? Partitioning By的作用是什么? 为什么我不能写Group By SalesOrderID进行查询?

I read about that clause and I don't understand why I need it. What does the function Over do? What does Partitioning By do? Why can't I make a query with writing Group By SalesOrderID?

推荐答案

您可以使用GROUP BY SalesOrderID.区别在于，使用GROUP BY，您只能拥有未包含在GROUP BY中的列的汇总值.

You can use GROUP BY SalesOrderID. The difference is, with GROUP BY you can only have the aggregated values for the columns that are not included in GROUP BY.

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

In contrast, using windowed aggregate functions instead of GROUP BY, you can retrieve both aggregated and non-aggregated values. That is, although you are not doing that in your example query, you could retrieve both individual OrderQty values and their sums, counts, averages etc. over groups of same SalesOrderIDs.

这是为什么窗口聚合效果很好的一个实际例子.假设您需要计算每个值占总数的百分比.如果没有窗口聚合，则必须首先派生一个聚合值列表，然后将其重新连接到原始行集，例如:

Here's a practical example of why windowed aggregates are great. Suppose you need to calculate what percent of a total every value is. Without windowed aggregates you'd have to first derive a list of aggregated values and then join it back to the original rowset, i.e. like this:

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]

现在看一下如何使用窗口聚合来做同样的事情:

Now look how you can do the same with a windowed aggregate:

SELECT
  [Partition],
  Value,
  Value * 100.0 / SUM(Value) OVER (PARTITION BY [Partition]) AS ValuePercent
FROM OriginalRowset orig

更容易，更清洁，不是吗?

Much easier and cleaner, isn't it?

这篇关于SQL OVER()子句-何时以及为何有用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！