Bài tập: chuyển báo cáo Excel sang SQL

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

• Chuyển một báo cáo Excel hoàn chỉnh sang SQL từng bước.
• Kiểm tra số liệu giữa hai công cụ.
• Xử lý các khác biệt hay gặp: ngày, NULL, nhân dòng.
• Trình bày kết quả đúng hạt và đúng định nghĩa.
• Tự kiểm tra bằng bộ câu hỏi cuối bài.

Đề bài

Bạn nhận file BaoCao.xlsx với ba sheet:

1. DonHang: mã đơn, ngày, mã khách, trạng thái.
2. ChiTiet: mã đơn, mã sản phẩm, số lượng, đơn giá.
3. KhachHang: mã khách, tên, thành phố, ngày đăng ký.

Báo cáo cần tái tạo:

• Mỗi thành phố một dòng.
• Số đơn hoàn thành.
• Doanh thu.
• Giá trị đơn trung bình.
• Số khách khác nhau đã mua.
• Xếp hạng thành phố theo doanh thu.

Trong Excel, báo cáo này thường là PivotTable cộng vài cột SUMIFS và RANK. Hãy chuyển sang một truy vấn SQL.

Bước 1 — Chuyển sheet thành bảng

CREATE TABLE customers (
  id integer PRIMARY KEY,
  name text NOT NULL,
  city text NOT NULL,
  signup_date date NOT NULL
);

CREATE TABLE orders (
  id integer PRIMARY KEY,
  customer_id integer NOT NULL REFERENCES customers(id),
  order_date date NOT NULL,
  status text NOT NULL
);

CREATE TABLE order_items (
  id integer PRIMARY KEY,
  order_id integer NOT NULL REFERENCES orders(id),
  product_id integer NOT NULL,
  quantity integer NOT NULL,
  unit_price numeric(12, 2) NOT NULL
);

Ba điểm cần kiểm tra khi nhập:

• Ngày đúng YYYY-MM-DD, kiểu date.
• status chỉ có các giá trị đã biết (completed, pending, cancelled).
• order_items không có dòng trùng (order_id, product_id) nếu nghiệp vụ không cho phép.

Bước 2 — Viết truy vấn theo đúng hạt

WITH don AS (
  SELECT o.id,
         o.customer_id,
         SUM(oi.quantity * oi.unit_price) AS doanh_thu
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  WHERE o.status = 'completed'
  GROUP BY o.id, o.customer_id
)
SELECT c.city,
       COUNT(*) AS so_don,
       SUM(d.doanh_thu) AS doanh_thu,
       ROUND(SUM(d.doanh_thu) / COUNT(*), 0) AS gia_tri_don_tb,
       COUNT(DISTINCT d.customer_id) AS so_khach,
       RANK() OVER (ORDER BY SUM(d.doanh_thu) DESC) AS hang
FROM don d
JOIN customers c ON c.id = d.customer_id
GROUP BY c.city
ORDER BY hang;

Vì sao phải có CTE don:

• SUM(oi.quantity * oi.unit_price) phải tính theo đơn trước, nếu không mỗi dòng hàng bị đếm riêng.
• COUNT(*) trên don là số đơn, không phải số dòng hàng.
• COUNT(DISTINCT d.customer_id) là số khách khác nhau, không phải số đơn.
• RANK() chạy trên kết quả đã nhóm theo thành phố.

Đây là bản dịch trung thực của báo cáo Excel: mỗi con số có hạt rõ ràng.

Bước 3 — Đối chiếu với Excel

Trước khi tin SQL, so ba con số:

Nếu lệch, kiểm tra theo thứ tự:

1. Bộ lọc trạng thái có giống nhau không?
2. Có đơn thiếu khách hàng bị JOIN loại bỏ không?
3. Có dòng hàng trùng gây nhân doanh thu không?
4. Định dạng ngày có bị hiểu sai không?

Đừng sửa SQL cho khớp Excel ngay. Tìm ra nguyên nhân lệch trước — có thể Excel mới là bên sai.

Bước 4 — Xử lý khác biệt thường gặp

Bước 5 — Trình bày kết quả

Ba lưu ý khi đưa bảng SQL vào báo cáo:

1. Ghi rõ định nghĩa: "đơn hoàn thành", "doanh thu = số lượng × đơn giá", "khách đã mua tính theo customer_id".
2. Kèm tổng: thêm dòng tổng bằng ROLLUP hoặc một truy vấn riêng, để người đọc kiểm tra chéo.
3. Ghi thời gian: khoảng ngày lấy dữ liệu và thời điểm chạy.

-- Tổng toàn bộ để đối chiếu
SELECT COUNT(*) AS so_don,
       SUM(quantity * unit_price) AS doanh_thu
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status = 'completed';

Nếu tổng này khác tổng các dòng thành phố, có đơn không gắn được thành phố — dấu hiệu dữ liệu khách thiếu hoặc JOIN loại dòng.

Tự kiểm tra

1. Vì sao cần CTE don trước khi nhóm theo thành phố? Vì orders và order_items ở hai hạt khác nhau. Tổng hợp về hạt đơn trước để tránh nhân dòng và để COUNT(*) là số đơn.

2. COUNT(*) trong truy vấn chính đếm gì? Đếm số đơn hoàn thành trong CTE don, vì mỗi dòng CTE là một đơn.

3. Nếu dùng COUNT(DISTINCT o.id) mà không có CTE thì sao? Vẫn ra số đơn đúng, nhưng SUM doanh thu sẽ bị nhân theo số dòng hàng. Phải tổng hợp theo hạt trước.

4. Đơn không có khách hàng sẽ đi đâu? JOIN customers loại chúng. Nếu cần giữ, dùng LEFT JOIN và nhóm NULL thành "Không rõ".

5. RANK() đặt ở đâu trong truy vấn? Sau khi đã GROUP BY c.city; window chạy trên kết quả nhóm, không chạy trên bảng gốc.

6. Hai nguồn lệch 3% thì làm gì? Kiểm tra bộ lọc, hạt, NULL, định dạng ngày và dòng trùng. Không sửa số cho khớp trước khi hiểu nguyên nhân.

Lỗi thường gặp

Tóm tắt

• Chuyển sheet thành bảng có kiểu và khóa rõ ràng.
• Tổng hợp về hạt đơn trước khi nhóm theo thành phố.
• Đối chiếu ba con số với Excel trước khi công bố.
• Ghi rõ định nghĩa, khoảng thời gian và tổng để kiểm tra chéo.
• Lệch số là manh mối về hạt hoặc bộ lọc, không phải lý do sửa tay.