Window Functions ③ — Xếp hạng, hàm phân tích và thứ tự thực thi

Xếp hạng — ROW_NUMBER / RANK / DENSE_RANK

Các hàm xếp hạng chuyên biệt là ROW_NUMBER() / RANK() / DENSE_RANK().

Cả ba đều đánh số các dòng theo thứ tự cho bởi OVER (ORDER BY ...), nhưng xử lý dòng trùng giá trị khác nhau.

Đoạn SQL bên dưới — và sơ đồ minh họa kết quả — đi qua một trường hợp cụ thể có tổng trùng nhau theo từng dòng.

-- Tính tổng doanh số của mỗi nhân viên, rồi xếp hạng giảm dần theo tổng bằng 3 cách
SELECT emp_id,
  SUM(amount) AS total,
  ROW_NUMBER() OVER (ORDER BY SUM(amount) DESC) AS rn,
  RANK()       OVER (ORDER BY SUM(amount) DESC) AS rk,
  DENSE_RANK() OVER (ORDER BY SUM(amount) DESC) AS dr
FROM sales
GROUP BY emp_id
ORDER BY total DESC;
-- Có thể tổng hợp trước bằng GROUP BY rồi xếp hạng các giá trị tổng hợp đó

Hãy hình dung bạn muốn một bảng xếp hạng nhân viên theo tổng doanh số. Có trường hợp trùng, nên bạn muốn thấy cả ba cạnh nhau: đánh số liên tục, bỏ qua hạng kế, và không bỏ qua. (Nếu truy vấn của bạn chạy đúng, phần giải thích sẽ xuất hiện.)

① GROUP BY bảng sales theo emp_id và select SUM(amount) với alias total.

② Trên window sắp xếp theo total giảm dần, thêm ROW_NUMBER() alias rn, RANK() alias rk, và DENSE_RANK() alias dr.

③ Bộ chấm điểm kiểm tra thứ tự dòng, nên hãy kết thúc với ORDER BY total DESC, emp_id (giảm dần theo total, tăng dần theo emp_id khi trùng).

LAG / LEAD — lấy giá trị từ dòng trước / kế

LAG(column) kéo giá trị từ dòng trước lên dòng hiện tại, và LEAD(column) kéo từ dòng kế (lag = tụt lại, lead = đi trước).

OVER (PARTITION BY ... ORDER BY ...) quyết định "trước" và "kế" nghĩa là gì bằng cách đặt thứ tự.

Dùng chúng cho chênh lệch giữa các kỳ (so với giao dịch trước), delta so với tháng trước, hoặc so với sự kiện tiếp theo đã lên lịch.

LAG(amount) trả về NULL ở dòng đầu của mỗi phân vùng (không có dòng trước); tương tự LEAD là NULL ở dòng cuối.

Tham số thứ hai tùy chọn điều chỉnh độ lệch ("lùi bao nhiêu dòng") và tham số thứ ba đặt giá trị mặc định khi dòng đích không tồn tại — ví dụ, LAG(amount, 1, 0).

Trừ với amount - LAG(amount) OVER (...) để có chênh lệch so với dòng trước ngay lập tức.

-- Hiển thị giao dịch trước (LAG) và chênh lệch so với giao dịch trước
SELECT emp_id, sale_date, amount,
  LAG(amount) OVER (PARTITION BY emp_id ORDER BY sale_date) AS prev_amount,
  amount - LAG(amount) OVER (PARTITION BY emp_id ORDER BY sale_date) AS diff_prev
FROM sales
ORDER BY emp_id, sale_date;

-- Dùng LEAD nếu bạn muốn hiển thị giao dịch kế bên cạnh dòng hiện tại
SELECT emp_id, sale_date, amount,
  LEAD(amount) OVER (PARTITION BY emp_id ORDER BY sale_date) AS next_amount
FROM sales
ORDER BY emp_id, sale_date;

Hãy hình dung bạn muốn danh sách giao dịch của từng nhân viên sắp theo ngày, với giao dịch trước và chênh lệch so với giao dịch trước đó kế bên mỗi dòng.

① Select emp_id, sale_date, và amount từ sales.

② Trên window phân vùng theo từng nhân viên và sắp theo sale_date, thêm amount của dòng trước với alias prev_amount.

③ Cũng thêm amount trừ amount trước với alias diff_prev.

④ Bộ chấm điểm kiểm tra thứ tự dòng, nên hãy kết thúc với ORDER BY emp_id, sale_date.

NTILE — chia các dòng đã sắp xếp thành n nhóm đều nhau

NTILE(n) chia các dòng đã sắp theo OVER (ORDER BY ...) thành n nhóm đều nhau nhất có thể, gán cho mỗi dòng một số nhóm từ 1 đến n.

Dùng cho tứ phân vị, ngũ phân vị, "top 25%", và các kiểu chia nhóm tương tự.

Khi số dòng không chia hết cho n, các nhóm sớm hơn nhận thêm một dòng mỗi nhóm (ví dụ, chia 5 dòng thành 4 nhóm được 2 / 1 / 1 / 1).

Kết hợp với PARTITION BY để làm những việc như "chia doanh số của mỗi nhân viên thành nhóm trên / dưới trong nội bộ nhân viên đó."

Sơ đồ bên dưới cho thấy điều gì xảy ra khi 5 giao dịch của emp_id=11 được sắp theo amount desc và chia bằng NTILE(4).

-- Theo từng nhân viên, chia doanh số của nhân viên đó thành 4 nhóm theo amount desc
SELECT emp_id, sale_date, amount,
  NTILE(4) OVER (PARTITION BY emp_id ORDER BY amount DESC) AS quartile
FROM sales
ORDER BY emp_id, amount DESC;

-- Chia toàn bộ doanh số thành 4 nhóm theo amount (top 25% có quartile = 1)
SELECT sale_id, emp_id, amount,
  NTILE(4) OVER (ORDER BY amount DESC) AS quartile
FROM sales
ORDER BY amount DESC;

Hãy hình dung bạn muốn danh sách giao dịch chia thành 4 nhóm theo từng nhân viên (theo amount desc), hiển thị theo thứ tự tứ phân vị từ nhóm trên xuống.

① Select emp_id, sale_date, và amount từ sales.

② Trên window phân vùng theo từng nhân viên và sắp theo amount desc, tính NTILE(4) và thêm làm cột thứ 4 với alias quartile.

③ Bộ chấm điểm kiểm tra thứ tự dòng, nên hãy kết thúc với `ORDER BY quartile, emp_id, sale_id` (ORDER BY có thể tham chiếu đến alias quartile tính trong SELECT).

Hãy hình dung bạn muốn lọc xuống còn top 25% (quartile = 1). Thử thêm WHERE quartile = 1 vào truy vấn của Bài tập 3.

① Dùng cùng SELECT của Bài tập 3 và thêm WHERE quartile = 1 sau FROM sales.

② Chạy nó trả về lỗi (lỗi nghĩa là bạn đã làm đúng).

③ Phần tiếp theo, "thứ tự thực thi SQL", sẽ giải thích vì sao. quartile làm việc trong ORDER BY nhưng không làm việc trong WHERE — hãy giữ câu đố này trong đầu khi chúng ta đi tiếp.

Thứ tự thực thi SQL — vì sao không đặt được window function trong WHERE

Bạn có thể nghĩ, "chẳng phải tôi chỉ cần lọc trên rank đã tính, như WHERE rk = 1?"

Bạn sẽ nhận được lỗi. Lý do là thứ tự thực thi logic của SQL.

Thứ tự bạn viết các mệnh đề (SELECT → FROM → WHERE ...) không phải là thứ tự chúng được đánh giá.

Đánh giá đại khái đi: FROM / JOIN (lắp ráp bảng) → WHERE (lọc dòng) → GROUP BY (gộp nhóm) → HAVING (lọc nhóm) → SELECT (tính các cột — đây là nơi window function chạy) → ORDER BY (sắp xếp) → LIMIT (cắt).

Vì WHERE chạy trước SELECT, nó không thể thấy rank mà SELECT sắp tính.

Để lọc theo rank, đẩy truy vấn tính window function vào truy vấn con (hoặc CTE) và áp WHERE ở bên ngoài.

SELECT bên trong hoàn tất tính window function, và truy vấn bên ngoài có thể coi kết quả đó như một cột bình thường.

-- Đoạn này lỗi: WHERE chạy trước SELECT, nên rk chưa tồn tại
-- SELECT emp_id, RANK() OVER (ORDER BY SUM(amount) DESC) AS rk
-- FROM sales GROUP BY emp_id WHERE rk <= 3;

-- Đúng: tính rank trong truy vấn con bên trong, lọc ở bên ngoài
SELECT * FROM (
  SELECT emp_id,
    SUM(amount) AS total,
    RANK() OVER (ORDER BY SUM(amount) DESC) AS rk
  FROM sales
  GROUP BY emp_id
) AS ranked
WHERE rk <= 3
ORDER BY rk, emp_id;

Hãy hình dung bạn chỉ muốn top 3 nhân viên theo tổng doanh số. Vì bạn lọc theo rank, bạn cần truy vấn con để giải quyết vấn đề thứ tự thực thi.

① Trong truy vấn con bên trong, GROUP BY sales theo emp_id và select SUM(amount) alias total, cộng RANK() OVER (ORDER BY SUM(amount) DESC) alias rk. Đặt alias cho truy vấn con là ranked.

② Lọc truy vấn bên ngoài với WHERE rk <= 3.

③ Bộ chấm điểm kiểm tra thứ tự dòng, nên hãy kết thúc với ORDER BY rk, emp_id (tăng dần theo rk, rồi theo emp_id khi trùng).