Funciones First_Value, Last_Value, Lag y Lead

Funciones First_Value, Last_Value, Lag y Lead

En este artículo se va a explicar el uso de las funciones First_Value, Last_Value, Lag y Lead, estas funciones permiten mostrar un conjunto de resultados para analizarlos de acuerdo a como se van presentando.

Función First_Value

Devuelve el primer valor en un conjunto ordenado de valores, la función First_Value está disponible desde SQL Server 2012.
Sintaxis:
First_Value ( [ExpresiónNumérica ] )
OVER ( [ExpresiónPartición] ExpresiónOrden [ RangoFilas ] )

Donde
[ExpresiónNumérica]
Es el valor a devolver, puede ser una columna, una subconsulta u otra
expresión que dé como resultado un solo valor.
Otras funciones analíticas no están permitidas.
OVER ( [ExpresiónPartición] ExpresiónOrden [ RangoFilas ] )
Divide el conjunto de resultados producido por la cláusula FROM en particiones a las que se aplica la función. Si no se especifica, la función trata todas las filas del conjunto de resultados de la consulta como un solo grupo.
ExpresiónOrden determina el orden lógico en el que se realiza la operación. Esta expresión es obligatoria
RangoFilas limita aún más las filas dentro de la partición especificando los puntos de inicio y final.

Función Last_Value

Devuelve el último valor en un conjunto ordenado de valores, la función Last_Value está disponible desde SQL Server 2012.
Sintaxis:
Last_Value ( [ExpresiónNumérica ] )
OVER ( [ExpresiónPartición] ExpresiónOrden [ RangoFilas ] )

Donde
[ExpresiónNumérica]
Es el valor a devolver, puede ser una columna, una subconsulta u otra
expresión que dé como resultado un solo valor.
Otras funciones analíticas no están permitidas.
OVER ( [ExpresiónPartición] ExpresiónOrden [ RangoFilas ] )
Divide el conjunto de resultados producido por la cláusula FROM en particiones a las que se aplica la función. Si no se especifica, la función trata todas las filas del conjunto de resultados de la consulta como un solo grupo.
ExpresiónOrden determina el orden lógico en el que se realiza la operación. Esta expresión es obligatoria
RangoFilas limita aún más las filas dentro de la partición especificando los puntos de inicio y final.

Ejemplos

Usando Northwind
use Northwind
go

Ejercicio 1

Listar los productos que son menos costoso y más costoso en una determinada categoría, para este ejemplo se muestran los productos de la categoria 3.
select P.ProductName As ‘Producto’, P.UnitPrice As ‘Precio’,
FIRST_VALUE(P.ProductName) over (Order by P.UnitPrice Asc) As ‘Más Barato’,
Last_VALUE(P.ProductName) over (Order by P.UnitPrice Asc) As ‘Más Caro’
from Products As P
where P.CategoryID = 3
Order by P.UnitPrice asc
go

Explicación:
Para el primer producto, «Teatime Chocolate Biscuits», cuyo precio es 9.20, este, al ser el primer producto es el más barato y a la vez en más caro. Al mostrarse el segundo producto «Zaanse koeken», cuyo precio es de 9.50, en la columna más barato aparece «Teatime Chocolate Biscuits» cuyo precio es de 9.20, y el más caro aparece el mismo producto «Zaanse koeken» como el mas caro. Como han sido ordenados por el precio en orden ascendente, el primer producto «Teatime Chocolate Biscuits» aparece siempre como el más barato, luego el producto más caro va cambiando de acuerdo a como se muestran el resto de productos, hasta llegar al producto «Sir Rodney’s Marmalade» cuyo precio es 81.00 que es el más caro de la categoría.
La imagen muestra el resultado.

Ejercicio 2

Mostrar el más caro y el más barato de toda la categoría, usando la cláusula Rows.
select P.ProductName As ‘Producto’, P.UnitPrice As ‘Precio’,
FIRST_VALUE(P.ProductName) over (Order by P.UnitPrice Asc) As ‘Más Barato’,
Last_VALUE(P.ProductName) over (Order by P.UnitPrice Asc
rows between unbounded preceding and Unbounded following) As ‘Más Caro’
from Products As P
where P.CategoryID = 3
Order by P.UnitPrice asc
go

Explicación:
Compare el listado con la imagen anterior, en esta, usando la opción Rows se muestra para cada uno de los artículos el más barato de todos «Teatime Chocolate Biscuits» que tiene un precio de 9.20 y el más caro de todos «Sir Rodney’s Marmalade» con un precio de 81.00.
La imagen muestra el resultado.

Podemos incluir los productos de otra categoría y mostrar el código de la categoría para ver el resultado.
select P.ProductName As ‘Producto’, P.UnitPrice As ‘Precio’, P.CategoryID As ‘Cód. Categoría’,
FIRST_VALUE(P.ProductName) over (Order by P.UnitPrice Asc) As ‘Más Barato’,
Last_VALUE(P.ProductName) over (Order by P.UnitPrice Asc
rows between unbounded preceding and Unbounded following) As ‘Más Caro’
from Products As P
where P.CategoryID = 3 or P.CategoryID = 4
Order by P.CategoryID, P.UnitPrice asc
go

Explicación:
En este ejecicio, usando también la opción Rows, se muestra
para cada uno de los artículos el más barato de todos «Geitost» que tiene un precio de 2.50 y el más caro de todos «Sir Rodney’s Marmalade» con un precio de 81.00.
La imagen muestra el resultado.

Ejercicio 3

Particionando por categoría los que tienen menor y mayor unidades en Stock de las categorias 6 y 7

select
P.ProductName As ‘Producto’, P.UnitPrice As ‘Precio’,
P.UnitsInStock As ‘Stock’, P.CategoryID As ‘Cód. Categoría’,
FIRST_VALUE(P.ProductName)
over (Partition by P.CategoryID Order by P.UnitsInStock Asc) As ‘Menor Stock’,
Last_VALUE(P.ProductName)
over (Partition by P.CategoryID Order by P.UnitsInStock Asc) As ‘Mayor Stock’
from Products As P
where P.CategoryID in (6,7)
Order by P.CategoryID , P.UnitsInStock asc
go

Explicación:
Note que el artículo con menor stock es «Alice Mutton» con un valor de CERO para la categoría 6 y el de mayor stock es «Pâté chinois» con un valor de 115. Luego aparecen los artículos de la categoría 7, el de menor stock con 4 unidades es «Longlife Tofu» y el de mayor stock con 35 unidades es «Tofu«.
La imagen muestra el resultado.

Función Lag

Accede a los datos de una fila anterior en el mismo conjunto de resultados
sin el uso de una unión automática. LAG proporciona acceso a una fila en un desplazamiento físico dado que viene antes de la fila actual.
Utilice esta función analítica en una instrucción SELECT para comparar valores en la fila actual con valores en una fila anterior.
Sintaxis:
Lag ( ExpresiónNumérica [,offset] [,default] )
OVER ( [ExpresiónPartición] ExpresiónOrden )

Donde
[ExpresiónNumérica]

Es el valor a devolver, puede ser una columna, una subconsulta u otra
expresión que dé como resultado un solo valor.
Otras funciones analíticas no están permitidas.
OffSet
El número de filas desde la fila actual desde la que se obtiene un valor.
Si no se especifica, el valor predeterminado es 1. el desplazamiento puede
ser una columna, subconsulta u otra expresión que se evalúe como un entero positivo o se pueda convertir implícitamente a bigint. offset no puede ser un valor negativo o una función analítica.
Default
El valor a devolver cuando el desplazamiento está fuera del alcance de la
partición. Si no se especifica un valor predeterminado, se devuelve NULL.
el valor predeterminado puede ser una columna, una subconsulta u otra expresión, pero no puede ser una función analítica. el valor predeterminado debe ser compatible con el tipo con scalar_expression.
OVER ( [ExpresiónPartición] ExpresiónOrden)
Divide el conjunto de resultados producido por la cláusula FROM en particiones a las que se aplica la función. Si no se especifica, la función trata todas las filas del conjunto de resultados de la consulta como un solo grupo.
ExpresiónOrden determina el orden lógico en el que se realiza la operación. Esta expresión es obligatoria

Función Lead

Accede a datos de una fila posterior en el mismo conjunto de resultados
sin el uso de una unión automática que comienza con SQL Server 2012 (11.x).
LEAD proporciona acceso a una fila en un desplazamiento físico dado que sigue a la fila actual.
Use esta función analítica en una instrucción SELECT para comparar valores en la fila actual con valores en la siguiente fila.
Sintaxis:
Lead ( ExpresiónNumérica [,offset] [,default] )
OVER ( [ExpresiónPartición] ExpresiónOrden )

Donde
[ExpresiónNumérica]
Es el valor a devolver, puede ser una columna, una subconsulta u otra
expresión que dé como resultado un solo valor.
Otras funciones analíticas no están permitidas.
OffSet
El número de filas desde la fila actual desde la que se obtiene un valor.
Si no se especifica, el valor predeterminado es 1. el desplazamiento puede
ser una columna, subconsulta u otra expresión que se evalúe como un entero positivo o se pueda convertir implícitamente a bigint. offset no puede ser un valor negativo o una función analítica.
Default
El valor a devolver cuando el desplazamiento está fuera del alcance de la
partición. Si no se especifica un valor predeterminado, se devuelve NULL.
el valor predeterminado puede ser una columna, una subconsulta u otra expresión, pero no puede ser una función analítica. el valor predeterminado debe ser compatible con el tipo con scalar_expression.
OVER ( [ExpresiónPartición] ExpresiónOrden)
Divide el conjunto de resultados producido por la cláusula FROM en particiones a las que se aplica la función. Si no se especifica, la función trata todas las filas del conjunto de resultados de la consulta como un solo grupo.
ExpresiónOrden determina el orden lógico en el que se realiza la operación. Esta expresión es obligatoria

Ejemplos

Ejercicio 4

Este ejemplo utiliza una variable tipo tabla (Ver variables tipo tabla)
Se insertan datos de Ordenes en fechas determinadas, código del producto y cantidad vendida en la orden. (Ver Insertar registros)
Luego se muestran los datos mostrando la fecha más antigua y la más actual ordenados de acuerdo a la cantidad de producto vendido,
así como los fechas límites teniendo en cuenta ordenamiento según fecha.

set dateformat dmy
Declare @Ordenes As table
(OrdenesFecha date, ProductoCodigo int,
ProductoDescripcion nvarchar(40), Cantidad Numeric(9,2))
INSERT INTO @Ordenes VALUES
(’23/07/2013′, 142, ‘Impresora’, 74),
(’06/08/2013′, 123, ‘Teclado’, 95),
(’15/08/2013′, 101, ‘Mouse’, 38),
(’11/09/2013′, 130, ‘Monitor’, 12),
(’30/07/2013′, 142, ‘Impresora’, 29),
(’18/06/2013′, 142, ‘Impresora’, 100),
(’10/02/2013′, 142, ‘Impresora’, 40),
(’16/11/2013′, 101, ‘Mouse’, 28),
(’21/11/2013′, 123, ‘Teclado’, 57),
(’23/11/2013′, 101, ‘Mouse’, 12)
–Listado de las órdenes
SELECT
Format(OrdenesFecha,’dd/MM/yyy’) As ‘Fecha’,
ProductoCodigo As ‘Código’, ProductoDescripcion As ‘Producto’, Cantidad,
‘Fecha menor venta’ = Format(FIRST_VALUE(OrdenesFecha)
OVER(Partition BY ProductoCodigo Order by Cantidad),’dd/MM/yyy’),
‘Fecha mayor venta’ = Format(LAST_VALUE(OrdenesFecha)
OVER(Partition BY ProductoCodigo ORDER BY Cantidad
Rows Between UnBounded Preceding And UnBounded Following),’dd/MM/yyy’),
Previo = Format(LAG(OrdenesFecha, 1, OrdenesFecha)
OVER(Partition BY ProductoCodigo ORDER BY Cantidad ),’dd/MM/yyy’),
Siguiente = Format(LEAD(OrdenesFecha, 1, OrdenesFecha )
OVER(Partition BY ProductoCodigo ORDER BY Cantidad),’dd/MM/yyy’)
FROM @Ordenes
ORDER BY Código, Cantidad
go

Explicación:
Para explicar el conjunto de resultados se analizará los datos de cada producto. Para el primer producto que aparece que es «Mouse», tiene registradas tres fechas de ventas: 23/11/2013, 16/11/2013 y 15/08/2013
ordenadas de acuerdo a la cantidad vendida en orden descedente 12.00, 28.00 y 38.00 respectivamente.
Usando las funciones First_Value y Last_Value se muestran las fechas en que se realizó la menor venta de 12 unidades (23/11/2013) y la de mayor venta de 38 unidades (15/08/2013).
Las funciones LAG y LEAD muestran el valor Previo (LAG) y el Siguiente (LEAD) respecto de las fechas de compra ordenados NO POR FECHA sino por la cantidad vendida, para la primera línea, con una venta de 12 unidades, no existe valor previo y se muestra la misma fecha (23/11/2013), la fecha siguiente en que se vendió mas unidades es 16/11/2013 con una cantidad de 28.00 unidades. La segunda línea de los resultados muestra la fecha de la venta previa (23/11/2013) y la recha de la siguiente venta 15/08/2013. La tercera línea, siempre con las venta de Mouse muestra la fecha previa que se vendió 28.00 unidades y la siguiente muestra la misma fecha de compra 15/08/2013 como la siguiente porque el siguiente producto ya no es Mouse, sino Teclado.
Para el caso del producto Monitor note que las fechas todas son iguales porque hay un solo registro..
La imagen muestra el resultado.

Ejercicio 5

Usando la base de datos AdventureWorks
Use AdventureWorks
go

El siguiente ejemplo utiliza la función LEAD para comparar las ventas anuales entre empleados.
La cláusula PARTITION BY se especifica para dividir las filas en el conjunto de resultados por territorio de ventas.
La función LEAD se aplica a cada partición por separado y la computación se reinicia para cada partición.
La cláusula ORDER BY especificada en la cláusula OVER ordena las filas en cada partición antes de que se aplique la función. La cláusula ORDER BY en la instrucción SELECT ordena las filas en todo el conjunto de resultados. Tenga en cuenta que debido a que no hay un valor inicial disponible para la última fila de cada partición, se devuelve el valor predeterminado de cero (0).

Primero se va a mostrar los empleados y sus ventas anuales en cada uno de las regiones.
select S.BusinessEntityID As ‘Código’,
Empleado = CONCAT_WS(‘ ‘, S.FirstName, S.LastName),
TerritoryName As ‘Territorio’,
SalesYTD As ‘Ventas Anuales’
from Sales.vSalesPerson As S
WHERE TerritoryName = ‘Northwest
go
La imagen muestra el resultado.

select S.BusinessEntityID As ‘Código’,
Empleado = CONCAT_WS(‘ ‘, S.FirstName, S.LastName),
TerritoryName As ‘Territorio’,
SalesYTD As ‘Ventas Anuales’
from Sales.vSalesPerson As S
WHERE TerritoryName = ‘Southwest’
go
La imagen muestra el resultado.

Ahora la comparación de las ventas por empleado
SELECT S.BusinessEntityID As ‘Código’,
Empleado = CONCAT_WS(‘ ‘,S.FirstName, S.LastName),
TerritoryName As ‘Territorio’,
SalesYTD As ‘Ventas Anuales’,
LEAD (SalesYTD, 1, 0)
OVER (PARTITION BY TerritoryName ORDER BY SalesYTD DESC)
AS ‘Ventas siguiente Empleado’
FROM Sales.vSalesPerson As S
WHERE TerritoryName IN (‘Northwest’, ‘Southwest’)
ORDER BY TerritoryName
go

Explicación:
Al mostrar los empleados y las ventas por territorio se puede ver que existen tres empleados
del territorio Northwest, el empleado «Tete Mensa-Annan» tiene ventas anuales por el monto de 1576562,1966,
se puede ver que el empleado «David Campbell», que es el siguiente empleado tiene una ventas anuales de
1573012,9383, que es el valor que aparece en la columna «Ventas siguiente Empleado».
Para el empleado David Campbell, las ventas que aparecen en la columna «Ventas siguiente Empleado» pertenecen
a la empleada «Pamela Ansman-Wolfe». Para el empleado «Pamela Ansman-Wolfe» las ventas del siguiente empleado
aparecen en CERO porque el siguiente empleado pertenece a otro territorio que es «Southwest».
La imagen muestra el resultado.