Bạn có bảng lượt xử lý theo ngày và muốn hiển thị chênh lệch trên trang vận hành. Một thiết bị có dữ liệu thứ Hai và thứ Tư, nhưng thiếu thứ Ba. Nếu dùng LAG rồi đặt nhãn là “so với hôm qua”, người xem có thể đọc nhầm một thay đổi qua hai ngày thành thay đổi trong một ngày.
Xác định thứ tự của phép so sánh
LAG lấy giá trị từ dòng đứng trước theo thứ tự trong cửa sổ. PARTITION BY giúp mỗi thiết bị có dòng trước riêng. ORDER BY bên trong OVER xác định thứ tự cho phép tính, còn ORDER BY cuối SELECT phục vụ việc trình bày kết quả. Hai việc này cần được viết riêng nếu muốn cả kết quả tính và thứ tự hiển thị rõ ràng.
Bài dùng SQLite 3.54.0 và dữ liệu giả lập. Giả định mỗi thiết bị có tối đa một dòng mỗi ngày, ngày ở dạng ISO cố định. Nếu bảng thật có nhiều dòng trong ngày, hãy tổng hợp về đúng cấp hoặc thêm một khóa phân xử thứ tự trước khi chọn ý nghĩa của dòng trước.
Tính cả ngày và giá trị trước đó
WITH readings(device, day, amount) AS (
VALUES ('A', '2026-11-01', 10),
('A', '2026-11-03', 16),
('A', '2026-11-04', 19),
('B', '2026-11-03', 30)
), ordered AS (
SELECT device, day, amount,
LAG(day) OVER w AS previous_day,
LAG(amount) OVER w AS previous_amount
FROM readings
WINDOW w AS (PARTITION BY device ORDER BY day)
)
SELECT device, day, previous_day,
amount - previous_amount AS delta_from_previous,
CASE WHEN previous_day = date(day, '-1 day')
THEN amount - previous_amount END AS daily_delta
FROM ordered
ORDER BY device, day;Thiết bị A ngày 03 có dòng trước là ngày 01, nên chênh lệch so lần ghi trước bằng 6. Cột daily_delta là NULL vì không có dòng của ngày 02 đứng ngay trước. Ngày 04 có đủ hai ngày liên tiếp nên cả hai cột đều bằng 3. Thiết bị B chỉ có một dòng, không mượn số liệu của A để tính.
Chọn cách xử lý ngày thiếu
Nếu người xem thực sự muốn biết thay đổi so với lần ghi gần nhất, hãy đặt tên cột theo ý đó và hiển thị cả ngày của lần ghi trước. Nếu muốn chênh lệch theo lịch, có thể để trống khi thiếu dữ liệu, như CASE ở trên. Một lựa chọn khác là tạo lịch đầy đủ rồi nối số liệu vào; lúc đó cần quyết định ngày thiếu là chưa có dữ liệu hay có số lượng thực bằng 0.
Không biến NULL thành 0 chỉ để biểu đồ liền mạch. Một ngày không nhận được bản ghi có thể cần điều tra đường truyền, trong khi số 0 hợp lệ thể hiện một hoạt động đã được đo. Chọn cách thể hiện hai trạng thái trước khi tổng hợp lên dashboard.
Kiểm tra và giới hạn
Đọc kết quả theo từng thiết bị, kiểm tra dòng đầu và một cặp ngày liên tiếp. Thêm trường hợp ngày trống ở giữa. Nếu muốn chỉ hiển thị một khoảng ngắn, hãy cân nhắc tính LAG trước khi lọc khoảng hiển thị: lọc quá sớm có thể bỏ mất dòng dùng làm mốc cho ngày đầu của báo cáo.
Fixture chỉ chạy truy vấn đọc trong SQLite, không đo hiệu năng hoặc kiểm tra dữ liệu theo múi giờ. Với hệ khác, kiểm tra cú pháp trừ ngày và chính sách chuyển timestamp thành ngày trước khi chuyển truy vấn. Đừng dựa vào thứ tự chèn dữ liệu để suy ra dòng trước.
Kết luận
LAG giúp so với dòng trước trong một thứ tự đã khai báo. Hãy mang theo ngày của dòng đó để phân biệt “lần ghi trước” với “hôm qua” và giữ ý nghĩa của dữ liệu thiếu.
