Window functions: LAG, LEAD, FIRST_VALUE, LAST_VALUE

Mở đầu — vì sao bài này quan trọng

Hãy tưởng tượng bạn là BA tại một sàn thương mại điện tử như Tiki. Sếp hỏi: "Doanh thu tháng này tăng hay giảm so với tháng trước? Tăng bao nhiêu phần trăm?". Với kiến thức GROUP BY thông thường, bạn chỉ có thể tạo ra một bảng doanh thu từng tháng đứng riêng lẻ. Để so sánh tháng này với tháng trước, bạn buộc phải export ra Excel rồi kéo công thức =B3-B2. Vừa thủ công, vừa dễ sai, vừa không lặp lại được khi dữ liệu cập nhật.

Đây chính là khoảng trống mà nhóm window functions điều hướng (navigation functions) lấp đầy. Ở bài trước, bạn đã làm quen với ROW_NUMBER, RANK, DENSE_RANK — các hàm dùng để đánh số và xếp hạng trong một cửa sổ dữ liệu. Bài này tập trung vào bốn hàm cho phép bạn "nhìn sang" các dòng khác mà không cần self-join: LAG, LEAD, FIRST_VALUE, LAST_VALUE.

Điểm cốt lõi cần nhớ: bốn hàm này không gộp dòng lại như aggregate. Chúng giữ nguyên từng dòng kết quả, nhưng cho phép mỗi dòng "với tay" lấy giá trị của một dòng khác trong cùng cửa sổ — dòng phía trước, dòng phía sau, dòng đầu tiên hoặc dòng cuối cùng. Đây là công cụ nền tảng để tính tăng trưởng (growth), so sánh kỳ trước (period-over-period), và phát hiện thay đổi trạng thái — những phép tính mà BA phải làm gần như hằng ngày.

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

Mọi window function navigation đều có chung cấu trúc:

HÀM(...) OVER (PARTITION BY ... ORDER BY ...)

• PARTITION BY chia dữ liệu thành các nhóm độc lập (giống như tách bảng thành nhiều bảng con). Hàm điều hướng chỉ "nhìn" trong phạm vi partition của dòng hiện tại, không bao giờ vượt sang partition khác.
• ORDER BY xác định thứ tự "trước" và "sau". Đây là phần bắt buộc với navigation functions, vì khái niệm "dòng trước" chỉ có nghĩa khi đã sắp xếp.

LAG() — lấy giá trị của dòng TRƯỚC

LAG(cột, offset, default) trả về giá trị của cột tại dòng nằm offset vị trí phía trên dòng hiện tại theo thứ tự ORDER BY.

SELECT
  month,
  revenue,
  LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue
FROM monthly_sales;

Với dòng đầu tiên, không có dòng nào phía trước nên LAG trả về NULL. Tham số default (thứ ba) cho phép bạn thay NULL bằng một giá trị khác, ví dụ LAG(revenue, 1, 0).

LEAD() — lấy giá trị của dòng SAU

LEAD(cột, offset, default) là phiên bản ngược lại của LAG: nó nhìn xuống offset dòng phía dưới.

SELECT
  month,
  revenue,
  LEAD(revenue, 1) OVER (ORDER BY month) AS next_month_revenue
FROM monthly_sales;

LEAD đặc biệt hữu ích khi bạn muốn tính "khoảng cách đến sự kiện tiếp theo" — ví dụ thời gian giữa hai lần mua hàng của cùng một khách.

FIRST_VALUE() và LAST_VALUE() — lấy giá trị đầu/cuối cửa sổ

FIRST_VALUE(cột) trả về giá trị của cột tại dòng đầu tiên trong cửa sổ; LAST_VALUE(cột) trả về dòng cuối cùng. Hai hàm này rất hợp để so sánh mọi dòng với một mốc cố định — ví dụ so doanh thu mỗi tháng với tháng đầu năm.

SELECT
  month,
  revenue,
  FIRST_VALUE(revenue) OVER (ORDER BY month) AS first_month_revenue
FROM monthly_sales;

Lưu ý quan trọng: LAST_VALUE ẩn chứa một cái bẫy về frame (khung cửa sổ) mà chúng ta sẽ phân tích kỹ ở phần lỗi thường gặp. Đây là chỗ mà rất nhiều BA viết sai mà không biết.

Frame mặc định — gốc rễ của mọi hiểu lầm

Khi bạn viết OVER (ORDER BY month), SQL ngầm hiểu frame là RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Nghĩa là cửa sổ tính từ dòng đầu tiên đến... dòng hiện tại, chứ không phải đến hết partition. LAG, LEAD, FIRST_VALUE không bị ảnh hưởng bởi điều này, nhưng LAST_VALUE thì có — vì "dòng cuối cùng của cửa sổ tính đến dòng hiện tại" lại chính là dòng hiện tại. Hãy ghi nhớ chi tiết này.

Tình huống thực tế

Tình huống 1: Báo cáo tăng trưởng doanh thu MoM tại Tiki

Bối cảnh: Bạn là BA phụ trách dashboard doanh thu của Tiki. Ban giám đốc muốn một bảng thể hiện doanh thu từng tháng, mức tăng tuyệt đối và phần trăm tăng trưởng so với tháng liền trước (MoM — month over month). Dữ liệu nằm trong bảng monthly_sales(month, revenue).

SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
  revenue - LAG(revenue) OVER (ORDER BY month) AS mom_change,
  ROUND(
    (revenue - LAG(revenue) OVER (ORDER BY month))
    * 100.0 / LAG(revenue) OVER (ORDER BY month),
    2
  ) AS mom_growth_pct
FROM monthly_sales
ORDER BY month;

Kết quả mẫu:

Diễn giải: Tháng 1 có prev_revenue là NULL vì không có tháng nào trước nó — điều này hoàn toàn đúng và đẹp về mặt logic. Tháng 2 tăng 8,24%, tháng 3 giảm 3,26%. Chỉ một câu query, bạn đã thay thế toàn bộ thao tác kéo công thức trong Excel, và bảng này tự cập nhật mỗi khi dữ liệu mới đổ về.

Bài học rút ra: LAG biến phép so sánh kỳ trước thành một cột tính sẵn. Khi cần đo phần trăm tăng trưởng, hãy nhớ nhân 100.0 (có dấu chấm) để ép kiểu thập phân — nếu không, một số hệ như PostgreSQL sẽ làm phép chia số nguyên và cho ra 0.

Tình huống 2: Khoảng cách giữa hai lần đặt xe tại Be

Bối cảnh: Đội tăng trưởng của hãng gọi xe Be muốn hiểu hành vi quay lại của khách. Câu hỏi nghiệp vụ: "Trung bình mỗi khách cách bao nhiêu ngày giữa hai chuyến đi liên tiếp?". Dữ liệu nằm trong bảng trips(trip_id, user_id, trip_date).

Ở đây ta dùng LEAD để nhìn tới chuyến đi tiếp theo của cùng một khách, và PARTITION BY user_id để mỗi khách là một cửa sổ riêng:

SELECT
  user_id,
  trip_date,
  LEAD(trip_date) OVER (
    PARTITION BY user_id
    ORDER BY trip_date
  ) AS next_trip_date,
  LEAD(trip_date) OVER (
    PARTITION BY user_id
    ORDER BY trip_date
  ) - trip_date AS days_to_next_trip
FROM trips;

Với một khách có 3 chuyến vào 01/06, 05/06, 12/06, kết quả là:

Chuyến cuối cùng có next_trip_date là NULL vì chưa có chuyến nào sau đó. Để ra con số trung bình toàn hệ thống, bạn chỉ cần bọc query trên trong một subquery và lấy AVG(days_to_next_trip).

Bài học rút ra: LEAD kết hợp PARTITION BY là công thức kinh điển để đo khoảng cách thời gian giữa các sự kiện liên tiếp của cùng một thực thể. Nếu không có PARTITION BY user_id, query sẽ tính nhầm "chuyến tiếp theo" là chuyến của khách khác — một lỗi nghiệp vụ nghiêm trọng.

Tình huống 3: So sánh giá cổ phiếu với phiên mở cửa đầu kỳ

Bối cảnh: Một quỹ đầu tư muốn so sánh giá đóng cửa mỗi ngày của một mã chứng khoán với giá của phiên đầu tiên trong tháng, để thấy mức biến động lũy kế. Dữ liệu: daily_price(symbol, trade_date, close_price).

SELECT
  symbol,
  trade_date,
  close_price,
  FIRST_VALUE(close_price) OVER (
    PARTITION BY symbol, DATE_TRUNC('month', trade_date)
    ORDER BY trade_date
  ) AS month_open_price,
  ROUND(
    (close_price - FIRST_VALUE(close_price) OVER (
      PARTITION BY symbol, DATE_TRUNC('month', trade_date)
      ORDER BY trade_date
    )) * 100.0 / FIRST_VALUE(close_price) OVER (
      PARTITION BY symbol, DATE_TRUNC('month', trade_date)
      ORDER BY trade_date
    ),
    2
  ) AS pct_from_month_open
FROM daily_price;

Diễn giải: FIRST_VALUE luôn trả về giá của phiên đầu tháng làm mốc cố định, nhờ PARTITION BY symbol, DATE_TRUNC('month', trade_date) chia dữ liệu theo từng tháng của từng mã. Mỗi dòng vì thế tự tính được "đã tăng/giảm bao nhiêu phần trăm so với đầu tháng".

Bài học rút ra: FIRST_VALUE là cách tự nhiên để gắn một mốc neo (baseline) vào mọi dòng. Pattern này tái sử dụng được cho rất nhiều bài toán BA: so doanh thu với tháng gốc, so cân nặng với lần đo đầu, so giá với giá niêm yết ban đầu.

Hướng dẫn từng bước

Khi gặp một yêu cầu "so sánh dòng này với dòng khác", hãy đi theo quy trình sau:

• Xác định chiều so sánh. Bạn cần nhìn về quá khứ (LAG), tương lai (LEAD), điểm đầu (FIRST_VALUE) hay điểm cuối (LAST_VALUE)? Đây là quyết định đầu tiên và quan trọng nhất.

• Xác định partition. Phép so sánh chỉ có nghĩa trong phạm vi nào? So sánh tháng — không cần partition. So sánh chuyến đi của từng khách — PARTITION BY user_id. So sánh trong từng tháng của từng mã — PARTITION BY symbol, tháng. Quên partition là lỗi phổ biến nhất.

• Xác định thứ tự ORDER BY. Navigation functions bắt buộc phải có ORDER BY. "Trước" và "sau" được định nghĩa theo cột nào? Thường là cột thời gian.

• Viết hàm navigation và đặt alias rõ ràng. Ví dụ prev_revenue, next_trip_date — tên cột nên nói lên ý nghĩa nghiệp vụ.

• Tính phép so sánh dẫn xuất. Sau khi đã có giá trị dòng khác, mới tính hiệu, tỷ lệ phần trăm, hay số ngày chênh lệch.

• Xử lý NULL ở biên. Dòng đầu (với LAG) hoặc dòng cuối (với LEAD) sẽ ra NULL. Quyết định: giữ NULL cho đúng nghĩa, hay thay bằng default, hay lọc bỏ ở tầng ngoài.

Lỗi thường gặp & mẹo

Lỗi 1: Quên PARTITION BY khi dữ liệu có nhiều nhóm. Đây là lỗi gây sai số liệu mà query vẫn chạy bình thường — nguy hiểm nhất. Nếu bảng trips có nhiều khách mà bạn chỉ viết LEAD(trip_date) OVER (ORDER BY trip_date), thì "chuyến tiếp theo" của khách A có thể là chuyến của khách B. Luôn tự hỏi: phép so sánh này có được phép vượt ranh giới nhóm không?

Lỗi 2: Bẫy frame của LAST_VALUE. Đây là lỗi kinh điển. Viết:

LAST_VALUE(revenue) OVER (ORDER BY month)

bạn kỳ vọng nó trả về doanh thu tháng cuối cùng, nhưng thực tế nó trả về... chính doanh thu của dòng hiện tại. Lý do: frame mặc định là RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, nên "dòng cuối của cửa sổ" luôn là dòng hiện tại. Cách sửa là mở rộng frame ra hết partition:

LAST_VALUE(revenue) OVER (
  ORDER BY month
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

Mẹo thực dụng: nếu bạn muốn lấy giá trị cuối cùng, nhiều khi dễ hơn là dùng FIRST_VALUE với ORDER BY đảo chiều (ORDER BY month DESC).

Lỗi 3: Phép chia số nguyên khi tính phần trăm. (revenue - prev) / prev có thể ra 0 nếu cả hai là kiểu nguyên. Luôn nhân 100.0 hoặc ép CAST(... AS DECIMAL).

Lỗi 4: Chia cho 0 hoặc cho NULL. Khi prev_revenue là NULL (dòng đầu) hoặc bằng 0, phép chia sẽ ra NULL hoặc lỗi. Dùng NULLIF(prev_revenue, 0) ở mẫu số để tránh lỗi chia cho 0 một cách an toàn.

Lỗi 5: Dùng navigation function trong WHERE. Window functions được tính sau WHERE, nên bạn không thể lọc trực tiếp WHERE mom_growth_pct > 10. Phải bọc query vào subquery hoặc CTE rồi mới lọc ở tầng ngoài.

Mẹo về tính khả chuyển: Cả MySQL 8.0+ và PostgreSQL đều hỗ trợ đầy đủ bốn hàm này với cú pháp giống nhau. Khác biệt nhỏ nằm ở hàm ngày tháng đi kèm — PostgreSQL dùng DATE_TRUNC, MySQL dùng DATE_FORMAT. Lưu ý MySQL trước phiên bản 8.0 không có window functions.

Bài tập thực hành

Dùng bảng monthly_sales(month, revenue) và bảng orders(order_id, customer_id, order_date, amount):

• MoM cơ bản: Viết query tạo cột doanh thu tháng trước và phần trăm tăng trưởng MoM. Đảm bảo dòng đầu tiên ra NULL thay vì lỗi.

• So với 2 tháng trước: Dùng LAG(revenue, 2) để so sánh mỗi tháng với tháng cách đó 2 kỳ. Khi nào bạn dùng offset bằng 2 trong thực tế?

• Khoảng cách đơn hàng: Với mỗi khách trong bảng orders, tính số ngày giữa đơn hiện tại và đơn kế tiếp bằng LEAD và PARTITION BY customer_id.

• Neo đầu kỳ: Dùng FIRST_VALUE để hiển thị doanh thu mỗi tháng cùng doanh thu tháng đầu năm và phần trăm chênh so với mốc đó.

• Thử thách LAST_VALUE: Viết query lấy doanh thu tháng cuối cùng gắn vào mọi dòng. Trước tiên viết sai (không khai báo frame) để tự thấy lỗi, rồi sửa lại bằng ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

• Lọc sau cửa sổ: Từ bài 1, chỉ giữ những tháng có tăng trưởng MoM trên 10%. Bạn sẽ cần CTE hoặc subquery — vì sao không thể lọc trực tiếp trong WHERE?

Tóm tắt

Bốn hàm navigation — LAG, LEAD, FIRST_VALUE, LAST_VALUE — cho phép mỗi dòng "với tay" lấy giá trị của một dòng khác trong cùng cửa sổ mà không cần self-join. LAG nhìn về dòng trước (so sánh kỳ trước, tính tăng trưởng MoM/YoY), LEAD nhìn xuống dòng sau (đo khoảng cách đến sự kiện kế tiếp), còn FIRST_VALUE và LAST_VALUE lấy giá trị đầu/cuối cửa sổ (neo so sánh với một mốc cố định).

Ba điều cần khắc cốt: (1) luôn cân nhắc PARTITION BY để phép so sánh không vượt ranh giới nhóm; (2) ORDER BY là bắt buộc vì nó định nghĩa "trước" và "sau"; (3) cảnh giác với bẫy frame của LAST_VALUE và phép chia số nguyên khi tính phần trăm. Nắm vững bốn hàm này, bạn đã có trong tay công cụ chủ lực cho hầu hết báo cáo tăng trưởng và phân tích chuỗi thời gian mà một BA cần làm. Ở bài tiếp theo, chúng ta sẽ kết hợp partition với aggregate để tính running totals và partitioned aggregates — nâng tầm khả năng phân tích lên một mức mới.