LEAD (Transact-SQL)

Artikel
10/31/2024

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL Analytics-Endpunkt in Microsoft Fabric Warehouse in Microsoft Fabric

Greift im selben Resultset auf Daten in einer nachfolgenden Zeile zu, ohne dass ein Selbstjoin ab SQL Server 2012 (11.x) verwendet wird. LEAD ermöglicht den Zugriff auf eine Zeile bei einem bestimmten physischen Offset, der auf die aktuelle Zeile folgt. Verwenden Sie diese Analysefunktion in einer SELECT Anweisung, um Werte in der aktuellen Zeile mit Werten in einer folgenden Zeile zu vergleichen.

Transact-SQL-Syntaxkonventionen

Syntax

LEAD ( scalar_expression [ , offset ] [ , default ] ) [ IGNORE NULLS | RESPECT NULLS ]
    OVER ( [ partition_by_clause ] order_by_clause )

Argumente

scalar_expression

Der zurückzugebende Wert auf Grundlage des angegebenen Offsets. Es handelt sich um einen Ausdruck eines beliebigen Typs, der einen einzelnen (skalaren) Wert zurückgibt. scalar_expression kann keine Analysefunktion sein.

offset

Die Anzahl der Zeilen nach der aktuellen Zeile, aus der ein Wert abgerufen werden soll. Wenn nichts angegeben ist, wird der Standardwert 1 verwendet. offset kann eine Spalte, eine Unterabfrage oder ein anderer Ausdruck sein, der eine positive ganze Zahl ergibt, kann aber auch implizit in einen Wert vom Typ bigint konvertiert werden. Offset kann kein negativer Wert oder eine Analysefunktion sein.

default

Der Wert, der zurückgegeben wird, wenn sich offset außerhalb des Partitionsbereichs befindet. Wenn kein Standardwert angegeben ist, NULL wird dieser zurückgegeben. Der Standardwert kann eine Spalte, Unterabfrage oder ein anderer Ausdruck sein, kann aber keine Analysefunktion sein. default muss mit scalar_expression typkompatibel sein.

[ IGNORE NULLS | RESPECT NULLS ]

Gilt für: SQL Server 2022 (16.x) und höhere Versionen, Azure SQL-Datenbank, Azure SQL verwaltete Instanz, Azure SQL Edge

IGNORE NULLS - Werte im Dataset beim Berechnen des ersten Werts über einer Partition ignorieren NULL .

RESPECT NULLS - Respektieren Sie NULL Werte im Dataset, wenn Sie den ersten Wert über eine Partition berechnen. RESPECT NULLS ist das Standardverhalten, wenn keine NULLS Option angegeben wird.

Es gab einen Fehlerkorrektur in SQL Server 2022 CU4 im Zusammenhang mit IGNORE NULLS in LAG und LEAD.

Weitere Informationen zu diesem Argument in Azure SQL Edge finden Sie unter Imputing fehlender Werte.

OVER ( [ partition_by_clause ] order_by_clause )

partition_by_clause dividiert das von der FROM Klausel erzeugte Resultset in Partitionen, auf die die Funktion angewendet wird. Wird dies nicht angegeben, verarbeitet die Funktion alle Zeilen des Abfrageresultsets als einzelne Gruppe.
order_by_clause bestimmt die Reihenfolge der Daten, bevor die Funktion angewendet wird.

Wenn partition_by_clause angegeben wird, wird hierdurch die Reihenfolge der Daten in jeder Partition bestimmt. order_by_clause ist erforderlich. Weitere Informationen finden Sie unter SELECT - OVER-Klausel.

Rückgabetypen

Der Datentyp des angegebenen scalar_expression-Ausdrucks. NULL wird zurückgegeben, wenn scalar_expression nullfähig ist oder der Standardwert auf NULL.

LEAD ist nicht deterministisch. Weitere Informationen finden Sie unter Deterministic and Nondeterministic Functions.

Beispiele

Die Transact-SQL-Codebeispiele in diesem Artikel verwenden die AdventureWorks2022- oder AdventureWorksDW2022-Beispieldatenbank, die Sie von der Homepage Microsoft SQL Server Samples and Community Projects herunterladen können.

A. Vergleichen von Werten aus verschiedenen Jahren

Die Abfrage verwendet die LEAD Funktion, um die Differenz der Verkaufskontingente für einen bestimmten Mitarbeiter über nachfolgende Jahre zurückzugeben. Da für die letzte Zeile kein Leadwert verfügbar ist, wird der Standardwert null (0) zurückgegeben.

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

Hier sehen Sie das Ergebnis.

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. Vergleichen von Werten innerhalb von Partitionen

Im folgenden Beispiel wird die LEAD Funktion verwendet, um den Umsatz von Jahr zu Datum zwischen Mitarbeitern zu vergleichen. Die PARTITION BY Klausel wird angegeben, um die Zeilen im Resultset nach Vertriebsgebiet zu partitionieren. Die LEAD Funktion wird auf jede Partition separat angewendet, und die Berechnung wird für jede Partition neu gestartet. Die ORDER BY in der OVER Klausel angegebene Klausel sortiert die Zeilen in jeder Partition, bevor die Funktion angewendet wird. Die ORDER BY Klausel in der SELECT Anweisung sortiert die Zeilen im gesamten Resultset. Da für die letzte Zeile jeder Partition kein Leadwert verfügbar ist, wird der Standardwert von Null (0) zurückgegeben.

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;

Hier sehen Sie das Ergebnis.

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. Angeben beliebiger Ausdrücke

Das folgende Beispiel veranschaulicht das Angeben verschiedener beliebiger Ausdrücke und Ignorieren von NULL Werten in der LEAD Funktionssyntax.

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;

Hier sehen Sie das Ergebnis.

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

D: Verwenden von IGNORE NULLS zum Suchen von Nicht-NULL-Werten

Die folgende Beispielabfrage veranschaulicht die Verwendung des IGNORE NULLS Arguments.

Das IGNORE NULLS Argument wird sowohl mit LAG LEAD als auch zum Veranschaulichen der Ersetzung von NULL Werten für vorangehende oder nächste nicht NULL-Werte verwendet.

Wenn die vorangehende Zeile enthalten ist NULL LAG, verwendet die aktuelle Zeile den letzten NichtwertNULL .
Wenn die nächste Zeile einen NULL Mit enthält LEAD, verwendet die aktuelle Zeile den nächsten verfügbaren NichtwertNULL .

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. Verwenden von RESPECT NULLS zum Beibehalten `NULL` von Werten

Die folgende Beispielabfrage veranschaulicht die Verwendung des RESPECT NULLS Arguments, bei dem es sich um das Standardverhalten handelt, falls nicht angegeben, im Gegensatz zu dem IGNORE NULLS Argument im vorherigen Beispiel.

Wenn die vorangehende Zeile enthalten ist NULL LAG, verwendet die aktuelle Zeile den letzten Wert.
Wenn die nächste Zeile einen NULL Mit enthält LEAD, verwendet die aktuelle Zeile den nächsten Wert.

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

Beispiele: Azure Synapse Analytics und Analytics-Plattformsystem (PDW)

A. Vergleichen von Werten aus verschiedenen Quartalen

Im folgenden Beispiel wird die LEAD Funktion veranschaulicht. Die Abfrage ruft den Unterschied in den Sollvorgabenwerten für den Verkauf für einen angegebenen Mitarbeiter über nachfolgende Kalenderquartale ab. Da nach der letzten Zeile kein Leadwert verfügbar ist, wird der Standardwert von Null (0) verwendet.

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

Hier sehen Sie das Ergebnis.

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

Freigeben über

LEAD (Transact-SQL)

Syntax

Argumente

scalar_expression

offset

default

[ IGNORE NULLS | RESPECT NULLS ]

OVER ( [ partition_by_clause ] order_by_clause )

Rückgabetypen

Beispiele

A. Vergleichen von Werten aus verschiedenen Jahren

B. Vergleichen von Werten innerhalb von Partitionen

C. Angeben beliebiger Ausdrücke

D: Verwenden von IGNORE NULLS zum Suchen von Nicht-NULL-Werten

E. Verwenden von RESPECT NULLS zum Beibehalten `NULL` von Werten

Beispiele: Azure Synapse Analytics und Analytics-Plattformsystem (PDW)

A. Vergleichen von Werten aus verschiedenen Quartalen

Feedback

Zusätzliche Ressourcen

Freigeben über

LEAD (Transact-SQL)

Syntax

Argumente

scalar_expression

offset

default

[ IGNORE NULLS | RESPECT NULLS ]

OVER ( [ partition_by_clause ] order_by_clause )

Rückgabetypen

Beispiele

A. Vergleichen von Werten aus verschiedenen Jahren

B. Vergleichen von Werten innerhalb von Partitionen

C. Angeben beliebiger Ausdrücke

D: Verwenden von IGNORE NULLS zum Suchen von Nicht-NULL-Werten

E. Verwenden von RESPECT NULLS zum Beibehalten NULL von Werten

Beispiele: Azure Synapse Analytics und Analytics-Plattformsystem (PDW)

A. Vergleichen von Werten aus verschiedenen Quartalen

Zugehöriger Inhalt

Feedback

Zusätzliche Ressourcen

E. Verwenden von RESPECT NULLS zum Beibehalten `NULL` von Werten