SQL OVER()子句-什么时候以及为什么它是有用的？
SQL OVER()子句-什么时候以及为什么它是有用的？
提问于 2011-06-03 02:45:06
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进行查询
回答 5
Stack Overflow用户
发布于 2011-06-03 02:59:52
无论您是否使用GROUP BY，OVER子句都很强大，因为您可以在不同的范围内拥有聚合(“窗口”
示例:获取每个SalesOrderID的计数和全部计数
SELECT
SalesOrderID, ProductID, OrderQty
,COUNT(OrderQty) AS 'Count'
,COUNT(*) OVER () AS 'CountAll'
FROM Sales.SalesOrderDetail
WHERE
SalesOrderID IN(43659,43664)
GROUP BY
SalesOrderID, ProductID, OrderQty获取不同的COUNT%s，没有GROUP BY
SELECT
SalesOrderID, ProductID, OrderQty
,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'CountQtyPerOrder'
,COUNT(OrderQty) OVER(PARTITION BY ProductID) AS 'CountQtyPerProduct',
,COUNT(*) OVER () AS 'CountAllAgain'
FROM Sales.SalesOrderDetail
WHERE
SalesOrderID IN(43659,43664)Stack Overflow用户
发布于 2011-06-03 03:01:21
如果只想按SalesOrderID分组，那么就不能在SELECT子句中包含ProductID和OrderQty列。
PARTITION BY子句让您拆分聚合函数。一个明显且有用的示例是，如果您想要为订单上的订单行生成行号：
SELECT
O.order_id,
O.order_date,
ROW_NUMBER() OVER(PARTITION BY O.order_id) AS line_item_no,
OL.product_id
FROM
Orders O
INNER JOIN Order_Lines OL ON OL.order_id = O.order_id(我的语法可能稍有偏差)
然后，您将得到类似以下内容的结果：
order_id order_date line_item_no product_id
-------- ---------- ------------ ----------
1 2011-05-02 1 5
1 2011-05-02 2 4
1 2011-05-02 3 7
2 2011-05-12 1 8
2 2011-05-12 2 1Stack Overflow用户
发布于 2014-05-08 14:37:49
让我用一个例子来解释一下，你将能够看到它是如何工作的。
假设您有以下表DIM_EQUIPMENT：
VIN MAKE MODEL YEAR COLOR
-----------------------------------------
1234ASDF Ford Taurus 2008 White
1234JKLM Chevy Truck 2005 Green
5678ASDF Ford Mustang 2008 Yellow在SQL下运行
SELECT VIN,
MAKE,
MODEL,
YEAR,
COLOR ,
COUNT(*) OVER (PARTITION BY YEAR) AS COUNT2
FROM DIM_EQUIPMENT结果如下所示
VIN MAKE MODEL YEAR COLOR COUNT2
----------------------------------------------
1234JKLM Chevy Truck 2005 Green 1
5678ASDF Ford Mustang 2008 Yellow 2
1234ASDF Ford Taurus 2008 White 2看看发生了什么。
您可以在不使用分组依据的情况下进行计数，并与行匹配。
另一种有趣的方法可以获得相同的结果，就像下面使用WITH Clause，WITH作为内联视图，可以简化查询，特别是复杂的查询，但这里不是这样，因为我只是想显示用法
WITH EQ AS
( SELECT YEAR AS YEAR2, COUNT(*) AS COUNT2 FROM DIM_EQUIPMENT GROUP BY YEAR
)
SELECT VIN,
MAKE,
MODEL,
YEAR,
COLOR,
COUNT2
FROM DIM_EQUIPMENT,
EQ
WHERE EQ.YEAR2=DIM_EQUIPMENT.YEAR;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/6218902
复制相关文章
相似问题
腾讯云开发者
