LEAD (Transact-SQL)
Accède aux données d'une ligne ultérieure dans le même jeu de résultats sans recourir à une jointure réflexive dans SQL Server 2012. LEAD permet d'accéder à une ligne à un décalage physique donné qui suit 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 ultérieure.
.gif "Icône Lien de rubrique")
Conventions de la syntaxe Transact-SQL (Transact-SQL)
Syntaxe
LEAD ( 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 avant 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. Lorsque partition_by_clause est spécifié, il détermine l'ordre des données dans chaque partition. L'attribut order_by_clause est requis. Pour plus d'informations, consultez Clause OVER (Transact-SQL).
Types de retour
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
La requête utilise la fonction LEAD pour retourner la différence entre des quotas de ventes pour un employé spécifique sur plusieurs années. Notez qu'étant donné qu'il n'y a aucune valeur de tête pour la derniè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,
LEAD(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS NextQuota
FROM Sales.SalesPersonQuotaHistory
WHERE BusinessEntityID = 275 and YEAR(QuotaDate) IN ('2005','2006');
Voici l'ensemble des résultats.
BusinessEntityID SalesYear CurrentQuota NextQuota
---------------- ----------- --------------------- ---------------------
275 2005 367000.00 556000.00
275 2005 556000.00 502000.00
275 2006 502000.00 550000.00
275 2006 550000.00 1429000.00
275 2006 1429000.00 1324000.00
275 2006 1324000.00 0.00
B.Comparer des valeurs dans des partitions
L'exemple suivant utilise la fonction LEAD pour comparer les ventes annuelles cumulées entre les employés. La clause PARTITION BY est spécifiée pour partitionner les lignes du jeu de résultats par secteur de vente. La fonction LEAD est appliquée à chaque partition séparément et le calcul redémarre pour chaque partition. La clause ORDER BY spécifiée dans la clause OVER classe les lignes de chaque partition avant que la fonction soit appliquée. La clause ORDER BY dans l'instruction SELECT classe les lignes dans le jeu de résultats entier. Notez qu'étant donné qu'il n'y a aucune valeur de tête pour la dernière ligne de chaque partition, la valeur par défaut de zéro (0) est retournée.
USE AdventureWorks2012;
GO
SELECT TerritoryName, BusinessEntityID, SalesYTD,
LEAD (SalesYTD, 1, 0) OVER (PARTITION BY TerritoryName ORDER BY SalesYTD DESC) AS NextRepSales
FROM Sales.vSalesPerson
WHERE TerritoryName IN (N'Northwest', N'Canada')
ORDER BY TerritoryName;
Voici l'ensemble des résultats.
TerritoryName BusinessEntityID SalesYTD NextRepSales
----------------------- ---------------- --------------------- ---------------------
Canada 282 2604540.7172 1453719.4653
Canada 278 1453719.4653 0.00
Northwest 284 1576562.1966 1573012.9383
Northwest 283 1573012.9383 1352577.1325
Northwest 280 1352577.1325 0.00
C.Spécification d'expressions arbitraires
L'exemple suivant illustre la spécification de diverses expressions arbitraires dans la syntaxe de la fonction LEAD.
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,
LEAD(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 8
2 4 2
1 NULL 2
3 1 0
2 NULL NULL
1 5 -2