Trino SQL Bài học 28: Window Functions: Truy xuất dữ liệu chuỗi (LEAD, LAG)

Mục tiêu bài học:

• Xóa bỏ sự phụ thuộc vào Self Join khi cần so sánh một dòng dữ liệu với dòng liền trước hoặc liền sau nó.

• Nắm vững cú pháp của LAG (nhìn về quá khứ) và LEAD (nhìn tới tương lai).

• Giải quyết các bài toán phân tích chuỗi thời gian (Time-series): Tính toán độ trễ (Time Delta), mức tăng trưởng (Growth Rate), doanh thu tháng này so với tháng trước (MoM).

• Xử lý an toàn các giá trị rỗng (NULL) sinh ra ở hai đầu biên của tập dữ liệu.

1. Bản chất: Tại sao không dùng Self Join?

Trước khi có Window Functions, nếu bạn muốn so sánh doanh thu của đơn hàng ngày hôm nay với đơn hàng ngày hôm qua, bạn phải dùng Self Join (Bài 23) để nối bảng với chính nó dựa trên điều kiện ngày tháng ($Ngày_A = Ngày_B - 1$).

Cách làm này cực kỳ cồng kềnh, dễ sinh lỗi logic nếu có ngày nghỉ (không có đơn hàng), và hiệu năng rất thấp.

Hàm LAG và LEAD giải quyết bài toán này một cách thanh lịch bằng cách trượt một "con trỏ" lên hoặc xuống ngay bên trong khung dữ liệu đã được sắp xếp, giúp bạn "nhặt" giá trị của dòng khác đắp vào dòng hiện tại.

• LAG(): Lấy dữ liệu của dòng phía trước (quá khứ).

• LEAD(): Lấy dữ liệu của dòng phía sau (tương lai).

2. Cú pháp cơ bản và Các tham số

Cả hai hàm đều dùng chung một cấu trúc tham số:

LAG(cột_cần_lấy, [bước_nhảy], [giá_trị_mặc_định]) OVER (PARTITION BY ... ORDER BY ...)

• cột_cần_lấy (Bắt buộc): Cột bạn muốn truy xuất giá trị.

• bước_nhảy (Tùy chọn): Số lượng dòng muốn lùi/tiến. Mặc định là 1 (dòng liền kề).

• giá_trị_mặc_định (Tùy chọn): Nếu không tìm thấy dòng nào (ví dụ: đang ở dòng đầu tiên nên không có dòng phía trước), Trino sẽ trả về NULL. Bạn có thể thay NULL bằng một số cụ thể ở tham số này.

(Quy tắc thép: Mệnh đề OVER của LAG/LEAD bắt buộc phải có ORDER BY để xác định rõ dòng nào đứng trước, dòng nào đứng sau).

3. Bài toán 1: Tính toán khoảng cách thời gian (Time Delta)

Bạn muốn phân tích hành vi mua sắm của khách hàng: Mỗi khách hàng thường cách bao lâu mới quay lại đặt một đơn hàng mới?

Sử dụng LAG kết hợp với hàm date_diff() (Bài 13), chúng ta có thể lấy ngày đặt đơn liền trước trừ đi ngày đặt đơn hiện tại.

SQL

SELECT 
    custkey,
    orderkey,
    orderdate AS ngay_hien_tai,
    -- Lấy ngày đặt hàng của chính khách hàng đó ở lần mua liền trước
    LAG(orderdate, 1) OVER (
        PARTITION BY custkey 
        ORDER BY orderdate ASC
    ) AS ngay_mua_lan_truoc,
    
    -- Tính khoảng cách (số ngày) giữa 2 lần mua
    date_diff(
        'day', 
        LAG(orderdate, 1) OVER (PARTITION BY custkey ORDER BY orderdate ASC), 
        orderdate
    ) AS so_ngay_cho_doi
FROM tpch.tiny.orders
ORDER BY custkey, orderdate;

Lưu ý: Ở dòng đầu tiên của mỗi khách hàng, cột ngay_mua_lan_truoc sẽ bị NULL, dẫn đến so_ngay_cho_doi cũng NULL (vì đây là lần đầu họ mua, không có quá khứ để so sánh).

4. Bài toán 2: Đánh giá tăng trưởng (Growth & Delta)

So sánh doanh thu đơn hàng hiện tại với đơn hàng liền sau nó (sắp xảy ra) để xem xu hướng tăng hay giảm.

SQL

SELECT 
    orderkey, 
    orderdate, 
    totalprice AS doanh_thu_hien_tai,
    -- Nhìn tới tương lai: Doanh thu của đơn hàng tiếp theo
    LEAD(totalprice, 1) OVER (ORDER BY orderdate ASC, orderkey ASC) AS doanh_thu_don_tiep_theo,
    
    -- Tính phần chênh lệch
    LEAD(totalprice, 1) OVER (ORDER BY orderdate ASC, orderkey ASC) - totalprice AS chenh_lech
FROM tpch.tiny.orders
WHERE orderdate BETWEEN DATE '1995-01-01' AND DATE '1995-01-10';

5. Xử lý ngoại lệ với Tham số mặc định

Khi làm báo cáo tài chính, hiển thị NULL ở các ô chênh lệch tăng trưởng là điều tối kỵ. Bạn nên dùng tham số thứ 3 của LAG/LEAD để lấp đầy khoảng trống này (hoạt động giống hàm COALESCE).

Cạm bẫy "Kiểu tĩnh" của Trino: Tham số mặc định này bắt buộc phải cùng kiểu dữ liệu với cột đang truy xuất. Nếu totalprice là kiểu DOUBLE, bạn phải truyền 0.0 chứ không được truyền 0 (số nguyên) hay 'Không có' (chuỗi).

SQL

-- Nếu là dòng đầu tiên, coi doanh thu tháng trước là 0.0 để tính toán
SELECT 
    EXTRACT(MONTH FROM orderdate) AS thang,
    SUM(totalprice) AS doanh_thu_thang_nay,
    LAG(SUM(totalprice), 1, 0.0) OVER (
        ORDER BY EXTRACT(MONTH FROM orderdate) ASC
    ) AS doanh_thu_thang_truoc
FROM tpch.tiny.orders
WHERE orderdate BETWEEN DATE '1995-01-01' AND DATE '1995-12-31'
GROUP BY EXTRACT(MONTH FROM orderdate);

(Ghi chú: Trong truy vấn trên, chúng ta đã lồng hàm SUM bên trong hàm LAG. Trino sẽ thực hiện GROUP BY tính tổng từng tháng trước, sau đó Window Function mới trượt qua các tháng đó để nhặt kết quả).