Menu
Học tập
Khóa học Lộ trình nghề nghiệp Roadmap BA → CPO BA → Product Manager Tài liệu & Sách
Cộng đồng
Cộng đồng Interview Questions
Công cụ
Phân tích CV Theo dõi ứng tuyển Hiring Pipeline Luyện phỏng vấn Tối ưu LinkedIn Đàm phán lương
Tài khoản
ESC

Nhập từ khóa để tìm kiếm

↑↓ Di chuyển
Enter Mở
ESC Đóng

Window functions: partitioned aggregates và running totals

SQL and Data Analysis cho BA Bài 20/60

Mở đầu — vì sao bài này quan trọng

Nếu bạn từng làm báo cáo và bị sếp hỏi: "Doanh thu tháng này đóng góp bao nhiêu phần trăm vào cả quý?", hoặc "Cho tôi xem doanh thu luỹ kế (running total) qua từng ngày để biết khi nào chạm mốc 1 tỷ", thì bạn đã chạm đúng vùng đất mà bài học này phục vụ.

Ở Bài 18 và 19 bạn đã làm quen với window functions để xếp hạng (ROW_NUMBER, RANK) và để nhìn sang dòng kế bên (LAG, LEAD). Bài 20 này tập trung vào một nhóm câu hỏi cực kỳ phổ biến trong công việc của BA: tính toán tổng hợp (aggregate) NHƯNG không làm mất từng dòng dữ liệu chi tiết. Đó là hai pattern: partitioned aggregates (tổng hợp theo nhóm mà vẫn giữ chi tiết) và running totals (cộng dồn theo thời gian).

Đây là điểm khiến nhiều BA mới chuyển từ Excel sang SQL bị "vướng". Trong Excel, bạn có thể vừa nhìn từng dòng giao dịch, vừa kéo công thức để có tổng cộng dồn ở cột bên cạnh — rất tự nhiên. Khi sang SQL, bản năng đầu tiên là dùng GROUP BY, nhưng GROUP BY lại "nuốt" mất các dòng chi tiết. Window aggregate chính là cây cầu nối lại đúng cảm giác Excel đó, nhưng với sức mạnh và độ chính xác của database. Nắm vững nó, bạn sẽ viết được những báo cáo mà trước đây phải export ra Excel rồi xử lý thủ công.

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

Sự khác biệt nền tảng: GROUP BY làm mất dòng, window aggregate thì không

Hãy ghi nhớ một câu duy nhất, vì nó là linh hồn của cả bài: GROUP BY gộp nhiều dòng thành một; window aggregate giữ nguyên số dòng, chỉ "đính kèm" thêm một giá trị tổng hợp vào mỗi dòng.

Hình dung bảng orders có 1.000 dòng đơn hàng. Nếu bạn viết:

SELECT region, SUM(amount) AS total
FROM orders
GROUP BY region;

Kết quả còn lại vài dòng — mỗi vùng một dòng. Bạn không còn thấy từng đơn hàng nữa.

Nhưng nếu bạn viết:

SELECT
    order_id,
    region,
    amount,
    SUM(amount) OVER (PARTITION BY region) AS region_total
FROM orders;

Kết quả vẫn là 1.000 dòng. Mỗi dòng giữ nguyên order_id, amount của nó, nhưng có thêm cột region_total cho biết tổng doanh thu của cả vùng mà đơn đó thuộc về. Đây chính là điều GROUP BY không làm được nếu không phải tự join lại.

Cú pháp OVER và mệnh đề PARTITION BY

Mọi window aggregate đều có dạng:

hàm_tổng_hợp(cột) OVER ( [PARTITION BY ...] [ORDER BY ...] [frame] )
  • hàm_tổng_hợp là SUM, AVG, COUNT, MIN, MAX — đúng những hàm bạn đã biết ở Bài 13.
  • OVER (...) là phần khai báo "cửa sổ" (window) mà hàm sẽ tính trên đó.
  • PARTITION BY chia dữ liệu thành các nhóm độc lập. Hàm tổng hợp tính lại từ đầu cho mỗi nhóm. Nó giống GROUP BY về mặt "chia nhóm", nhưng không làm mất dòng.
  • Nếu không có PARTITION BY, cửa sổ là toàn bộ kết quả — hàm tính trên tất cả các dòng.
Ví dụ tính tỷ trọng từng đơn so với tổng vùng:

SELECT
    order_id,
    region,
    amount,
    SUM(amount) OVER (PARTITION BY region) AS region_total,
    ROUND(amount * 100.0 / SUM(amount) OVER (PARTITION BY region), 2) AS pct_of_region
FROM orders;

Cột pct_of_region trả lời thẳng câu hỏi "đơn này chiếm bao nhiêu phần trăm doanh thu vùng" — một dạng câu hỏi BA gặp hằng tuần.

Running total: thêm ORDER BY vào trong OVER

Đây là điểm mấu chốt thứ hai. Khi bạn thêm ORDER BY vào bên trong OVER(...), window aggregate chuyển từ "tổng của cả nhóm" sang "tổng cộng dồn đến dòng hiện tại". Đó chính là running total (tổng luỹ kế):

SELECT
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM daily_sales;

Hãy chú ý sự khác biệt tinh tế nhưng cực kỳ quan trọng:

  • SUM(amount) OVER () → tổng của TẤT CẢ các dòng (một con số duy nhất lặp lại).
  • SUM(amount) OVER (ORDER BY order_date) → tổng từ dòng đầu tiên ĐẾN dòng hiện tại, cộng dồn dần.
Lý do là khi có ORDER BY trong OVER, SQL ngầm áp dụng một "frame" mặc định là RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — tức "từ đầu đến dòng hiện tại". (Chi tiết về frame sẽ được mổ xẻ sâu hơn ở Bài 51; ở đây bạn chỉ cần hiểu cơ chế cộng dồn.)

Kết hợp PARTITION BY và ORDER BY: running total theo từng nhóm

Sức mạnh thực sự đến khi ghép cả hai. Ví dụ: running total doanh thu nhưng reset lại đầu mỗi tháng:

SELECT
    order_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY DATE_FORMAT(order_date, '%Y-%m')
        ORDER BY order_date
    ) AS monthly_running_total
FROM daily_sales;

Mỗi khi sang tháng mới, PARTITION BY tạo nhóm mới, và running total bắt đầu lại từ 0. Đây là dạng báo cáo "doanh thu luỹ kế trong tháng" mà phòng tài chính rất hay cần.

Tình huống thực tế

Tình huống 1 — Tiki: tỷ trọng đóng góp của từng ngành hàng

Bối cảnh: Bạn là BA tại một sàn thương mại điện tử kiểu Tiki. Phòng kinh doanh muốn một bảng cho biết: với mỗi ngành hàng (category), từng nhà bán (seller) đóng góp bao nhiêu phần trăm doanh thu của ngành đó, để biết ngành nào đang phụ thuộc quá mức vào một nhà bán duy nhất.

Giả sử bảng sales có cột category, seller_name, revenue.

SELECT
    category,
    seller_name,
    revenue,
    SUM(revenue) OVER (PARTITION BY category) AS category_revenue,
    ROUND(
        revenue * 100.0 / SUM(revenue) OVER (PARTITION BY category),
        2
    ) AS pct_of_category
FROM sales
ORDER BY category, pct_of_category DESC;

Diễn giải: với ngành "Điện tử" có tổng 5 tỷ đồng, nếu nhà bán "FPT Shop" mang về 3,5 tỷ thì pct_of_category = 70%. Một con số như vậy lập tức gióng chuông cảnh báo: ngành điện tử đang quá phụ thuộc vào một đối tác. Nếu họ rời sàn, doanh thu ngành sụp 70%.

Bài học rút ra: Nếu dùng GROUP BY category thông thường, bạn chỉ có tổng ngành mà không còn thấy từng seller. Còn ở đây, nhờ window aggregate, bạn vừa giữ được chi tiết từng nhà bán, vừa có mẫu số là tổng ngành để tính tỷ trọng — tất cả trong một câu query, không cần tự join hay export sang Excel.

Tình huống 2 — Be/Grab: doanh thu luỹ kế trong ngày để theo dõi mục tiêu

Bối cảnh: Bạn hỗ trợ đội vận hành của một ứng dụng gọi xe kiểu Be. Mỗi ngày họ đặt mục tiêu doanh thu (GMV) và muốn biết tại từng khung giờ, doanh thu cộng dồn đã đạt bao nhiêu, để quyết định có cần tung khuyến mãi giờ thấp điểm hay không.

Bảng trips có trip_time (datetime), fare (giá cước).

SELECT
    DATE(trip_time)        AS ngay,
    HOUR(trip_time)        AS gio,
    SUM(fare)              AS doanh_thu_gio,
    SUM(SUM(fare)) OVER (
        PARTITION BY DATE(trip_time)
        ORDER BY HOUR(trip_time)
    )                      AS luy_ke_trong_ngay
FROM trips
GROUP BY DATE(trip_time), HOUR(trip_time)
ORDER BY ngay, gio;

Đây là một ví dụ "nâng cao một nhịp": bạn vừa GROUP BY để gộp các chuyến theo từng giờ, vừa dùng window SUM(SUM(fare)) để cộng dồn các con số theo giờ đó lại. Phần bên ngoài SUM(...) OVER (...) chạy SAU khi GROUP BY đã gộp xong, nên SUM(fare) bên trong là doanh thu mỗi giờ, còn SUM(...) bên ngoài cộng dồn các giờ.

Kết quả: 6h có 10 triệu, 7h thêm 25 triệu → luỹ kế 35 triệu, 8h thêm 40 triệu → luỹ kế 75 triệu... Nhờ PARTITION BY DATE, mỗi ngày mới, con số reset về 0.

Bài học rút ra: Khi cần kết hợp gộp nhóm (theo giờ) và cộng dồn (qua các giờ), bạn lồng window aggregate bên ngoài aggregate thường. Đây là pattern đắt giá mà rất nhiều BA không biết, nên họ phải làm hai bước rời rạc trong Excel.

Tình huống 3 — MoMo: so sánh từng giao dịch với trung bình của người dùng

Bối cảnh: Đội risk (phòng chống gian lận) của một ví điện tử kiểu MoMo muốn rà các giao dịch bất thường. Ý tưởng đơn giản: với mỗi user, tính giá trị giao dịch trung bình của họ, rồi đánh dấu giao dịch nào cao gấp nhiều lần trung bình cá nhân.

Bảng transactions có user_id, txn_id, txn_time, amount.

SELECT
    user_id,
    txn_id,
    amount,
    ROUND(AVG(amount) OVER (PARTITION BY user_id), 0) AS avg_user_amount,
    ROUND(
        amount / AVG(amount) OVER (PARTITION BY user_id),
        1
    ) AS ratio_vs_avg
FROM transactions;

Diễn giải: một user thường giao dịch quanh 200.000đ. Đột nhiên có giao dịch 5.000.000đ → ratio_vs_avg = 25. Một dòng có tỷ lệ cao bất thường như vậy đáng để đội risk xem lại. Quan trọng là mỗi giao dịch vẫn được giữ nguyên (không bị gộp), nhờ vậy đội risk có thể lọc ra đúng dòng đáng ngờ và điều tra.

Bài học rút ra: AVG ... OVER (PARTITION BY user_id) cho phép so sánh từng dòng với mức "bình thường" của chính nhóm chứa nó. Đây là nền tảng của rất nhiều phân tích phát hiện bất thường — một chủ đề bạn sẽ gặp lại sâu hơn ở Bài 35.

Hướng dẫn từng bước

Khi đối diện một yêu cầu báo cáo, hãy đi theo trình tự suy nghĩ sau để quyết định viết window aggregate thế nào:

Bước 1 — Hỏi: tôi có cần giữ lại từng dòng chi tiết không? Nếu câu trả lời là "có" (cần thấy từng đơn, từng giao dịch, từng seller bên cạnh con số tổng hợp) → dùng window aggregate. Nếu chỉ cần con số tổng gọn gàng → GROUP BY thông thường là đủ và thường nhanh hơn.

Bước 2 — Xác định "nhóm" để tính tổng hợp. Tổng hợp theo vùng? PARTITION BY region. Theo user? PARTITION BY user_id. Trên toàn bộ dữ liệu? Bỏ trống PARTITION BY, chỉ viết OVER ().

Bước 3 — Hỏi: tôi cần "tổng của cả nhóm" hay "tổng cộng dồn"?

  • Tổng của cả nhóm (để tính tỷ trọng, so sánh với trung bình) → KHÔNG thêm ORDER BY vào OVER.
  • Tổng cộng dồn theo thời gian (running total) → THÊM ORDER BY vào OVER.
Đây là cái bẫy hay gặp nhất, nên hãy khắc cốt: có ORDER BY trong OVER = cộng dồn; không có = tổng toàn nhóm.

Bước 4 — Chọn cột sắp xếp cho running total. Thường là order_date, txn_time, hoặc một cột thứ tự rõ ràng. Lưu ý: nếu cột này có giá trị trùng nhau (ví dụ nhiều đơn cùng một ngày), running total có thể nhảy cả cụm cùng lúc — đây là hệ quả của RANGE mặc định, sẽ bàn kỹ ở Bài 51.

Bước 5 — Viết tỷ trọng hoặc tỷ lệ nếu cần. Lấy giá trị từng dòng chia cho window aggregate, nhân 100, nhớ ép kiểu số thực bằng cách nhân 100.0 để tránh phép chia số nguyên trả về 0.

Bước 6 — Kiểm tra bằng mắt. Sắp xếp kết quả theo nhóm và theo thời gian, rồi cộng nhẩm vài dòng đầu để chắc running total và tỷ trọng khớp với kỳ vọng.

Lỗi thường gặp & mẹo

Lỗi 1 — Quên rằng có ORDER BY trong OVER sẽ biến tổng thành cộng dồn. Rất nhiều người muốn lấy tổng cả nhóm nhưng vô tình thêm ORDER BY, rồi ngạc nhiên vì con số "tổng" mỗi dòng một khác. Nhớ: muốn tổng cả nhóm thì OVER (PARTITION BY x) thôi, đừng thêm ORDER BY.

Lỗi 2 — Chia số nguyên ra 0. amount / SUM(amount) OVER (...) nếu cả hai là số nguyên có thể trả về 0 ở một số database. Luôn nhân 100.0 hoặc ép CAST(... AS DECIMAL) để có kết quả thập phân đúng.

Lỗi 3 — Đặt window aggregate trong WHERE. Bạn KHÔNG thể viết WHERE SUM(amount) OVER (...) > 1000. Window function được tính SAU khi WHERE đã lọc xong, nên không dùng được trong WHERE. Muốn lọc theo kết quả window, hãy bọc query vào một subquery hoặc CTE (Bài 16) rồi lọc ở lớp ngoài:

WITH ranked AS (
    SELECT *, SUM(amount) OVER (PARTITION BY region) AS region_total
    FROM orders
)
SELECT * FROM ranked WHERE region_total > 1000000000;

Lỗi 4 — Nhầm PARTITION BY với GROUP BY. PARTITION BY không gộp dòng. Nếu bạn đã GROUP BY rồi mà vẫn cần cộng dồn, hãy lồng window aggregate bên ngoài aggregate thường như ví dụ Be ở trên (SUM(SUM(...)) OVER (...)).

Mẹo 1 — Running total reset theo kỳ. Muốn luỹ kế reset đầu mỗi tháng/quý, chỉ cần thêm PARTITION BY theo kỳ đó vào trong OVER. Không cần viết nhiều query riêng.

Mẹo 2 — Trung bình trượt là họ hàng gần. Khi bạn đã quen running total, việc chuyển sang trung bình trượt (moving average) chỉ là đổi SUM thành AVG và thêm frame giới hạn số dòng — kiến thức mở rộng ở Bài 51.

Mẹo 3 — Đặt tên cột rõ ràng bằng tiếng Việt không dấu hoặc tiếng Anh. region_total, luy_ke_trong_ngay dễ đọc hơn nhiều so với để hệ thống tự đặt tên. Báo cáo của BA cần người khác hiểu được.

Bài tập thực hành

Giả sử bạn có bảng don_hang(order_id, khu_vuc, ngay_dat, gia_tri) mô phỏng dữ liệu một sàn TMĐT Việt Nam.

  • Viết query giữ nguyên từng đơn hàng và thêm cột tong_khu_vuc là tổng doanh thu của khu vực đơn đó thuộc về. (Gợi ý: SUM ... OVER (PARTITION BY khu_vuc).)
  • Thêm cột ty_trong cho biết mỗi đơn chiếm bao nhiêu phần trăm doanh thu khu vực của nó. Nhớ nhân 100.0.
  • Viết query tính running_total doanh thu toàn hệ thống theo ngay_dat, để biết khi nào tổng doanh thu chạm mốc 10 tỷ.
  • Sửa câu 3 để running total reset lại đầu mỗi tháng (gợi ý: PARTITION BY theo YEAR-MONTH của ngay_dat).
  • Thử thách: dùng CTE để chỉ giữ lại những khu vực có tong_khu_vuc vượt 1 tỷ đồng. Giải thích vì sao không thể đặt điều kiện này trực tiếp trong WHERE.
  • So sánh từng đơn với giá trị trung bình của khu vực: thêm cột tb_khu_vuc (AVG ... OVER (PARTITION BY khu_vuc)) và đánh dấu những đơn lớn gấp 3 lần trung bình.
Hãy chạy thử trên môi trường MySQL hoặc PostgreSQL bạn đã cài ở Bài 5, và cộng nhẩm vài dòng đầu để tự kiểm chứng.

Tóm tắt

  • Window aggregate (SUM/AVG/COUNT/MIN/MAX ... OVER (...)) giữ nguyên số dòng dữ liệu, khác hẳn GROUP BY vốn gộp dòng. Đây là cây cầu nối lại cảm giác làm việc tự nhiên như trong Excel nhưng với sức mạnh của database.
  • PARTITION BY chia dữ liệu thành nhóm độc lập để tính tổng hợp riêng cho mỗi nhóm, mà không mất chi tiết — lý tưởng cho việc tính tỷ trọng và so sánh từng dòng với mức trung bình của nhóm.
  • Thêm ORDER BY vào trong OVER sẽ biến tổng của cả nhóm thành tổng cộng dồn (running total). Đây là khác biệt cốt lõi cần khắc ghi: có ORDER BY = cộng dồn; không có = tổng toàn nhóm.
  • Kết hợp PARTITION BY + ORDER BY cho phép running total reset theo từng kỳ (tháng, quý, user...).
  • Không dùng được window function trong WHERE; muốn lọc theo kết quả của nó, hãy bọc vào CTE hoặc subquery rồi lọc ở lớp ngoài.
  • Ba pattern bạn sẽ dùng nhiều nhất trong công việc BA: tỷ trọng đóng góp, doanh thu luỹ kế theo thời gian, và so sánh từng dòng với trung bình nhóm — cả ba đều gói gọn trong một câu query duy nhất nhờ window aggregate.
Trước Tiếp