SQL cho phân tích dữ liệu · Hướng dẫn · 30 tháng 10, 2026 · 15 phút đọc

Hàm LAG và LEAD trong SQL: lấy giá trị dòng trước và dòng sau, kèm ví dụ

LAG(column) trả về giá trị của column ở dòng trước, còn LEAD(column) ở dòng sau, theo thứ tự bạn đặt bằng OVER (ORDER BY ...). Cả hai đều giữ nguyên mọi dòng trong kết quả, nên bạn có thể đặt “kỳ này” và “kỳ trước” cạnh nhau rồi trừ đi. Thêm PARTITION BY để chỉ nhìn các dòng trước hoặc sau của cùng một khách hàng, sản phẩm hay chuyến bay. Cú pháp đầy đủ là LAG(column, offset, default): offset là lùi lại bao nhiêu dòng (mặc định 1), default là giá trị trả về khi không có dòng đó (mặc định NULL).

Mọi ví dụ đều chạy trên cơ sở dữ liệu hãng hàng không của khóa SQL trên Nomto. Truy vấn dùng cú pháp PostgreSQL và chạy trên DuckDB, động cơ của phòng học SQL trên Nomto; mọi bảng kết quả đều là kết quả chạy thật. LAG và LEAD là hàm cửa sổ (window function); nếu OVER và PARTITION BY còn mới với bạn, hãy bắt đầu từ bài hàm cửa sổ trong SQL.

LAG và LEAD: dòng trước và dòng sau

Số đơn đặt chỗ mỗi ngày đầu tháng 8, kèm ngày hôm trước và ngày hôm sau trên mỗi dòng:

WITH daily AS (
  SELECT CAST(book_date AS date) AS day, COUNT(*) AS bookings
  FROM bookings
  WHERE book_date >= '2026-08-01' AND book_date < '2026-08-06'
  GROUP BY CAST(book_date AS date)
)
SELECT day, bookings,
       LAG(bookings)  OVER (ORDER BY day) AS prev_day,
       LEAD(bookings) OVER (ORDER BY day) AS next_day
FROM daily
ORDER BY day;

Dòng đầu tiên không có dòng trước nên LAG trả về NULL; dòng cuối không có dòng sau nên LEAD trả về NULL. Lưu ý rằng cửa sổ chỉ thấy những dòng đã qua WHERE: ngày 1/8 không có “ngày hôm trước” ở đây vì ngày 31/7 đã bị lọc đi, chứ không phải vì hôm đó không có đơn nào.

LEAD(x) giống LAG(x) với thứ tự đảo ngược: LEAD(bookings) OVER (ORDER BY day) bằng LAG(bookings) OVER (ORDER BY day DESC). Hãy dùng cách nào đọc tự nhiên hơn.

Offset và giá trị mặc định

Tham số thứ hai cho phép nhìn xa hơn về phía trước, tham số thứ ba thay cho NULL khi không có gì để nhìn:

WITH daily AS (
  SELECT CAST(book_date AS date) AS day, COUNT(*) AS bookings
  FROM bookings
  WHERE book_date >= '2026-07-01' AND book_date < '2026-08-01'
  GROUP BY CAST(book_date AS date)
)
SELECT day, bookings,
       LAG(bookings, 1, 0) OVER (ORDER BY day) AS prev_or_zero,
       LAG(bookings, 7)    OVER (ORDER BY day) AS same_day_last_week,
       ROUND(100.0 * (bookings - LAG(bookings, 7) OVER (ORDER BY day))
             / LAG(bookings, 7) OVER (ORDER BY day), 1) AS wow_pct
FROM daily
ORDER BY day
LIMIT 10;

(Hiển thị 10 ngày đầu tháng 7.)

• LAG(bookings, 7) so sánh thứ Tư với thứ Tư tuần trước. Với dữ liệu ngày có nhịp theo tuần, so sánh này công bằng hơn so với hôm qua.
• LAG(bookings, 1, 0) trả về 0 ở dòng đầu tiên. Cẩn thận với giá trị mặc định: số 0 ở đây nghĩa là “không có dòng trước”, không phải “hôm qua không có đơn”, và phần trăm thay đổi so với 0 là phép chia cho 0. Với các phép tính thay đổi, NULL thường là câu trả lời trung thực hơn.
• Giá trị mặc định phải có kiểu tương thích với cột. LAG(day, 1, 'none') trên cột kiểu ngày sẽ báo lỗi.

LAG(x, 7) nghĩa là lùi bảy dòng, không phải bảy ngày. Nếu daily thiếu một ngày, phép so sánh âm thầm bị lệch. Khi có thể có ngày trống, hãy dựng lịch đầy đủ trước; xem bài date spine (tiếng Anh).

So sánh với kỳ trước

Cách dùng LAG phổ biến nhất: doanh thu tuần này so với tuần trước, theo giá trị tuyệt đối và theo phần trăm. Chỉ lấy các tuần trọn vẹn, từ 29/6 đến 9/8:

WITH weekly AS (
  SELECT CAST(date_trunc('week', book_date) AS date) AS week,
         SUM(total_amount) AS revenue
  FROM bookings
  WHERE book_date >= '2026-06-29' AND book_date < '2026-08-10'
  GROUP BY 1
)
SELECT week, revenue,
       LAG(revenue) OVER (ORDER BY week) AS prev_week,
       revenue - LAG(revenue) OVER (ORDER BY week) AS change,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY week))
             / LAG(revenue) OVER (ORDER BY week), 1) AS pct_change
FROM weekly
ORDER BY week;

Doanh thu tính bằng đô la Mỹ. Cửa sổ chạy sau GROUP BY, nên nó làm việc trên tổng theo tuần chứ không phải từng đơn đặt chỗ. Hai thói quen hữu ích ở đây:

• Bỏ các kỳ chưa trọn. Dữ liệu bắt đầu giữa tuần vào ngày 17/6 và kết thúc ngày 11/8; giữ lại những tuần dở dang đó sẽ tạo ra một cú tăng giả ở đầu và một cú sụt giả ở cuối.
• Viết 100.0, không phải 100. Trong PostgreSQL và SQL Server, số nguyên chia số nguyên ra số nguyên, và những thay đổi nhỏ bị làm tròn về 0. Ở đây revenue là số thập phân nên không sao, nhưng với COUNT(*) thì sẽ sai.

Lặp lại LAG(revenue) OVER (ORDER BY week) ba lần hơi dài dòng nhưng vô hại: các cửa sổ giống hệt nhau dùng chung một lần sắp xếp. Để dễ đọc hơn, bạn cũng có thể tính prev_week trong một CTE rồi làm phép tính ở truy vấn bên ngoài. Báo cáo “tháng này so với tháng trước” (MoM) mà team kinh doanh hay hỏi cũng viết y hệt, chỉ thay 'week' bằng 'month'.

LAG với PARTITION BY

Không có PARTITION BY, cửa sổ là toàn bộ kết quả. Với một chuỗi thời gian duy nhất thì ổn, nhưng sẽ sai ngay khi các dòng thuộc về nhiều đối tượng. Dưới đây là các chuyến bay của hai thành viên, với sân bay đến trước đó tính theo cả hai cách:

WITH trips AS (
  SELECT tm.member_id, f.scheduled_departure, f.arrival_airport
  FROM ticket_members tm
  JOIN ticket_flights tf ON tf.ticket_no = tm.ticket_no
  JOIN flights f ON f.flight_id = tf.flight_id
  WHERE tm.member_id IN (2969, 12589)
), x AS (
  SELECT member_id, scheduled_departure,
         LAG(arrival_airport) OVER (ORDER BY member_id, scheduled_departure) AS prev_no_partition,
         LAG(arrival_airport) OVER (PARTITION BY member_id ORDER BY scheduled_departure) AS prev_partitioned,
         ROW_NUMBER() OVER (PARTITION BY member_id ORDER BY scheduled_departure) AS n
  FROM trips
)
SELECT member_id, scheduled_departure, prev_no_partition, prev_partitioned
FROM x
WHERE n <= 2
ORDER BY member_id, scheduled_departure;

Không chia nhóm, chuyến bay đầu tiên của thành viên 12589 “thừa hưởng” VKO, sân bay đến cuối cùng của thành viên 2969. Mọi khoảng thời gian tính theo cách này đều vô nghĩa ở dòng đầu tiên của từng thành viên. Với PARTITION BY member_id, dòng đầu tiên của mỗi người nhận đúng NULL.

Thời gian giữa hai sự kiện

Lấy một timestamp trừ đi LAG của chính nó, bạn được khoảng thời gian kể từ sự kiện trước. Các chuyến bay đầu tiên của thành viên 12589, kèm số giờ kể từ lần cất cánh trước:

WITH trips AS (
  SELECT tm.member_id, f.scheduled_departure, f.departure_airport, f.arrival_airport
  FROM ticket_members tm
  JOIN ticket_flights tf ON tf.ticket_no = tm.ticket_no
  JOIN flights f ON f.flight_id = tf.flight_id
  WHERE tm.member_id = 12589
)
SELECT scheduled_departure, departure_airport AS dep, arrival_airport AS arr,
       LAG(arrival_airport) OVER (ORDER BY scheduled_departure) AS prev_arr,
       ROUND(EXTRACT(EPOCH FROM scheduled_departure
             - LAG(scheduled_departure) OVER (ORDER BY scheduled_departure)) / 3600, 1) AS hours_since_prev
FROM trips
ORDER BY scheduled_departure
LIMIT 6;

Timestamp trừ timestamp ra một INTERVAL; EXTRACT(EPOCH FROM ...) đổi nó thành số giây, dễ lấy trung bình và so sánh hơn. Với kiểu ngày, date - date trong PostgreSQL và DuckDB đã là số ngày nguyên. Xem thêm trong bài hàm xử lý ngày tháng trong SQL.

Chuyến bay thứ hai khởi hành từ đúng nơi chuyến đầu hạ cánh (ESL) sau 4,6 giờ: đó là một chuyến nối. Đưa quy tắc này vào bộ lọc và đếm số chuyến nối theo sân bay cho mọi thành viên. Thời gian chờ là khoảng từ giờ hạ cánh dự kiến của chuyến trước đến giờ cất cánh này, và hàm cửa sổ được tính trong CTE vì nó không thể nằm trong WHERE:

WITH trips AS (
  SELECT tm.member_id, f.scheduled_departure, f.scheduled_arrival,
         f.departure_airport, f.arrival_airport
  FROM ticket_members tm
  JOIN ticket_flights tf ON tf.ticket_no = tm.ticket_no
  JOIN flights f ON f.flight_id = tf.flight_id
), legs AS (
  SELECT member_id, departure_airport,
         LAG(arrival_airport) OVER w AS prev_arr,
         EXTRACT(EPOCH FROM scheduled_departure - LAG(scheduled_arrival) OVER w) / 60 AS layover_min
  FROM trips
  WINDOW w AS (PARTITION BY member_id ORDER BY scheduled_departure)
)
SELECT departure_airport AS hub, COUNT(*) AS connections,
       MIN(layover_min) AS min_layover, ROUND(AVG(layover_min)) AS avg_layover
FROM legs
WHERE prev_arr = departure_airport AND layover_min < 360
GROUP BY departure_airport
ORDER BY connections DESC
LIMIT 5;

WINDOW w AS (...) đặt tên cho một cửa sổ một lần rồi dùng lại trong nhiều hàm; PostgreSQL, DuckDB và MySQL 8 hỗ trợ, còn SQL Server có từ bản 2022.

Điều gì xảy ra tiếp theo: LEAD trên chuỗi sự kiện

LEAD trả lời câu hỏi “người dùng làm gì ngay sau X?”. Sự kiện kế tiếp sau select_fare trong ứng dụng, kèm thời gian trung vị đến sự kiện đó:

WITH e AS (
  SELECT client_id, event_time, event_name,
         LEAD(event_name) OVER w AS next_event,
         EXTRACT(EPOCH FROM LEAD(event_time) OVER w - event_time) AS secs_to_next
  FROM app_events
  WINDOW w AS (PARTITION BY client_id ORDER BY event_time, event_id)
)
SELECT COALESCE(next_event, '(none)') AS next_event,
       COUNT(*) AS n,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY secs_to_next) AS median_secs
FROM e
WHERE event_name = 'select_fare'
GROUP BY 1
ORDER BY n DESC;

86,4% người dùng đi tiếp sang bước thêm hành khách, thường trong vòng một phút. (none) nghĩa là client không làm gì thêm sau sự kiện đó trong dữ liệu. Còn select_fare theo sau bởi select_fare sau 0 giây là dấu hiệu đáng ngờ: đó là sự kiện bị gửi trùng, cần được loại bỏ trước (mẫu khử trùng có trong bài ROW_NUMBER, RANK và DENSE_RANK). Chú ý cột phá hòa event_id trong thứ tự của cửa sổ: nếu hai sự kiện xảy ra cùng một giây, “sự kiện kế tiếp” sẽ bị chọn tùy ý.

Phát hiện thay đổi và chia phiên

LAG dùng được với mọi kiểu dữ liệu, không chỉ số. So sánh hạng của một thành viên với hạng trước đó để thấy mọi lần lên và xuống hạng:

SELECT member_id, tier, valid_from,
       LAG(tier)        OVER (PARTITION BY member_id ORDER BY valid_from) AS prev_tier,
       LEAD(valid_from) OVER (PARTITION BY member_id ORDER BY valid_from) AS next_change
FROM loyalty_tier_history
WHERE member_id = 12589
ORDER BY valid_from;

Tổng hợp trên mọi thành viên, cùng phép LAG đó cho ra bảng chuyển hạng:

WITH h AS (
  SELECT member_id, tier, valid_from,
         LAG(tier) OVER (PARTITION BY member_id ORDER BY valid_from) AS prev_tier
  FROM loyalty_tier_history
)
SELECT prev_tier, tier, COUNT(*) AS moves
FROM h
WHERE prev_tier IS NOT NULL AND prev_tier <> tier
GROUP BY prev_tier, tier
ORDER BY moves DESC;

LEAD(valid_from) ở truy vấn trước là nền tảng của việc kiểm tra lịch sử: phiên bản kế tiếp phải bắt đầu đúng lúc phiên bản này kết thúc. Phép kiểm tra đó, cùng những chỗ chồng lấn nó tìm ra trong bảng này, có trong bài SCD type 2 (tiếng Anh). Cùng câu hỏi chuyển hạng cũng giải được bằng self join, và sự chênh lệch giữa hai cách cho thấy những liên kết hỏng trong dữ liệu.

Chia phiên (session). Quy tắc phổ biến: một phiên mới bắt đầu khi đã qua hơn 30 phút kể từ sự kiện trước đó của người dùng. Đánh dấu điều đó bằng LAG, rồi một phép SUM lũy kế trên các cờ sẽ đánh số các phiên:

WITH e AS (
  SELECT client_id, event_time,
         CASE WHEN event_time - LAG(event_time) OVER (PARTITION BY client_id ORDER BY event_time)
                   <= INTERVAL '30 minutes' THEN 0 ELSE 1 END AS new_session
  FROM app_events
  WHERE client_id = '00017a7b4ac7'
)
SELECT event_time, new_session,
       SUM(new_session) OVER (PARTITION BY client_id ORDER BY event_time
                              ROWS UNBOUNDED PRECEDING) AS session_no
FROM e
ORDER BY event_time
LIMIT 8;

Sự kiện đầu tiên có khoảng cách NULL, phép so sánh không ra true, và ELSE 1 mở phiên 1 một cách chính xác. Cùng ý tưởng “đánh cờ rồi cộng dồn” còn dùng để tìm chuỗi ngày liên tiếp và các khoảng trống giữa chúng; họ bài toán đó được trình bày trong bài gaps and islands trong SQL (tiếng Anh).

Các lỗi thường gặp

• Dùng LAG trong WHERE. WHERE LAG(status) OVER (...) = 'Cancelled' báo lỗi (“WHERE clause cannot contain window functions”). Hãy tính nó trong một CTE và lọc ở bên ngoài.
• Lọc trước khi tính cửa sổ. WHERE theo khoảng ngày sẽ loại bỏ chính những dòng mà LAG cần; dòng đầu tiên trong khoảng của bạn khi đó hiện NULL. Nếu cần giá trị trước của ngày đầu tiên, hãy tính LAG trên khoảng rộng hơn rồi lọc ở truy vấn bên ngoài.
• Quên PARTITION BY. Dòng đầu tiên của mỗi đối tượng lấy giá trị của đối tượng trước đó.
• ORDER BY không duy nhất. Nếu hai dòng có cùng timestamp, dòng nào là “dòng trước” không xác định. Hãy thêm cột phá hòa.
• Coi mỗi dòng là một kỳ. LAG(x, 7) là bảy dòng, không phải bảy ngày. Hãy lấp các ngày bị thiếu trước.
• Tưởng đâu cũng có IGNORE NULLS. Để lấy giá trị khác NULL gần nhất, Oracle và Snowflake chấp nhận LAG(x) IGNORE NULLS OVER (...), DuckDB chấp nhận LAG(x IGNORE NULLS) OVER (...), nhưng PostgreSQL không có IGNORE NULLS. Cách làm dùng được ở mọi nơi: COUNT(x) OVER (ORDER BY t) cho ra một số nhóm chỉ tăng ở các dòng khác NULL, rồi MAX(x) OVER (PARTITION BY that_group) điền giá trị xuôi xuống.

Câu hỏi thường gặp

LAG và LEAD trong SQL khác nhau thế nào?

LAG trả về giá trị từ một dòng đứng trước dòng hiện tại, LEAD từ một dòng đứng sau, theo thứ tự xác định bởi OVER (ORDER BY ...). LEAD(x) OVER (ORDER BY t) bằng LAG(x) OVER (ORDER BY t DESC).

Làm sao lấy giá trị của dòng trước trong SQL?

LAG(column) OVER (ORDER BY sort_column). Thêm PARTITION BY nếu các dòng thuộc về những người dùng, sản phẩm hay tài khoản khác nhau, để dòng đầu tiên của mỗi đối tượng nhận NULL thay vì giá trị của đối tượng khác.

Làm sao tính mức thay đổi so với tháng trước?

Tổng hợp về một dòng mỗi tháng, rồi dùng value - LAG(value) OVER (ORDER BY month) cho thay đổi tuyệt đối và 100.0 * (value - LAG(value) OVER (ORDER BY month)) / LAG(value) OVER (ORDER BY month) cho phần trăm. Loại bỏ các tháng chưa trọn.

Làm sao thay NULL trong LAG?

Truyền tham số thứ ba: LAG(value, 1, 0) trả về 0 khi không có dòng trước. Hoặc bọc lại: COALESCE(LAG(value) OVER (...), 0). Khác biệt: COALESCE còn thay cả NULL được lưu sẵn ở dòng trước, còn tham số mặc định thì không.

MySQL và SQL Server có hỗ trợ LAG và LEAD không?

Có. MySQL hỗ trợ từ bản 8.0, SQL Server từ bản 2012; PostgreSQL, DuckDB, Oracle, BigQuery và Snowflake đều hỗ trợ với cùng cú pháp. Nếu công ty bạn còn chạy MySQL 5.7, ở đó không có hàm cửa sổ, và dòng trước phải lấy bằng self join hoặc truy vấn con.

Ngôn ngữ khác: English, Español, Português (Brasil), 简体中文