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

Pivot table trong SQL: CASE WHEN aggregation

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

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

Hãy hình dung bạn là BA tại một sàn thương mại điện tử. Sếp gửi tin nhắn lúc 8 giờ tối: "Mai họp, cho anh xem bảng doanh thu của từng ngành hàng theo từng tháng quý vừa rồi." Trong đầu sếp đang tưởng tượng một bảng đẹp: cột bên trái là tên ngành hàng, các cột ngang là Tháng 1, Tháng 2, Tháng 3, mỗi ô là một con số doanh thu. Đó chính là cái mà dân Excel gọi là cross-tab hay PivotTable.

Nhưng dữ liệu trong database của bạn lại không nằm ở dạng đó. Nó nằm ở dạng "dài" (long format): mỗi dòng là một giao dịch, có cột ngành hàng, cột tháng, cột doanh thu. Nếu bạn GROUP BY ngành hàng và tháng, bạn sẽ ra một bảng dài hàng chục dòng — đúng số liệu nhưng nhìn rất khó so sánh. Sếp sẽ cau mày. Việc biến cái bảng "dài" thành bảng "rộng" (wide format) — biến các giá trị của một cột thành nhiều cột riêng — chính là pivot.

Đây là một trong những kỹ năng tạo ra giá trị nhìn-thấy-được rõ nhất của BA. Người khác đưa ra số liệu thô; bạn đưa ra bảng mà sếp có thể đọc trong ba giây và ra quyết định. Bài này dạy bạn cách pivot bằng SQL thuần — kỹ thuật CASE WHEN kết hợp với hàm tổng hợp (aggregate). Đây là cách pivot hoạt động trên mọi hệ quản trị cơ sở dữ liệu: MySQL, PostgreSQL, SQL Server, BigQuery... — không phụ thuộc cú pháp đặc thù, nên là kỹ thuật bạn buộc phải nắm vững.

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

Long format và wide format

Trước hết phải phân biệt hai hình dạng dữ liệu:

Long format — dữ liệu "dài", mỗi thuộc tính nằm trên một dòng:

nganh_hangthangdoanh_thu
Điện tử1500
Điện tử2600
Thời trang1300
Thời trang2450
Wide format — dữ liệu "rộng", các giá trị của cột thang trở thành tên cột:

nganh_hangthang_1thang_2
Điện tử500600
Thời trang300450
Pivot chính là phép biến đổi từ long sang wide. Database thích lưu long (dễ thêm dữ liệu, dễ truy vấn), còn con người thích đọc wide. BA đứng ở giữa, làm cầu nối.

Công thức pivot bằng CASE WHEN

Ý tưởng cốt lõi gói gọn trong một câu: mỗi cột bạn muốn tạo ra là một biểu thức aggregate(CASE WHEN điều_kiện THEN giá_trị END).

Hãy đọc kỹ logic này. Khi bạn GROUP BY nganh_hang, mỗi nhóm gom tất cả các dòng của một ngành hàng. Trong nhóm đó, bạn muốn tách doanh thu tháng 1 ra một cột, doanh thu tháng 2 ra một cột khác. CASE WHEN thang = 1 THEN doanh_thu END sẽ trả về doanh thu khi dòng đó thuộc tháng 1, và trả về NULL ở các dòng tháng khác. Khi bạn bọc nó trong SUM(...), hàm SUM bỏ qua NULL, nên kết quả chính là tổng doanh thu tháng 1 — đúng thứ ta cần.

SELECT
    nganh_hang,
    SUM(CASE WHEN thang = 1 THEN doanh_thu END) AS thang_1,
    SUM(CASE WHEN thang = 2 THEN doanh_thu END) AS thang_2,
    SUM(CASE WHEN thang = 3 THEN doanh_thu END) AS thang_3
FROM ban_hang
GROUP BY nganh_hang;

Ba thành phần luôn xuất hiện trong mọi câu pivot:

  • Cột nhóm (nganh_hang) — nằm trong GROUP BY, trở thành các dòng của bảng kết quả.
  • Cột pivot (thang) — giá trị của nó (1, 2, 3) trở thành các cột. Mỗi giá trị cần một biểu thức CASE WHEN riêng.
  • Cột giá trị (doanh_thu) — thứ bị tổng hợp trong mỗi ô.

Vì sao phải có hàm tổng hợp bao ngoài

Một câu hỏi rất hay của người mới: tại sao không viết thẳng CASE WHEN thang = 1 THEN doanh_thu END mà không cần SUM? Vì khi GROUP BY, mỗi nhóm có nhiều dòng. SQL bắt buộc mọi cột không nằm trong GROUP BY phải đi qua một hàm tổng hợp — nếu không, database không biết chọn dòng nào trong nhóm. SUM gộp các dòng lại; nếu một ngành hàng có nhiều giao dịch trong tháng 1, SUM cộng tất cả. Nếu bạn chỉ muốn đếm số đơn thì dùng COUNT, muốn trung bình thì AVG. Hàm tổng hợp không chỉ là thủ tục bắt buộc — nó quyết định ý nghĩa của con số trong ô.

Tình huống thực tế

Ví dụ 1: Báo cáo doanh thu theo ngành hàng và tháng (Tiki)

Một BA tại sàn Tiki cần dựng bảng doanh thu quý 1/2026, dòng là ngành hàng, cột là từng tháng. Dữ liệu nằm trong bảng don_hang với các cột nganh_hang, ngay_dat, gia_tri.

SELECT
    nganh_hang,
    SUM(CASE WHEN MONTH(ngay_dat) = 1 THEN gia_tri ELSE 0 END) AS T1,
    SUM(CASE WHEN MONTH(ngay_dat) = 2 THEN gia_tri ELSE 0 END) AS T2,
    SUM(CASE WHEN MONTH(ngay_dat) = 3 THEN gia_tri ELSE 0 END) AS T3,
    SUM(gia_tri) AS Tong_Quy1
FROM don_hang
WHERE ngay_dat >= '2026-01-01' AND ngay_dat < '2026-04-01'
GROUP BY nganh_hang
ORDER BY Tong_Quy1 DESC;

Kết quả thu được:

nganh_hangT1T2T3Tong_Quy1
Điện tử4.2 tỷ3.8 tỷ5.1 tỷ13.1 tỷ
Gia dụng2.1 tỷ2.0 tỷ2.6 tỷ6.7 tỷ
Thời trang1.5 tỷ1.9 tỷ1.4 tỷ4.8 tỷ
Diễn giải: Để ý chi tiết quan trọng — tôi thêm ELSE 0 thay vì để mặc định NULL. Vì sao? Nếu một ngành hàng không có đơn nào trong tháng 1, ô đó sẽ là NULL. Khi sếp dán bảng này vào Excel rồi tính tổng, NULL có thể gây lỗi hoặc hiển thị trống khó hiểu. Đặt ELSE 0 cho ra con số sạch sẽ, an toàn. Tôi cũng thêm cột Tong_Quy1 để sếp thấy ngay tổng cả quý — một cột tổng theo chiều ngang luôn được đánh giá cao.

Bài học: Pivot tốt không chỉ đúng số, mà còn phải lường trước cách người ta dùng bảng. Thêm cột tổng và xử lý NULL là chi tiết phân biệt một BA chuyên nghiệp.

Ví dụ 2: Đếm số đơn theo trạng thái và thành phố (Be — gọi xe)

BA tại hãng gọi xe Be muốn biết phân bố trạng thái chuyến đi tại các thành phố lớn: bao nhiêu chuyến hoan_thanh, huy_boi_khach, huy_boi_tai_xe. Đây là pivot bằng COUNT thay vì SUM, vì ta đếm số chuyến chứ không cộng tiền.

SELECT
    thanh_pho,
    COUNT(CASE WHEN trang_thai = 'hoan_thanh' THEN 1 END) AS Hoan_thanh,
    COUNT(CASE WHEN trang_thai = 'huy_boi_khach' THEN 1 END) AS Khach_huy,
    COUNT(CASE WHEN trang_thai = 'huy_boi_tai_xe' THEN 1 END) AS Taixe_huy,
    COUNT(*) AS Tong_chuyen,
    ROUND(
        COUNT(CASE WHEN trang_thai = 'hoan_thanh' THEN 1 END)  100.0 / COUNT(),
    1) AS Ty_le_hoan_thanh
FROM chuyen_di
WHERE ngay >= '2026-06-01' AND ngay < '2026-07-01'
GROUP BY thanh_pho;
thanh_phoHoan_thanhKhach_huyTaixe_huyTong_chuyenTy_le_hoan_thanh
Hồ Chí Minh182.40021.1009.800213.30085.5
Hà Nội141.20018.70012.400172.30081.9
Đà Nẵng38.6004.1002.90045.60084.6
Diễn giải: Với COUNT, ta dùng THEN 1 (đếm 1 cho mỗi dòng khớp điều kiện) và không cần ELSE — vì COUNT chỉ đếm các giá trị khác NULL, nên các dòng không khớp tự động bị bỏ qua. Lưu ý quan trọng: đừng viết COUNT(CASE WHEN ... THEN 1 ELSE 0 END), vì 0 cũng là một giá trị khác NULL nên COUNT sẽ đếm cả nó, làm sai kết quả. Với COUNT thì để mặc định NULL; với SUM thì mới cân nhắc ELSE 0.

Cột Ty_le_hoan_thanh cho thấy Hà Nội có tỷ lệ tài xế hủy cao bất thường — một insight đáng để team vận hành đào sâu.

Bài học: SUM và COUNT xử lý ELSE hoàn toàn khác nhau. Hiểu sai chỗ này là lỗi pivot kinh điển làm lệch số liệu mà không hề báo lỗi.

Ví dụ 3: Pivot hai chiều — sản phẩm theo kênh thanh toán (MoMo)

Một BA fintech làm việc với dữ liệu ví MoMo muốn xem: với mỗi loại dịch vụ (nạp điện thoại, thanh toán hóa đơn, chuyển tiền), khách dùng nguồn tiền nào nhiều hơn — số dư ví hay liên kết ngân hàng. Đây là pivot mà cột pivot có ít giá trị (2 nguồn) nhưng giá trị đo là cả số lượng lẫn tổng tiền.

SELECT
    loai_dich_vu,
    COUNT(CASE WHEN nguon_tien = 'vi' THEN 1 END) AS SL_Vi,
    COUNT(CASE WHEN nguon_tien = 'ngan_hang' THEN 1 END) AS SL_NganHang,
    SUM(CASE WHEN nguon_tien = 'vi' THEN so_tien ELSE 0 END) AS Tien_Vi,
    SUM(CASE WHEN nguon_tien = 'ngan_hang' THEN so_tien ELSE 0 END) AS Tien_NganHang
FROM giao_dich
WHERE ngay >= '2026-06-01' AND ngay < '2026-07-01'
GROUP BY loai_dich_vu
ORDER BY loai_dich_vu;
loai_dich_vuSL_ViSL_NganHangTien_ViTien_NganHang
Chuyển tiền890.000410.0001.780 tỷ2.460 tỷ
Nạp điện thoại1.240.00095.000124 tỷ14 tỷ
Thanh toán hóa đơn520.000380.000416 tỷ760 tỷ
Diễn giải: Bảng này gói gọn một insight kinh doanh sắc bén. Giao dịch nhỏ (nạp điện thoại) chủ yếu trừ từ số dư ví; giao dịch lớn (chuyển tiền, thanh toán hóa đơn) lại kéo từ ngân hàng. Điều này gợi ý chiến lược: muốn tăng giữ tiền trong ví, cần khuyến khích nạp ví số lớn. Một bảng pivot duy nhất, đặt cạnh nhau số lượng và giá trị, làm bật ra mẫu hành vi mà bảng dài không bao giờ cho thấy rõ.

Bài học: Pivot mạnh nhất khi đặt các cột so sánh sát cạnh nhau, để mắt người đọc tự bắt được sự đối lập.

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

Khi nhận một yêu cầu pivot, hãy đi theo quy trình bốn bước để không bao giờ bị rối:

Bước 1 — Xác định ba vai trò. Tự hỏi: dòng là gì (cột nhóm), cột là gì (cột pivot), ô chứa số gì (cột giá trị). Ví dụ "doanh thu theo ngành hàng và tháng": dòng = ngành hàng, cột = tháng, ô = tổng doanh thu. Viết rõ ba vai trò này ra giấy trước khi gõ SQL.

Bước 2 — Liệt kê các giá trị của cột pivot. Bạn cần biết trước cột pivot có những giá trị nào, vì mỗi giá trị thành một cột phải viết tay. Nếu là tháng thì có 12 (hoặc 3 cho một quý). Nếu là trạng thái thì chạy SELECT DISTINCT trang_thai FROM ... để lấy danh sách. Đây là giới hạn cố hữu của pivot bằng CASE WHEN: bạn phải biết trước các cột.

Bước 3 — Viết khung GROUP BY. Bắt đầu bằng câu đơn giản nhất: SELECT cot_nhom, ... FROM bang GROUP BY cot_nhom. Đảm bảo cột nhóm và GROUP BY khớp nhau.

Bước 4 — Điền từng biểu thức CASE WHEN. Với mỗi giá trị ở bước 2, thêm một dòng aggregate(CASE WHEN cot_pivot = 'giá_trị' THEN cot_gia_tri END) AS ten_cot. Chọn SUM hay COUNT hay AVG theo đúng ý nghĩa con số. Cuối cùng thêm cột tổng theo hàng nếu hữu ích, và ORDER BY cho dễ đọc.

Một mẹo thực chiến cho cột pivot có nhiều giá trị: nếu danh sách cố định (như 12 tháng), bạn có thể dùng công cụ bảng tính sinh nhanh 12 dòng CASE WHEN rồi dán vào, thay vì gõ tay từng dòng dễ sai.

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

Lỗi 1 — Quên hàm tổng hợp bao ngoài. Viết CASE WHEN thang = 1 THEN doanh_thu END mà không có SUM, kèm GROUP BY. Database sẽ báo lỗi cột không nằm trong GROUP BY (ở chế độ chuẩn) hoặc trả về kết quả vô nghĩa. Luôn nhớ: trong câu pivot, mọi CASE WHEN phải nằm trong một hàm tổng hợp.

Lỗi 2 — Dùng ELSE 0 với COUNT. Như đã nói ở ví dụ 2: COUNT(CASE WHEN ... THEN 1 ELSE 0 END) đếm cả những dòng không khớp (vì 0 khác NULL), khiến mọi cột đều bằng tổng số dòng. Với COUNT thì bỏ ELSE; với SUM thì ELSE 0 mới an toàn.

Lỗi 3 — NULL trong bảng kết quả. Khi dùng SUM mà không có ELSE 0, các ô không có dữ liệu hiện NULL. Nếu sau đó người dùng cộng các cột này lại, NULL có thể lan ra làm hỏng phép tính. Bọc bằng COALESCE(SUM(...), 0) hoặc đặt ELSE 0 ngay trong CASE.

Lỗi 4 — Cột pivot động. SQL thuần không tự sinh cột mới khi dữ liệu có giá trị mới. Nếu tháng sau xuất hiện một ngành hàng mới mà bạn muốn nó thành cột, bạn phải sửa câu SQL bằng tay. Pivot bằng CASE WHEN chỉ hợp khi tập giá trị cột cố định và biết trước. Nếu cần pivot thật sự động, đó là lúc chuyển dữ liệu sang công cụ BI (Metabase, Power BI) để chúng pivot, hoặc dùng SQL động (nâng cao, ngoài phạm vi bài này).

Mẹo 1 — Luôn thêm cột tổng và dòng tổng. Cột tổng theo hàng: thêm SUM(cot_gia_tri) AS Tong. Người đọc rất thích thấy tổng ngay cạnh chi tiết.

Mẹo 2 — Đặt tên cột rõ ràng, dễ đọc. AS T1 hay AS Thang_01 tốt hơn nhiều so với để database tự đặt tên dài ngoằng. Bảng pivot là để người đọc, nên tên cột phải thân thiện.

Mẹo 3 — Kiểm tra bằng cách so với bản long. Trước khi gửi bảng pivot, chạy nhanh một câu GROUP BY long format và cộng nhẩm vài ô để chắc chắn số khớp. Pivot sai rất khó phát hiện bằng mắt vì bảng nhìn vẫn "đẹp".

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

Giả sử bạn có bảng don_hang của một shop trên Shopee với các cột: ma_don, khu_vuc (Miền Bắc / Miền Trung / Miền Nam), phuong_thuc_tt (COD / Vi_dien_tu / The), ngay_dat, gia_tri.

  • Cơ bản: Viết câu pivot cho bảng doanh thu theo khu_vuc (dòng) và phuong_thuc_tt (cột), mỗi ô là tổng gia_tri. Nhớ thêm cột tổng theo hàng và xử lý NULL bằng ELSE 0.
  • Đếm: Sửa câu trên để mỗi ô là số đơn thay vì tổng tiền. Chú ý cách xử lý ELSE khác đi khi dùng COUNT.
  • Hai chiều kết hợp: Tạo bảng có cả số đơn lẫn tổng tiền cho từng phương thức thanh toán, đặt cạnh nhau (giống ví dụ MoMo), nhóm theo khu_vuc. Sau đó tính thêm cột tỷ lệ phần trăm đơn COD trên tổng số đơn của mỗi khu vực.
  • Theo thời gian: Pivot doanh thu theo khu_vuc (dòng) và tháng của ngay_dat (cột) cho năm 2026, kèm cột tổng cả năm. Tự kiểm tra số bằng cách chạy thêm câu long format GROUP BY khu_vuc, MONTH(ngay_dat) và đối chiếu vài ô.
Khi làm, hãy luôn viết ba vai trò (dòng / cột / ô) ra trước, rồi mới gõ SQL. Thói quen này giúp bạn không bao giờ lẫn lộn GROUP BY với cột pivot.

Tóm tắt

Pivot là kỹ thuật biến dữ liệu long thành wide — biến các giá trị của một cột thành nhiều cột riêng — để cho ra bảng cross-tab kiểu Excel mà người ra quyết định dễ đọc. Cách làm phổ quát, chạy trên mọi database, là dùng hàm tổng hợp bao quanh biểu thức CASE WHEN: mỗi cột bạn muốn tạo là một SUM/COUNT/AVG của một CASE WHEN cot_pivot = giá_trị THEN cot_gia_tri.

Ba vai trò cần xác định mỗi lần: cột nhóm (thành dòng, nằm trong GROUP BY), cột pivot (thành các cột), cột giá trị (bị tổng hợp trong mỗi ô). Nhớ hai điểm dễ sai nhất: SUM thì dùng ELSE 0 để tránh NULL, nhưng COUNT thì tuyệt đối không dùng ELSE 0 vì sẽ đếm sai. Luôn thêm cột tổng, đặt tên cột thân thiện, và đối chiếu với bản long để kiểm tra.

Giới hạn cần nhớ: pivot bằng CASE WHEN đòi hỏi bạn biết trước các giá trị của cột pivot và viết tay từng cột. Khi tập giá trị thay đổi liên tục hoặc quá nhiều, hãy để công cụ BI lo phần pivot. Nhưng với phần lớn báo cáo BA hằng ngày — doanh thu theo tháng, trạng thái theo khu vực, hành vi theo kênh — kỹ thuật CASE WHEN aggregation này là công cụ nhanh, chắc, và luôn sẵn sàng trong tay bạn.

Trước Tiếp