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);
Li sobre essa cláusula e não't compreendo porque preciso dela.
O que é que a função Over
faz? O que faz a função "Particionar por"?
Porque posso't faço uma pergunta com a escrita Grupo por ordem de venda
?
A cláusula "SOBRE" é poderosa na medida em que pode ter agregados em diferentes gamas ("windowing"), quer utilize um "GRUPO POR" ou não
Exemplo: obter a contagem por SalesOrderID
e contagem de todos
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
Obter diferentes COUNT
s, sem `GROUPO POR''.
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)
Se apenas quisesse GRUPO PELO SalesOrderID então não't poderia incluir as colunas ProductID e OrderQty na cláusula SELECT.
A cláusula PARTITION BY let's separa as suas funções agregadas. Um exemplo óbvio e útil seria se quisesse gerar números de linha para linhas de encomenda numa encomenda:
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
(A minha sintaxe pode estar ligeiramente fora do normal)
Recuperar-se-ia então algo do género:
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
A cláusula OVER quando combinada com PARTITION BY declara que a chamada de função anterior deve ser feita analiticamente, avaliando as linhas devolvidas da consulta. Pense nela como uma declaração em linha GROUP BY.
PARTITION BY SalesOrderID (PARTITION BY SalesOrderID) está a declarar que para a função SUM, AVG, etc..., devolva o valor SOBRE um subconjunto dos registos devolvidos da consulta, e PARTITION that subset BY the foreign key SalesOrderID (PARTITION BY SalesOrderID).
Por isso, vamos SUMAR todos os registos de cada OrderQty para CADA ENCOMENDA ÚNICO, e esse nome de coluna será chamado 'Total'.
É um meio MUITO mais eficiente do que utilizar múltiplas vistas em linha para descobrir a mesma informação. Pode então colocar esta consulta dentro de uma vista em linha e filtrar no Total.
SELECT ...,
FROM (your query) inlineview
WHERE Total < 200