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