Aplicación de funciones de ventana para análisis

14 minutos

Las consultas analíticas suelen requerir cálculos que abarquen varias filas mientras siguen devolviendo detalles de fila individuales. Las funciones de agregado tradicionales contraen filas en grupos, lo que pierde información de nivel de fila. Las funciones de ventana resuelven este desafío realizando cálculos en un conjunto de filas relacionadas con la fila actual, sin contraer el conjunto de resultados.

Comprender la sintaxis de la función de ventana

Las funciones de ventana calculan los valores en una "ventana" de filas definidas por la OVER cláusula . A diferencia de las funciones de agregado normales, las funciones de ventana no agrupan filas en una sola fila de salida. En su lugar, calculan los valores en las filas relacionadas, a la vez que conservan todas las filas originales del resultado.

La sintaxis general de una función de ventana es:

function_name(arguments) OVER (
    [PARTITION BY partition_expression]
    [ORDER BY order_expression [ASC | DESC]]
    [ROWS | RANGE frame_specification]
)

Los OVER componentes de la cláusula controlan cómo se define la ventana:

PARTITION BY: divide las filas en grupos (particiones) para el cálculo.
ORDER BY: determina el orden lógico de las filas dentro de cada partición.
ROWS/RANGE: define los límites de marco relativos a la fila actual.

En la consulta siguiente se muestra una función de ventana sencilla que calcula un total de pedidos en ejecución por cliente:

SELECT 
    CustomerID,
    SalesOrderID,
    OrderDate,
    TotalDue,
    SUM(TotalDue) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS RunningTotal
FROM SalesLT.SalesOrderHeader
ORDER BY CustomerID, OrderDate;

Nota:

Cuando se especifica ORDER BY en la OVER cláusula sin una especificación de marco, el marco predeterminado es RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW para las funciones de agregado. Esto crea cálculos acumulativos.

Uso de funciones de clasificación

Las funciones de clasificación asignan números secuenciales a filas en función de su posición dentro de una partición. SQL Server proporciona cuatro funciones de clasificación. Cada función controla los vínculos de forma diferente:

ROW_NUMBER() asigna un número secuencial único a cada fila, sin duplicados incluso para los valores vinculados:

SELECT 
    ProductID,
    Name,
    ListPrice,
    ROW_NUMBER() OVER (ORDER BY ListPrice DESC) AS PriceRank
FROM SalesLT.Product
WHERE ListPrice > 0;

El conjunto de resultados tiene este aspecto:

ProductID   Name                        ListPrice   PriceRank
---------   -------------------------   ---------   ---------
749         Road-150 Red, 62            3578.27     1
750         Road-150 Red, 44            3578.27     2
751         Road-150 Red, 48            3578.27     3
771         Mountain-100 Silver, 38     3399.99     4

Esta consulta clasifica todos los productos por precio de mayor a menor. Cada producto recibe un número único independientemente de si varios productos comparten el mismo precio.

RANK() asigna la misma clasificación a los valores vinculados y, a continuación, omite los números para tener en cuenta los vínculos:

SELECT 
    ProductID,
    Name,
    ListPrice,
    RANK() OVER (ORDER BY ListPrice DESC) AS PriceRank
FROM SalesLT.Product
WHERE ListPrice > 0;

El conjunto de resultados tiene este aspecto:

ProductID   Name                        ListPrice   PriceRank
---------   -------------------------   ---------   ---------
749         Road-150 Red, 62            3578.27     1
750         Road-150 Red, 44            3578.27     1
751         Road-150 Red, 48            3578.27     1
771         Mountain-100 Silver, 38     3399.99     4

Cuando dos productos tienen precios idénticos, ambos reciben el mismo rango. La clasificación del siguiente producto refleja el número total de productos clasificados como superiores, creando brechas en la secuencia.

DENSE_RANK() asigna la misma clasificación a los valores vinculados, pero no omite los números:

SELECT 
    ProductID,
    Name,
    ListPrice,
    DENSE_RANK() OVER (ORDER BY ListPrice DESC) AS PriceRank
FROM SalesLT.Product
WHERE ListPrice > 0;

El conjunto de resultados tiene este aspecto:

ProductID   Name                        ListPrice   PriceRank
---------   -------------------------   ---------   ---------
749         Road-150 Red, 62            3578.27     1
750         Road-150 Red, 44            3578.27     1
751         Road-150 Red, 48            3578.27     1
771         Mountain-100 Silver, 38     3399.99     2

Al igual que RANK(), los valores vinculados comparten la misma clasificación. Sin embargo, DENSE_RANK() continúa con el siguiente número consecutivo, por lo que puede usarlo para contar distintos niveles de precios.

NTILE(n) distribuye filas en un número especificado de grupos aproximadamente iguales:

SELECT 
    ProductID,
    Name,
    ListPrice,
    NTILE(4) OVER (ORDER BY ListPrice DESC) AS PriceQuartile
FROM SalesLT.Product
WHERE ListPrice > 0;

El conjunto de resultados tiene este aspecto:

ProductID   Name                          ListPrice   PriceQuartile
---------   ---------------------------   ---------   -------------
749         Road-150 Red, 62              3578.27     1
771         Mountain-100 Silver, 38       3399.99     1
722         LL Road Frame - Black, 58     337.22      2
859         Half-Finger Gloves, S         24.49       4

Esta consulta divide los productos en cuatro grupos en función del precio. Los productos de mayor precio están en cuartil 1, y el precio más bajo se encuentra en cuartil 4. Se usa NTILE() para el análisis de percentil o la distribución uniforme del trabajo.

La combinación de PARTITION BY con funciones de clasificación permite clasificaciones por grupo:

SELECT 
    pc.Name AS Category,
    p.Name AS Product,
    p.ListPrice,
    ROW_NUMBER() OVER (
        PARTITION BY p.ProductCategoryID 
        ORDER BY p.ListPrice DESC
    ) AS CategoryPriceRank
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
    ON p.ProductCategoryID = pc.ProductCategoryID
WHERE p.ListPrice > 0;

El conjunto de resultados tiene este aspecto:

Category          Product                     ListPrice   CategoryPriceRank
---------------   -------------------------   ---------   -----------------
Road Bikes        Road-150 Red, 62            3578.27     1
Road Bikes        Road-150 Red, 44            3578.27     2
Mountain Bikes    Mountain-100 Silver, 38     3399.99     1
Mountain Bikes    Mountain-100 Black, 38      3374.99     2

Esta consulta clasifica los productos dentro de cada categoría por separado. La clasificación se reinicia en 1 para cada categoría, por lo que puede identificar el producto más caro de cada categoría filtrando por CategoryPriceRank = 1.

Sugerencia

Use ROW_NUMBER() cuando necesite exactamente una fila por clasificación (por ejemplo, al buscar los valores N principales por grupo). Use RANK() o DENSE_RANK() cuando necesite conservar la información de vinculación para generar informes.

Aplicación de funciones de ventana de agregado

Las funciones de agregado estándar como SUM, AVG, COUNT, MIN y MAX se pueden usar como funciones de ventana añadiendo la cláusula OVER. Esto le permite calcular agregados mientras conserva los detalles individuales de cada fila.

En la consulta siguiente se muestra cómo calcular los totales en ejecución y los agregados acumulativos:

SELECT 
    SalesOrderID,
    OrderDate,
    TotalDue,
    SUM(TotalDue) OVER (ORDER BY OrderDate, SalesOrderID) AS RunningTotal,
    AVG(TotalDue) OVER (ORDER BY OrderDate, SalesOrderID) AS RunningAverage,
    COUNT(*) OVER (ORDER BY OrderDate, SalesOrderID) AS OrderNumber
FROM SalesLT.SalesOrderHeader
ORDER BY OrderDate, SalesOrderID;

El conjunto de resultados tiene este aspecto:

SalesOrderID   OrderDate    TotalDue    RunningTotal   RunningAverage   OrderNumber
------------   ----------   ---------   ------------   --------------   -----------
71774          2008-06-01   972.785     972.785        972.785          1
71776          2008-06-01   87.083      1059.868       529.934          2
71780          2008-06-01   42452.65    43512.518      14504.172        3
71782          2008-06-01   43962.79    87475.308      21868.827        4

Importante

Cuando se usan funciones de ventana de agregado sin ORDER BY en la cláusula OVER, la función calcula a lo largo de toda la partición. Agregar ORDER BY crea un cálculo en ejecución desde el inicio de la partición a la fila actual.

Definir marcos de ventana con `ROWS` y `RANGE`

Los marcos de ventana permiten especificar exactamente qué filas relativas a la fila actual deben incluirse en el cálculo. La ROWS cláusula cuenta filas físicas, mientras que RANGE agrupa filas con valores iguales.

Los límites de marco se pueden especificar mediante:

UNBOUNDED PRECEDING: desde el inicio de la partición
n PRECEDING: n filas antes de la fila actual
CURRENT ROW: fila actual
n FOLLOWING: n filas después de la fila actual
UNBOUNDED FOLLOWING: al final de la partición

La consulta siguiente calcula una media móvil en los últimos tres pedidos:

SELECT 
    SalesOrderID,
    OrderDate,
    TotalDue,
    AVG(TotalDue) OVER (
        ORDER BY OrderDate
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS MovingAvg3Orders
FROM SalesLT.SalesOrderHeader
ORDER BY OrderDate;

El conjunto de resultados tiene este aspecto:

SalesOrderID   OrderDate    TotalDue    MovingAvg3Orders
------------   ----------   ---------   ----------------
71774          2008-06-01   972.785     972.785
71776          2008-06-01   87.083      529.934
71780          2008-06-01   42452.65    14504.172
71782          2008-06-01   43962.79    28834.174

Esta consulta calcula una media móvil de orden 3 incluyendo la fila actual y las dos filas anteriores. Para la primera fila, solo hay un valor disponible, por lo que el promedio es igual a TotalDue. En la tercera fila, la ventana incluye las tres filas.

Uso de funciones analíticas

Las funciones analíticas permiten acceder a los datos de otras filas sin usar autocombinaciones ni subconsultas. Estas funciones son útiles para el análisis de series temporales, la detección de tendencias y la comparación de valores actuales con valores históricos o futuros. A diferencia de las funciones de ventana de agregado que calculan resúmenes, las funciones analíticas recuperan valores específicos de filas específicas de la ventana.

LAG() y LEAD() acceden a los valores de las filas anteriores o posteriores, de la siguiente manera:

SELECT 
    SalesOrderID,
    OrderDate,
    TotalDue,
    LAG(TotalDue, 1, 0) OVER (ORDER BY OrderDate) AS PreviousOrderTotal,
    LEAD(TotalDue, 1, 0) OVER (ORDER BY OrderDate) AS NextOrderTotal,
    TotalDue - LAG(TotalDue, 1, 0) OVER (ORDER BY OrderDate) AS ChangeFromPrevious
FROM SalesLT.SalesOrderHeader
ORDER BY OrderDate;

El conjunto de resultados tiene este aspecto:

SalesOrderID   OrderDate    TotalDue    PreviousOrderTotal   NextOrderTotal   ChangeFromPrevious
------------   ----------   ---------   ------------------   --------------   ------------------
71774          2008-06-01   972.785     0                    87.083           972.785
71776          2008-06-01   87.083      972.785              42452.65         -885.702
71780          2008-06-01   42452.65    87.083               43962.79         42365.567
71782          2008-06-01   43962.79    42452.65             0                1510.14

LAG() recupera un valor de una fila anterior, mientras LEAD() recupera de una fila siguiente. El segundo parámetro especifica cuántas filas se van a mirar hacia atrás o adelante (el valor predeterminado es 1) y el tercer parámetro proporciona un valor predeterminado cuando no existe ninguna fila (por ejemplo, para la primera fila con LAG()). Use estas funciones para calcular los cambios de período a lo largo del período, identificar tendencias o detectar anomalías en datos secuenciales.

FIRST_VALUE() y LAST_VALUE() devuelven valores de la primera o última fila del marco:

SELECT 
    ProductID,
    Name,
    ListPrice,
    ProductCategoryID,
    FIRST_VALUE(Name) OVER (
        PARTITION BY ProductCategoryID 
        ORDER BY ListPrice DESC
    ) AS MostExpensiveInCategory,
    LAST_VALUE(Name) OVER (
        PARTITION BY ProductCategoryID 
        ORDER BY ListPrice DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS LeastExpensiveInCategory
FROM SalesLT.Product
WHERE ListPrice > 0;

El conjunto de resultados tiene este aspecto:

ProductID   Name                        ListPrice   ProductCategoryID   MostExpensiveInCategory    LeastExpensiveInCategory
---------   -------------------------   ---------   -----------------   ------------------------   ------------------------
749         Road-150 Red, 62            3578.27     5                   Road-150 Red, 62           LL Road Frame - Red, 58
750         Road-150 Red, 44            3578.27     5                   Road-150 Red, 62           LL Road Frame - Red, 58
722         LL Road Frame - Red, 58     337.22      5                   Road-150 Red, 62           LL Road Frame - Red, 58
771         Mountain-100 Silver, 38     3399.99     6                   Mountain-100 Silver, 38    Mountain-500 Black, 52

FIRST_VALUE() devuelve el valor de la primera fila de la ventana ordenada, que en este caso es el producto más caro por categoría. LAST_VALUE() devuelve el menos costoso, pero requiere un marco explícito para incluir todas las filas. Estas funciones le ayudan a comparar cada fila con valores de pruebas comparativas, como el valor más alto, más bajo o de línea de base de un grupo.

Nota:

LAST_VALUE() requiere una especificación de marco explícita para incluir filas después de la fila actual. Sin él, el marco predeterminado solo incluye filas hasta la fila actual, lo que devuelve LAST_VALUE() el valor de la fila actual.

PERCENT_RANK() y CUME_DIST() calculan la posición relativa dentro de una partición:

SELECT 
    Name,
    ListPrice,
    PERCENT_RANK() OVER (ORDER BY ListPrice) AS PercentRank,
    CUME_DIST() OVER (ORDER BY ListPrice) AS CumulativeDistribution
FROM SalesLT.Product
WHERE ListPrice > 0
ORDER BY ListPrice;

El conjunto de resultados tiene este aspecto:

Name                        ListPrice   PercentRank   CumulativeDistribution
-------------------------   ---------   -----------   ----------------------
Patch Kit/8 Patches         2.29        0.0           0.0081
Road Tire Tube              3.99        0.0081        0.0162
Touring Tire Tube           4.99        0.0162        0.0243
Road-150 Red, 62            3578.27     0.9919        1.0

PERCENT_RANK() devuelve un valor entre 0 y 1 que indica qué porcentaje de filas tienen valores inferiores (0 significa más bajo, uno significa más alto). CUME_DIST() muestra la distribución acumulativa, que indica qué porcentaje de filas tienen valores menores o iguales que la fila actual. Use estas funciones para el análisis de percentil, la identificación de valores atípicos o la creación de informes de distribución.

Para obtener más información sobre las funciones de ventana, vea Funciones de ventana (Transact-SQL) y Funciones de clasificación.

Comentarios

¿Le ha resultado útil esta página?

Aplicación de funciones de ventana para análisis

Comprender la sintaxis de la función de ventana

Uso de funciones de clasificación

Aplicación de funciones de ventana de agregado

Definir marcos de ventana con ROWS y RANGE

Uso de funciones analíticas

Comentarios

Definir marcos de ventana con `ROWS` y `RANGE`