A cláusula SQL OVER () - quando e por que é útil?
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 percebo porque preciso dela.
O que faz a função Over
? O que faz?
Porque não posso fazer uma pergunta com a escrita?
7 answers
Tu podes usar GROUP BY SalesOrderID
. A diferença é que, com o grupo por você só pode ter os valores agregados para as colunas que não estão incluídas no grupo por.
Em contraste, usando funções agregadas windowed em vez de agrupar por, você pode obter valores agregados e não agregados. Isto é, embora você não esteja fazendo isso em sua consulta de exemplo, você poderia recuperar os valores individuais OrderQty
e suas somas, contagens, médias etc. sobre grupos da mesma SalesOrderID
s.
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]
Agora Veja como pode fazer o mesmo com um agregado windowed.
SELECT
[Partition],
Value,
Value * 100.0 / SUM(Value) OVER (PARTITION BY [Partition]) AS ValuePercent
FROM OriginalRowset orig
Muito mais fácil e mais limpo, não é?
A cláusula OVER
é poderosa na medida em que pode ter agregados sobre diferentes gamas ("windowing"), quer utilize um GROUP BY
ou não
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, não 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)
Se só quisesse agrupar-se pelo vendedor, não poderia incluir as colunas de produtos e ordenados na cláusula de selecção.
A partição por cláusula vamos separar as suas funções agregadas. Um exemplo óbvio e útil seria se você quisesse gerar números de linha para linhas de ordem em uma ordem:
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 desligada)
Depois recuperavas 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
Assumindo que tem o seguinte quadro:
VIN MAKE MODEL YEAR COLOR
-----------------------------------------
1234ASDF Ford Taurus 2008 White
1234JKLM Chevy Truck 2005 Green
5678ASDF Ford Mustang 2008 Yellow
Corra abaixo de SQL
SELECT VIN,
MAKE,
MODEL,
YEAR,
COLOR ,
COUNT(*) OVER (PARTITION BY YEAR) AS COUNT2
FROM DIM_EQUIPMENT
O resultado seria o seguinte
VIN MAKE MODEL YEAR COLOR COUNT2
----------------------------------------------
1234JKLM Chevy Truck 2005 Green 1
5678ASDF Ford Mustang 2008 Yellow 2
1234ASDF Ford Taurus 2008 White 2
Vê o que aconteceu.
Você é capaz de contar sem Grupo por ano e igualar com linha.
Outra maneira interessante de obter o mesmo resultado se como abaixo usar com Cláusula, com trabalhos como vista em linha e pode simplificar a consulta especialmente os complexos, o que não é o caso aqui, porém, uma vez que eu estou apenas tentando mostrar uso
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;
A cláusula sobre quando combinada com a partição por estado que a chamada de função precedente deve ser feita analiticamente, avaliando as linhas devolvidas da consulta. Pense nisso como um grupo inline por Declaração.
OVER (PARTITION BY SalesOrderID)
é afirmar que para SUM, AVG, etc... função, retornar o valor sobre um subconjunto dos registros retornados da consulta, e partição que subconjunto pela chave estrangeira vendedor.
É um meio muito mais eficiente do que usar várias vistas em linha para descobrir a mesma informação. Você pode colocar esta consulta dentro de uma visualização em linha e filtrar no Total então.
SELECT ...,
FROM (your query) inlineview
WHERE Total < 200
- Também Chamada
Query Petition
Cláusula. -
Semelhante à Cláusula
Group By
- dividir os dados em blocos (ou partições)
- separado pelos limites da partição
- a função funciona dentro das partições
- reinicializado ao atravessar a fronteira de separação
Sintaxe:
function (...) OVER (PARTITION BY col1 col3,...)
-
Funções
- funções familiares tais como
COUNT()
,SUM()
,MIN()
,MAX()
, etc - novas funções também (eg
ROW_NUMBER()
,RATION_TO_REOIRT()
, etc.)
- funções familiares tais como
Mais informações com exemplo: http://msdn.microsoft.com/en-us/library/ms189461.aspx
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
Isso é resultado de uma consulta. A tabela usada como fonte é a mesma exceto que não tem a última coluna. Esta coluna é uma soma em movimento da terceira.
Query:
SELECT prkey,whatsthat,cash,SUM(cash) over (order by whatsthat)
FROM public.iuk order by whatsthat,prkey
;
(A mesa é pública.iuk)
sql version: 2012
É um pouco acima do nível dbase(1986), não sei por que mais de 25 anos foram necessários para terminá-lo.