LEAD (Transact-SQL)

Article
10/16/2024

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric

Accède aux données à partir d’une ligne ultérieure dans le même jeu de résultats sans recourir à une jointure réflexive, à compter de SQL Server 2012 (11.x). LEAD fournit l’accès à une ligne à un décalage physique donné qui suit la ligne actuelle. Utilisez cette fonction analytique dans une SELECT instruction pour comparer les valeurs de la ligne actuelle avec les valeurs d’une ligne suivante.

Conventions de la syntaxe Transact-SQL

Syntaxe

LEAD ( scalar_expression [ , offset ] [ , default ] ) [ IGNORE NULLS | RESPECT NULLS ]
    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 n’importe quel type qui retourne une valeur unique (scalaire). 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 peut être converti en bigint. offset ne peut pas être une valeur négative ou une fonction analytique.

default

Valeur à retourner quand offset est au-delà de l’étendue de la partition. Si aucune valeur par défaut n’est spécifiée, NULL elle est retournée. la valeur par défaut peut être une colonne, une sous-requête ou une autre expression, mais elle ne peut pas être une fonction analytique. default doit être compatible en matière de type avec scalar_expression.

[ IGNORER NULLS | RESPECTER NULLS ]

S’applique à : SQL Server 2022 (16.x) et versions ultérieures, Azure SQL Database, Azure SQL Managed Instance, Azure SQL Edge

IGNORE NULLS - Ignorez les NULL valeurs dans le jeu de données lors de l’informatique de la première valeur sur une partition.

RESPECT NULLS - Respectez les NULL valeurs dans le jeu de données lors de l’informatique de la première valeur sur une partition. RESPECT NULLS est le comportement par défaut si aucune NULLS option n’est spécifiée.

Il y avait un correctif de bogue dans SQL Server 2022 CU4 lié à IGNORE NULLS et LEADLAG .

Pour plus d’informations sur cet argument dans Azure SQL Edge, consultez Entrée de valeurs manquantes.

OVER ( [ partition_by_clause ] order_by_clause )

partition_by_clause divise le jeu de résultats produit par la FROM clause 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.

Quand partition_by_clause est spécifié, il détermine l’ordre des données dans chaque partition. order_by_clause est requis. Pour plus d’informations, consultez SELECT - Clause OVER.

Types de retour

Type de données de l’argument scalar_expression spécifié. NULL est retourné si scalar_expression est nullable ou si la valeur par défaut est définie NULLsur .

LEAD n’est pas déterministe. Pour plus d’informations, consultez Fonctions déterministes et non déterministes.

Exemples

Les exemples de code Transact-SQL de cet article sont fondés sur l’échantillon de base de données AdventureWorks2022 ou AdventureWorksDW2022 fourni, que vous pouvez télécharger à partir de la page d’accueil Échantillons et projets communautaires Microsoft SQL Server.

R : Comparer des valeurs entre des années

La requête utilise la LEAD fonction pour retourner la différence entre les quotas de ventes d’un employé spécifique au cours des années suivantes. Étant donné qu’aucune valeur de prospect n’est disponible pour la dernière ligne, la valeur par défaut zéro (0) est retournée.

USE AdventureWorks2022;
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 le jeu de 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 LEAD fonction pour comparer les ventes annuelles entre les employés. La PARTITION BY clause est spécifiée pour partitionner les lignes dans le jeu de résultats par territoire de vente. La LEAD fonction est appliquée à chaque partition séparément et le calcul redémarre pour chaque partition. La ORDER BY clause spécifiée dans la OVER clause commande les lignes de chaque partition avant l’application de la fonction. La ORDER BY clause de l’instruction SELECT trie les lignes dans l’ensemble du jeu de résultats. Étant donné qu’aucune valeur de prospect n’est disponible pour la dernière ligne de chaque partition, la valeur par défaut zéro (0) est retournée.

USE AdventureWorks2022;
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 le jeu de 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écifier des expressions arbitraires

L’exemple suivant illustre la spécification de différentes expressions arbitraires et l’ignorance des NULL valeurs dans la syntaxe de la LEAD fonction.

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) IGNORE NULLS OVER (ORDER BY a) AS i
FROM T;

Voici le jeu de résultats.

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

D. Utiliser IGNORE NULLS pour rechercher des valeurs non NULL

L’exemple de requête suivant illustre l’utilisation de l’argument IGNORE NULLS .

L’argument IGNORE NULLS est utilisé avec le LAG et LEAD pour illustrer la substitution de NULL valeurs pour les valeurs précédentes ou non NULL suivantes.

Si la ligne précédente contient NULL LAG, la ligne actuelle utilise la valeur non-laNULL plus récente.
Si la ligne suivante contient une NULL valeur avec LEAD, la ligne active utilise la valeur nonNULL disponible suivante.

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. Utiliser RESPECT NULLS pour conserver `NULL` les valeurs

L’exemple de requête suivant illustre l’utilisation de l’argument RESPECT NULLS , qui est le comportement par défaut s’il n’est pas spécifié, par opposition à l’argument IGNORE NULLS dans l’exemple précédent.

Si la ligne précédente contient NULL LAG, la ligne actuelle utilise la valeur la plus récente.
Si la ligne suivante contient une NULL valeur avec LEAD, la ligne active utilise la valeur suivante.

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

Exemples : Azure Synapse Analytics et Analytics Platform System (PDW)

R : Comparer des valeurs entre des trimestres

L’exemple suivant illustre la LEAD fonction. La requête obtient la différence entre les valeurs de quota de ventes pour un employé spécifique sur des trimestres calendaires consécutifs. Étant donné qu’aucune valeur de prospect n’est disponible après la dernière ligne, la valeur par défaut zéro (0) est utilisée.

-- Uses AdventureWorks

SELECT CalendarYear AS Year,
    CalendarQuarter AS Quarter,
    SalesAmountQuota AS SalesQuota,
    LEAD(SalesAmountQuota, 1, 0) OVER (ORDER BY CalendarYear, CalendarQuarter) AS NextQuota,
    SalesAmountQuota - LEAD(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;

Voici le jeu de résultats.

Year Quarter  SalesQuota  NextQuota  Diff
---- -------  ----------  ---------  -------------
2001 3        28000.0000   7000.0000   21000.0000
2001 4         7000.0000  91000.0000  -84000.0000
2001 1        91000.0000 140000.0000  -49000.0000
2002 2       140000.0000   7000.0000    7000.0000
2002 3         7000.0000 154000.0000   84000.0000
2002 4       154000.0000      0.0000  154000.0000

Partager via

LEAD (Transact-SQL)

Syntaxe

Arguments

scalar_expression

offset

default

[ IGNORER NULLS | RESPECTER NULLS ]

OVER ( [ partition_by_clause ] order_by_clause )

Types de retour

Exemples

R : Comparer des valeurs entre des années

B. Comparer des valeurs dans des partitions

C. Spécifier des expressions arbitraires

D. Utiliser IGNORE NULLS pour rechercher des valeurs non NULL

E. Utiliser RESPECT NULLS pour conserver `NULL` les valeurs

Exemples : Azure Synapse Analytics et Analytics Platform System (PDW)

R : Comparer des valeurs entre des trimestres

Commentaires

Ressources supplémentaires

Partager via

LEAD (Transact-SQL)

Syntaxe

Arguments

scalar_expression

offset

default

[ IGNORER NULLS | RESPECTER NULLS ]

OVER ( [ partition_by_clause ] order_by_clause )

Types de retour

Exemples

R : Comparer des valeurs entre des années

B. Comparer des valeurs dans des partitions

C. Spécifier des expressions arbitraires

D. Utiliser IGNORE NULLS pour rechercher des valeurs non NULL

E. Utiliser RESPECT NULLS pour conserver NULL les valeurs

Exemples : Azure Synapse Analytics et Analytics Platform System (PDW)

R : Comparer des valeurs entre des trimestres

Contenu connexe

Commentaires

Ressources supplémentaires

E. Utiliser RESPECT NULLS pour conserver `NULL` les valeurs