LAG (Transact-SQL)
Se aplica a:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)Punto de conexión de análisis SQL en Microsoft FabricAlmacenamiento en Microsoft Fabric
Tiene acceso a datos de una fila anterior en el mismo conjunto de resultados sin usar una autocombinación que empiece por SQL Server 2012 (11.x). LAG proporciona acceso a una fila en un desplazamiento físico especificado que hay antes de la fila actual. Use esta función analítica en una instrucción SELECT para comparar valores de la fila actual con valores de una fila anterior.
Convenciones de sintaxis de Transact-SQL
Sintaxis
LAG (scalar_expression [ , offset ] [ , default ] ) [ IGNORE NULLS | RESPECT NULLS ]
OVER ( [ partition_by_clause ] order_by_clause )
Nota:
Para ver la sintaxis de Transact-SQL para SQL Server 2014 (12.x) y versiones anteriores, consulta la Documentación de versiones anteriores.
Argumentos
scalar_expression
El valor que se va a devolver en función del desplazamiento especificado. Es una expresión de cualquier tipo que devuelve un único valor (escalar). scalar_expression no puede ser una función analítica.
offset
El número de filas hacia atrás de la fila actual de la que se va a obtener un valor. Si no se especifica, el valor predeterminado es 1. offset puede ser una columna, una subconsulta u otra expresión que se evalúa como un entero positivo o que se puede convertir implícitamente en bigint. offset no puede ser un valor negativo o una función analítica.
default
Valor que se devuelve cuando offset está fuera del ámbito de la partición. Si no se especifica ningún valor predeterminado, se devuelve NULL. default puede ser una columna, una subconsulta u otra expresión, pero no puede ser una función analítica. default debe tener un tipo compatible con scalar_expression.
[ IGNORE NULLS | RESPECT NULLS ]
Se aplica a: SQL Server (a partir de SQL Server 2022 [16.x]), Azure SQL Database, Azure SQL Managed Instance y Azure SQL Edge
IGNORE NULLS: se omiten los valores NULL del conjunto de datos al calcular el primer valor en una partición.
RESPECT NULLS: se respetan los valores NULL del conjunto de datos al calcular el primer valor en una partición. RESPECT NULLS es el comportamiento predeterminado cuando no se especifica la opción NULLS.
Se ha corregido un error en SQL Server 2022 CU4 relacionado con IGNORE NULLS en LAG y LEAD.
Para más información sobre este argumento en Azure SQL Edge, consulte Imputación de valores que faltan.
OVER ( [ partition_by_clause ] order_by_clause )
partition_by_clause divide el conjunto de resultados generado 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 único grupo. order_by_clause determina el orden de los datos antes de que se aplique la función. Si se especifica partition_by_clause, determina el orden de los datos en la partición. order_by_clause es obligatorio. Para más información, vea Cláusula OVER (Transact-SQL).
Tipos de valor devuelto
El tipo de datos de la scalar_expression especificada. Se devuelve NULL si scalar_expression acepta valores NULL o si default se establece en NULL.
Notas generales
LAG es no determinista. Para obtener más información, consulte Deterministic and Nondeterministic Functions.
Ejemplos
A. Comparar valores entre años
En el ejemplo siguiente se usa la función LAG para devolver la diferencia en cuotas de venta para un empleado concreto en años anteriores. Observe que como no hay ningún valor de intervalo disponible para la primera fila, se devuelve el valor predeterminado de cero (0).
USE AdventureWorks2022;
GO
SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota,
LAG(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS PreviousQuota
FROM Sales.SalesPersonQuotaHistory
WHERE BusinessEntityID = 275 AND YEAR(QuotaDate) IN ('2005','2006');
El conjunto de resultados es el siguiente:
BusinessEntityID SalesYear CurrentQuota PreviousQuota
---------------- ----------- --------------------- ---------------------
275 2005 367000.00 0.00
275 2005 556000.00 367000.00
275 2006 502000.00 556000.00
275 2006 550000.00 502000.00
275 2006 1429000.00 550000.00
275 2006 1324000.00 1429000.00
B. Comparar valores dentro de particiones
En el ejemplo siguiente se usa la función LAG para comparar las ventas anuales hasta la fecha entre los empleados. La cláusula PARTITION BY se especifica para dividir las filas del conjunto de resultados por territorio de ventas. La función LAG se aplica a cada partición por separado y el cálculo se reinicia para cada partición. La cláusula ORDER BY de la cláusula OVER ordena las filas de cada partición. La cláusula ORDER BY de la instrucción SELECT ordena las filas del conjunto de resultados completo. Observe que como no hay ningún valor de intervalo disponible para la primera fila de cada partición, se devuelve el valor predeterminado de cero (0).
USE AdventureWorks2022;
GO
SELECT TerritoryName, BusinessEntityID, SalesYTD,
LAG (SalesYTD, 1, 0) OVER (PARTITION BY TerritoryName ORDER BY SalesYTD DESC) AS PrevRepSales
FROM Sales.vSalesPerson
WHERE TerritoryName IN (N'Northwest', N'Canada')
ORDER BY TerritoryName;
El conjunto de resultados es el siguiente:
TerritoryName BusinessEntityID SalesYTD PrevRepSales
----------------------- ---------------- --------------------- ---------------------
Canada 282 2604540.7172 0.00
Canada 278 1453719.4653 2604540.7172
Northwest 284 1576562.1966 0.00
Northwest 283 1573012.9383 1576562.1966
Northwest 280 1352577.1325 1573012.9383
C. Especificar expresiones arbitrarias
En el ejemplo siguiente se muestra cómo especificar una serie de expresiones arbitrarias e ignorar valores NULL en la sintaxis de la función LAG.
CREATE TABLE T (a INT, b INT, c INT);
GO
INSERT INTO T VALUES (1, 1, -3), (2, 2, 4), (3, 1, NULL), (4, 3, 1), (5, 2, NULL), (6, 1, 5);
SELECT b, c,
LAG(2*c, b*(SELECT MIN(b) FROM T), -c/2.0) IGNORE NULLS OVER (ORDER BY a) AS i
FROM T;
El conjunto de resultados es el siguiente:
b c i
----------- ----------- -----------
1 -3 1
2 4 -2
1 NULL 8
3 1 -6
2 NULL 8
1 5 2
D. Use IGNORE NULLS para buscar valores que no son NULL
En la consulta de ejemplo siguiente se muestra el uso del argumento IGNORE NULLS.
El argumento IGNORE NULLS se usa con LAG y LEAD para demostrar la sustitución de valores NULL para los valores anteriores o distintos de NULL.
- Si la fila anterior contenía NULL con
LAG, la fila actual usa el valor distinto de NULL más reciente. - Si la siguiente fila contiene un valor NULL con
LEAD, la fila actual usa el siguiente valor no NULL disponible.
DROP TABLE IF EXISTS #test_ignore_nulls;
CREATE TABLE #test_ignore_nulls (column_a int, column_b int);
GO
INSERT INTO #test_ignore_nulls VALUES
(1, 8),
(2, 9),
(3, NULL),
(4, 10),
(5, NULL),
(6, NULL),
(7, 11);
SELECT column_a, column_b,
[Previous value for column_b] = LAG(column_b) IGNORE NULLS OVER (ORDER BY column_a),
[Next value for column_b] = LEAD(column_b) IGNORE NULLS OVER (ORDER BY column_a)
FROM #test_ignore_nulls
ORDER BY column_a;
--cleanup
DROP TABLE #test_ignore_nulls;
column_a column_b Previous value for column_b Next value for column_b
1 8 NULL 9
2 9 8 10
3 NULL 9 10
4 10 9 11
5 NULL 10 11
6 NULL 10 11
7 11 10 NULL
E. Use RESPECT NULLS para mantener valores NULL
La consulta de ejemplo siguiente muestra el uso del argumento RESPECT NULLS, que es el comportamiento predeterminado si no se especifica, en lugar del argumento IGNORE NULLS del ejemplo anterior.
- Si la fila anterior contenía NULL con
LAG, la fila actual usa el valor más reciente. - Si la fila siguiente contiene un valor NULL con
LEAD, la fila actual usa el siguiente valor.
DROP TABLE IF EXISTS #test_ignore_nulls;
CREATE TABLE #test_ignore_nulls (column_a int, column_b int);
GO
INSERT INTO #test_ignore_nulls VALUES
(1, 8),
(2, 9),
(3, NULL),
(4, 10),
(5, NULL),
(6, NULL),
(7, 11);
SELECT column_a, column_b,
[Previous value for column_b] = LAG(column_b) RESPECT NULLS OVER (ORDER BY column_a),
[Next value for column_b] = LEAD(column_b) RESPECT NULLS OVER (ORDER BY column_a)
FROM #test_ignore_nulls
ORDER BY column_a;
--Identical output
SELECT column_a, column_b,
[Previous value for column_b] = LAG(column_b) OVER (ORDER BY column_a),
[Next value for column_b] = LEAD(column_b) OVER (ORDER BY column_a)
FROM #test_ignore_nulls
ORDER BY column_a;
--cleanup
DROP TABLE #test_ignore_nulls;
column_a column_b Previous value for column_b Next value for column_b
1 8 NULL 9
2 9 8 NULL
3 NULL 9 10
4 10 NULL NULL
5 NULL 10 NULL
6 NULL NULL 11
7 11 NULL NULL
Ejemplos: Azure Synapse Analytics y Sistema de la plataforma de análisis (PDW)
A Comparar valores entre trimestres
En este ejemplo se muestra el uso de la función LAG. La consulta usa la función LAG para devolver la diferencia en cuotas de ventas para un empleado concreto en trimestres anteriores. Observe que como no hay ningún valor de intervalo disponible para la primera fila, se devuelve el valor predeterminado de cero (0).
-- Uses AdventureWorks
SELECT CalendarYear, CalendarQuarter, SalesAmountQuota AS SalesQuota,
LAG(SalesAmountQuota,1,0) OVER (ORDER BY CalendarYear, CalendarQuarter) AS PrevQuota,
SalesAmountQuota - LAG(SalesAmountQuota,1,0) OVER (ORDER BY CalendarYear, CalendarQuarter) AS Diff
FROM dbo.FactSalesQuota
WHERE EmployeeKey = 272 AND CalendarYear IN (2001, 2002)
ORDER BY CalendarYear, CalendarQuarter;
El conjunto de resultados es el siguiente:
Year Quarter SalesQuota PrevQuota Diff
---- ------- ---------- --------- -------------
2001 3 28000.0000 0.0000 28000.0000
2001 4 7000.0000 28000.0000 -21000.0000
2001 1 91000.0000 7000.0000 84000.0000
2002 2 140000.0000 91000.0000 49000.0000
2002 3 7000.0000 140000.0000 -70000.0000
2002 4 154000.0000 7000.0000 84000.0000
Pasos siguientes
Comentarios
Próximamente: A lo largo de 2024 iremos eliminando gradualmente GitHub Issues como mecanismo de comentarios sobre el contenido y lo sustituiremos por un nuevo sistema de comentarios. Para más información, vea: https://aka.ms/ContentUserFeedback.
Enviar y ver comentarios de