Buscar..


Parámetros

Parámetro Detalles
PARTICIÓN POR El (los) campo (s) que sigue a PARTICIÓN POR es el que se basará en la "agrupación"

Observaciones

La cláusula OVER determina una ventana o un subconjunto de fila dentro de un conjunto de resultados de consulta. Se puede aplicar una función de ventana para establecer y calcular un valor para cada fila en el conjunto. La cláusula OVER se puede utilizar con:

  • Funciones de clasificación
  • Funciones agregadas

por lo tanto, alguien puede calcular valores agregados, como promedios móviles, agregados acumulados, totales acumulados o una N superior por resultados de grupo.

De una manera muy abstracta podemos decir que OVER se comporta como GROUP BY. Sin embargo, OVER se aplica por campo / columna y no a la consulta en su totalidad como lo hace GROUP BY.

Nota # 1: En SQL Server 2008 (R2) la cláusula ORDER BY no se puede usar con funciones de ventana agregadas ( enlace ).

Usando las funciones de agregación con OVER

Usando la tabla de autos , calcularemos la cantidad total, máxima, mínima y promedio de dinero que gastó cada cliente y muchas veces (COUNT) que ella trajo un auto para reparación.

Id CustomerId MechanicId Modelo Estado Costo Total

SELECT CustomerId,  
       SUM(TotalCost) OVER(PARTITION BY CustomerId) AS Total,
       AVG(TotalCost) OVER(PARTITION BY CustomerId) AS Avg,
       COUNT(TotalCost) OVER(PARTITION BY CustomerId) AS Count,
       MIN(TotalCost) OVER(PARTITION BY CustomerId) AS Min,
       MAX(TotalCost) OVER(PARTITION BY CustomerId) AS Max
  FROM CarsTable
 WHERE Status = 'READY'

Tenga en cuenta que usar OVER de esta manera no agregará las filas devueltas. La consulta anterior devolverá lo siguiente:

Identificación del cliente Total Avg Contar Min Max
1 430 215 2 200 230
1 430 215 2 200 230

La fila (s) duplicada (s) puede no ser tan útil para propósitos de reporte.

Si desea simplemente agregar datos, será mejor que use la cláusula GROUP BY junto con las funciones agregadas apropiadas, por ejemplo:

SELECT CustomerId,  
       SUM(TotalCost) AS Total,
       AVG(TotalCost) AS Avg,
       COUNT(TotalCost) AS Count,
       MIN(TotalCost) AS Min,
       MAX(TotalCost)  AS Max
  FROM CarsTable
 WHERE Status = 'READY'
GROUP BY CustomerId

Suma acumulativa

Usando la Tabla de ventas de artículos , intentaremos descubrir cómo aumentan las ventas de nuestros artículos a través de las fechas. Para ello, calcularemos la suma acumulada de las ventas totales por pedido de artículo antes de la fecha de venta.

SELECT item_id, sale_Date 
       SUM(quantity * price) OVER(PARTITION BY item_id ORDER BY sale_Date ROWS BETWEEN UNBOUNDED PRECEDING) AS SalesTotal
  FROM SalesTable

Usando funciones de agregación para encontrar los registros más recientes

Usando la base de datos de la biblioteca , tratamos de encontrar el último libro agregado a la base de datos para cada autor. Para este ejemplo simple, asumimos una identificación siempre creciente para cada registro agregado.

SELECT MostRecentBook.Name, MostRecentBook.Title
FROM ( SELECT Authors.Name,
              Books.Title,
              RANK() OVER (PARTITION BY Authors.Id ORDER BY Books.Id DESC) AS NewestRank
       FROM Authors
       JOIN Books ON Books.AuthorId = Authors.Id
     ) MostRecentBook
WHERE MostRecentBook.NewestRank = 1

En lugar de RANK, se pueden usar otras dos funciones para ordenar. En el ejemplo anterior, el resultado será el mismo, pero darán resultados diferentes cuando el pedido dé múltiples filas para cada rango.

  • RANK() : los duplicados obtienen el mismo rango, el siguiente rango toma en cuenta el número de duplicados en el rango anterior
  • DENSE_RANK() : los duplicados obtienen el mismo rango, el siguiente rango es siempre uno más alto que el anterior
  • ROW_NUMBER() : dará a cada fila un 'rango' único, 'clasificando' los duplicados al azar

Por ejemplo, si la tabla tenía una columna CreationDate no única y el orden se realizó en base a eso, la siguiente consulta:

SELECT Authors.Name,
       Books.Title,
       Books.CreationDate,
       RANK() OVER (PARTITION BY Authors.Id ORDER BY Books.CreationDate DESC) AS RANK,
       DENSE_RANK() OVER (PARTITION BY Authors.Id ORDER BY Books.CreationDate DESC) AS DENSE_RANK,
       ROW_NUMBER() OVER (PARTITION BY Authors.Id ORDER BY Books.CreationDate DESC) AS ROW_NUMBER,
FROM Authors
JOIN Books ON Books.AuthorId = Authors.Id

Podría resultar en:

Autor Título Fecha de creación RANGO DENSE_RANK NUMERO DE FILA
Autor 1 Libro 1 22/07/2016 1 1 1
Autor 1 Libro 2 22/07/2016 1 1 2
Autor 1 Libro 3 21/07/2016 3 2 3
Autor 1 Libro 4 21/07/2016 3 2 4
Autor 1 Libro 5 21/07/2016 3 2 5
Autor 1 Libro 6 07/04/2016 6 3 6
Autor 2 Libro 7 07/04/2016 1 1 1

Dividir datos en cubos igualmente particionados usando NTILE

Digamos que tiene puntajes de examen para varios exámenes y quiere dividirlos en cuartiles por examen.

-- Setup data:
declare @values table(Id int identity(1,1) primary key, [Value] float, ExamId int)
insert into @values ([Value], ExamId) values
(65, 1), (40, 1), (99, 1), (100, 1), (90, 1), -- Exam 1 Scores
(91, 2), (88, 2), (83, 2), (91, 2), (78, 2), (67, 2), (77, 2) -- Exam 2 Scores

-- Separate into four buckets per exam:
select ExamId, 
       ntile(4) over (partition by ExamId order by [Value] desc) as Quartile, 
       Value, Id 
from @values 
order by ExamId, Quartile

Nuestros datos de examen divididos en cuartiles por examen.

ntile funciona muy bien cuando realmente necesita un número determinado de cubos y cada uno se llena aproximadamente al mismo nivel. Tenga en cuenta que sería trivial separar estas puntuaciones en percentiles simplemente usando ntile(100) .



Modified text is an extract of the original Stack Overflow Documentation
Licenciado bajo CC BY-SA 3.0
No afiliado a Stack Overflow