LAG và LEAD

Bạn sẽ học được

• LAG() và LEAD() lấy giá trị của dòng trước/sau trong cửa sổ.
• Tính chênh lệch và phần trăm thay đổi giữa hai kỳ.
• Chia cửa sổ theo khách để so sánh đơn này với đơn trước của chính khách đó.
• Xử lý NULL ở dòng đầu và dòng cuối.
• Vì sao thiếu kỳ trong chuỗi thời gian làm kết quả sai âm thầm.

Khái niệm cốt lõi

LAG và LEAD trả về giá trị của một dòng khác trong cùng cửa sổ mà không làm mất dòng hiện tại. LAG nhìn về trước, LEAD nhìn về sau:

LAG(cot, khoang_cach, gia_tri_mac_dinh)
LEAD(cot, khoang_cach, gia_tri_mac_dinh)

Cả ba tham số sau tên cột đều tuỳ chọn; mặc định khoảng cách là 1, mặc định giá trị là NULL.

Bài toán kinh điển: doanh thu tháng này so với tháng trước. Không cần tự nối bảng với chính nó — chỉ cần một cửa sổ:

WITH monthly AS (
  SELECT TO_CHAR(o.order_date, 'YYYY-MM') AS thang,
         SUM(oi.quantity * oi.unit_price) AS doanh_thu
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  GROUP BY 1
)
SELECT thang, doanh_thu,
       LAG(doanh_thu) OVER (ORDER BY thang) AS thang_truoc,
       doanh_thu - LAG(doanh_thu) OVER (ORDER BY thang) AS chenh_lech
FROM monthly
ORDER BY thang;

Kết quả thật trên dữ liệu mẫu:

Tháng 1 có NULL vì không có tháng nào trước nó. Đây là hành vi đúng, không phải lỗi — và cũng là lý do phải cẩn thận khi tính phần trăm: chia cho NULL ra NULL.

Phần trăm thay đổi cần bảo vệ phép chia:

WITH monthly AS (
  SELECT TO_CHAR(o.order_date, 'YYYY-MM') AS thang,
         SUM(oi.quantity * oi.unit_price) AS doanh_thu
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  GROUP BY 1
)
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 pct_thay_doi
FROM monthly
ORDER BY thang;

NULLIF(x, 0) biến mẫu số 0 thành NULL, tránh lỗi chia cho 0. Trên dữ liệu này không tháng nào bằng 0 nên chỉ tháng đầu trả NULL. Kết quả: tháng 2 −19,0%; tháng 4 +56,0%; tháng 5 −61,6%; tháng 9 −85,6%; tháng 10 +163,8%.

LEAD là chiếc gương của LAG:

WITH monthly AS (
  SELECT TO_CHAR(o.order_date, 'YYYY-MM') AS thang,
         SUM(oi.quantity * oi.unit_price) AS doanh_thu
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  GROUP BY 1
)
SELECT thang, doanh_thu,
       LEAD(doanh_thu) OVER (ORDER BY thang) AS thang_sau,
       LEAD(doanh_thu) OVER (ORDER BY thang) - doanh_thu AS se_tang_them
FROM monthly
ORDER BY thang;

Dùng khi cần "tháng này còn cách tháng sau bao nhiêu" — hữu ích cho dự báo ngược hoặc kiểm tra mục tiêu. Tháng 11 trả NULL ở hai cột cuối.

Khoảng cách có thể lớn hơn 1. LAG(doanh_thu, 2) so với hai tháng trước, phù hợp khi dữ liệu có yếu tố mùa vụ và bạn muốn so cùng kỳ. Tham số thứ ba là giá trị thay thế khi không có dòng:

SELECT thang, doanh_thu,
       LAG(doanh_thu, 1, 0) OVER (ORDER BY thang) AS thang_truoc_mac_dinh_0
FROM monthly
ORDER BY thang;

Tháng 1 sẽ có thang_truoc_mac_dinh_0 = 0 thay vì NULL, và chênh lệch bằng đúng doanh thu tháng 1. Cách này tiện cho biểu đồ nhưng che mất sự thật "không có kỳ trước" — hãy dùng có chủ đích.

Ví dụ chạy được

So sánh trong từng khách: mỗi đơn so với đơn liền trước của cùng khách. PARTITION BY customer_id là mảnh ghép quan trọng — thiếu nó, đơn của Bình sẽ bị so với đơn của người khác:

WITH totals AS (
  SELECT o.id, o.customer_id, c.name, o.order_date,
         SUM(oi.quantity * oi.unit_price) AS total
  FROM orders o
  JOIN customers c ON c.id = o.customer_id
  JOIN order_items oi ON oi.order_id = o.id
  GROUP BY o.id, o.customer_id, c.name, o.order_date
)
SELECT name, order_date, total,
       LAG(total) OVER (PARTITION BY customer_id ORDER BY order_date) AS don_truoc,
       order_date - LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS so_ngay
FROM totals
ORDER BY name, order_date;

Mười sáu dòng, nhưng chỉ những khách có từ hai đơn mới có giá trị ở hai cột cuối:

Phép trừ hai date trong PostgreSQL trả về số ngày dạng integer, nên order_date - LAG(order_date) cho luôn khoảng cách ngày. Đây là mẫu dùng để tính "chu kỳ mua lại" — chỉ số trung tâm của phân tích giữ chân khách hàng.

Kiểm tra tháng bị thiếu trước khi dùng LAG — dựng đủ chuỗi kỳ rồi nối trái:

WITH monthly AS (
  SELECT TO_CHAR(o.order_date, 'YYYY-MM') AS thang,
         SUM(oi.quantity * oi.unit_price) AS doanh_thu
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  GROUP BY 1
), expected AS (
  SELECT TO_CHAR(m, 'YYYY-MM') AS thang
  FROM generate_series(DATE '2025-01-01', DATE '2025-11-01', INTERVAL '1 month') AS m
)
SELECT e.thang, m.doanh_thu
FROM expected e
LEFT JOIN monthly m USING (thang)
ORDER BY e.thang;

Tháng nào không có doanh thu sẽ hiện NULL thay vì biến mất khỏi kết quả — đó là dấu hiệu để bạn xử lý trước khi tính chênh lệch. Với dữ liệu mẫu, cả 11 tháng đều có số. Nếu tháng 6 vắng doanh thu, LAG sẽ so tháng 7 với tháng 5 và báo mức tăng sai — lỗi không hề báo, chỉ lệch số. Trong phân tích thời gian, luôn kiểm tra COUNT(*) có bằng số kỳ mong đợi không.

Một biến thể hữu ích: đánh dấu đơn hàng lớn hơn đơn trước:

WITH totals AS (
  SELECT o.id, o.customer_id, o.order_date,
         SUM(oi.quantity * oi.unit_price) AS total
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  GROUP BY o.id, o.customer_id, o.order_date
)
SELECT id, customer_id, order_date, total,
       CASE WHEN total > LAG(total) OVER (PARTITION BY customer_id ORDER BY order_date)
            THEN 'tăng' ELSE 'giảm hoặc bằng' END AS xu_huong
FROM totals
ORDER BY customer_id, order_date;

Dòng đầu tiên của mỗi khách rơi vào nhánh ELSE vì so sánh với NULL không bao giờ đúng. Nếu cần phân biệt "chưa có đơn trước", thêm một nhánh WHEN LAG(...) IS NULL THEN 'đơn đầu' đặt trước các nhánh còn lại — CASE dừng ở điều kiện đúng đầu tiên.

Thực hành

1. Chạy câu tính chênh lệch tháng và đối chiếu bảng 11 dòng ở trên.
2. Thêm cột pct_thay_doi với NULLIF và kiểm tra tháng 10 là +163,8%.
3. Đổi LAG thành LEAD và xác nhận tháng 11 trả NULL.
4. Tính số ngày giữa hai đơn liên tiếp của Bình Trần: phải là 56 và 111.
5. Bỏ PARTITION BY customer_id khỏi câu số ngày và giải thích vì sao con số trở nên vô nghĩa.

Lỗi thường gặp

Lỗi thứ ba và thứ tư là hai lỗi "im lặng" nguy hiểm nhất: câu lệnh chạy, không có cảnh báo, nhưng mọi con số đều sai. Cách phòng thủ là luôn đối chiếu vài dòng bằng tay trước khi đưa vào báo cáo.

Tóm tắt

• LAG/LEAD lấy giá trị dòng trước/sau, giữ nguyên dòng hiện tại.
• Tham số thứ hai là khoảng cách, thứ ba là giá trị mặc định.
• PARTITION BY quyết định "trước" là trước trong nhóm nào.
• Bảo vệ phép chia bằng NULLIF và xử lý NULL ở biên.
• Kiểm tra đủ kỳ trước khi tin vào chuỗi thời gian.