Al igual que las funciones de grupo, las funciones de
ventana también le permiten realizar cálculos de análisis de datos. La
diferencia entre los dos está, en cómo define el conjunto de filas para la
función con la que trabaja. Con funciones de grupo, utiliza consultas agrupadas
para organizar las filas consultadas en grupos, y luego las funciones de grupo
son aplicadas a cada grupo. Obtiene una fila de resultado por grupo, no por
fila originaria. Con funciones de ventana, se define el conjunto de filas por
función, y luego retorna un valor de resultado por cada función y fila
originaria. Se define el conjunto de filas para la función a trabajar con el
uso de una cláusula llamada OVER.
Esta post cubre tres tipos de funciones de ventana:
aggregate, ranking, y offset.
Funciones Aggregate de Ventana
Las funciones agregadas de ventana son las mismas, que
las funciones agregadas de agrupación (por ejemplo, SUM, COUNT, AVG, MIN y
MAX), excepto que las funciones agregadas de ventana son aplicadas a una
ventana de filas definidas por la cláusula OVER.
Uno de los beneficios de usar funciones de ventana, es
que a diferencia de las consultas agrupadas, las consultas con ventanas no
ocultan el detalle, retornan una fila por cada fila de consulta originaria.
Esto significa que puede mezclar detalles y elementos agregados en la misma
consulta, e incluso en la misma expresión. Usando la cláusula OVER, se define
un conjunto de filas para la función a trabajar por cada fila originaria. En
otras palabras, una consulta de ventana define una ventana de filas por cada
función y fila en la consulta originaria.
Como se ha mencionado, se utiliza una cláusula OVER para
definir una ventana de filas para la función. La ventana es definida con
respecto a la fila actual. Cuando utiliza paréntesis vacíos, la cláusula OVER
representa el conjunto resultado de la consulta originaria completa. Por
ejemplo, la expresión SUM(val) OVER() representa el total general de todas las
filas en la consulta originaria. Puede utilizar una cláusula de partición de
ventana para restringir la ventana. Por ejemplo, la expresión SUM(val)
OVER(PARTITION BY custid) representa el total de clientes actuales. Como un
ejemplo, si la fila actual tiene un custid de 1, la cláusula OVER filtra sólo estas
filas del conjunto resultado de la consulta originaria, donde el custid es 1; por
lo tanto, la expresión retorna el total para el cliente 1.
He aquí un ejemplo de una consulta sobre la vista
Sales.OrderValues, retornando para cada pedido el custid, orderid, y el valor
de pedido; utilizando funciones de ventana, la consulta también retorna el
total general de todos los valores y el total por cliente.
SELECT
custid, orderid,
val,
SUM(val) OVER(PARTITION BY custid) AS custtotal,
SUM(val) OVER()
AS grandtotal
FROM
Sales.OrderValues;
Esta consulta genera la siguiente salida (mostrado aquí
en forma abreviada).
custid orderid val custtotal
grandtotal
------- -------- -------
---------- -----------
1
10643
814.50 4273.00 1265793.22
1
10692
878.00 4273.00 1265793.22
1
10702 330.00 4273.00
1265793.22
1
10835 845.80 4273.00
1265793.22
1
10952 471.20 4273.00
1265793.22
1
11011 933.50 4273.00
1265793.22
2
10926 514.40 1402.95
1265793.22
2
10759 320.00 1402.95
1265793.22
2
10625 479.75 1402.95
1265793.22
2
10308 88.80 1402.95
1265793.22
...
El total general es, por supuesto, el mismo para todas
las filas. El total del cliente es el mismo para todas las filas con el mismo
custid.
Puede mezclar elementos de detalle y agregados de ventana
en la misma expresión. Por ejemplo, la siguiente consulta, calcula para cada pedido
el porcentaje del valor de pedido actual sobre el total del cliente, y también
el porcentaje del total general.
SELECT
custid, orderid,
val,
CAST(100.0*val/SUM(val) OVER(PARTITION BY custid) AS NUMERIC(5,2)) AS pctcust,
CAST(100.0*val/SUM(val) OVER() AS NUMERIC(5,2)) AS pcttotal
FROM Sales.OrderValues;
Esta consulta genera la siguiente salida (mostrada aquí
en forma abreviada).
custid orderid val pctcust
pcttotal
------- -------- ------- --------
---------
1
10643 814.50 19.06
0.06
1
10692 878.00 20.55
0.07
1
10702 330.00 7.72
0.03
1
10835 845.80 19.79 0.07
1
10952 471.20 11.03
0.04
1
11011 933.50 21.85
0.07
2
10926 514.40 36.67
0.04
2
10759 320.00 22.81
0.03
2
10625 479.75 34.20
0.04
2
10308 88.80 6.33
0.01
...
La suma de todos los porcentajes sobre el total general
es 100. La suma de todos los porcentajes sobre el total de clientes es 100, por
cada partición de filas con el mismo cliente.
Las funciones agregadas de ventana soportan, otra opción
de filtrado llamado enmarcado. La idea es que defina el ordenamiento en la partición
utilizando una cláusula de orden de ventana, y luego basado en ese orden, puede
confinar un marco de filas entre dos delimitadores. Define los delimitadores
utilizando una cláusula marco de ventana. La cláusula marco de ventana requiere
que una cláusula de orden de ventana este presente, debido a que un conjunto no
tiene orden; y sin orden, el limitar las filas entre dos delimitadores podría
no tener sentido.
En la cláusula marco de ventana, indica las unidades marco
de ventana (ROWS o RANGE) y las extensiones marco de ventana (los
delimitadores). Con la unidad marco de ventana ROWS, puede indicar los
delimitadores como una de las tres opciones:
·
UNBOUNDED
PRECEDING o FOLLOWING, es decir, el principio o el fin de la partición,
respectivamente
·
CURRENT
ROW, obviamente, representando la fila actual
·
<n> ROWS PRECEDING o FOLLOWING, es decir,
n filas antes o después de la actual,
respectivamente
A modo de ejemplo, suponga que quiere consultar la vista
Sales.OrderValues y calcular los valores totales acumulados, desde el comienzo
de la actividad del cliente actual hasta el pedido actual. Necesita utilizar el
agregado SUM. Particionar la ventana por custid. Ordenar la ventana por
orderdate, orderid. Luego, enmarcar las filas desde el inicio de la partición
(UNBOUNDED PRECEDING) hasta la fila actual. Su consulta debería ser similar a
la siguiente.
SELECT custid, orderid, orderdate, val,
SUM(val) OVER(PARTITION BY custid
ORDER BY orderdate, orderid
ROWS BETWEEN UNBOUNDED PRECEDING
AND
CURRENT ROW) AS runningtotal
FROM Sales.OrderValues;
Esta consulta genera la siguiente salida (mostrada aquí
en forma abreviada).
custid orderid orderdate val runningtotal
------- -------- -----------
------- -------------
1
10643
2007-08-25 814.50 814.50
1
10692
2007-10-03 878.00 1692.50
1
10702 2007-10-13 330.00 2022.50
1
10835 2008-01-15 845.80 2868.30
1
10952 2008-03-16 471.20 3339.50
1
11011 2008-04-09 933.50 4273.00
2
10308 2006-09-18 88.80 88.80
2
10625 2007-08-08 479.75 568.55
2
10759 2007-11-28 320.00 888.55
2
10926 2008-03-04 514.40 1402.95
...
Observe cómo los valores se van acumulando desde el
principio de la partición de cliente hasta la fila actual. Por cierto, en vez
de la forma detallada de la extensión marco ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW, puede utilizar la forma más corta ROWS UNBOUNDED PRECEDING, y
mantener el mismo significado.
Usando las funciones agregadas de ventana, para realizar
cálculos como totales acumulados, normalmente obtiene mucho mejor rendimiento en
comparación con el uso de joins o subconsultas y funciones agregadas de grupo. Las
funciones de ventana nos dan una buena optimización, especialmente cuando se
utiliza UNBOUNDED PRECEDING como el primer delimitador.
En términos del procesamiento de consulta lógico, el
resultado de una consulta es logrado cuando lo obtiene de la fase SELECT,
después de que las fases FROM, WHERE, GROUP BY y HAVING han sido procesadas.
Debido a que supuestamente las funciones de ventana operan, sobre el conjunto resultado
de la consulta originaria, son permitidos sólo en las cláusulas SELECT y ORDER
BY. Si necesita referirse al resultado de una función de ventana en cualquier
cláusula, que es evaluada antes de la cláusula SELECT, necesita utilizar una
expresión de tabla. Se invoca la función de ventana en la cláusula SELECT de la
consulta interna, asignando la expresión con un alias de columna. Luego, puede
referirse a ese alias de columna en la consulta externa en todas las cláusulas.
Por ejemplo, suponga que necesita filtrar el resultado de
la última consulta, retornando sólo esas filas donde el total acumulado es
inferior a 1,000.00. El siguiente código logra esto definiendo una expresión de
tabla común (CTE), basada en la consulta anterior y luego haciendo el filtrado
en la consulta externa.
WITH
RunningTotals AS
(
SELECT custid, orderid, orderdate, val,
SUM(val) OVER(PARTITION BY custid
ORDER BY orderdate, orderid
ROWS BETWEEN UNBOUNDED PRECEDING
AND
CURRENT ROW) AS runningtotal
FROM Sales.OrderValues
)
SELECT
*
FROM
RunningTotals
WHERE runningtotal <
1000.00;
Esta consulta genera la siguiente salida (mostrada aquí
en forma abreviada).
custid orderid orderdate val runningtotal
------- -------- -----------
------- -------------
1
10643 2007-08-25 814.50 814.50
2
10308 2006-09-18 88.80 88.80
2
10625 2007-08-08 479.75 568.55
2
10759 2007-11-28 320.00 888.55
3
10365 2006-11-27 403.20 403.20
...
Como otro ejemplo de una extensión marco de ventana, si
quiere que el marco incluya sólo las tres últimas filas, debería utilizar la
forma ROWS BETWEEN 2 PRECEDING AND CURRENT ROW.
En cuanto a la extensión marco de ventana RANGE, de
acuerdo al SQL estándar, se permite definir los delimitadores basados en offsets
lógicos de la llave de ordenación de la fila actual. Recuerde que ROWS define
los delimitadores basados en offsets físicos en términos del número de filas de
la fila actual. Sin embargo, SQL Server 2012 tiene una implementación muy
limitada de la opción RANGE, soportando sólo UNBOUNDED PRECEDING o FOLLOWING y CURRENT
ROW como delimitadores. Una diferencia sutil entre ROWS y RANGE cuando se utiliza
los mismos delimitadores es que el primero no incluye pares (filas empatadas en
términos de la llave de ordenación) y el segundo si lo hace.
ROWS versus RANGE
En SQL Server 2012, la opción ROWS
generalmente es optimizada mucho mejor que RANGE, cuando se utilizan los mismos
delimitadores. Si se define una ventana con una cláusula de orden de ventana,
pero sin una cláusula marco de ventana, el predeterminado es RANGE BETWEEN
UNBOUNDED PRECEDING AND CURRENT ROW. Por lo tanto, a menos que después del comportamiento
especial que obtiene de RANGE incluya pares, asegúrese de utilizar
explícitamente la opción ROWS.
Funciones Ranking de Ventana
Con funciones ranking de ventana, puede rankear las filas
en una partición basada en un ordenamiento especificado. Al igual que con las
otras funciones de ventana, si no indica una cláusula de partición de ventana,
el resultado de consulta originario completo es considerado una partición. La
cláusula de orden de ventana es obligatoria. Las funciones ranking de ventana
no soportan una cláusula marco de ventana. T-SQL soporta cuatro funciones
ranking de ventana: ROW_NUMBER, RANK, DENSE_RANK y NTILE.
La consulta siguiente demuestra el uso de estas
funciones.
SELECT
custid, orderid,
val,
ROW_NUMBER() OVER(ORDER BY val) AS rownum,
RANK()
OVER(ORDER BY val) AS rnk,
DENSE_RANK() OVER(ORDER BY val) AS densernk,
NTILE(100) OVER(ORDER BY val) AS ntile100
FROM Sales.OrderValues;
Esta consulta genera la siguiente salida (mostrada aquí
en forma abreviada).
custid orderid val rownum
rnk densernk ntile100
-------
-------- ------ ------- ---- --------- ---------
12
10782 12.50 1 1
1 1
27
10807 18.40 2 2
2 1
66
10586 23.80 3 3
3 1
76
10767 28.00 4 4
4 1
54
10898 30.00 5 5
5 1
88
10900 33.75 6 6
6 1
48
10883 36.00 7 7
7 1
41
11051 36.00 8 7
7 1
71
10815 40.00 9 9
8 1
38
10674 45.00 10 10
9 2
53
11057 45.00 11 10
9 2
75
10271 48.00 12 12
10 2
...
Importante:
Orden de Presentación versus Orden de Ventana
La consulta de muestra no tiene una cláusula
ORDER BY de presentación, y por lo tanto, no hay garantía de que las filas serán
presentadas en algún orden en particular. La cláusula de orden de ventana sólo
determina el ordenamiento para el cálculo de función de ventana. Si se invoca
una función de ventana en su consulta, pero no especifica una cláusula ORDER BY
de presentación, no hay garantía de que las filas serán presentadas en el mismo
orden como el orden de función de ventana. Si necesita una garantía, necesita
agregar una cláusula ORDER BY de presentación.
La función ROW_NUMBER calcula un entero secuencial único
a partir de 1 en la partición de ventana basado en el orden de ventana. Debido
a que la consulta de ejemplo no tiene una cláusula de partición de ventana, la
función considera el conjunto resultado de consulta completa, como una
partición; por lo tanto, la función asigna números de fila únicos a través del
conjunto de resultados de consulta completo.
Note que si el ordenamiento no es único, la función
ROW_NUMBER no es determinística. Por ejemplo, note en el resultado, que dos
filas tienen el mismo valor de ordenamiento de 36.00, pero las dos filas obtienen
diferentes números de fila. Eso es porque la función debe generar enteros
únicos, en la partición. Actualmente, no hay un desempate explícito, y por lo
tanto la elección de que fila obtiene el número de fila más alto es arbitrario
(dependiente de la optimización). Si necesita un cálculo determinístico
(resultados repetibles garantizados), necesita agregar un desempate. Por
ejemplo, puede agregar la llave primaria para hacer el ordenamiento único, como
en ORDER BY val, orderid.
RANK y DENSE_RANK difieren de ROW_NUMBER en el sentido
que asigna el mismo valor rango a todas las filas que comparten el mismo valor
de ordenamiento. La función RANK retorna el número de filas en la partición que
tiene un valor de ordenamiento menor que el actual, más 1. Por ejemplo,
considere las filas en el resultado de consulta de ejemplo que tiene un valor
de ordenamiento de 45.00. Nueve filas tienen valores de ordenamiento que son
menores que 45.00; por lo tanto, estas filas obtuvieron el rango de 10 (9 + 1).
La función DENSE_RANK retorna el número de valores de ordenamiento
distintos que son más bajos que el actual, más 1. Por ejemplo, las mismas filas
que obtuvieron el rango de 10 obtuvieron el rango denso de 9. Eso es porque
estas filas tienen un valor de ordenamiento de 45.00, y hay ocho valores de
ordenamiento distintos que son inferiores que 45.00. Debido a que RANK
considera filas y DENSE_RANK considera valores distintos, el primero puede
tener brechas entre los valores ranking de resultado, y el último no puede
tener brechas. Debido a que las funciones RANK y DENSE_RANK calculan el mismo
valor ranking a las filas con el mismo valor de ordenamiento, ambas funciones
son determinísticas, incluso cuando el ordenamiento no es único. De hecho, si
utiliza el ordenamiento único, ambas funciones retornan el mismo resultado como
la función ROW_NUMBER. Así que usualmente estas funciones son interesantes de
utilizar cuando el ordenamiento no es único.
Con la función NTILE, puede organizar las filas en la
partición, en un número solicitado de fichas de igual tamaño, basado en el
orden especificado. Se especifica el número deseado de fichas como entrada a la
función. En la consulta de ejemplo, solicitó 100 fichas. Hay 830 filas en el
conjunto resultado, y por lo tanto el tamaño de ficha base es 830/100 = 8 con
un resto de 30. Debido a que hay un resto de 30, las primeras 30 fichas son
asignados con una fila adicional.
A saber, las fichas 1 al 30 tendrán nueve filas cada una,
y todas las fichas restantes (31 a 100) tendrán ocho filas cada una. Observe en
el resultado de esta consulta de muestra que las primeras nueve filas (de
acuerdo al ordenamiento val) son asignados con la ficha número 1, entonces las siguientes
nueve filas son asignadas con la ficha número 2, y así sucesivamente. Como
ROW_NUMBER, la función NTILE no es determinística cuando el ordenamiento no es
único. Si necesita garantizar el determinismo, necesita definir el ordenamiento
único.
Tema Clave
Como se explica en la discusión de las
funciones agregadas de ventana, las funciones de ventana son permitidas sólo en
las cláusulas SELECT y ORDER BY de la consulta. Si necesita referirse a ellas
en otras cláusulas, por ejemplo, en la cláusula WHERE, necesita usar una
expresión de tabla, tal como un CTE. Se invoca la función de ventana en la
cláusula SELECT de la consulta interna, asignando la expresión con un alias de
columna. Luego se refiere a ese alias de columna en la consulta WHERE de la
consulta externa.
Funciones Offset de Ventana
Las funciones offset de ventana retornan un elemento de
una sola fila que está en un offset dado de la fila actual en la partición de
ventana, o de la primera o la última fila en el marco de ventana. T-SQL soporta
las siguientes funciones offset de ventana: LAG, LEAD, FIRST_VALUE y
LAST_VALUE. Las funciones LAG y LEAD confían en un offset con respecto a la
fila actual, y las funciones FIRST_VALUE y LAST_VALUE operan en la primera o la
última fila en el marco, respectivamente.
Las funciones LAG y LEAD soportan cláusulas de partición
y de ordenamiento de ventana. No soportan una cláusula marco de ventana. La
función LAG retorna un elemento de la fila en la partición actual, que es un
número solicitado de filas antes de la fila actual (basado en la ordenación de
ventana), con 1 asumido como el offset predeterminado. La función LEAD retorna
un elemento de la fila que está en el offset solicitado después de la fila
actual.
A modo de ejemplo, la siguiente consulta utiliza las
funciones LAG y LEAD para retornar junto con cada pedido, el valor del pedido
del cliente anterior, además del valor del pedido del cliente posterior.
SELECT
custid, orderid,
orderdate, val,
LAG(val) OVER(PARTITION BY custid
ORDER BY orderdate, orderid) AS prev_val,
LEAD(val) OVER(PARTITION BY custid
ORDER BY orderdate, orderid) AS next_val
FROM Sales.OrderValues;
Esta consulta genera la siguiente salida (que se muestra
aquí en forma abreviada).
custid orderid orderdate val prev_val next_val
-------
-------- ----------- ------- --------- ---------
1
10643 2007-08-25 814.50 NULL
878.00
1
10692 2007-10-03 878.00 814.50
330.00
1
10702 2007-10-13 330.00 878.00
845.80
1
10835 2008-01-15 845.80 330.00
471.20
1
10952 2008-03-16 471.20 845.80
933.50
1
11011 2008-04-09 933.50 471.20
NULL
2
10308 2006-09-18 88.80 NULL
479.75
2
10625 2007-08-08 479.75 88.80
320.00
2
10759 2007-11-28 320.00 479.75
514.40
2
10926 2008-03-04 514.40 320.00
NULL
...
Debido a que un \ explícito no fue especificado, ambas
funciones confían en el offset predeterminado de 1. Si quiere un offset
diferente de 1, se le especifica como el segundo argumento, como en LAG(val,
3). Note que si una fila no existe en el offset solicitado, la función retorna
un NULL por defecto. Si desea retornar un valor diferente en tal caso, especifíquelo
como el tercer argumento, como en LAG(val, 3, 0).
Las funciones FIRST_VALUE y LAST_VALUE retornan una expresión
de valor de la primera o la última fila en el marco de ventana,
respectivamente. Naturalmente, las funciones soportan cláusulas de partición de
ventana, de orden y de marco. A modo de ejemplo, la siguiente consulta retorna
junto con cada pedido los valores del primer y último pedido del cliente.
SELECT
custid, orderid,
orderdate, val,
FIRST_VALUE(val) OVER(PARTITION BY custid
ORDER
BY orderdate,
orderid
ROWS
BETWEEN UNBOUNDED
PRECEDING
AND CURRENT ROW) AS first_val,
LAST_VALUE(val) OVER(PARTITION BY custid
ORDER
BY orderdate,
orderid
ROWS
BETWEEN CURRENT
ROW
AND
UNBOUNDED FOLLOWING) AS last_val
FROM Sales.OrderValues;
Esta consulta genera la siguiente salida (mostrada aquí
en forma abreviada).
custid orderid orderdate val first_val last_val
-------
-------- ----------- ------- ---------- ----------
1
11011 2008-04-09 933.50 814.50
933.50
1
10952 2008-03-16 471.20 814.50
933.50
1
10835 2008-01-15 845.80 814.50
933.50
1
10702 2007-10-13 330.00 814.50
933.50
1
10692 2007-10-03 878.00 814.50
933.50
1
10643 2007-08-25 814.50 814.50
933.50
2
10926 2008-03-04 514.40 88.80
514.40
2
10759 2007-11-28 320.00 88.80
514.40
2
10625 2007-08-08 479.75 88.80
514.40
2
10308 2006-09-18 88.80 88.80
514.40
...
FRAME Predeterminado
y Rendimiento de RANGE
Como recordatorio, cuando
un marco de ventana es aplicable a una función, pero no se especifica una
cláusula marco de ventana explícita, el predeterminado es RANGE BETWEEN
UNBOUNDED PRECEDING AND CURRENT ROW. Por motivos de rendimiento, en general se
recomienda evitar la opción RANGE; para ello, tiene que ser explícito con la
cláusula ROWS. Además, si esta después de la primera fila en la partición,
utilizando la función FIRST_VALUE con el marco por defecto al menos le da el
resultado correcto. Sin embargo, si está después de la última fila en la
partición, utilizar la función LAST_VALUE con el marco por defecto no le dará
lo que quiere, porque la última fila en el marco predeterminado es la fila
actual. Así que con LAST_VALUE, necesita ser explícito sobre el marco de
ventana con el fin de obtener lo que está después. Y si necesita un elemento de
la última fila en la partición, el segundo delimitador en el marco debería ser UNBOUNDED
FOLLOWING.
Ejercicio 1: Usar Funciones Agregadas de Ventana
En este ejercicio, se le da una tarea que requiere
escribir consultas utilizando funciones agregadas de ventana. Trate primero de
llegar con su propia solución, antes de mirar a la proporcionada.
1. Abra el SSMS y conéctese a la base de
datos de muestra TSQL2012.
2. Escriba una consulta sobre la vista Sales.OrderValues
que retorna por cada cliente y pedido el valor promedio de movimiento de los tres
últimos pedidos del cliente.
Su consulta de solución debería ser
similar a la siguiente consulta.
SELECT custid, orderid, orderdate, val,
AVG(val) OVER(PARTITION BY custid
ORDER BY orderdate, orderid
ROWS BETWEEN 2 PRECEDING
AND
CURRENT ROW) AS movingavg
FROM
Sales.OrderValues;
Esta consulta genera el siguiente
resultado, mostrada aquí en forma abreviada.
custid orderid orderdate val movingavg
------ -------- -----------
------- -----------
1 10643 2007-08-25
814.50 814.500000
1 10692 2007-10-03
878.00 846.250000
1 10702
2007-10-13 330.00 674.166666
1 10835 2008-01-15
845.80 684.600000
1 10952 2008-03-16
471.20 549.000000
1 11011 2008-04-09
933.50 750.166666
2 10308 2006-09-18
88.80 88.800000
2 10625 2007-08-08
479.75 284.275000
2 10759 2007-11-28
320.00 296.183333
2 10926 2008-03-04
514.40 438.050000
...
Ejercicio 2: Utilice Funciones Ranking y Offset de Ventana
En este ejercicio, tiene tareas dadas que requieren que
escriba consultas utilizando funciones ranking y offset de ventana. Se le pide
filtrar filas basadas en el resultado de una función de ventana, y escribir
expresiones que mezclan elementos de detalle y funciones de ventana.
1. Como la siguiente tarea, escriba una
consulta sobre la tabla Sales.Orders, y filtre los tres pedidos con los valores
de flete más altos por cada distribuidor utilizando orderid como desempate.
Necesita utilizar la función
ROW_NUMBER para filtrar las filas deseadas. Pero recuerde que no se permite
referirse a funciones de ventana directamente en la cláusula WHERE. La solución
es definir una expresión de tabla basada en una consulta que invoca la función
ROW_NUMBER y asigna la expresión con un alias de columna. Entonces puede
manejar el filtrado de la consulta externa utilizando ese alias de columna.
Aquí está la consulta de solución completa.
WITH C AS
(
SELECT
shipperid, orderid,
freight,
ROW_NUMBER()
OVER(PARTITION BY
shipperid
ORDER
BY freight DESC, orderid) AS rownum
FROM Sales.Orders
)
SELECT shipperid, orderid, freight
FROM C
WHERE rownum <= 3
ORDER BY shipperid, rownum;
Esta consulta genera el siguiente
resultado.
shipperid orderid freight
---------- -------- ---------
1 10430 458.78
1 10836 411.88
1 10658 364.15
2 10372 890.78
2 11030 830.75
2 10691 810.05
3 10540 1007.64
3 10479 708.95
3 11032 606.19
2. Así como su última tarea, consulte la
vista Sales.OrderValues. Necesita calcular la diferencia entre el valor del pedido
actual y el valor del pedido previo del cliente, además a la diferencia entre
el valor de pedido actual y el valor de pedido siguiente del cliente.
Para obtener los valores de los pedidos
anteriores y posteriores del cliente, puede utilizar las funciones LAG y LEAD,
respectivamente. Luego, puede restar los resultados de esas funciones de la
columna val para obtener las diferencias deseadas. Aquí está la consulta de solución
completa.
SELECT
custid, orderid,
orderdate, val,
val - LAG(val) OVER(PARTITION BY custid
ORDER
BY orderdate,
orderid) AS
diffprev,
val - LEAD(val) OVER(PARTITION BY custid
ORDER
BY orderdate,
orderid) AS
diffnext
FROM
Sales.OrderValues;
Esta consulta genera el siguiente
resultado, que se muestra aquí en forma abreviada.
custid
orderid orderdate val
diffprev diffnext
------- -------- -----------
------- --------- ---------
1 10643 2007-08-25
814.50 NULL -63.50
1 10692 2007-10-03
878.00 63.50 548.00
1 10702 2007-10-13
330.00 -548.00 -515.80
1 10835 2008-01-15
845.80 515.80 374.60
1 10952 2008-03-16
471.20 -374.60 -462.30
1 11011 2008-04-09
933.50 462.30 NULL
2 10308 2006-09-18
88.80 NULL -390.95
2 10625 2007-08-08
479.75 390.95 159.75
2 10759 2007-11-28
320.00 -159.75 -194.40
2 10926 2008-03-04
514.40 194.40 NULL
...
