Bài 33: Hàm cửa sổ: tính chung nhưng giữ từng dòng

Cô Mai muốn gửi mỗi học viên một dòng gồm số xu của bạn ấy và tổng xu của khối. Cô cũng cần xếp hạng sao cho hai bạn bằng xu đứng cùng hạng. Báo cáo gom nhóm chỉ giữ một dòng cho mỗi khối, nên không còn đủ tên từng người. Cô cần tính trên nhiều dòng mà vẫn giữ các dòng chi tiết.

Thêm tổng vào từng người bằng OVER

Hàm cửa sổ (window function) tính từ một nhóm dòng, rồi đặt kết quả cạnh từng dòng ban đầu. Từ khóa OVER cho biết hàm đang tính theo cách này. Trước hết, hãy cộng số xu của cả danh sách nhưng vẫn giữ tên từng người:

SELECT id, name, coins, SUM(coins) OVER () AS total
FROM students
ORDER BY id;

Cột total ở cả sáu dòng đều bằng 780. Mỗi dòng vẫn có tên và số xu riêng vì OVER () không gộp các dòng lại. Cặp ngoặc rỗng nghĩa là phép cộng dùng toàn bộ tập dòng đang xét. Tổng ấy được tính chung, không phải xu được cộng thêm vào tài khoản.

Đối chiếu với cách gom nhóm đã học, bạn có thể thấy rõ khác biệt. Câu sau chỉ cần một tổng cho mỗi khối:

SELECT grade, SUM(coins) AS grade_total
FROM students
GROUP BY grade
ORDER BY grade;

Kết quả có bốn dòng vì dữ liệu có bốn khối khác nhau. Tên từng học viên không còn là cột của báo cáo này. Nếu cần gửi thông tin riêng cho mỗi người, bạn phải giữ chi tiết thay vì chỉ giữ tổng. Hai cách tính phục vụ hai yêu cầu khác nhau, dù đều dùng hàm SUM.

Phân vùng (partition) là nhóm dòng được một phép tính cửa sổ xét chung. Viết PARTITION BY grade để chia các học viên theo khối trước khi cộng:

SELECT id, name, grade, coins,
  SUM(coins) OVER (PARTITION BY grade) AS grade_total
FROM students
ORDER BY id;

An có 120 xu, Dung có 60 xu, và cả hai cùng nhận grade_total = 180. Bình và Lan thuộc khối khác nên cùng nhận tổng 240. Cột tổng lặp lại là điều cần có, không phải dữ liệu bị nhân sai. Mỗi dòng vẫn đại diện cho một học viên như trước khi thêm phép tính.

Chọn quy tắc đồng hạng trước khi đánh số

Chi và Huy cùng có 180 xu. Nếu hai bạn phải đứng cùng hạng, chỉ sắp danh sách rồi đánh số liên tiếp là chưa đủ. SQL có ba hàm phục vụ ba quy tắc khác nhau. Cả ba đều đọc thứ tự ghi bên trong OVER.

• ROW_NUMBER() đánh số từng dòng liên tiếp, không chia sẻ số thứ tự.
• RANK() cho các dòng bằng nhau cùng hạng và bỏ qua số hạng tiếp theo.
• DENSE_RANK() cho các dòng bằng nhau cùng hạng nhưng không bỏ số hạng.

Hãy đặt hai cách đồng hạng cạnh nhau trên cùng dữ liệu:

SELECT id, name, coins,
  RANK() OVER (ORDER BY coins DESC) AS r,
  DENSE_RANK() OVER (ORDER BY coins DESC) AS d
FROM students
ORDER BY coins DESC, id;

Chi và Huy đều có r = 1 và d = 1. Lan đứng tiếp theo, với r = 3 nhưng d = 2. RANK() tính rằng đã có hai người đứng trước Lan. DENSE_RANK() chỉ đếm các mức xu khác nhau, nên 150 là mức thứ hai.

Bạn cần tách tiêu chí chia hạng khỏi cách đặt các dòng hòa lên màn hình. Nếu đưa mã học viên vào tiêu chí chia hạng, kết quả sẽ thay đổi:

SELECT id, name, coins,
  DENSE_RANK() OVER (ORDER BY coins DESC, id) AS d
FROM students
ORDER BY coins DESC, id;

Chi có d = 1, còn Huy có d = 2. Hai cặp gồm xu và mã khác nhau, nên hàm coi chúng là hai mức riêng. Muốn đồng hạng theo xu, hãy giữ mỗi coins DESC bên trong hàm. Mã id chỉ đứng trong ORDER BY ngoài cùng để sắp hai dòng hòa.

Với ROW_NUMBER(), yêu cầu lại khác: mỗi dòng phải nhận một số riêng. Khi bằng xu, cần một cột duy nhất để xác định ai đứng trước. Câu dưới đây chọn mã nhỏ hơn đứng trước:

SELECT id, name, coins,
  ROW_NUMBER() OVER (ORDER BY coins DESC, id) AS position
FROM students
ORDER BY id;

Chi nhận position = 1, Huy nhận position = 2, nhưng kết quả hiển thị theo mã học viên. Dòng của An vẫn xuất hiện đầu tiên với position = 4. Điều đó cho thấy thứ tự tính và thứ tự hiển thị là hai việc riêng. Bạn cần viết cả hai nếu cả hai đều có ý nghĩa với báo cáo.

Cộng lũy kế theo đúng thứ tự sự kiện

Tổng lũy kế (running total) cộng từ dòng đầu đến dòng đang đọc. Khung cửa sổ (window frame) xác định những dòng nào tham gia phép cộng tại mỗi vị trí. Với lịch sử xu, ta cần một khung kết thúc ở sự kiện hiện tại.

Bảng ledger lưu từng biến động, không lưu số dư sau mỗi lần thay đổi. Dùng PARTITION BY student_id để mỗi người có một phép cộng riêng. Dùng mã sự kiện id để xác định thứ tự:

SELECT id, student_id, delta,
  SUM(delta) OVER (
    PARTITION BY student_id
    ORDER BY id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_change
FROM ledger
ORDER BY student_id, id;

Hai dòng của An lần lượt có running_change là 20 và 10. Dòng của Bình có giá trị 15, vì phép cộng của Bình bắt đầu riêng. UNBOUNDED PRECEDING chỉ đầu phân vùng, còn CURRENT ROW chỉ dòng hiện tại. ROWS yêu cầu xét các vị trí dòng trong thứ tự đã chọn.

Đừng gọi running_change là số xu đang có. Lịch sử mẫu chỉ ghi một phần biến động, không chứa số dư ban đầu. Cột lũy kế trả lời câu hỏi “đã thay đổi ròng bao nhiêu trong các dòng đang xét”. Muốn suy ra số dư, bạn còn cần mốc ban đầu phù hợp.

Khi các dòng có cùng giá trị sắp xếp, khung mặc định có thể khác điều bạn mong đợi. So sánh hai phép cộng sau, cùng sắp theo xu:

SELECT id, name, coins,
  SUM(coins) OVER (ORDER BY coins DESC) AS by_value,
  SUM(coins) OVER (
    ORDER BY coins DESC, id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS by_row
FROM students
ORDER BY coins DESC, id;

Ở dòng Chi, by_value đã bằng 360, còn by_row mới bằng 180. Khung mặc định của phép cộng có thứ tự bao gồm cả những dòng đồng mức với dòng hiện tại. Viết khung ROWS cùng cột phá hòa giúp bạn diễn đạt việc cộng từng người một. Khi tính toàn phân vùng, bạn có thể bỏ thứ tự trong OVER như ở mục đầu.

Tính xong ở bên trong rồi mới lọc bên ngoài

Cô Mai chỉ muốn xem hai vị trí đầu của danh sách xu. Bạn có thể nghĩ đến việc tạo cột số thứ tự rồi lọc nó ngay trong WHERE. Nhưng ở cùng một cấp truy vấn, WHERE lọc trước bước tính hàm cửa sổ. Vì thế cột kết quả ấy chưa dùng được tại chỗ lọc.

Nếu lọc cột cửa sổ ngay trong câu lệnh đang tạo nó, SQLite báo lỗi:

SELECT id, name,
  ROW_NUMBER() OVER (ORDER BY coins DESC, id) AS position
FROM students
WHERE position <= 2;

Lỗi không có nghĩa ROW_NUMBER() không hỗ trợ xếp thứ tự. Vấn đề nằm ở chỗ sử dụng kết quả quá sớm. CTE của bài 31 giúp tách bước đánh số khỏi bước chọn dòng. Đặt phép tính vào bên trong, rồi lọc ở truy vấn đọc kết quả ấy:

WITH positioned AS (
  SELECT id, name, coins,
    ROW_NUMBER() OVER (ORDER BY coins DESC, id) AS position
  FROM students
)
SELECT id, name, coins, position
FROM positioned
WHERE position <= 2
ORDER BY position;

Kết quả có Chi và Huy, với hai số thứ tự riêng. CTE không tạo thêm bảng vĩnh viễn, mà cung cấp kết quả trung gian cho câu lệnh này. Nếu cần chọn hai mức xu cao nhất, hãy lọc hạng thay vì vị trí:

WITH ranked AS (
  SELECT id, name, coins,
    DENSE_RANK() OVER (ORDER BY coins DESC) AS xu_rank
  FROM students
)
SELECT id, name, coins, xu_rank
FROM ranked
WHERE xu_rank <= 2
ORDER BY xu_rank, id;

Chi và Huy cùng hạng 1, còn Lan có hạng 2, nên cả ba được giữ. Câu dùng ROW_NUMBER() phía trên chỉ giữ hai người. Vì vậy “hai người” và “hai hạng” cần hai yêu cầu khác nhau.

Vị trí lọc còn ảnh hưởng tập dòng dùng để tính. Nếu chỉ giữ một khối trước cửa sổ, hạng sẽ tính trong khối đó. Nếu xếp toàn danh sách rồi mới giữ một khối, hạng vẫn là hạng toàn danh sách. Hãy đọc câu hỏi để chọn đúng phạm vi, thay vì chỉ sửa cho hết lỗi cú pháp.

Bạn cũng có thể chọn đúng một dòng cho mỗi nhóm. Cô Mai cần lần chấm gần nhất của mỗi học viên có điểm. Hãy đánh số riêng trong từng người, từ ngày mới nhất và mã lớn nhất, rồi giữ số 1:

WITH latest AS (
  SELECT student_id, id, score, day,
    ROW_NUMBER() OVER (
      PARTITION BY student_id ORDER BY day DESC, id DESC
    ) AS rn
  FROM scores
)
SELECT student_id, id AS score_id, score, day
FROM latest
WHERE rn = 1
ORDER BY student_id;

Kết quả có năm dòng cho năm học viên có điểm. An (mã 1) giữ lần chấm mã 2, còn Chi (mã 3) giữ lần chấm mã 5. Hai lần này đều thuộc ngày 2026-09-02, muộn hơn lần trước của mỗi bạn.

Nếu một người có nhiều lần cùng ngày, mã lớn hơn đứng trước. Lan chưa có điểm nên không xuất hiện.

Lỗi thường gặp

Tóm tắt

• Hàm cửa sổ thêm kết quả tính chung mà vẫn giữ dòng chi tiết.
• PARTITION BY chia phạm vi tính, không gộp các dòng thành báo cáo nhóm.
• RANK() bỏ số hạng sau hòa, còn DENSE_RANK() giữ các số hạng liên tiếp.
• ROW_NUMBER() cần thứ tự phá hòa rõ nếu mỗi vị trí phải ổn định.
• Khung ROWS diễn đạt cộng theo vị trí dòng, còn truy vấn ngoài lọc kết quả đã tính.

Kiểm tra nhanh

Đọc thêm

• SQLite: hàm cửa sổ và các kiểu khung.
• PostgreSQL: hướng dẫn nhập môn hàm cửa sổ.