Tổng quan OVER

Bạn sẽ học được

• Window function khác GROUP BY ở chỗ nào.
• Cú pháp OVER, PARTITION BY, ORDER BY bên trong cửa sổ.
• Khung cửa sổ mặc định và cách nó tạo tổng luỹ kế.
• Đặt tên cửa sổ bằng WINDOW khi dùng lại.
• Vì sao không lọc được window function trong WHERE.

Khái niệm cốt lõi

GROUP BY gộp nhiều dòng thành một. Window function thì giữ nguyên từng dòng và thêm một cột tính trên một "cửa sổ" dòng liên quan. Đây là khác biệt cốt lõi:

-- GROUP BY: 4 dòng (mỗi danh mục một dòng)
SELECT category, AVG(price) FROM products GROUP BY category;

-- Window: 9 dòng (mỗi sản phẩm một dòng, kèm trung bình danh mục)
SELECT name, category, price,
       AVG(price) OVER (PARTITION BY category) AS avg_category
FROM products;

Câu thứ hai trả cả 9 sản phẩm, mỗi dòng có thêm giá trung bình của danh mục nó thuộc về. Không mất chi tiết. Đó là lý do window function là công cụ chính của báo cáo: bạn vừa xem từng dòng, vừa thấy bối cảnh.

Cú pháp tổng quát:

ham() OVER (
  PARTITION BY cot_chia_nhom
  ORDER BY cot_sap_xep
  [khung_cua_so]
)

Ba thành phần, mỗi cái có vai trò riêng:

OVER () rỗng nghĩa là "toàn bộ bảng":

SELECT name, price,
       ROUND(AVG(price) OVER (), 2) AS gia_trung_binh,
       ROUND(price - AVG(price) OVER (), 2) AS chenh_lech
FROM products
ORDER BY chenh_lech DESC;

Trung bình 634.444,44 lặp trên cả 9 dòng. Nồi chiên không dầu có chênh lệch +1.055.555,56; Sách SQL thực chiến thấp nhất với −454.444,44.

Có ORDER BY trong cửa sổ, khung mặc định là từ đầu đến dòng hiện tại — nên SUM trở thành tổng luỹ kế:

WITH monthly AS (
  SELECT TO_CHAR(o.order_date, 'YYYY-MM') AS thang,
         SUM(oi.quantity * oi.unit_price) AS doanh_thu
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  GROUP BY 1
)
SELECT thang, doanh_thu,
       SUM(doanh_thu) OVER (ORDER BY thang) AS luy_ke
FROM monthly
ORDER BY thang;

Tháng 1 luỹ kế 3.850.000; tháng 4 lên 16.160.000; tháng 11 đạt 31.230.000 — đúng tổng doanh thu. Khung mặc định này là RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Chữ RANGE quan trọng: nếu nhiều dòng có cùng giá trị ORDER BY, chúng được tính cùng nhau, không cộng dồn từng dòng. Với ngày/tháng thường không trùng nên ít thấy khác biệt; với điểm số trùng thì ROWS và RANGE cho kết quả khác nhau — bài cửa sổ trượt sẽ nói kỹ.

Vì sao không lọc được window function trong WHERE? Thứ tự thực thi SQL là FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. Window function được tính ở bước SELECT, sau WHERE. Khi WHERE chạy, cột avg_category chưa tồn tại:

-- LỖI: window functions are not allowed in WHERE
SELECT name, price, RANK() OVER (ORDER BY price DESC) AS r
FROM products
WHERE r <= 3;

Cách đúng: tính trong CTE, lọc ở lớp ngoài — kỹ năng đã học ở chương 1.

Khi nhiều cột dùng chung một cửa sổ, đặt tên bằng WINDOW để khỏi lặp:

SELECT name, price,
       RANK() OVER w AS hang,
       SUM(price) OVER w AS tong_luy_ke
FROM products
WINDOW w AS (ORDER BY price DESC)
ORDER BY hang;

Câu này ngắn hơn và ít lỗi sao chép hơn — nếu sửa khung, chỉ sửa một chỗ.

Ví dụ chạy được

So sánh giá từng sản phẩm với giá trung bình danh mục:

SELECT name, category, price,
       ROUND(AVG(price) OVER (PARTITION BY category), 2) AS avg_category,
       ROUND(price - AVG(price) OVER (PARTITION BY category), 2) AS chenh_lech
FROM products
ORDER BY category, price DESC;

Điện tử có trung bình 863.333,33; Bàn phím cơ +386.666,67, Tai nghe +26.666,67, Chuột −413.333,33. Gia dụng trung bình 672.500; Nồi chiên +1.017.500, còn Giá kê, Bình giữ nhiệt và Đèn bàn đều âm. Sách và Thời trang chỉ một sản phẩm nên chênh lệch bằng 0.

Tỷ trọng doanh thu từng đơn trên tổng:

WITH totals AS (
  SELECT o.id, SUM(oi.quantity * oi.unit_price) AS total
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  GROUP BY o.id
)
SELECT id, total,
       ROUND(100.0 * total / SUM(total) OVER (), 1) AS pct
FROM totals
ORDER BY total DESC
LIMIT 3;

Ba đơn lớn nhất chiếm 12,9%; 12,7%; 9,4% tổng doanh thu. Không cần CTE phụ để tính tổng toàn bảng — OVER () làm việc đó trong cùng một câu.

Tổng luỹ kế theo tháng:

WITH monthly AS (
  SELECT TO_CHAR(o.order_date, 'YYYY-MM') AS thang,
         SUM(oi.quantity * oi.unit_price) AS doanh_thu
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  GROUP BY 1
)
SELECT thang, doanh_thu,
       SUM(doanh_thu) OVER (ORDER BY thang) AS luy_ke,
       ROUND(100.0 * SUM(doanh_thu) OVER (ORDER BY thang)
             / SUM(doanh_thu) OVER (), 1) AS pct_luy_ke
FROM monthly
ORDER BY thang;

Cột pct_luy_ke cho biết đến cuối mỗi tháng đã đạt bao nhiêu phần trăm tổng doanh thu — dạng báo cáo tiến độ rất hay dùng. Tháng 6 đạt 65,5%; tháng 9 đạt 86,8%.

Một cửa sổ có PARTITION BY nhưng không ORDER BY:

SELECT c.name, o.id AS don,
       COUNT(*) OVER (PARTITION BY c.id) AS so_don_cua_khach
FROM customers c
JOIN orders o ON o.customer_id = c.id
ORDER BY c.name, o.id;

Mười sáu dòng — mỗi dòng là một đơn, kèm tổng số đơn của khách đó lặp lại. Bình Trần có 3 đơn, ba khách có 2 đơn, bảy khách có 1 đơn. Nếu dùng GROUP BY c.id bạn sẽ mất cột don; window function giữ cả hai.

Thực hành

1. Hiện giá từng sản phẩm và giá trung bình toàn cửa hàng trên cùng dòng. Kiểm tra trung bình là 634.444,44.
2. Tính tỷ trọng doanh thu của từng trong 16 đơn. Tổng các tỷ trọng phải xấp xỉ 100%.
3. Tính tổng luỹ kế doanh thu theo tháng và xác nhận tháng cuối bằng 31.230.000.
4. Thử lọc WHERE RANK() OVER (...) <= 3 trực tiếp và đọc lỗi. Sửa bằng CTE.
5. Đặt tên cửa sổ bằng WINDOW w AS (...) cho câu có hai hàm dùng chung OVER.

Lỗi thường gặp

Lỗi thứ ba tinh tế: với ORDER BY thang, mỗi tháng là một giá trị duy nhất nên RANGE và ROWS như nhau. Nhưng nếu sắp theo segment và cộng doanh thu, RANGE sẽ gộp mọi khách cùng segment vào một mức luỹ kế. Hiểu khung là hiểu window function.

Tóm tắt

• Window function tính trên cửa sổ dòng nhưng giữ nguyên từng dòng kết quả.
• PARTITION BY chia nhóm; ORDER BY sắp trong nhóm; khung giới hạn phạm vi tính.
• OVER () là toàn bảng; có ORDER BY thì mặc định là tổng luỹ kế.
• Không lọc window function trong WHERE — dùng CTE.
• WINDOW đặt tên cửa sổ dùng lại, giảm lặp và lỗi.