El rincón de JMACOE

Dos de las mejores adiciones al T-SQL en SQL Server 2005 son las expresiones de tabla común (CTE), y la función Row_number (). En este artículo vamos a ver cómo se pueden utilizar estas dos características juntas para proporcionar una solución elegante a un problema histórico.

En primer lugar daremos una mirada muy rápida en cada uno de ellos.

Expresiones de tabla común

Un CTE es un conjunto de resultados temporales, que es válido para el ámbito de un sola declaración SELECT, UPDATE, INSERT o DELETE. He oído decir que este ámbito de aplicación muy restringida limita seriamente su utilidad, y que su única función es mejorar la legibilidad de tu SQL. (Escribir consultas recursivas es el área donde las CTE entran en juego – pero no las voy a cubrir este en este artículo.)

Espero demostrar en este artículo que la utilidad CTE no es puramente cosmético.

Un punto clave que a menudo se pasa por alto es que el conjunto de resultados se puede hacer referencia varias veces en la declaración S/U/I/D. Vamos a ver que este hecho será útil más adelante.

Row_number ()

La función row_number () ha estado en la lista de Navidad de la mayoría de los desarrolladores SQL desde hace muchos años. Proporciona el número de fila secuencial para cada registro de un conjunto de resultados. Un orden debe ser especificado (para brindar una base a la numeración) y, opcionalmente una partición se puede especificar para dividir el conjunto de resultados de numeración. Vamos a mirar más de cerca lo que significa esto con nuestro ejemplo.

Ejemplo del Precio Histórico

Nuestro cliente minorista está almacenando un historial de cambios de los precios en la tabla PriceHistory. Para cada elemento (en la tabla Items), podemos ver el precio inicial, y un registro adicional para cada cambio de precio subsecuente. El script de creación de la tabla está disponible a continuación en la sección de Recursos.

Los datos de la tabla PriceHistory

Al cliente le gustaría ver un informe que muestra el nombre del artículo, el precio antiguo, el nuevo precio y las fechas para las que se aplicó el nuevo precio. Este tipo de pregunta a menudo ha dado dolores de cabeza a los desarrolladores SQL, ya que suelen involucrar las subconsultas confusas con las funciones max/min. Algunos DBAs prefieren almacenar tanto la fecha de inicio y final en la tabla, pero esto también causa problemas, ya que es difícil mantener sincronizados los registros posteriores, sobre todo cuando se produce una modificación.please visit:– TRUSTOPT

El formato que estamos buscando

Si examinamos una línea del informe, podemos ver que incluye información de tres filas consecutivas en la tabla. La clave para resolver este problema radica en la vinculación de la fila actual a la fila anterior y la siguiente fila.

El primer paso es construir un CTE con los números de las filas de la tabla de datos. (Ten en cuenta que el punto y coma es necesario cuando el CTE no es la primera instrucción de un lote.)

;WITH PriceCompare AS (
SELECT i.Item, ph.ItemId, ph.PriceStartDate, ph.Price,
ROW_NUMBER() OVER (Partition BY ph.ItemId ORDER BY PriceStartDate) AS rownum 
FROM Items i INNER JOIN PriceHistory ph 
ON i.ItemId = ph.ItemId) 

SELECT * FROM PriceCompare 

En el CTE, puedo añadir una nueva columna, rownum, cuyo valor proviene de la función row_number (). Puedes ver que he particionado por ItemId lo que significa que se reanude la numeración con cada nuevo elemento. He ordenado sobre la columna PriceStartDate, para decir del CTE cómo se debe aplicar la numeración es decir, los primeros PriceStartDate de un artículo también tiene el número 1, etc.

Ahora, voy a mejorar la instrucción SELECT que sigue a la CTE.

SELECT currow.Item, prevrow.Price AS OldPrice, currow.Price AS RangePrice, currow.PriceStartDate AS StartDate, nextrow.PriceStartDate AS EndDate 
FROM PriceCompare currow 
LEFT JOIN PriceCompare nextrow 
        ON currow.rownum = nextrow.rownum - 1
        AND currow.ItemId = nextrow.ItemId
LEFT JOIN PriceCompare prevrow
        ON currow.rownum = prevrow.rownum + 1
        AND currow.ItemId = prevrow.ItemId

Ten en cuenta que uso LEFT JOINS para la primera fila de un artículo no tiene ningún registro anterior, al igual que la última fila no tiene ninguna fila siguiente.

Puedo usar el alias antes de cada campo para indicar de cual fila los datos deberían venir. Así PriceStartDate de la fila actual es la fecha de inicio, mientras que el mismo campo de la fila siguiente es la fecha final.

Ejecuta la consulta ahora, dara el conjunto de resultados necesarios para el informe de Historia del precio. Una ventaja adicional es que puedes envolver el CTE en una vista!

CREATE VIEW [dbo].[PriceCompare] AS
WITH PriceCompare AS 
(
SELECT i.Item, ph.ItemId, ph.PriceStartDate, ph.Price, 
ROW_NUMBER() OVER (Partition BY ph.ItemId ORDER BY PriceStartDate) AS rownum 
FROM 
        Items i 
INNER JOIN 
        PriceHistory ph 
ON i.ItemId = ph.ItemId
)

 SELECT
        currow.Item, 
        prevrow.Price AS OldPrice, 
        currow.Price AS RangePrice, 
        currow.PriceStartDate AS StartDate, 
        nextrow.PriceStartDate AS EndDate 
FROM 
        PriceCompare currow 
LEFT JOIN PriceCompare nextrow 
        ON currow.rownum = nextrow.rownum - 1 AND currow.ItemId = nextrow.ItemId 
LEFT JOIN PriceCompare prevrow 
        ON currow.rownum = prevrow.rownum + 1 AND currow.ItemId = prevrow.ItemId

Espero haber demostrado con este artículo por qué debes utilizar CTE en tus aplicaciones, y no sólo como una manera de poner en orden a tu SQL.

Recursos

CreationScripts.sql

Blogs similares