sql - transact - sum over

La cláusula SQL OVER(): ¿cuándo y por qué es útil?

Question

 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);

Leí sobre esa cláusula y no entiendo por qué la necesito. ¿Qué hace la función Over do? ¿Qué hace el Partitioning By ? ¿Por qué no puedo hacer una consulta escribiendo Group By SalesOrderID ?

Answer 1

Déjame explicarte con un ejemplo y serás capaz de ver cómo funciona.

Suponiendo que tiene la siguiente tabla DIM_EQUIPMENT:

VIN MAKE MODEL YEAR COLOR
-----------------------------------------
1234ASDF Ford Taurus 2008 White
1234JKLM Chevy Truck 2005 Green
5678ASDF Ford Mustang 2008 Yellow

Ejecutar debajo de SQL

SELECT VIN,
MAKE,
MODEL,
YEAR,
COLOR ,
COUNT(*) OVER (PARTITION BY YEAR) AS COUNT2
FROM DIM_EQUIPMENT

El resultado sería el siguiente

VIN MAKE MODEL YEAR COLOR COUNT2
----------------------------------------------
1234JKLM Chevy Truck 2005 Green 1
5678ASDF Ford Mustang 2008 Yellow 2
1234ASDF Ford Taurus 2008 White 2

Mira lo que sucedió

Puede contar sin Group By en YEAR y Match with ROW.

Otra forma interesante de obtener el mismo resultado si se usa la cláusula WITH con WITH, funciona como VIEW en línea y puede simplificar la consulta especialmente compleja, lo que no es el caso aquí, ya que solo intento mostrar el 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;

Answer 2

La cláusula OVER es poderosa porque puede tener agregados en diferentes rangos ("ventana"), ya sea que use un GROUP BY o no

Ejemplo: obtener recuento por SalesOrderID y el recuento 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

Obtenga diferentes COUNT s, sin 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)

Answer 3

La cláusula OVER, cuando se combina con PARTITION BY, indica que la llamada de función precedente debe realizarse analíticamente evaluando las filas devueltas de la consulta. Piénselo como una declaración GROUP BY en línea.

OVER (PARTITION BY SalesOrderID) indica que, para la función SUM, AVG, etc ..., devuelve el valor OVER de un subconjunto de los registros devueltos de la consulta, y PARTITION ese subconjunto BY la clave externa SalesOrderID.

Por lo tanto, SUMAREMOS cada registro de OrderQty para CADA UNO SalesOrderID, y ese nombre de columna se llamará ''Total''.

Es un medio MUCHO más eficiente que usar múltiples vistas en línea para encontrar la misma información. Puede colocar esta consulta dentro de una vista en línea y filtrar en Total luego.

SELECT ...,
FROM (your query) inlineview
WHERE Total < 200

Answer 4

Puede usar GROUP BY SalesOrderID . La diferencia es que con GROUP BY solo puede tener los valores agregados para las columnas que no están incluidas en GROUP BY.

Por el contrario, al utilizar funciones de agregado en ventana en lugar de GROUP BY, puede recuperar valores agregados y no agregados. Es decir, aunque no está haciendo eso en su consulta de ejemplo, puede recuperar los valores de OrderQty individuales y sus sumas, recuentos, promedios, etc. sobre grupos de los mismos SalesOrderID de SalesOrderID .

Aquí hay un ejemplo práctico de por qué los agregados en ventana son geniales. Supongamos que necesita calcular qué porcentaje del total es cada valor. Sin agregados en ventana, primero debe obtener una lista de valores agregados y luego volver a unirla al conjunto de filas original, es decir, de esta manera:

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]

Ahora mira cómo puedes hacer lo mismo con un agregado en ventanas:

SELECT
[Partition],
Value,
Value * 100.0 / SUM(Value) OVER (PARTITION BY [Partition]) AS ValuePercent
FROM OriginalRowset orig

Mucho más fácil y más limpio, ¿no?

Answer 5

Si solo desea agrupar BY SalesOrderID, no podrá incluir las columnas ProductID y OrderQty en la cláusula SELECT.

La cláusula PARTITION BY le permite dividir sus funciones agregadas. Un ejemplo obvio y útil sería si desea generar números de línea para líneas de pedido en una orden:

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

(Mi sintaxis puede estar un poco apagada)

Luego obtendrías algo así como:

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

Answer 6

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

Eso es un resultado de la consulta. La tabla utilizada como fuente es el mismo exept que no tiene la última columna. Esta columna es una suma móvil de la tercera.

Consulta:

SELECT prkey,whatsthat,cash,SUM(cash) over (order by whatsthat)
FROM public.iuk order by whatsthat,prkey
;

(la tabla va como public.iuk)

sql version: 2012

Es un poco sobre el nivel de dbase (1986), no sé por qué se necesitan más de 25 años para terminarlo.

Answer 7

• También se llama Cláusula de Query Petition .

• Similar al Group By cláusula

  • dividir los datos en fragmentos (o particiones)
  • separar por límites de partición
  • la función funciona dentro de las particiones
  • reinicializado al cruzar el límite de separación

Sintaxis:
función (...) OVER (PARTICIÓN POR col1 col3, ...)

• Funciones

  • Funciones familiares como COUNT() , SUM() , MIN() , MAX() , etc.
  • Nuevas funciones también (por ejemplo, ROW_NUMBER() , RATION_TO_REOIRT() , etc.)

Más información con el ejemplo: http://msdn.microsoft.com/en-us/library/ms189461.aspx