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

Hàm cửa sổ (window function) trong SQL: ROW_NUMBER, RANK, LAG và tổng lũy kế

Hàm cửa sổ (window function) tính một giá trị cho từng dòng dựa trên một tập các dòng liên quan (gọi là “cửa sổ”) mà không gộp các dòng đó thành một. GROUP BY biến 33.121 chuyến bay thành sáu dòng trạng thái; COUNT(*) OVER (PARTITION BY status) giữ nguyên cả 33.121 chuyến bay và ghi bên cạnh mỗi chuyến số chuyến có cùng trạng thái. Chỉ từ một ý tưởng đó, bạn có được xếp hạng, top N mỗi nhóm, tổng lũy kế, trung bình trượt, tỷ trọng trên tổng và so sánh với dòng trước.

Cú pháp luôn giống nhau:

function(...) OVER (
  PARTITION BY ...   -- tùy chọn: tính lại từ đầu cho mỗi nhóm
  ORDER BY ...       -- tùy chọn: sắp xếp các dòng bên trong cửa sổ
  ROWS/RANGE ...     -- tùy chọn: dùng những dòng nào quanh dòng hiện tại
)

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 (104 sân bay, 9 mẫu máy bay, 33.121 chuyến bay trong tháng 7–9/2026, 262.788 đơn đặt chỗ; tiền tính bằng đô la Mỹ), theo cú pháp PostgreSQL. Mọi kết quả bên dưới đều là kết quả chạy thật, và bạn có thể chạy bất kỳ truy vấn nào trong Sân tập SQL của Nomto để kiểm tra.

OVER (): giá trị tổng hợp bên cạnh mỗi dòng

Cửa sổ đơn giản nhất là toàn bộ kết quả. So sánh tầm bay của từng mẫu máy bay với trung bình cả đội bay:

SELECT model, range,
       ROUND(AVG(range) OVER ()) AS fleet_avg,
       range - ROUND(AVG(range) OVER ()) AS diff
FROM aircrafts
ORDER BY range DESC
LIMIT 5;

Nếu không có hàm cửa sổ, bạn sẽ cần một truy vấn con hoặc JOIN với một bảng một dòng để đưa fleet_avg vào mỗi dòng.

Tỷ trọng trên tổng

Hàm cửa sổ chạy sau GROUP BY, nên bạn có thể bọc một hàm tổng hợp trong cửa sổ. SUM(COUNT(*)) OVER () là tổng số đếm của tất cả các nhóm:

SELECT status, COUNT(*) AS flights,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct
FROM flights
GROUP BY status
ORDER BY flights DESC;

100.0 rất quan trọng: với số nguyên, PostgreSQL sẽ chia lấy phần nguyên và hầu hết tỷ trọng sẽ ra 0.

PARTITION BY: mỗi nhóm một cửa sổ riêng

PARTITION BY chia các dòng thành nhóm và tính cửa sổ bên trong từng nhóm, giống GROUP BY nhưng không gộp dòng. Tỷ trọng doanh thu theo hạng ghế trong từng mẫu máy bay:

SELECT a.model, tf.fare_conditions,
       SUM(tf.amount) AS revenue,
       ROUND(100.0 * SUM(tf.amount) / SUM(SUM(tf.amount)) OVER (PARTITION BY a.model), 1) AS pct_of_model
FROM ticket_flights tf
JOIN flights f ON f.flight_id = tf.flight_id
JOIN aircrafts a ON a.aircraft_code = f.aircraft_code
WHERE a.aircraft_code IN ('773', 'SU9')
GROUP BY a.model, tf.fare_conditions
ORDER BY a.model, revenue DESC;

Các tỷ lệ cộng lại bằng 100 trong từng mẫu máy bay, chứ không phải trên toàn bảng.

ROW_NUMBER, RANK và DENSE_RANK khác nhau thế nào

Cả ba đều đánh số dòng theo thứ tự do ORDER BY bên trong OVER quy định. Chúng chỉ khác nhau khi có giá trị bằng nhau:

• ROW_NUMBER() cho mỗi dòng một số duy nhất; các dòng bằng nhau được xếp tùy ý trừ khi bạn thêm cột phá hòa.
• RANK() cho các dòng bằng nhau cùng một số, rồi nhảy cóc: 1, 2, 2, 4.
• DENSE_RANK() cho các dòng bằng nhau cùng một số và không nhảy cóc: 1, 2, 2, 3.

Xếp hạng các sân bay theo số điểm đến khác nhau. Phần thú vị nằm quanh hạng mười, nơi năm sân bay cùng có 9 điểm đến:

WITH d AS (
  SELECT departure_airport, COUNT(DISTINCT arrival_airport) AS destinations
  FROM flights
  GROUP BY departure_airport
), r AS (
  SELECT departure_airport, destinations,
         ROW_NUMBER() OVER (ORDER BY destinations DESC, departure_airport) AS row_num,
         RANK()       OVER (ORDER BY destinations DESC) AS rnk,
         DENSE_RANK() OVER (ORDER BY destinations DESC) AS dense_rnk
  FROM d
)
SELECT * FROM r
WHERE row_num BETWEEN 8 AND 15
ORDER BY row_num;

Sau năm sân bay đồng hạng, RANK nhảy lên 15 (bốn vị trí đã “bị dùng”), còn DENSE_RANK tiếp tục với 11. Cột phá hòa departure_airport trong ROW_NUMBER giúp kết quả lặp lại được; nếu không có nó, IKT, KZN, PEE, SGC và TJM có thể ra theo bất kỳ thứ tự nào ở lần chạy khác.

Nên dùng hàm nào:

• “Lấy đúng N dòng” hoặc “loại bỏ bản ghi trùng”: ROW_NUMBER.
• Xếp hạng “kiểu Olympic”, đồng hạng thì chung vị trí và vị trí tiếp theo bị bỏ qua: RANK.
• “Top 3 giá trị khác nhau”, ví dụ ba mức giá cao nhất, bất kể mỗi mức có bao nhiêu dòng: DENSE_RANK.

Top N mỗi nhóm

Cách dùng kinh điển của ROW_NUMBER với PARTITION BY: hai điểm đến nhiều chuyến nhất từ mỗi sân bay ở Moscow.

WITH r AS (
  SELECT departure_airport, arrival_airport, COUNT(*) AS flights,
         ROW_NUMBER() OVER (PARTITION BY departure_airport
                            ORDER BY COUNT(*) DESC, arrival_airport) AS rn
  FROM flights
  WHERE departure_airport IN ('SVO', 'DME', 'VKO')
  GROUP BY departure_airport, arrival_airport
)
SELECT departure_airport, arrival_airport, flights, rn
FROM r
WHERE rn <= 2
ORDER BY departure_airport, rn;

Hai điều cần chú ý:

1. Không thể lọc hàm cửa sổ trong WHERE. WHERE chạy trước khi cửa sổ được tính, nên WHERE ROW_NUMBER() OVER (...) <= 2 là lỗi. Hãy tính số thứ tự trong một CTE hoặc truy vấn con, rồi lọc ở bên ngoài. (Một số hệ quản trị có mệnh đề QUALIFY cho việc này; PostgreSQL thì không.)
2. Kiểm tra giá trị bằng nhau. Từ Vnukovo, BZK và LED đều có 183 chuyến; ROW_NUMBER xếp BZK lên trước chỉ nhờ cột phá hòa theo thứ tự chữ cái. Nếu muốn hiện cả các dòng đồng hạng, hãy dùng RANK() ... <= 2; khi đó mỗi nhóm có thể trả về hơn hai dòng.

Khử trùng: dòng mới nhất cho mỗi khóa

Cùng mẫu này giữ lại một dòng cho mỗi khóa. Lần cất cánh đã hạ cánh gần nhất của mỗi số hiệu chuyến bay từ Kazan:

WITH ranked AS (
  SELECT flight_no, arrival_airport, scheduled_departure,
         ROW_NUMBER() OVER (PARTITION BY flight_no ORDER BY scheduled_departure DESC) AS rn
  FROM flights
  WHERE departure_airport = 'KZN' AND status = 'Arrived'
)
SELECT flight_no, arrival_airport, scheduled_departure
FROM ranked
WHERE rn = 1
ORDER BY flight_no
LIMIT 5;

Tổng lũy kế

Thêm ORDER BY bên trong OVER của một hàm tổng hợp, nó sẽ trở thành tổng lũy kế. Số đơn đặt chỗ mỗi ngày trong tuần đầu tháng 8 kèm tổng lũy kế:

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-08'
  GROUP BY CAST(book_date AS date)
)
SELECT day, bookings,
       SUM(bookings) OVER (ORDER BY day) AS running_total,
       LAG(bookings) OVER (ORDER BY day) AS prev_day,
       bookings - LAG(bookings) OVER (ORDER BY day) AS change
FROM daily
ORDER BY day;

Thêm PARTITION BY để tổng lũy kế bắt đầu lại cho mỗi nhóm, ví dụ theo từng sân bay hoặc từng tháng.

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

LAG(x) trả về x của dòng trước theo thứ tự cửa sổ, LEAD(x) trả về x của dòng sau. Bảng ở trên dùng LAG để tính thay đổi so với ngày trước. Ở dòng đầu tiên không có dòng trước, nên kết quả là NULL; LAG(bookings, 1, 0) sẽ trả về 0 thay vào đó, còn LAG(bookings, 7) sẽ nhìn lùi một tuần.

LEAD trả lời câu hỏi “tiếp theo là gì”. Chuyến PG0579 bay bao lâu một lần, và lần cất cánh cuối cùng là khi nào?

SELECT flight_no,
       CAST(scheduled_departure AS date) AS day,
       LEAD(CAST(scheduled_departure AS date)) OVER (ORDER BY scheduled_departure)
         - CAST(scheduled_departure AS date) AS days_to_next
FROM flights
WHERE flight_no = 'PG0579'
ORDER BY scheduled_departure DESC
LIMIT 3;

Đây là chuyến bay hằng tuần; lần cất cánh cuối cùng trong lịch không có lần tiếp theo. Lưu ý rằng ORDER BY của cửa sổ (tăng dần) độc lập với ORDER BY của truy vấn (giảm dần): cửa sổ được tính trước, rồi kết quả mới được sắp xếp.

Khi so sánh các dòng của nhiều đối tượng cùng lúc, luôn chia nhóm: LEAD(...) OVER (PARTITION BY flight_no ORDER BY scheduled_departure). Nếu không, lần cất cánh cuối của một chuyến bay sẽ bị so với lần cất cánh đầu của số hiệu chuyến kế tiếp.

Khung cửa sổ: ROWS và RANGE

Khung (frame) xác định hàm nhìn thấy những dòng nào quanh dòng hiện tại. Có hai điều cần nhớ.

Khung mặc định. Khi có ORDER BY mà không khai báo khung, khung là RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE coi mọi dòng có cùng giá trị ORDER BY là một bước, nên các dòng bằng nhau nhận cùng một tổng lũy kế:

WITH d AS (
  SELECT departure_airport, COUNT(DISTINCT arrival_airport) AS destinations
  FROM flights
  GROUP BY departure_airport
), w AS (
  SELECT departure_airport, destinations,
         SUM(destinations) OVER (ORDER BY destinations DESC) AS range_total,
         SUM(destinations) OVER (ORDER BY destinations DESC, departure_airport
                                 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_total
  FROM d
)
SELECT * FROM w
WHERE destinations BETWEEN 9 AND 10
ORDER BY destinations DESC, departure_airport;

Với RANGE, cả năm sân bay đồng hạng nhảy thẳng lên 261. Với ROWS, tổng tăng dần từng dòng một. Nếu tổng lũy kế của bạn “nhảy bậc” ở các mốc thời gian bằng nhau, đây chính là lý do. Để có tổng lũy kế theo từng dòng, hãy dùng ROWS và một thứ tự sắp xếp duy nhất.

Trung bình trượt. Một khung như ROWS BETWEEN 2 PRECEDING AND CURRENT ROW là cửa sổ trượt 3 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-08'
  GROUP BY CAST(book_date AS date)
)
SELECT day, bookings,
       ROUND(AVG(bookings) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 1) AS avg_3d
FROM daily
ORDER BY day;

Hai dòng đầu lấy trung bình trên ít hơn ba ngày, vì trong cửa sổ này không có gì trước ngày 1/8. ROWS đếm dòng, không đếm ngày: nếu daily thiếu một ngày, trung bình “3 ngày” sẽ âm thầm trải trên bốn ngày lịch. Hãy lấp các ngày trống bằng một chuỗi ngày trước, hoặc dùng RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW trên một cột kiểu ngày.

Hàm cửa sổ nằm ở đâu trong thứ tự thực thi truy vấn

Về mặt logic, cửa sổ được tính sau FROM, WHERE, GROUP BY và HAVING, và trước ORDER BY và LIMIT. Điều đó giải thích ba quy tắc:

• Cửa sổ chỉ thấy những dòng còn lại sau WHERE; muốn xếp hạng tất cả chuyến bay nhưng chỉ hiện một phần, hãy xếp hạng trong truy vấn con rồi lọc ở ngoài.
• Bạn có thể dùng hàm tổng hợp bên trong cửa sổ (SUM(COUNT(*)) OVER ()) vì việc nhóm đã xong.
• Không thể dùng hàm cửa sổ trong WHERE, GROUP BY hay HAVING; hãy bọc truy vấn lại.

Quan hệ giữa WHERE, GROUP BY và HAVING được giải thích trong bài GROUP BY và HAVING trong SQL. Xếp hạng và “top N mỗi nhóm” cũng là một trong những câu hỏi phỏng vấn SQL phổ biến nhất; muốn tự luyện, hãy làm bài 14 và 15 trong bài tập SQL có lời giải.

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

Hàm cửa sổ khác GROUP BY thế nào?

GROUP BY trả về một dòng cho mỗi nhóm. Hàm cửa sổ trả về mọi dòng đầu vào và thêm một giá trị được tính trên một nhóm dòng. Bạn có thể dùng cả hai trong một truy vấn: cửa sổ được áp dụng lên kết quả đã nhóm.

RANK và DENSE_RANK khác nhau thế nào?

Cả hai đều cho các dòng bằng nhau cùng một số. Sau nhóm đồng hạng, RANK bỏ qua các số (1, 2, 2, 4), còn DENSE_RANK thì không (1, 2, 2, 3). Trong ví dụ sân bay, năm sân bay đồng hạng 10; sân bay tiếp theo đứng hạng 15 theo RANK và hạng 11 theo DENSE_RANK.

Vì sao tổng lũy kế của tôi giống nhau ở nhiều dòng?

Vì khung mặc định là RANGE, coi các dòng có cùng giá trị ORDER BY là ngang hàng và tính tất cả chúng cùng lúc. Hãy khai báo ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW và làm cho thứ tự sắp xếp là duy nhất.

Có dùng được hàm cửa sổ trong WHERE không?

Không. Hãy tính nó trong một CTE hoặc truy vấn con rồi lọc ở truy vấn bên ngoài.

ROW_NUMBER có cần ORDER BY không?

PostgreSQL cho phép ROW_NUMBER() OVER () nhưng khi đó việc đánh số là tùy ý. Với mọi kết quả bạn muốn lặp lại được, hãy cho nó một ORDER BY kèm cột phá hòa duy nhất.

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