Microsoft SQL Server
Sobre la cláusula

Parámetros

Observaciones

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:

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:

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

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