LAG (Transact-SQL)

Accède aux données d'une ligne précédente dans le même jeu de résultats sans recourir à une jointure réflexive dans SQL Server 2012. LAG permet d'accéder à une ligne à un décalage physique donné qui précède la ligne actuelle. Utilisez cette fonction analytique dans une instruction SELECT pour comparer des valeurs sur la ligne actuelle avec des valeurs sur une ligne précédente.

Conventions de la syntaxe Transact-SQL (Transact-SQL)

Syntaxe

LAG (scalar_expression [,offset] [,default])
    OVER ( [ partition_by_clause ] order_by_clause )

Arguments

• scalar_expression
  Valeur à retourner en fonction du décalage spécifié. Il s'agit d'une expression de tout type qui retourne une valeur (scalaire) unique. scalar_expression ne peut pas être une fonction analytique.

• offset
  Nombre de lignes en arrière de la ligne actuelle à partir de laquelle obtenir une valeur. Si cet argument n'est pas spécifié, la valeur par défaut est 1. offset peut être une colonne, une sous-requête ou une autre expression qui aboutit à un entier positif ou pouvant être converti en bigint. offset ne peut pas être une valeur négative ou une fonction analytique.

• default
  Valeur à retourner lorsque scalar_expression à offset est NULL. Si aucune valeur par défaut n'est spécifiée, la valeur NULL est renvoyée. default peut être une colonne, une sous-requête ou une autre expression, mais il ne peut pas s'agir d'une fonction analytique. Le type default doit être compatible avec scalar_expression.

• OVER ( [ partition_by_clause ] order_by_clause**)**
  partition_by_clause divise le jeu de résultats généré par la clause FROM en partitions auxquelles la fonction est appliquée. S'il n'est pas spécifié, la fonction gère toutes les lignes du jeu de résultats de la requête en un seul groupe. order_by_clause détermine l'ordre des données avant que la fonction soit appliquée. Si partition_by_clause est spécifié, il détermine l'ordre des données dans la partition. L'attribut order_by_clause est requis. Pour plus d'informations, consultez Clause OVER (Transact-SQL).

Type des valeurs retournées

Type de données de l'objet scalar_expression spécifié. NULL est retourné si scalar_expression peut avoir la valeur NULL ou si default a la valeur NULL.

Exemples

A.Comparer des valeurs entre des années

L'exemple suivant utilise la fonction LAG pour retourner la différence dans les quotas de ventes pour un employé spécifique sur les années précédentes. Notez qu'étant donné qu'il n'y a aucune valeur de décalage pour la première ligne, la valeur par défaut de zéro (0) est retournée.

USE AdventureWorks2012;
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');

Voici l'ensemble des résultats.

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.Comparer des valeurs dans des partitions

L'exemple suivant utilise la fonction LAG pour comparer les ventes annuelles cumulées entre les employés. La clause PARTITION BY est spécifiée pour diviser les lignes du jeu de résultats par secteur de vente. La fonction LAG est appliquée à chaque partition séparément et le calcul redémarre pour chaque partition. La clause ORDER BY de la clause OVER ordonnance les lignes dans chaque partition. La clause ORDER BY dans l'instruction SELECT trie les lignes dans le jeu de résultats entier. Notez qu'étant donné qu'il n'y a aucune valeur de décalage pour la première ligne de chaque partition, la valeur par défaut de zéro (0) est retournée.

USE AdventureWorks2012;
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;

Voici l'ensemble des résultats.

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.Spécification d'expressions arbitraires

L'exemple suivant illustre la spécification de diverses expressions arbitraires dans la syntaxe de la fonction 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) OVER (ORDER BY a) AS i
FROM T;

Voici l'ensemble des résultats.

b           c           i
----------- ----------- -----------
1           -3          1
2           4           -2
1           NULL        8
3           1           -6
2           NULL        NULL
1           5           NULL

Voir aussi

Référence

LEAD (Transact-SQL)