Por Kathi Kellenberger
He estado usando ROW_NUMBER en mi código T-SQL durante años sin darme cuenta de que es uno de un grupo de funciones increíblemente útiles llamadas funciones de ventana. Sólo al armar una presentación sobre las nuevas características de T-SQL en la versión de SQL Server 2012 me di cuenta de que había tanto que hablar que creé una presentación separada sólo sobre las nuevas funciones de ventana. Eventualmente esa presentación se convirtió en dos presentaciones, mi libro publicado por Apress, y un curso de Pluralsight. He estado hablando a todo el que quiera escuchar sobre estas grandes funciones, y me encuentro utilizándolas a diario en el trabajo.
Las funciones de ventana le permiten realizar cálculos sobre un conjunto de filas. Su funcionamiento es similar al de la agrupación con funciones agregadas, pero permiten una importante diferencia: Las consultas agregadas devuelven una fila por grupo, y los detalles se eliminan en los resultados. Las consultas con funciones de ventana devuelven todas las filas de detalle junto con el resultado de la expresión de la función de ventana. Usted tiene el detalle, y obtener el agregado también.
Cuando hablo en SQL Server y eventos de desarrolladores sobre las funciones de ventana, por lo general alrededor de la mitad de la sala está familiarizado con ROW_NUMBER. Un puñado sabrá más que eso. Eso es decepcionante ya que estas funciones útiles han estado disponibles durante años.
Mi función de ventana favorita se llama LAG. LAG le permite tomar cualquier columna de otra fila en el conjunto de resultados. Puede lograr lo mismo con otros métodos, pero el rendimiento es mucho peor. A continuación se muestran dos consultas contra la base de datos AdventureWorks. Ambas consultas dan el mismo resultado. La primera lo hace a través de un outer join, mientras que la segunda aprovecha el LAG.
–Lista los clientes con fechas de pedido y los días desde el pedido anterior
SELECT CustomerID, OrderDate,
DATEDIFF(day,OAPPLY.PrevOrder, OrderDate) AS DaysSincePrevOrder
FROM Sales.SalesOrderHeader AS SOH
OUTER APPLY (
SELECT TOP(1) OrderDate AS PrevOrder
FROM Sales.SalesOrderHeader AS OA
WHERE OA.CustomerID = SOH.CustomerID
AND OA.OrderDate < SOH.OrderDate
ORDER BY OrderDate) AS OAPPLY
ORDENAR POR ID de cliente, Fecha de pedido;
–Producir los mismos resultados utilizando LAG
SELECT CustomerID, OrderDate,
DATEDIFF(day,LAG(OrderDate) OVER(PARTITION BY CustomerID ORDER BY OrderDate),OrderDate) AS DaysSinceLastOrder
FROM Sales.SalesOrderHeader
ORDER BY CustomerID, OrderDate;
La segunda consulta que utiliza la función LAG es más sencilla de escribir y rinde mejor. El plan de ejecución real muestra que la primera consulta ocupa el 99% de los recursos del lote. La segunda consulta sólo necesita el 1% de los recursos. Aquí están los dos planes de ejecución, y usted puede ver por sí mismo:
Si usted no está familiarizado con la sintaxis de la función de ventana, la consulta LAG puede ser un poco confusa. Todas las funciones de ventana tendrán una cláusula OVER que define la ventana, o conjunto de filas, para el cálculo. No quiero comparar las fechas de los pedidos entre los clientes, así que he utilizado la opción PARTITION BY para dividir las filas en grupos por CustomerID:
LAG(OrderDate) OVER(PARTITION BY CustomerID …
La cláusula OVER también tiene una opción ORDER BY. Esto es necesario en el caso de LAG, para que las filas se alineen en el orden correcto para que el motor tome los valores respectivos de sus filas correctas. Mi invocación final de LAG tiene el siguiente aspecto:
LAG(OrderDate) OVER(PARTITION BY CustomerID ORDER BY OrderDate)
LAG es sólo una función de muchas funciones de ventana. Estas funciones no le permiten hacer nada que sea imposible sin ellas, pero definitivamente facilitan la codificación. También encontrará que muchas veces sus consultas se ejecutan más rápido cuando se formulan utilizando estas nuevas funciones.
Termino utilizando las funciones de ventana a diario. Me facilitan la vida y mejoran el rendimiento de mis consultas. Me encanta utilizarlas, y usted también debería hacerlo.
Acerca del autor
Kathi Kellenberger es consultora de Linchpin People. Le gusta escribir y hablar sobre temas de SQL Server, habiendo escrito más de dos docenas de artículos, contribuido a cuatro libros y presentado en muchos eventos de SQL Server. En su tiempo libre, Kathi disfruta pasando tiempo con su familia y amigos, cantando y montando en bicicleta.
Aprenda aún más sobre las funciones de ventana y su poder para resolver problemas de consulta difíciles en el libro de Kathi Kellenberger Expert T-SQL Window Functions in SQL Server (ISBN 9781484211045). Los otros libros de Kathi incluyen Beginning SQL Server Reporting Services (2016) y Beginning T-SQL (2014).
Deja una respuesta