Usar funciones RANK, AGGREGATE y OFFSET

Completado

En las operaciones de ventana, puede usar funciones de agregado como SUM, MIN y MAX para operar en un conjunto de filas definidas por la cláusula OVER y sus argumentos.

Las funciones de ventana se pueden clasificar como:

• Funciones de agregado. Por ejemplo, SUM, AVG y COUNT que operan en una ventana y devuelven un valor escalar.
• Funciones de clasificación. Por ejemplo, RANK, ROW_NUMBER y NTILE. Las funciones de clasificación requieren un criterio de ordenación y devuelven un valor de clasificación para cada fila de una partición.
• Funciones analíticas. Por ejemplo, CUME_DIST, PERCENTILE_CONT o PERCENTILE_DISC. Las funciones analíticas calculan la distribución de valores en la partición.
• Funciones de desplazamiento. Por ejemplo, LAG, LEAD y LAST_VALUE. Las funciones de desplazamiento devuelven valores de otras filas en relación con la posición de la fila actual.

Funciones agregadas

Las funciones de agregado devuelven totales, promedios o recuentos de cosas. Las funciones de agregado realizan un cálculo y devuelven un único valor. Con la excepción de COUNT(*), las funciones de agregado no cuentan valores NULL.

Tenga en cuenta el código siguiente, que aplica algunas funciones de agregado comunes a los precios de los productos de la tabla de productos:

SELECT Name, ProductNumber, Color, SUM(Weight) 
OVER(PARTITION BY Color) AS WeightByColor
FROM SalesLT.Product
ORDER BY ProductNumber;

Esto devuelve una columna denominada WeightByColor que contiene el peso total de todos los productos del mismo color que se muestran en el conjunto de resultados parcial siguiente.

Funciones de clasificación

Las funciones de clasificación asignan un número a cada fila, en función de su posición dentro de un orden especificado. El orden se especifica mediante la cláusula ORDER BY.

Tenga en cuenta el código siguiente, que aplica las cuatro funciones de clasificación a los productos de la tabla products.

SELECT productid, name, listprice 
    ,ROW_NUMBER() OVER (ORDER BY productid) AS "Row Number"  
    ,RANK() OVER (ORDER BY listprice) AS PriceRank  
    ,DENSE_RANK() OVER (ORDER BY listprice) AS "Dense Rank"  
    ,NTILE(4) OVER (ORDER BY listprice) AS Quartile  
FROM SalesLT.Product 

Devuelve una columna para cada una de las funciones, con el número de clasificación adecuado.

Funciones analíticas

Las funciones analíticas calculan un valor basado en un grupo de filas. Las funciones analíticas se usan para calcular los promedios móviles, los totales en ejecución y los resultados principales de N. Estas funciones incluyen:

• CUME_DIST
• FIRST_VALUE
• RANGO_PORCENTAJE
• PERCENTILE_CONT
• PERCENTIL_DISC

Funciones OFFSET

Las funciones de desplazamiento permiten devolver un valor posterior o filas anteriores dentro de un conjunto de resultados.

Las funciones de desplazamiento funcionan en una posición relativa a la fila actual o en relación con el límite inicial o final del marco de la ventana. Las funciones de desplazamiento son:

• LAG y LEAD: operan en un desplazamiento a la fila actual y requieren la cláusula ORDER BY.
• FIRST_VALUE y LAST_VALUE: operan en un desplazamiento desde el marco de la ventana. A continuación se muestra la sintaxis de la función LAG. La función LEAD funciona de la misma manera.

LAG (scalar_expression [,offset] [,default])  
    OVER ( [ partition_by_clause ] order_by_clause )

En el ejemplo de código siguiente, la función de desplazamiento LEAD devuelve el valor presupuestado del año siguiente:

SELECT [Year], Budget, LEAD(Budget, 1, 0) OVER (ORDER BY [Year]) AS 'Next'
    FROM dbo.Budget
    ORDER BY [Year];

A continuación se muestra la sintaxis de LAST_VALUE. FIRST_VALUE funciona de la misma manera.

LAST_VALUE ( [ scalar_expression ] )  
OVER ( [ partition_by_clause ] order_by_clause rows_range_clause )  

La sintaxis es similar a LAG y LEAD, con la adición de la cláusula rows/range.