Mở đầu — vì sao bài này quan trọng
Nếu bạn đã đi qua các bài về ROW_NUMBER, RANK, LAG, LEAD và running total, bạn có thể nghĩ rằng mình đã "biết window function". Nhưng thực tế, phần lớn lỗi báo cáo sai số liệu mà tôi từng chứng kiến trong các đội BA đều đến từ một chỗ duy nhất: frame clause — cái mệnh đề mà hầu hết mọi người bỏ qua hoặc dùng theo bản năng mà không hiểu.
Frame clause là phần định nghĩa "chính xác những dòng nào được đưa vào tính toán" cho mỗi dòng trong cửa sổ (window). Khi bạn viết SUM(...) OVER (ORDER BY ngay), bạn nghĩ mình đang tính tổng theo thứ tự thời gian. Nhưng thực ra SQL đã âm thầm áp một frame mặc định mà bạn không khai báo — và frame mặc định đó có một cái bẫy khiến running total của bạn bị sai khi có ngày trùng nhau.
Bài này tập trung hoàn toàn vào frame và các trường hợp biên (edge cases). Đây là kiến thức phân biệt một BA "dùng được window function" với một BA "thật sự kiểm soát được con số mình báo cáo". Khi sếp hỏi "tại sao doanh thu lũy kế tháng này lệch so với báo cáo tài chính?", người hiểu frame sẽ biết ngay nên nhìn vào đâu.
Khái niệm cốt lõi
Cấu trúc đầy đủ của OVER
Một window function đầy đủ có ba thành phần trong OVER(...):
SUM(doanh_thu) OVER (
PARTITION BY khu_vuc -- chia dữ liệu thành các nhóm độc lập
ORDER BY ngay -- sắp xếp trong mỗi nhóm
ROWS BETWEEN ... AND ... -- frame clause: chọn dòng nào tham gia tính
)
PARTITION BY và ORDER BY bạn đã quen. Hôm nay ta mổ xẻ dòng thứ ba — frame clause.
Frame được tạo bởi ba yếu tố
Một frame được mô tả bằng: chế độ frame (ROWS, RANGE, hoặc GROUPS), điểm bắt đầu và điểm kết thúc.
Các mốc biên thường gặp:
UNBOUNDED PRECEDING— từ dòng đầu tiên của partition.n PRECEDING— lùi lạindòng (hoặcngiá trị, tuỳ chế độ).CURRENT ROW— dòng hiện tại.n FOLLOWING— tiến tớindòng phía sau.UNBOUNDED FOLLOWING— đến dòng cuối cùng của partition.
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW nghĩa là "lấy dòng hiện tại cộng 2 dòng ngay trước nó" — đúng 3 dòng. Đây là nền tảng để tính trung bình trượt (moving average).Khác biệt sống còn: ROWS vs RANGE
Đây là phần quan trọng nhất của cả bài, hãy đọc kỹ.
ROWSđếm theo số dòng vật lý.2 PRECEDINGnghĩa là đúng 2 dòng phía trên, bất kể giá trị của chúng.RANGEđếm theo giá trị logic của cột trongORDER BY. Mọi dòng có cùng giá trị với dòng hiện tại trong cột sắp xếp đều được coi là "ngang hàng" (peers) và được gộp chung.
RANGE, khi tính tới dòng đầu tiên của ngày 15/06, SQL đã gộp luôn cả 3 đơn của ngày đó vào tổng. Với ROWS, mỗi đơn được cộng dồn lần lượt từng dòng một.Cái bẫy của frame mặc định
Đây là điều khiến nhiều BA "dính chưởng". Khi bạn viết ORDER BY trong OVER nhưng KHÔNG khai báo frame clause, SQL tự động áp dụng:
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
Chú ý từ RANGE chứ không phải ROWS. Nghĩa là running total mặc định của bạn sẽ gộp tất cả các dòng có cùng giá trị sắp xếp. Nếu bạn tính doanh thu lũy kế mà ORDER BY ngay và một ngày có nhiều đơn, thì tại mọi dòng của cùng một ngày, running total sẽ hiển thị cùng một con số (tổng cộng dồn đến hết ngày đó), chứ không tăng dần từng đơn.
Nếu bạn muốn running total tăng đúng từng dòng, bạn phải viết rõ:
SUM(doanh_thu) OVER (ORDER BY ngay ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
Một câu thần chú để nhớ: "ORDER BY mà không có frame thì kết quả tính theo RANGE — hãy luôn tự hỏi mình có muốn vậy không."
Tình huống thực tế
Tình huống 1 — Tiki: running total bị "đứng yên" trong cùng một ngày
Một BA tại Tiki được giao dựng báo cáo doanh thu lũy kế (cumulative revenue) theo từng đơn hàng để kiểm tra thời điểm vượt mốc 1 tỷ đồng trong ngày khuyến mãi 6/6. Bảng don_hang có nhiều đơn cùng một ngày ngay_dat.
Bạn ấy viết:
SELECT
ma_don,
ngay_dat,
gia_tri,
SUM(gia_tri) OVER (ORDER BY ngay_dat) AS luy_ke
FROM don_hang;
Kết quả khiến cả nhóm bối rối: nhiều đơn hàng trong cùng ngày 6/6 đều hiển thị luy_ke giống hệt nhau, ví dụ tất cả đều bằng 1.250.000.000. Báo cáo trông như doanh thu "nhảy bậc thang" theo ngày chứ không tăng đều theo từng đơn.
Diễn giải: Vì không có frame clause, SQL áp RANGE ... CURRENT ROW. Tất cả đơn cùng ngày 6/6 là peers nên được gộp chung, mỗi dòng đều hiển thị tổng đến hết ngày đó. Đây không phải bug — đây là frame mặc định hoạt động đúng như định nghĩa, chỉ là không đúng ý người viết.
Cách sửa là chuyển sang ROWS và thêm một tiêu chí phụ để thứ tự ổn định:
SELECT
ma_don, ngay_dat, gia_tri,
SUM(gia_tri) OVER (
ORDER BY ngay_dat, ma_don
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS luy_ke
FROM don_hang;
Bài học: Khi làm running total trên dữ liệu có khoá sắp xếp bị trùng (ngày, tháng, hạng), luôn dùng ROWS và bổ sung một cột phân định thứ tự (như ma_don) để con số tăng đều và tái lập được.
Tình huống 2 — Grab: trung bình trượt 7 ngày và cái bẫy thiếu dữ liệu đầu kỳ
Đội phân tích vận hành của Grab muốn theo dõi số chuyến xe trung bình trượt 7 ngày (7-day moving average) ở khu vực TP.HCM để làm mượt biến động cuối tuần. Họ viết:
SELECT
ngay,
so_chuyen,
AVG(so_chuyen) OVER (
ORDER BY ngay
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS tb_7_ngay
FROM thong_ke_chuyen_ngay;
Logic hoàn toàn đúng cho phần lớn dữ liệu. Nhưng có một edge case ở đầu chuỗi: ngày đầu tiên chỉ có 1 dòng để tính, ngày thứ hai có 2 dòng, v.v. Vì vậy 6 ngày đầu tiên không phải là trung bình của đủ 7 ngày — chúng là trung bình của số dòng ít hơn. Một bạn trong nhóm vội kết luận "tuần đầu tháng số chuyến cao bất thường", trong khi thực ra chỉ là mẫu số nhỏ.
Để xử lý đúng, họ thêm COUNT để biết mỗi điểm được tính trên bao nhiêu ngày, rồi chỉ tin tưởng các điểm đủ 7 ngày:
SELECT
ngay, so_chuyen,
AVG(so_chuyen) OVER (ORDER BY ngay ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS tb_7_ngay,
COUNT(*) OVER (ORDER BY ngay ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS so_ngay_co
FROM thong_ke_chuyen_ngay;
Sau đó trong báo cáo, họ lọc hoặc đánh dấu các dòng có so_ngay_co < 7 là "chưa đủ cửa sổ".
Bài học: Frame kiểu sliding (n PRECEDING) luôn bị "thiếu chân" ở đầu và cuối chuỗi. Đừng giả định mọi điểm đều có đủ cửa sổ. Hãy luôn đếm số dòng thực tế trong frame để biết điểm nào đáng tin. Lưu ý thêm: với dữ liệu chuỗi thời gian, ROWS 6 PRECEDING chỉ đúng khi không có ngày bị thiếu — nếu có ngày không phát sinh chuyến, "6 dòng trước" sẽ không tương ứng "6 ngày trước".
Tình huống 3 — MoMo: so sánh với tổng nhóm bằng UNBOUNDED hai đầu
BA tại MoMo cần báo cáo: với mỗi giao dịch nạp tiền trong một ví, tính tỷ trọng giá trị giao dịch đó so với tổng toàn bộ giao dịch của ví trong tháng. Họ cần frame bao trùm toàn partition, không phụ thuộc thứ tự dòng:
SELECT
vi_id, giao_dich_id, gia_tri,
gia_tri * 100.0 / SUM(gia_tri) OVER (
PARTITION BY vi_id
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS ty_trong_phan_tram
FROM giao_dich_thang;
Ở đây không có ORDER BY vì ta muốn tổng của cả nhóm, và frame UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING đảm bảo mọi dòng trong partition đều thấy đúng tổng đầy đủ. Thực ra khi không có ORDER BY, frame mặc định đã là toàn partition — nhưng viết rõ giúp người đọc code sau này không phải đoán.
Bài học: Khi cần "so với tổng nhóm" (phần trăm đóng góp), dùng frame UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING hoặc đơn giản là bỏ ORDER BY. Đừng vô tình để lại ORDER BY thừa, vì nó sẽ biến tổng-cả-nhóm thành tổng-lũy-kế và làm sai tỷ trọng.
Hướng dẫn từng bước
Khi viết bất kỳ window function nào có tính cộng dồn/trượt, hãy đi theo trình tự sau:
- Xác định mục tiêu của con số. Bạn muốn running total (cộng dồn từ đầu), moving average (cửa sổ trượt), hay tỷ trọng so với tổng nhóm? Mỗi mục tiêu cần một frame khác nhau.
- Chọn PARTITION BY. Mỗi nhóm có cần tính độc lập không? Ví dụ mỗi khu vực, mỗi ví, mỗi sản phẩm.
- Chọn ORDER BY và kiểm tra trùng lặp. Hỏi ngay: "cột sắp xếp này có giá trị trùng không?" Nếu có (ngày, tháng), bạn gần như luôn cần
ROWSvà một cột phụ để phá vỡ thế trùng (tie-breaker).
- Viết frame clause một cách tường minh. Đừng dựa vào mặc định. Ba mẫu thường dùng:
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
- Moving window n kỳ: ROWS BETWEEN n-1 PRECEDING AND CURRENT ROW
- Tỷ trọng nhóm: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING- Kiểm tra edge case đầu/cuối chuỗi. Thêm một cột
COUNT(*) OVER (cùng frame)để xác nhận số dòng thực sự tham gia. So sánh con số đầu kỳ với kỳ vọng.
- Đối chiếu với tổng kiểm tra (sanity check). Dòng cuối cùng của running total phải bằng
SUMtoàn bộ. Nếu không khớp, frame của bạn đang sai.
Lỗi thường gặp & mẹo
- Quên rằng mặc định là RANGE, không phải ROWS. Đây là lỗi số một. Running total trên cột có giá trị trùng sẽ bị "đứng yên" trong nhóm peers. Mẹo: luôn viết frame tường minh khi có
ORDER BY.
- Dùng RANGE với khoảng số tuỳ ý nhưng quên rằng nó bám theo giá trị, không theo dòng.
RANGE BETWEEN 7 PRECEDING AND CURRENT ROWchỉ hoạt động khi cộtORDER BYlà kiểu số hoặc ngày mà engine hỗ trợ, và nó nghĩa là "mọi dòng có giá trị nằm trong khoảng 7 đơn vị", không phải "7 dòng". Hai cái cho kết quả rất khác nhau.
- Thiếu tie-breaker khiến kết quả không tái lập. Nếu
ORDER BY ngaycó nhiều dòng cùng ngày và bạn dùngROWS, thứ tự giữa các dòng cùng ngày là không xác định — chạy lại có thể ra số khác. Luôn thêm cột định danh:ORDER BY ngay, id.
- Nhầm chuỗi thời gian có lỗ hổng.
ROWS n PRECEDINGđếm dòng, không đếm thời gian. Nếu dữ liệu thiếu ngày, hãy điền đủ ngày (dùng bảng lịch / calendar table) trước khi tính moving average theo ngày.
- Để lại ORDER BY thừa khi muốn tổng nhóm. Như tình huống MoMo: có
ORDER BYlà tự động thành lũy kế. Bỏ nó đi nếu bạn muốn tổng đầy đủ.
- Mẹo gỡ rối nhanh: khi nghi ngờ frame sai, thêm tạm hai cột
COUNT(*) OVER(...)vàMIN(khoa) OVER(...),MAX(khoa) OVER(...)với cùng frame để "nhìn thấy" frame đang gồm những dòng nào. Đây là kỹ thuật debug window function hiệu quả nhất.
- Lưu ý phương ngữ: PostgreSQL hỗ trợ đầy đủ
ROWS,RANGEvà cảGROUPS, cùngEXCLUDE(loại trừ current row hoặc peers khỏi frame). MySQL (từ 8.0) hỗ trợROWSvàRANGEnhưng không cóGROUPS/EXCLUDE. Khi viết query dùng chung hai môi trường, hãy bám vàoROWScho an toàn và dễ đoán nhất.
Bài tập thực hành
Giả sử bạn có bảng doanh_thu_ngay(khu_vuc, ngay, doanh_thu) mô phỏng dữ liệu một sàn TMĐT.
- Running total đúng. Viết query tính doanh thu lũy kế theo từng khu vực, đảm bảo con số tăng đúng kể cả khi có nhiều dòng cùng ngày. Khai báo frame tường minh.
- So sánh RANGE vs ROWS. Với cùng dữ liệu, viết hai cột lũy kế cạnh nhau: một dùng
RANGE(mặc định) và một dùngROWS, rồi giải thích vì sao chúng khác nhau ở những ngày có giá trị trùng.
- Moving average 7 ngày có kiểm soát. Tính trung bình trượt 7 ngày cho mỗi khu vực, kèm cột
so_ngay_co = COUNT(*) OVER(...). Đánh dấu các dòng chưa đủ 7 ngày là "chưa đáng tin".
- Tỷ trọng nhóm. Với mỗi dòng, tính phần trăm doanh thu của ngày đó so với tổng cả tháng của khu vực, dùng frame phủ toàn partition.
- Edge case nâng cao (PostgreSQL). Thử frame
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWINGđể tính trung bình của dòng hiện tại với hàng xóm hai bên, và quan sát kết quả ở dòng đầu và dòng cuối partition.
SUM toàn bộ khu vực đó.Tóm tắt
Frame clause là phần "ẩn" nhưng quyết định độ chính xác của mọi window function mang tính cộng dồn hoặc trượt. Hãy ghi nhớ ba điều cốt lõi:
- Mặc định khi có ORDER BY là
RANGE ... CURRENT ROW, nó gộp mọi dòng cùng giá trị — đây là nguyên nhân số một khiến running total bị sai. Hãy luôn viết frame tường minh, và dùngROWSkhi muốn cộng dồn từng dòng. ROWSđếm dòng vật lý,RANGEđếm giá trị logic. Hiểu khác biệt này giúp bạn chọn đúng công cụ cho từng bài toán.- Edge case đầu/cuối chuỗi luôn thiếu cửa sổ đầy đủ. Hãy đếm số dòng thực tế trong frame, thêm tie-breaker để kết quả tái lập, và đối chiếu với tổng kiểm tra trước khi báo cáo.