Giới thiệu function LEAD, LAG, FILTER – PostgreSQL

Bài viết xin phép giới thiệu về 3 function mà theo mình ít được biết đến, nhưng cũng có ứng dụng khi truy vấn trong cơ sở dữ liệu PostgreSQL.

Để hiểu rõ hơn về từng function bài viết sẽ đi vào từng ví dụ cụ thể.

LEAD và LAG trong PostgreSQL

Định nghĩa và ứng dụng hàm LEAD

Trong PostgreSQL, hàm LEAD() được sử dụng để truy cập vào một hàng tiếp theo của hàng hiện tại, theo một tham số OFFSET cụ thể nào đó, ví dụ: OFFSET = 1 thì sẽ truy cập đến hàng liền sau hàng hiện tại, OFFSET = 2 thì truy cập vào liền sau hàng thứ 2 tính từ hàng hiện tại, …

Chú ý rằng vị trí của các hàng được nói đến ở đây là vị trí vật lý của nó được lưu trong bảng (thông thường là vị trí theo primiary key)

Thường được sử dụng để lấy giá trị của các hàng liền sau mà hạn chế được những câu query phức tạp.

Cú pháp

LEAD(expression [, offset [, default_value]]) 
OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

• expression: có thể là tên cột, có thể là một biểu thức tính toán nào đó.
• offset: là vị trí của dòng muốn truy cập vào tính từ dòng hiện tại, mặc định là 1

Ví dụ bài toán cụ thể

Cho bảng Logs gồm 2 cột

• id: int là khóa chính của bảng và tăng dần
• num: varchar

Tìm tất cả các số (ý là giá trị ở cột num) mà xuất hiện ít nhất ba lần liên tiếp (ý là ít nhất 3 record đều có num bằng nhau theo thứ tự vật lý của table)

Ví dụ

Logs table:
+----+-----+
| id | num |
+----+-----+
| 1  | 1   |
| 2  | 1   |
| 3  | 1   |
| 4  | 2   |
| 5  | 1   |
| 6  | 2   |
| 7  | 2   |
+----+-----+
Output: 
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+
Giải thích: chỉ có số 1 có 3 dòng đầu xuất hiện liên tiếp, còn số 2 thì mặc dù xuất hiên 3 lần nhưng 3 record đó không liên tiếp.

Nếu không dùng hàm LEAD() trong bài này, bạn sẽ phải dùng tới ROW_NUMBER() rồi JOIN 2 lần để kiểm tra 3 dòng có row number liên tiếp nhau. Nhưng với LEAD() câu query chỉ cần đơn giản như sau:

WITH logs_with_2_next_rows_data AS (
    SELECT 
        num,
        LEAD(num, 1) OVER() AS num1,
        LEAD(num, 2) OVER() AS num2
    FROM Logs
)
SELECT num AS ConsecutiveNums
FROM logs_with_2_next_rows_data
WHERE num = num1 AND num = num2
GROUP BY num;

Đến đây thì các bạn đã có thể áp dụng hàm LEAD() để giải bài Consecutive Numbers trên leetcode rồi.

Giới thiệu thêm về hàm LAG

LAG() có cách sử dụng cũng khá giống với LEAD(), hàm LAG được sử dụng để truy cập vào hàng đằng trước với hàng hiện tại.

LAG(expression [, offset [, default_value]]) 
OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

FILTER trong PostgreSQL

Hàm FILTER trong PostgreSQL được sử dụng kết hợp cùng các aggregate function như COUNT, SUM để lọc kết quả của aggregate function theo 1 điều kiện nào đó.

Ứng dụng rõ ràng nhất khi ta phải COUNT đồng thời nhiều điều kiện. Nếu như không sử dụng FILTER ta sẽ phải sử dụng COUNT kết hợp với CASE … WHEN nhưng cú pháp rất dài dòng, sử dụng FILTER sẽ giúp code ngắn gọn và dễ theo dõi hơn.

Cú pháp

AGGREGATE_FUNCTION(expression) FILTER (WHERE condition_expression)
Ví dụ COUNT(1) FILTER (WHERE status = 'active')

Mời bạn đọc ứng dụng COUNT và FILTER để thử sức với bài Trips and Users trên leetcode