Cómo utilizar SQL: 2011 funciones de la ventana

Añadido en SQL: 2011 son cinco funciones de la ventana que evalúan una expresión en una fila R2 que está en algún lugar en el marco de la ventana de la fila actual R1. Las funciones son LAG

Estas funciones le permiten extraer información de filas especificadas que están dentro del marco de la ventana de la fila actual.

Mirar hacia atrás con la función LAG

los LAG función le permite recuperar la información de la fila actual en la ventana que está examinando, así como información de otra fila que se especifica que precede a la fila actual.

Supongamos, por ejemplo, que tiene una tabla que registra las ventas totales para cada día del año en curso. Una cosa que usted puede ser que desee saber es cómo las ventas de hoy en comparación con la de ayer. Usted puede hacer esto con la LAG función, como sigue:

SELECT VentaTotal AS TodaySale, LAG (VentaTotal) OVER (ORDER BY Fecha de Venta) AS PrevDaySaleFROM DailyTotals-

Para cada fila de DailyTotals, esta consulta devolvería una fila lista cifra total de ventas de esa fila y la figura total de ventas del día anterior. El desplazamiento por defecto es 1, por lo que resultado del día anterior se devuelve en lugar de cualquier otro.

Para comparar las ventas del día actual a los de una semana antes, se puede utilizar el siguiente:

SELECT VentaTotal AS TodaySale, LAG (VentaTotal, 7) OVER (ORDER BY Fecha de Venta) AS PrevDaySaleFROM DailyTotals-

Los primeros siete filas en un marco de ventana no tendrán un antecesor que es una semana más. La respuesta por defecto a esta situación es para devolver un resultado nulo para PrevDaySale. Si prefiere algún otro resultado a un resultado nulo, por ejemplo cero, puede especificar lo que desea obtener en esta situación en lugar del valor nulo por defecto, por ejemplo, 0 (cero), como se muestra aquí:

SELECT VentaTotal AS TodaySale, LAG (VentaTotal, 7, 0) OVER (ORDER BY Fecha de Venta) AS PrevDaySaleFROM DailyTotals-

El comportamiento por defecto es contar filas que tienen un grado de retraso, que en este caso es VentaTotal, que contiene un valor nulo. Si desea pasar por alto esas filas y contar sólo las filas que tienen un valor real en el grado de retraso, puede hacerlo añadiendo las palabras clave IGNORE NULLS como se muestra en la siguiente variante del ejemplo:

SELECT VentaTotal AS TodaySale, LAG (VentaTotal, 7, 0) IGNORAR NULLSOVER (ORDER BY Fecha de Venta) AS PrevDaySaleFROM DailyTotals-

Mire hacia adelante con la función LEAD

los DIRIGIR función de ventana funciona exactamente de la misma manera la LAG función opera la excepción de que, en lugar de mirar hacia atrás a una fila anterior, se ve por delante a una fila después de la fila actual en el marco de la ventana. Un ejemplo podría ser:

SELECT VentaTotal AS TodaySale, LEAD (VentaTotal, 7, 0) IGNORAR NULLSOVER (ORDER BY Fecha de Venta) AS NextDaySaleFROM DailyTotals-

Mira a una fila especificada con la función NTH_VALUE

los NTH_VALUE función es similar a la LAG y DIRIGIR funciones, excepto que en lugar de evaluar una expresión en una fila antes o después de la fila actual, se evalúa una expresión en una fila que se encuentra en un desplazamiento especificado desde la primera o la última fila en el marco de la ventana.

He aquí un ejemplo:

SELECT VentaTotal AS ChosenSale, NTH_VALUE (VentaTotal, 2) DE FIRSTIGNORE NULLSOVER (ORDER BY Fecha de Venta) FILAS ENTRE 10 Y 10 ANTERIOR SIGUIENTE) AS EarlierSaleFROM DailyTotals-

En este ejemplo, EarlierSale se evalúa como sigue:

• Se forma el marco de la ventana asociada a la fila actual. Incluye los diez anteriores y los diez siguientes filas.

• VentaTotal se evalúa en cada fila del marco de la ventana.

• IGNORE NULLS se especifican, por lo que ninguna fila que contiene un valor nulo para VentaTotal se omiten.

• A partir del primer valor que queda después de la exclusión de las filas que contienen un valor nulo para VentaTotal, avanzar por dos filas (adelante porque DE PRIMERA se especificó).

El valor de EarlierSale es el valor de VentaTotal de la fila especificada.

Si no desea omitir filas que tienen un valor nulo para VentaTotal, especificar NULLS RESPETO Más bien que IGNORE NULLS. los NTH_VALUE función funciona de manera similar si se especifica DE ÚLTIMO en lugar de DE PRIMERA, excepto que en lugar de contar hacia adelante desde el primer registro en el marco de la ventana, se cuenta hacia atrás desde el último registro en el marco de la ventana.

El número que especifica el número de filas para contar sigue siendo positiva, a pesar de que usted está contando hacia atrás en lugar de hacia adelante.

Mira a un valor muy específico con FIRST_VALUE y LAST_VALUE

los FIRST_VALUE y LAST_VALUE funciones son casos especiales de la NTH_VALUE función. FIRST_VALUE es equivalente a NTH_VALUE dónde DE PRIMERA se especifica y el offset es 0 (cero). LAST_VALUE es equivalente a NTH_VALUE dónde DE ÚLTIMO se especifica y el offset es 0. En ambos, se puede optar por ignorar o respetar los valores nulos.