Las funciones de ventana (o Windowing) son una gran manera de obtener diferentes perspectivas de un conjunto de datos sin tener que hacer repetidas llamadas al servidor para esos datos. Por ejemplo, podemos recoger la suma de una columna y mostrarla junto a los datos a nivel de detalle, de forma que «ImporteVentas» y «SUM(ImporteVentas)» pueden aparecer en la misma fila. También podemos realizar funciones analíticas como PERCENT_RANK y funciones de clasificación como ROW_NUMBER, todo ello sin alterar la granularidad del conjunto de resultados ni realizar viajes adicionales para obtener los mismos datos de origen una y otra vez.
«Observe cómo equilibro sin esfuerzo dos cálculos renales de Val Kilmer. Muy matón!»
Todas las funciones de ventana utilizan la cláusula OVER(), que se usa para definir cómo se evalúa la función. La cláusula OVER() acepta tres argumentos diferentes:
- PARTITION BY: Reinicia su contador cada vez que la(s) columna(s) indicada(s) cambia(n) de valor.
- ORDER BY: Ordena las filas que la función evaluará. Esto no ordena todo el conjunto de resultados, sólo la forma en que la función procede a través de las filas.
- FILAS ENTRE: Especifica cómo limitar aún más las filas evaluadas por la función.
Supongamos que estamos viendo datos simplificados de una competición de levantamiento de pesas. Aquí hay algunos ejemplos de código (los haremos todos en una sentencia SELECT porque añadir/quitar funciones de ventana no cambia en absoluto el número de filas que obtenemos):
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
|
SELECT
LiftID
, LiftDate
, LiftPersonID
, LiftWeight
/* ROW_NUMBER listará el número de la fila, ordenado por LiftID.
El contador se reiniciará con cada nueva combinación de LiftDate y LiftPersonID */
, ROW_NUMBER() OVER (PARTITION BY LiftDate, LiftPersonID ORDER BY LiftID) AS LiftNumForToday
/* SUM sumará los pesos levantados.
La primera SUMA mostrará el total general de todo el conjunto de resultados.
La segunda SUMA mostrará el peso total levantado para la fecha de levantamiento de esa fila.
La tercera SUMA mostrará el peso total de levantamiento para la fecha de levantamiento de esa fila y la persona.
, SUM(LiftWeight) OVER () AS WeightGrandTotal
, SUM(LiftWeight) OVER (PARTITION BY LiftDate) AS WeightTotal
, SUM(LiftWeight) OVER (PARTITION BY LiftDate, LiftPersonID) AS PersonWeightTotal
/* AVG mostrará el peso medio levantado.
El primer AVG mostrará el peso medio levantado para la fecha de levantamiento de esa fila.
El segundo AVG mostrará el peso medio levantado para la fecha de levantamiento de esa fila y la persona. */
, AVG(LiftWeight) OVER (PARTITION BY LiftDate) AS PersonWeightAvg
, AVG(LiftWeight) OVER (PARTITION BY LiftDate, LiftPersonID) AS PersonDayWeightAvg
/* LAG y LEAD permiten que la fila actual informe sobre los datos de las filas posteriores o anteriores a ella.
Esta función LAG devolverá el LiftWeight de 1 fila detrás de ella (en orden de LiftID) y si no se encuentra ningún valor, devolverá 0 en lugar de NULL.
La función LEAD obtendrá el LiftWeight de 3 filas por delante. Como no hemos especificado el valor opcional por defecto (como el «0» que le dimos a la función LAG, devolverá NULL si no hay ninguna fila 3 filas por delante. */
, LAG(LiftWeight, 1, 0) OVER (ORDER BY LiftID) AS PrevLift
, LEAD(LiftWeight, 3) OVER (ORDER BY LiftID) AS NextLift
/* FIRST_VALUE AND LAST_VALUE devolverá el primer y último valor de la columna especificada en el conjunto de resultados.
Esta función FIRST_VALUE devolverá el primer LiftWeight del conjunto de resultados.
Esta función LAST_VALUE devolverá el último LiftWeight del conjunto de resultados.
***ADVERTENCIA: sin las FILAS ENTRE en el LAST_VALUE, puede obtener resultados inesperados.***
*/
, FIRST_VALUE(LiftWeight) OVER (ORDER BY LiftDate) AS FirstLift
, LAST_VALUE(LiftWeight) OVER (ORDER BY LiftDate ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastLift
/* SUM using ROWS BETWEEN will narrow the scope evaluated by the window function.
La función comenzará y terminará donde especifique ROWS BETWEEN.
La primera SUMA sumará todos los valores de LiftWeight en las filas hasta la fila actual inclusive.
La segunda SUMA sumará todos los valores de LiftWeight en las filas entre la fila actual y las 3 filas anteriores.
*/
, SUM(LiftWeight) OVER (ORDER BY LiftDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS WeightRunningTotal
, SUM(LiftWeight) OVER (ORDER BY LiftDate ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS WeightSumLast4
FROM dbo.Lifts
|
Consideraciones para las funciones de ventana
Si no tienes SQL Server 2012 o posterior, tu armario de funciones de ventana está bastante vacío; SQL Server 2005 hasta 2008 R2 sólo permitía PARTITION BY en la cláusula OVER de una función agregada, y tenías RANK() y ROW_NUMBER(). Eso era todo. Si usted es un desarrollador que todavía está en una de esas versiones anteriores, este es un caso convincente para pasar a 2012 o posterior. Piensa en el tiempo que podrías ahorrar al no escribir múltiples CTEs y en lo rápido que irán tus consultas.
Hablando de rapidez…
Al evitar los viajes de ida y vuelta al servidor para los mismos datos, reducimos la E/S en esas tablas. Si estamos golpeando los índices, podemos realmente reducir las lecturas involucradas. Hay una compensación, pero suele ser muy favorable. Las funciones de ventana requieren que SQL Server construya la ventana y calcule la función (mostrada como tareas como Window Spool, Segment, Sequence Project y Compute Scalar). Al hacerlo, añade lecturas a la tabla de trabajo. Sin embargo, esto es generalmente menos costoso que volver a obtener los datos de origen varias veces, agregarlos si es necesario, y unirlos todos. Además, Worktable existe en tempdb, que – idealmente – está en su nivel de almacenamiento más rápido.
Por último, recuerde que las limitaciones que usted pone en una función de ventana – PARTITION BY, ORDER BY, o ROWS BETWEEN – están ahí para aplicar el contexto a la función de ventana y de ninguna manera se aplican al conjunto de resultados en su conjunto. En otras palabras, su sentencia SELECT no se verá afectada por cualquier cosa que le diga a una función ventana que haga.