Hàm cửa sổ cho dữ liệu thời gian: LEAD(), LAG()

Giờ nhiệm vụ của tụi mình là tiến thêm một bước nữa và học cách dùng hàm cửa sổ để phân tích dữ liệu thời gian. Sẵn sàng chưa? Hy vọng là bạn đã chuẩn bị sẵn một ly cà phê, vì phần này sẽ khá thú vị đấy.

Rồi, như thường lệ, đầu tiên trả lời câu hỏi chính: tại sao tụi mình lại cần hàm cửa sổ (LEAD(), LAG())? Hãy tưởng tượng bạn đang làm việc với dữ liệu thời gian, có thể là log sự kiện, giờ làm việc, chuỗi thời gian hay bất cứ thứ gì mà thứ tự sự kiện là quan trọng.

Ví dụ, bạn muốn:

• Biết khi nào sự kiện tiếp theo xảy ra sau sự kiện hiện tại.
• Tính chênh lệch thời gian giữa sự kiện hiện tại và sự kiện trước đó.
• Sắp xếp dữ liệu và tính chênh lệch giữa các bản ghi.

Lúc này, hai hàm cực kỳ xịn xuất hiện: LEAD() và LAG(). Chúng cho phép bạn lấy dữ liệu từ dòng trước hoặc dòng sau trong một "cửa sổ" xác định. Nó giống như bạn có một cuốn sách phép thuật, có thể nhìn trước trang tiếp theo mà không cần lật trang hiện tại.

LEAD() và LAG(): cú pháp và nguyên lý cơ bản

Cả hai hàm đều dùng cú pháp gần giống nhau:

LEAD(column_name, [offset], [default_value]) OVER (PARTITION BY column_name ORDER BY column_name)
LAG(column_name, [offset], [default_value]) OVER (PARTITION BY column_name ORDER BY column_name)

• column_name — cột mà bạn muốn lấy dữ liệu.
• offset (tùy chọn) — số dòng dịch chuyển so với dòng hiện tại. Mặc định là 1.
• default_value (tùy chọn) — giá trị trả về nếu không có dòng với offset yêu cầu (ví dụ, khi bạn ở dòng cuối cùng).
• OVER() — ở đây bạn xác định "cửa sổ" để tính toán. Thường là ORDER BY, đôi khi dùng PARTITION BY để chia nhóm dữ liệu.

Ví dụ: Đơn giản với LEAD() và LAG()

Làm thử một bảng events đơn giản để test nhé:

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_name TEXT NOT NULL,
    event_date TIMESTAMP NOT NULL
);

INSERT INTO events (event_name, event_date)
VALUES
    ('Sự kiện A', '2023-10-01 10:00:00'),
    ('Sự kiện B', '2023-10-01 11:00:00'),
    ('Sự kiện C', '2023-10-01 12:00:00'),
    ('Sự kiện D', '2023-10-01 13:00:00');

Bây giờ mình muốn xem sự kiện trước và sau của mỗi sự kiện là gì:

SELECT
    id,
    event_name,
    event_date,
    LAG(event_date) OVER (ORDER BY event_date) AS sự_kiện_trước,
    LEAD(event_date) OVER (ORDER BY event_date) AS sự_kiện_sau
FROM events;

Kết quả sẽ như sau:

Ở đây LAG() lấy dữ liệu từ dòng trước, còn LEAD() lấy từ dòng sau. Sự kiện đầu tiên không có gì để nhìn lại, sự kiện cuối cùng không có ai để vượt qua, nên chúng nhận NULL.

Ví dụ: chênh lệch giữa các sự kiện

Đôi khi bạn cần biết mất bao lâu giữa các sự kiện. Đơn giản chỉ cần trừ thời gian này cho thời gian kia:

SELECT
    id,
    event_name,
    event_date,
    event_date - LAG(event_date) OVER (ORDER BY event_date) AS thời_gian_từ_sự_kiện_trước
FROM events;

Kết quả:

Ví dụ: dùng PARTITION BY

Giả sử bạn có nhiều user, mỗi người có sự kiện riêng. Bạn muốn tính chênh lệch giữa các sự kiện cho từng user.

Cập nhật bảng và thêm cột user_id:

ALTER TABLE events ADD COLUMN user_id INT;

UPDATE events SET user_id = 1 WHERE id <= 2;
UPDATE events SET user_id = 2 WHERE id > 2;

Bây giờ có hai user. Dùng PARTITION BY để tính toán trong từng nhóm:

SELECT
    user_id,
    event_name,
    event_date,
    event_date - LAG(event_date) OVER (PARTITION BY user_id ORDER BY event_date) AS thời_gian_từ_sự_kiện_trước
FROM events;

Kết quả:

Ví dụ thực tế

1. Log sự kiện: phân tích thời gian giữa các sự kiện như login và logout của user.
2. Theo dõi thời gian: tính thời gian làm việc trên từng task.
3. Phân tích hành vi: phân tích chuỗi hành động của khách trong shop online.
4. Tính toán chỉ số tích lũy: dùng hàm cửa sổ cho chuỗi thời gian.

Lỗi thường gặp

Khi làm với LEAD() và LAG() dễ gặp mấy vấn đề sau:

• Quên ORDER BY trong OVER(). Không có nó thì hàm không biết thứ tự dòng đâu.
• Vấn đề với kiểu dữ liệu thời gian (TIMESTAMP vs DATE).
• Bỏ qua giá trị NULL xuất hiện ở đầu và cuối cửa sổ.

Để tránh lỗi, luôn kiểm tra dữ liệu và đảm bảo bạn xác định đúng cửa sổ cho thao tác của mình nhé.