LAG (Transact-SQL)
Berlaku untuk:
Titik akhir analitik SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Platform System (PDW) SQL di Microsoft Fabric Warehouse di Microsoft Fabric
Mengakses data dari baris sebelumnya dalam tataan hasil yang sama tanpa menggunakan gabungan mandiri yang dimulai dengan SQL Server 2012 (11.x). LAG menyediakan akses ke baris pada offset fisik tertentu yang datang sebelum baris saat ini. Gunakan fungsi analitik ini dalam pernyataan SELECT untuk membandingkan nilai di baris saat ini dengan nilai di baris sebelumnya.
Sintaks
LAG (scalar_expression [ , offset ] [ , default ] ) [ IGNORE NULLS | RESPECT NULLS ]
OVER ( [ partition_by_clause ] order_by_clause )
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 kembali 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. 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 (dimulai dengan SQL Server 2022 (16.x)), Azure SQL Database, Azure SQL Managed Instance, Azure SQL Edge
IGNORE NULLS - Abaikan nilai null dalam himpunan data saat menghitung nilai pertama atas partisi.
RESPECT NULLS - Hormati nilai null dalam himpunan data saat menghitung nilai pertama atas partisi. RESPECT NULLS adalah perilaku default jika opsi NULLS 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 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. Jika partition_by_clause ditentukan, partition_by_clause menentukan urutan data dalam partisi. Order_by_clause diperlukan. Untuk informasi selengkapnya, lihat Klausul OVER (Transact-SQL).
Jenis Kembalian
Jenis data scalar_expression yang ditentukan. NULL dikembalikan jika scalar_expression dapat diubah ke null atau default diatur ke NULL.
Keterangan Umum
LAG bersifat nondeterministik. Untuk informasi selengkapnya, lihat Fungsi Deterministik dan Nondeterministik.
Contoh
J. Membandingkan nilai antara tahun
Contoh berikut menggunakan fungsi LAG untuk mengembalikan perbedaan kuota penjualan untuk karyawan tertentu selama tahun-tahun sebelumnya. Perhatikan bahwa karena tidak ada nilai jeda yang tersedia untuk baris pertama, default nol (0) dikembalikan.
USE AdventureWorks2022;
GO
SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota,
LAG(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS PreviousQuota
FROM Sales.SalesPersonQuotaHistory
WHERE BusinessEntityID = 275 AND YEAR(QuotaDate) IN ('2005','2006');
Berikut set hasilnya.
BusinessEntityID SalesYear CurrentQuota PreviousQuota
---------------- ----------- --------------------- ---------------------
275 2005 367000.00 0.00
275 2005 556000.00 367000.00
275 2006 502000.00 556000.00
275 2006 550000.00 502000.00
275 2006 1429000.00 550000.00
275 2006 1324000.00 1429000.00
B. Membandingkan nilai dalam partisi
Contoh berikut menggunakan fungsi LAG untuk membandingkan penjualan tahun ke tanggal antara karyawan. Klausa PARTITION BY ditentukan untuk membagi baris dalam hasil yang ditetapkan oleh wilayah penjualan. Fungsi LAG diterapkan ke setiap partisi secara terpisah dan komputasi dimulai ulang untuk setiap partisi. Klausa ORDER BY dalam klausul OVER mengurutkan baris di setiap partisi. Klausa ORDER BY dalam pernyataan SELECT mengurutkan baris dalam seluruh tataan hasil. Perhatikan bahwa karena tidak ada nilai jeda yang tersedia untuk baris pertama setiap partisi, default nol (0) dikembalikan.
USE AdventureWorks2022;
GO
SELECT TerritoryName, BusinessEntityID, SalesYTD,
LAG (SalesYTD, 1, 0) OVER (PARTITION BY TerritoryName ORDER BY SalesYTD DESC) AS PrevRepSales
FROM Sales.vSalesPerson
WHERE TerritoryName IN (N'Northwest', N'Canada')
ORDER BY TerritoryName;
Berikut set hasilnya.
TerritoryName BusinessEntityID SalesYTD PrevRepSales
----------------------- ---------------- --------------------- ---------------------
Canada 282 2604540.7172 0.00
Canada 278 1453719.4653 2604540.7172
Northwest 284 1576562.1966 0.00
Northwest 283 1573012.9383 1576562.1966
Northwest 280 1352577.1325 1573012.9383
C. Menentukan ekspresi arbitrer
Contoh berikut menunjukkan menentukan berbagai ekspresi arbitrer dan mengabaikan nilai NULL dalam sintaks fungsi LAG.
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,
LAG(2*c, b*(SELECT MIN(b) FROM T), -c/2.0) IGNORE NULLS OVER (ORDER BY a) AS i
FROM T;
Berikut set hasilnya.
b c i
----------- ----------- -----------
1 -3 1
2 4 -2
1 NULL 8
3 1 -6
2 NULL 8
1 5 2
D. Gunakan IGNORE NULLS untuk menemukan nilai non-NULL
Contoh kueri berikut menunjukkan menggunakan argumen IGNORE NULLS.
Argumen IGNORE NULLS digunakan dengan LAG dan LEAD untuk menunjukkan penggantian nilai NULL untuk nilai sebelumnya atau non-NULL berikutnya.
- Jika baris sebelumnya berisi NULL dengan
LAG, maka baris saat ini menggunakan nilai non-NULL terbaru. - Jika baris berikutnya berisi NULL dengan
LEAD, maka baris saat ini menggunakan nilai non-NULL berikutnya yang tersedia.
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 mempertahankan nilai NULL
Contoh kueri berikut menunjukkan menggunakan argumen RESPECT NULLS, yang merupakan perilaku default jika tidak ditentukan, dibandingkan dengan argumen IGNORE NULLS dalam contoh sebelumnya.
- Jika baris sebelumnya berisi NULL dengan
LAG, 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 fungsi LAG. Kueri menggunakan fungsi LAG untuk mengembalikan perbedaan kuota penjualan untuk karyawan tertentu selama kuartal kalender sebelumnya. Perhatikan bahwa karena tidak ada nilai jeda yang tersedia untuk baris pertama, default nol (0) dikembalikan.
-- Uses AdventureWorks
SELECT CalendarYear, CalendarQuarter, SalesAmountQuota AS SalesQuota,
LAG(SalesAmountQuota,1,0) OVER (ORDER BY CalendarYear, CalendarQuarter) AS PrevQuota,
SalesAmountQuota - LAG(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 set hasilnya.
Year Quarter SalesQuota PrevQuota Diff
---- ------- ---------- --------- -------------
2001 3 28000.0000 0.0000 28000.0000
2001 4 7000.0000 28000.0000 -21000.0000
2001 1 91000.0000 7000.0000 84000.0000
2002 2 140000.0000 91000.0000 49000.0000
2002 3 7000.0000 140000.0000 -70000.0000
2002 4 154000.0000 7000.0000 84000.0000