LEAD (Transact-SQL)

Artikel
05/22/2024

Berlaku untuk:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)Titik akhir analitik SQL di Microsoft Fabric Warehouse di Microsoft Fabric

Mengakses data dari baris berikutnya dalam tataan hasil yang sama tanpa menggunakan gabungan mandiri yang dimulai dengan SQL Server 2012 (11.x). LEAD menyediakan akses ke baris pada offset fisik tertentu yang mengikuti baris saat ini. Gunakan fungsi analitik ini dalam SELECT pernyataan untuk membandingkan nilai di baris saat ini dengan nilai dalam baris berikut.

Konvensi sintaks transact-SQL

Sintaksis

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

Catatan

Untuk melihat sintaks Transact-SQL untuk SQL Server 2014 (12.x) dan versi yang lebih lama, lihat Dokumentasi versi sebelumnya.

Argumen

scalar_expression

Nilai yang akan dikembalikan berdasarkan offset yang ditentukan. Ini adalah ekspresi dari jenis apa pun yang mengembalikan nilai tunggal (skalar). scalar_expression tidak dapat menjadi fungsi analitik.

offset

Jumlah baris maju dari baris saat ini untuk mendapatkan nilai. Jika tidak ditentukan, defaultnya adalah 1. offset dapat berupa kolom, subkueri, atau ekspresi lain yang mengevaluasi ke bilangan bulat positif atau dapat dikonversi secara implisit ke bigint. offset tidak boleh menjadi nilai negatif atau fungsi analitik.

Default

Nilai yang akan dikembalikan saat offset berada di luar cakupan partisi. Jika nilai default tidak ditentukan, NULL dikembalikan. default dapat berupa kolom, subkueri, atau ekspresi lainnya, tetapi tidak dapat menjadi fungsi analitik. default harus kompatibel dengan jenis dengan scalar_expression.

[ ABAIKAN NULLS | HORMATI NULLS ]

Berlaku untuk: SQL Server 2022 (16.x) dan versi yang lebih baru, Azure SQL Database, Azure SQL Managed Instance, Azure SQL Edge

IGNORE NULLS - Abaikan NULL nilai dalam himpunan data saat menghitung nilai pertama atas partisi.

RESPECT NULLS - Hormati NULL nilai dalam himpunan data saat menghitung nilai pertama melalui partisi. RESPECT NULLS adalah perilaku default jika NULLS opsi tidak ditentukan.

Ada perbaikan bug di SQL Server 2022 CU4 yang terkait dengan IGNORE NULLS di LAG dan LEAD.

Untuk informasi selengkapnya tentang argumen ini di Azure SQL Edge, lihat Menghambat nilai yang hilang.

OVER ( [ partition_by_clause ] order_by_clause )

partition_by_clause membagi tataan hasil yang dihasilkan oleh FROM klausul menjadi partisi tempat fungsi diterapkan. Jika tidak ditentukan, fungsi memperlakukan semua baris hasil kueri yang ditetapkan sebagai grup tunggal.
order_by_clause menentukan urutan data sebelum fungsi diterapkan.

Saat partition_by_clause ditentukan, partition_by_clause menentukan urutan data di setiap partisi. Order_by_clause diperlukan. Untuk informasi selengkapnya, lihat SELECT - OVER Clause.

Jenis yang dikembalikan

Jenis data scalar_expression yang ditentukan. NULL dikembalikan jika scalar_expression nullable atau default diatur ke NULL.

LEAD bersifat nondeterministik. Untuk informasi selengkapnya, lihat Fungsi Deterministik dan Nondeterministik.

Contoh

Sampel kode Transact-SQL dalam artikel ini menggunakan AdventureWorks2022 database sampel, yang dapat Anda unduh dari beranda Sampel Microsoft SQL Server dan Proyek Komunitas.

J. Membandingkan nilai antara tahun

Kueri menggunakan LEAD fungsi untuk mengembalikan perbedaan kuota penjualan untuk karyawan tertentu selama tahun-tahun berikutnya. Karena tidak ada nilai prospek yang tersedia untuk baris terakhir, default nol (0) dikembalikan.

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

Berikut adalah hasil yang ditetapkan.

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. Membandingkan nilai dalam partisi

Contoh berikut menggunakan LEAD fungsi untuk membandingkan penjualan tahunan hingga saat ini antara karyawan. Klausa PARTITION BY ditentukan untuk mempartisi baris dalam hasil yang ditetapkan oleh wilayah penjualan. Fungsi LEAD ini diterapkan ke setiap partisi secara terpisah dan komputasi dimulai ulang untuk setiap partisi. Klausa ORDER BY yang ditentukan dalam OVER klausul mengurutkan baris di setiap partisi sebelum fungsi diterapkan. Klausa ORDER BY dalam SELECT pernyataan mengurutkan baris dalam seluruh tataan hasil. Karena tidak ada nilai prospek yang tersedia untuk baris terakhir setiap partisi, default nol (0) dikembalikan.

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;

Berikut adalah hasil yang ditetapkan.

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. Tentukan ekspresi arbitrer

Contoh berikut menunjukkan menentukan berbagai ekspresi arbitrer dan mengabaikan NULL nilai dalam LEAD sintaks fungsi.

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;

Berikut adalah hasil yang ditetapkan.

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

D. Gunakan IGNORE NULLS untuk menemukan nilai non-NULL

Contoh kueri berikut menunjukkan menggunakan IGNORE NULLS argumen .

Argumen IGNORE NULLS digunakan dengan LAG dan LEAD untuk menunjukkan penggantian NULL nilai untuk nilai sebelumnya atau non-NULL berikutnya.

Jika baris sebelumnya berisi NULLLAG, maka baris saat ini menggunakan nilai non-terbaruNULL .
Jika baris berikutnya berisi NULL dengan LEAD, maka baris saat ini menggunakan nilai non-yangNULL tersedia berikutnya.

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. Gunakan RESPECT NULLS untuk menyimpan `NULL` nilai

Contoh kueri berikut menunjukkan menggunakan RESPECT NULLS argumen , yang merupakan perilaku default jika tidak ditentukan, dibandingkan IGNORE NULLS dengan argumen dalam contoh sebelumnya.

Jika baris sebelumnya berisi NULLLAG, maka baris saat ini menggunakan nilai terbaru.
Jika baris berikutnya berisi NULL dengan LEAD, maka baris saat ini menggunakan nilai berikutnya.

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

Contoh: Azure Synapse Analytics and Analytics Platform System (PDW)

J. Membandingkan nilai antar kuartal

Contoh berikut menunjukkan LEAD fungsi . Kueri memperoleh perbedaan nilai kuota penjualan untuk karyawan tertentu selama kuartal kalender berikutnya. Karena tidak ada nilai prospek yang tersedia setelah baris terakhir, default nol (0) digunakan.

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

Berikut adalah hasil yang ditetapkan.

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

Bagikan melalui

LEAD (Transact-SQL)

Sintaksis

Argumen

scalar_expression

offset

Default

[ ABAIKAN NULLS | HORMATI NULLS ]

OVER ( [ partition_by_clause ] order_by_clause )

Jenis yang dikembalikan

Contoh

J. Membandingkan nilai antara tahun

B. Membandingkan nilai dalam partisi

C. Tentukan ekspresi arbitrer

D. Gunakan IGNORE NULLS untuk menemukan nilai non-NULL

E. Gunakan RESPECT NULLS untuk menyimpan `NULL` nilai

Contoh: Azure Synapse Analytics and Analytics Platform System (PDW)

J. Membandingkan nilai antar kuartal

Saran dan Komentar

Saran dan Komentar

Sumber Daya Tambahan:

Bagikan melalui

LEAD (Transact-SQL)

Sintaksis

Argumen

scalar_expression

offset

Default

[ ABAIKAN NULLS | HORMATI NULLS ]

OVER ( [ partition_by_clause ] order_by_clause )

Jenis yang dikembalikan

Contoh

J. Membandingkan nilai antara tahun

B. Membandingkan nilai dalam partisi

C. Tentukan ekspresi arbitrer

D. Gunakan IGNORE NULLS untuk menemukan nilai non-NULL

E. Gunakan RESPECT NULLS untuk menyimpan NULL nilai

Contoh: Azure Synapse Analytics and Analytics Platform System (PDW)

J. Membandingkan nilai antar kuartal

Konten terkait

Saran dan Komentar

Saran dan Komentar

Sumber Daya Tambahan:

E. Gunakan RESPECT NULLS untuk menyimpan `NULL` nilai