LAG và LEAD: xoá bỏ self-join khỏi mọi báo cáo tăng trưởng

Bạn đang dùng self-join để so tháng này với tháng trước. Có một hàm làm việc đó nhanh gấp ba và ngắn hơn một nửa.

Bài toán

Tính mức tăng trưởng doanh thu so với tháng liền trước. Không có cột “tháng trước” trong bảng — phải tự lấy.

Công thức

SELECT
  thang,
  doanh_thu,
  LAG(doanh_thu) OVER (ORDER BY thang) AS thang_truoc,
  ROUND(100.0 * (doanh_thu - LAG(doanh_thu) OVER (ORDER BY thang))
        / NULLIF(LAG(doanh_thu) OVER (ORDER BY thang), 0), 1) AS tang_pct
FROM doanh_thu_thang;

LAG nhìn về sau, LEAD nhìn về trước. Cả hai nhận thêm hai tham số tuỳ chọn:

LAG(cot, 2)        -- lùi 2 dòng
LAG(cot, 1, 0)     -- lùi 1 dòng, thiếu thì lấy 0

Lỗi thường gặp

1. Quên ORDER BY trong OVER

-- Kết quả ngẫu nhiên, đổi mỗi lần chạy
LAG(doanh_thu) OVER ()

LAG vô nghĩa nếu không có thứ tự. ORDER BY bên trong OVER là bắt buộc.

2. Quên PARTITION BY khi có nhiều nhóm

-- SAI: tháng 1 của Hà Nội lấy giá trị của tháng 12 Đà Nẵng
LAG(doanh_thu) OVER (ORDER BY thang)

-- ĐÚNG
LAG(doanh_thu) OVER (PARTITION BY thanh_pho ORDER BY thang)

Đây là lỗi âm thầm nhất trong bài: số liệu vẫn ra, chỉ là sai.

3. Không xử lý dòng đầu tiên

Dòng đầu không có dòng trước nên LAG trả về NULL, và phép chia cho NULL cũng ra NULL. Đúng về mặt logic — nhưng nếu báo cáo cần số 0, dùng LAG(doanh_thu, 1, 0).

4. Tháng bị thiếu

LAG lấy dòng liền trước trong tập kết quả, không phải “tháng liền trước theo lịch”. Nếu tháng 2 không có dữ liệu, tháng 3 sẽ so với tháng 1 mà không báo gì. Muốn chắc chắn, tạo dải tháng đầy đủ trước bằng CROSS JOIN.

Ứng dụng thứ hai: khoảng cách giữa hai lần mua

SELECT
  khach_hang_id,
  ngay_dat,
  ngay_dat - LAG(ngay_dat) OVER (
    PARTITION BY khach_hang_id ORDER BY ngay_dat
  ) AS so_ngay_cach
FROM don_hang;

Từ đây tính được chu kỳ mua trung bình và phát hiện khách đang giãn dần — nền của mọi mô hình dự báo rời bỏ.

Luyện tập

• LeetCode 197 — Rising Temperature
• LeetCode 1907 — Count Salary Categories
• LeetCode 1454 — Active Users
• LeetCode 2701 — Consecutive Transactions with Increasing Amounts
• HackerRank — Interviews