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`と書いたクエリを作ることができないのですか?
OVER句は、
GROUP BY`を使用するかどうかにかかわらず、異なる範囲での集計("windowing")ができるという点で強力です。
例: 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
を取得、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)
SalesOrderIDでGROUPしたいだけなら、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句とPARTITION BYを組み合わせると、先行する関数呼び出しは、クエリの返された行を評価することによって分析的に行われなければならないことを示します。 インラインGROUP BY文と考えてください。
OVER (PARTITION BY SalesOrderID)`は、SUMやAVGなどの関数では、クエリから返されたレコードのサブセットの値をOVERで返し、そのサブセットを外部キーのSalesOrderIDでPARTITIONすることを示しています。
つまり、ユニークなSalesOrderIDごとにすべてのOrderQtyレコードを合計し、そのカラム名を 'Total'とします。
これは、同じ情報を得るために複数のインラインビューを使用するよりも、はるかに効率的な手段です。 このクエリをインラインビューの中に入れて、Totalでフィルタリングすることができます。
SELECT ...,
FROM (your query) inlineview
WHERE Total < 200