Một báo cáo cần cộng giá trị đơn đã thanh toán theo khách, rồi chỉ giữ khách đạt ngưỡng. Nếu đặt mọi biểu thức vào một SELECT dài, việc kiểm tra từng bước trở nên khó. Common Table Expression, viết trong WITH, giúp đặt tên cho kết quả trung gian trong phạm vi một câu lệnh.
Viết từng bước theo đơn vị dữ liệu
Ví dụ dùng dữ liệu giả lập và đơn vị số nguyên để tránh đưa vấn đề làm tròn tiền vào bài. Bước paid chỉ giữ đơn đã thanh toán. Bước totals gom theo khách. SELECT cuối làm nhiệm vụ chọn khách đạt ngưỡng.
WITH orders(customer_id, status, amount) AS (
VALUES (1, 'paid', 70),
(1, 'paid', 50),
(1, 'cancelled', 900),
(2, 'paid', 30)
),
paid AS (
SELECT customer_id, amount
FROM orders
WHERE status = 'paid'
),
totals AS (
SELECT customer_id, sum(amount) AS paid_total
FROM paid
GROUP BY customer_id
)
SELECT customer_id, paid_total
FROM totals
WHERE paid_total >= 100
ORDER BY customer_id;
Kết quả cần có một hàng, khách 1 với tổng 120. Đơn hủy có giá trị 900 vẫn bị loại. Đây là ca hữu ích để phát hiện lỗi cộng tiền trước khi lọc trạng thái. Chạy toàn bộ câu lệnh trên SQLite của ứng dụng. Nếu muốn xem bước trung gian, giữ phần WITH và đổi SELECT cuối thành SELECT từ paid hoặc totals.
Tránh cộng nhân sau JOIN
Trước khi thêm bảng dòng sản phẩm, xác định mỗi hàng của paid đại diện một đơn hay một dòng hàng. JOIN một đơn với nhiều sản phẩm rồi cộng amount của đơn có thể nhân tổng. Hãy tổng hợp dòng sản phẩm về đơn trước hoặc cộng trực tiếp giá trị dòng theo đúng nghiệp vụ. Đặt tên CTE theo đơn vị dữ liệu giúp reviewer nhận ra phép chuyển đó.
CTE chỉ tồn tại trong câu lệnh đang chạy. Bạn không thể thực thi một SELECT riêng ở câu lệnh tiếp theo rồi mong tên totals còn tồn tại. Nếu công cụ SQL chỉ chạy phần văn bản được chọn, chọn cả WITH và SELECT cuối.
Đánh giá hiệu năng sau khi đúng
CTE làm rõ cấu trúc truy vấn nhưng không tự bảo đảm thời gian chạy tốt hơn. Với bảng thật, dùng EXPLAIN QUERY PLAN và kiểm tra chỉ mục hỗ trợ điều kiện trạng thái hoặc khoảng thời gian của báo cáo. Đo trên dữ liệu đại diện cho môi trường triển khai. Chỉ cân nhắc các gợi ý materialization sau khi đã kiểm tra phiên bản SQLite và biết vấn đề cần giải quyết.
Thêm ca khách không có đơn paid, ca tổng đúng bằng ngưỡng và ca nhiều đơn cùng khách vào kiểm thử. Nếu dashboard cần hiện cả khách không mua, lấy danh sách khách làm tập gốc và ghép kết quả totals, đồng thời quy định tổng của khách chưa có đơn.


