Hàm LAG và LEAD trong SQL

Hôm nay mình sẽ hướng dẫn các bạn sử dụng Hàm LAG và Hàm LEAD với ví dụ cụ thể để truy vấn dữ liệu theo dạng time-series ( dòng thời gian ).

Định nghĩa Hàm LAG và LEAD trong SQL server

Hàm LAG là 1 window function  cho phép bạn truy vấn một hoặc nhiều dòng trong 1 bảng mà không cần nối bảng với chính nó. Vì dụ như bạn đang chọn 1 hàng, hàm LAG sẽ trả về giá trị hàng trước nó trong bảng. Để trả về giá trị hàng tiếp theo trong bảng, chúng ta sử dụng hàm LEAD.

Hàm LAG và LEAD trong SQL
Học Lag and Lead trong SQL

Cú pháp:

Cú pháp của hàm LAG trong SQL server là: 

LAG ( expression [, offset [, default] ] )

OVER ( [ query_partition_clause ] order_by_clause )

Cú pháp của hàm LEAD trong SQL server là: 

LEAD  ( expression [, offset [, default] ] )

OVER ( [ query_partition_clause ] order_by_clause )

Chi tiết: 

expression

Một biểu thức có thể chứa các hàm lập sẵn ( sum, …) nhưng không được chứa bất kì hàm phân tích nào. 

Offset: 

Tùy chọn, Đây là phần lệch tính từ hàng hiện tại trong bảng, nếu tham số này bị bỏ qua, mặc định giá trị là 1.

default

Tùy chọn, Đây là giá trị được trả về nếu giá trị phần lệch chạy khỏi bảng. Nếu trường này để trống, giá trị mặc định sẽ là null.

order_by_clause:

Không bắt buộc. Nó được sử dụng để sắp xếp dữ liệu trong mỗi phân vùng.

query_partition_clause

Không bắt buộc. Nó được sử dụng để phân vùng kết quả thành các nhóm dựa trên một hoặc nhiều biểu thức.

order_by_clause: 

Không bắt buộc. Nó được sử dụng để sắp xếp dữ liệu trong mỗi phân vùng.

Ví dụ:

Cho một bảng chứa cách hoạt động của người dùng 1 app như sau:

date_keyevent_namecustomer_id
5/29/2021view7424
5/13/2021view7426
5/13/2021view7426
5/27/2021search7425
5/13/2021register7426
5/13/2021add_to_cart7425
5/13/2021add_to_cart7426
5/15/2021view7425
5/14/2021search7424
5/14/2021purchase7424
5/14/2021purchase7428
5/14/2021register7425
5/14/2021add_to_cart7428
5/14/2021app_installed7424

Và chúng ta chạy câu lệnh SQL sau:

1 2 3SELECT customer_id, date_key, event_name, LAG (event_name,1) OVER (ORDER BY event_name) AS near_last_event_name FROM example1  GROUP BY customer_id

Nó sẽ trả về kết quả sau:

customer_iddate_keyevent_namenear_last_event_name
74285/14/2021purchasenull
74255/27/2021searchpurchase
74245/29/2021viewsearch
74265/13/2021viewview

Trong ví dụ này, hàm LAG đã sắp xếp theo thứ tự thời gian tất cả các hoạt động trong bảng khách hàng và sau đó trả về hoạt động gần nhất kể từ sử dụng offset là 1.

Nếu sử dụng offset bằng 2 thay vào đó, nó sẽ trả lại hành động gần thứ 2 trước hành động cuối. 

Tương tự với hàm LEAD, Ta thử sử dụng Partitions cho bảng sau:

 Name  Team  Salary 
 Jae Crowder  Boston Celtics           6,796,117 
 Amir Johnson  Boston Celtics         12,000,000 
 Jared Sullinger  Boston Celtics           2,569,260 
 Isaiah Thomas  Boston Celtics           6,912,869 
 Bojan Bogdanovic  Brooklyn Nets           3,425,510 
 Markel Brown  Brooklyn Nets              845,059 
 Rondae Hollis-Jefferson  Brooklyn Nets           1,335,480 
 Brook Lopez  Brooklyn Nets         19,689,000 
 Al Horford  Atlanta Hawks         12,000,000 
 Kyle Korver  Atlanta Hawks           5,746,479 
 Dennis Schroder  Atlanta Hawks           1,763,400 
 Jeff Teague  Atlanta Hawks           8,000,000 

Mệnh đề truy vấn partition để trả lại mức lương cao nhất tiếp theo cho mỗi cầu  thủ nba trong team, Nhập câu lệnh SQL sau:

1 2 3SELECT team, name, salary, LEAD (salary,1) OVER (PARTITION BY TEAM ORDER BY salary) AS next_highest_salary FROM salary

Nó sẽ trả về kết quả sau:

TeamNameSalarynext_highest_salary
Atlanta HawksDennis Schroder17634005746479
Atlanta HawksKyle Korver57464798000000
Atlanta HawksJeff Teague800000012000000
Atlanta HawksAl Horford12000000null
Boston CelticsJared Sullinger25692606796117
Boston CelticsJae Crowder67961176912869
Boston CelticsIsaiah Thomas691286912000000
Boston CelticsAmir Johnson12000000null
Brooklyn NetsMarkel Brown8450591335480
Brooklyn NetsRondae Hollis-Jefferson13354803425510
Brooklyn NetsBojan Bogdanovic342551019689000
Brooklyn NetsBrook Lopez19689000null

Kết

Như vậy là mình đã trình bày xong phần ứng dụng lead và lag vào phân tích dữ liệu, nếu các bạn thấy hữu ích hãy share bài này nhé.

Support Tuan | Analytic

Trả lời