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查询?
如果您只想通过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 1
OVER子句的强大之处在于，无论是否使用GROUP BY，都可以在不同的范围内进行聚合(“窗口”)
示例:获取每个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
获得不同的计数，没有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)
当OVER子句与PARTITION BY结合使用时，表示前面的函数调用必须通过计算查询返回的行进行分析。可以把它看作内联的GROUP BY语句。
OVER (PARTITION BY SalesOrderID)表示对于SUM, AVG等…函数，返回值OVER查询返回记录的子集，并将该子集由外键SalesOrderID进行分区。
因此，我们将对每个UNIQUE SalesOrderID的每个OrderQty记录求和，并且该列名将被称为'Total'。
这是一种比使用多个内联视图查找相同信息更有效的方法。您可以将此查询放在一个内联视图中，然后在Total上进行筛选。
SELECT ...,
FROM (your query) inlineview
WHERE Total < 200
prkey whatsthat cash
890 "abb " 32 32
43 "abbz " 2 34
4 "bttu " 1 35
45 "gasstuff " 2 37
545 "gasz " 5 42
80009 "hoo " 9 51
2321 "ibm " 1 52
998 "krk " 2 54
42 "kx-5010 " 2 56
32 "lto " 4 60
543 "mp " 5 65
465 "multipower " 2 67
455 "O.N. " 1 68
7887 "prem " 7 75
434 "puma " 3 78
23 "retractble " 3 81
242 "Trujillo's stuff " 4 85
这是查询的结果。除了没有最后一列之外，用作源的表是相同的。这一列是第三个的移动和。
查询:
SELECT prkey,whatsthat,cash,SUM(cash) over (order by whatsthat)
FROM public.iuk order by whatsthat,prkey
;
(表格格式为public.iuk)
sql version: 2012
这有点超过dbase(1986)的水平，我不知道为什么需要25年以上的时间来完成它。
如果您只想通过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 1
