LEAD (Transact-SQL)
Berlaku untuk:
SQL Server (semua versi yang didukung) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)
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 pernyataan SELECT untuk membandingkan nilai di baris saat ini dengan nilai dalam baris berikut.
Konvensi Sintaksis SQL Transaksi (SQL Transact)
Sintaks
LEAD ( scalar_expression [ ,offset ] , [ default ] )
OVER ( [ partition_by_clause ] order_by_clause )
Catatan
Untuk melihat sintaks transact-SQL untuk SQL Server 2014 dan 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 berupa nilai negatif atau fungsi analitik.
default
Nilai yang akan dikembalikan saat offset berada di luar cakupan partisi. Jika nilai default tidak ditentukan, NULL dikembalikan. defaultnya bisa berupa kolom, subkueri, atau ekspresi lainnya, tetapi tidak dapat menjadi fungsi analitik. default harus kompatibel dengan jenis dengan scalar_expression.
OVER ( [ partition_by_clause ] order_by_clause)
partition_by_clause membagi tataan hasil yang dihasilkan oleh klausul FROM 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. Ketika partition_by_clause ditentukan, itu menentukan urutan data di setiap partisi. Order_by_clause diperlukan. Untuk informasi selengkapnya, lihat Klausul OVER (SQL Transact).
Jenis Pengembalian
Jenis data dari scalar_expression yang ditentukan. NULL dikembalikan jika scalar_expression dapat diubah ke null atau default diatur ke NULL.
LEAD bersifat nondeterministik. Untuk informasi selengkapnya, lihat Fungsi Deterministik dan Nondeterministik.
Contoh
A. Membandingkan nilai antara tahun
Kueri menggunakan fungsi LEAD untuk mengembalikan perbedaan kuota penjualan untuk karyawan tertentu selama tahun-tahun berikutnya. Perhatikan bahwa karena tidak ada nilai prospek yang tersedia untuk baris terakhir, default nol (0) dikembalikan.
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');
Berikut adalah tataan hasil.
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 fungsi LEAD 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 diterapkan ke setiap partisi secara terpisah dan komputasi dimulai ulang untuk setiap partisi. Klausa ORDER BY yang ditentukan dalam klausa OVER mengurutkan baris di setiap partisi sebelum fungsi diterapkan. Klausa ORDER BY dalam pernyataan SELECT mengurutkan baris dalam seluruh tataan hasil. Perhatikan bahwa karena tidak ada nilai prospek yang tersedia untuk baris terakhir setiap partisi, default nol (0) dikembalikan.
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;
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. Menentukan ekspresi arbitrer
Contoh berikut menunjukkan penentuan berbagai ekspresi arbitrer dalam sintaks fungsi 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;
Berikut adalah hasil yang ditetapkan.
b c i
----------- ----------- -----------
1 -3 8
2 4 2
1 NULL 2
3 1 0
2 NULL NULL
1 5 -2
Contoh: Azure Synapse Analytics and Analytics Platform System (PDW)
D: Bandingkan nilai antar kuartal
Contoh berikut menunjukkan fungsi LEAD. Kueri memperoleh perbedaan nilai kuota penjualan untuk karyawan tertentu selama kuartal kalender berikutnya. Perhatikan bahwa 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