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 ).
Table of Contents
Đị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.
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_key | event_name | customer_id |
| 5/29/2021 | view | 7424 |
| 5/13/2021 | view | 7426 |
| 5/13/2021 | view | 7426 |
| 5/27/2021 | search | 7425 |
| 5/13/2021 | register | 7426 |
| 5/13/2021 | add_to_cart | 7425 |
| 5/13/2021 | add_to_cart | 7426 |
| 5/15/2021 | view | 7425 |
| 5/14/2021 | search | 7424 |
| 5/14/2021 | purchase | 7424 |
| 5/14/2021 | purchase | 7428 |
| 5/14/2021 | register | 7425 |
| 5/14/2021 | add_to_cart | 7428 |
| 5/14/2021 | app_installed | 7424 |
Và chúng ta chạy câu lệnh SQL sau:
| 1 2 3 | SELECT 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_id | date_key | event_name | near_last_event_name |
| 7428 | 5/14/2021 | purchase | null |
| 7425 | 5/27/2021 | search | purchase |
| 7424 | 5/29/2021 | view | search |
| 7426 | 5/13/2021 | view | view |
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 3 | SELECT 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:
| Team | Name | Salary | next_highest_salary |
| Atlanta Hawks | Dennis Schroder | 1763400 | 5746479 |
| Atlanta Hawks | Kyle Korver | 5746479 | 8000000 |
| Atlanta Hawks | Jeff Teague | 8000000 | 12000000 |
| Atlanta Hawks | Al Horford | 12000000 | null |
| Boston Celtics | Jared Sullinger | 2569260 | 6796117 |
| Boston Celtics | Jae Crowder | 6796117 | 6912869 |
| Boston Celtics | Isaiah Thomas | 6912869 | 12000000 |
| Boston Celtics | Amir Johnson | 12000000 | null |
| Brooklyn Nets | Markel Brown | 845059 | 1335480 |
| Brooklyn Nets | Rondae Hollis-Jefferson | 1335480 | 3425510 |
| Brooklyn Nets | Bojan Bogdanovic | 3425510 | 19689000 |
| Brooklyn Nets | Brook Lopez | 19689000 | null |
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é.