Hàm LAG, LEAD trong SQL và ví dụ cụ thể

Xin chào mọi người, hôm nay mình sẽ chia sẻ với các bạn về hàm Lag và Lead trong SQL và ví dụ cụ thể để truy vấn dữ liệu.

Chúng ta có 1 bảng dữ liệu như sau:

Đề bài: Lấy ra giá trị cus_rank liền trước (Pre_cus_rank) của cùng 1 cus_id trong bảng Customer. Đối với case null thì gán Pre_cus_rank = cus_rank.

Để giải quyết được bài toán trên chúng ta sẽ phải sử dụng đến hàm LAG, vậy chức năng của hàm LAG là gì?

1. Chức năng hàm LAG

Cú pháp:

LAG (value_expr [, offset ]) [ IGNORE NULLS | RESPECT NULLS ] OVER ( [ PARTITION BY window_partition ] ORDER BY window_ordering )

Chức năng: Hàm LAG trả về các giá trị cho một hàng tại một khoảng lệch nhất định phía trên (trước) hàng hiện tại trong phân vùng.

Chi tiết:

• value_expr: Cột mục tiêu hoặc biểu thức mà hàm hoạt động trên đó.
• offset: Một tham số tùy chọn chỉ định số lượng hàng trước hàng hiện tại cần trả về giá trị. Phần bù có thể là một số nguyên không đổi hoặc một biểu thức đánh giá thành một số nguyên. Nếu bạn không chỉ định chênh lệch, Amazon Redshift sẽ sử dụng 1 làm giá trị mặc định. Độ lệch bằng 0 cho biết hàng hiện tại.
• IGNORE NULLS: Thông số kỹ thuật tùy chọn chỉ ra rằng Amazon Redshift nên bỏ qua các giá trị null khi xác định hàng nào sẽ sử dụng. Giá trị null được bao gồm nếu IGNORE NULLS không được liệt kê.
• RESPECT NULLS: Cho biết Amazon Redshift phải bao gồm các giá trị null khi xác định hàng nào sẽ sử dụng. RESPECT NULLS được hỗ trợ theo mặc định nếu bạn không chỉ định IGNORE NULLS.
• OVER: Chỉ định phân vùng và thứ tự.
• PARTITION BY window_partition: Đối số tùy chọn đặt phạm vi bản ghi cho mỗi nhóm trong mệnh đề OVER. 8 ORDER BY window_ordering: Sắp xếp các hàng trong mỗi phân vùn

2. Giải toán

Chúng ta cùng nhau đi giải quyết từng yêu cầu của bài toán trên nhé!

• Đối với yêu cầu ‘Lấy ra giá trị cus_rank liền trước của cùng 1 cus_id trong bảng Customer’ chúng ta sử dụng hàm lag để thực hiện truy vấn như sau:

Select *, lag(Cus_rank,1) over (partition by id order by update_dt) as Pre_cus_rank from Customer

Kết quả trả về như sau:

• Đối với case null thì gán Pre_cus_rank = cus_rank của bài toán, ta thực hiện truy vấn như sau:

with a as (Select *, lag(Cus_rank,1) over (partition by id order by update_dt) as Pre_cus_rank from Customer) select *, case when Pre_cus_rank <> Cus_rank then Pre_cus_rank else Cus_rank end as Pre_cus_rank_1 from a;

Kết quả trả về như sau:

• Trên đây là trường hợp lấy ra giá trị liền trước của 1 bản ghi (dùng hàm LAG), ngược lại muốn lấy giá trị tiếp sau của bản ghi đó chúng ta sẽ sử dụng hàm LEAD

Cú pháp:LEAD (value_expr [, offset ]) [ IGNORE NULLS | RESPECT NULLS ] OVER ( [ PARTITION BY window_partition ] ORDER BY window_ordering )

Áp dụng:

with a as (Select *, lead(Cus_rank) over (partition by id order by update_dt) as Update_cus_rank from Customer) select *, case when Update_cus_rank <> Cus_rank then Update_cus_rank else Cus_rank end as Update_cus_rank_1 from a;

Kết quả:

• Để tìm hiểu thêm về hàm LAG, LEAD, các bạn có thể tham khảo các link sau:
• https://docs.aws.amazon.com/redshift/latest/dg/r_WF_LAG.html
• https://docs.aws.amazon.com/redshift/latest/dg/r_WF_LEAD.html

Trên đây là một vài chia sẻ của mình, nếu các bạn có cách nào giải toán hay, đừng ngần ngại commnet nhé. Hẹn gặp lại mọi người trong bài viết tiếp theo.

Tài liệu tham khảo:

• https://docs.aws.amazon.com/redshift/latest/dg/r_WF_LAG.html
• https://docs.aws.amazon.com/redshift/latest/dg/r_WF_LEAD.html