GROUP BY biến dữ liệu giao dịch thành chỉ số quản trị, nhưng cũng là nơi nhiều báo cáo sai vì nhầm grain hoặc đếm số dòng thành số đơn. Bài viết xây báo cáo doanh thu ngày theo cửa hàng, có số đơn, số khách, sản phẩm bán ra và giá trị đơn trung bình.

Mục lục

1. Bài toán thực tế
2. Sản phẩm hoàn thành
3. Kiến thức cần dùng
4. Chuẩn bị môi trường
5. Dữ liệu thực hành
6. Xây dựng từng bước
7. Chương trình hoàn chỉnh
8. Kiểm tra kết quả
9. Lỗi thường gặp
10. Đưa vào thực tế
11. Bài tập thực hành
12. Project mở rộng
13. Tổng kết
14. Tài liệu tham khảo

1. Bài toán thực tế

Bảng order_items có một dòng mỗi sản phẩm trong đơn. Quản lý muốn xem báo cáo một dòng mỗi cửa hàng mỗi ngày. Nếu COUNT(*) trực tiếp sau JOIN, một đơn mua ba sản phẩm sẽ bị tính thành ba đơn.

Báo cáo còn phải loại đơn cancelled, tách doanh thu gộp, giảm giá và doanh thu thuần. Những định nghĩa này phải được chốt trước khi viết hàm SUM.

2. Sản phẩm hoàn thành

Sản phẩm là view daily_store_sales ở grain sale_date + store_id. View cung cấp order_count, customer_count, units_sold, gross_revenue, discount_amount, net_revenue và average_order_value.

Một bảng reconciliation đi kèm giúp đối chiếu tổng doanh thu nguồn và báo cáo. Người học có thể dùng view cho dashboard mà không lặp logic.

• Phân biệt WHERE và HAVING.
• Sử dụng COUNT, COUNT DISTINCT, SUM, AVG, MIN và MAX.
• Nhóm đúng grain và tránh đếm trùng sau JOIN.
• Tính tỷ lệ an toàn với NULLIF.
• Xây báo cáo có số kiểm soát.

3. Kiến thức cần dùng

Aggregate function nhận nhiều dòng và trả một giá trị cho mỗi nhóm. Mọi cột trong SELECT không nằm trong hàm tổng hợp phải xuất hiện trong GROUP BY, trừ trường hợp database suy luận functional dependency.

WHERE lọc dòng trước khi nhóm; HAVING lọc nhóm sau khi tính aggregate. Điều kiện status thuộc từng đơn phải đặt ở WHERE. Điều kiện tổng doanh thu một cửa hàng vượt 100 triệu phải đặt ở HAVING.

COUNT(*) đếm dòng, COUNT(column) bỏ qua NULL và COUNT(DISTINCT order_id) đếm mã đơn khác nhau. Chọn sai biến thể có thể thay đổi KPI.

4. Chuẩn bị môi trường

Sử dụng orders và order_items. orders giữ một dòng mỗi đơn; order_items giữ nhiều dòng mỗi đơn. Đặt timezone trước khi chuyển order_date thành ngày.

Để kết quả dễ kiểm tra, bắt đầu với một ngày và hai cửa hàng. Sau khi đối chiếu bằng tay mới mở rộng cả tháng.

SET TIME ZONE 'Asia/Ho_Chi_Minh';

SELECT o.order_id, COUNT(*) AS item_lines
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY o.order_id
ORDER BY item_lines DESC
LIMIT 10;

5. Dữ liệu thực hành

orders gồm order_id, customer_id, store_id, order_date và status. order_items gồm product_id, quantity, unit_price và discount_amount. Doanh thu gộp mỗi dòng bằng quantity nhân unit_price; doanh thu thuần trừ discount_amount.

Grain mục tiêu là một dòng mỗi sale_date và store_id. product_id không xuất hiện trong GROUP BY cuối vì báo cáo không chi tiết đến sản phẩm.

CREATE TABLE order_items (
  order_id bigint NOT NULL REFERENCES orders(order_id),
  product_id bigint NOT NULL,
  quantity integer NOT NULL CHECK (quantity > 0),
  unit_price numeric(12,2) NOT NULL CHECK (unit_price >= 0),
  discount_amount numeric(12,2) NOT NULL DEFAULT 0,
  PRIMARY KEY (order_id, product_id)
);

6. Xây dựng từng bước

Bước 1: Tính chỉ số cấp dòng

Tạo CTE line_metrics để công thức doanh thu chỉ được định nghĩa một lần. Lọc đơn completed trước khi tổng hợp.

WITH line_metrics AS (
  SELECT o.order_id, o.customer_id, o.store_id,
         o.order_date::date AS sale_date,
         oi.quantity,
         oi.quantity * oi.unit_price AS gross_revenue,
         oi.discount_amount,
         oi.quantity * oi.unit_price - oi.discount_amount AS net_revenue
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status = 'completed'
)
SELECT * FROM line_metrics LIMIT 20;

Kết quả mong đợi: Mỗi dòng sản phẩm có ba giá trị tiền tường minh.

Bước 2: Tổng hợp đúng grain

COUNT DISTINCT bảo đảm một đơn nhiều dòng chỉ được đếm một lần. SUM quantity vẫn cộng ở cấp dòng sản phẩm.

SELECT
  o.order_date::date AS sale_date,
  o.store_id,
  COUNT(DISTINCT o.order_id) AS order_count,
  COUNT(DISTINCT o.customer_id) AS customer_count,
  SUM(oi.quantity) AS units_sold
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status = 'completed'
GROUP BY o.order_date::date, o.store_id;

Kết quả mong đợi: Một dòng cho mỗi cửa hàng mỗi ngày.

Bước 3: Tính doanh thu và AOV

AOV là doanh thu thuần chia số đơn. NULLIF tránh chia cho 0, dù nhóm hiện tại luôn có ít nhất một dòng.

SELECT
  o.store_id,
  SUM(oi.quantity * oi.unit_price - oi.discount_amount) AS net_revenue,
  SUM(oi.quantity * oi.unit_price - oi.discount_amount)
    / NULLIF(COUNT(DISTINCT o.order_id), 0) AS average_order_value
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status = 'completed'
GROUP BY o.store_id;

Kết quả mong đợi: Doanh thu và giá trị đơn trung bình theo cửa hàng.

Bước 4: Lọc nhóm bằng HAVING

HAVING chỉ giữ cửa hàng-ngày đạt ngưỡng doanh thu. Không thể đặt SUM trong WHERE cùng cấp.

SELECT o.order_date::date AS sale_date, o.store_id,
       SUM(oi.quantity * oi.unit_price - oi.discount_amount) AS net_revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status = 'completed'
GROUP BY o.order_date::date, o.store_id
HAVING SUM(oi.quantity * oi.unit_price - oi.discount_amount) >= 10000000
ORDER BY sale_date, net_revenue DESC;

Kết quả mong đợi: Chỉ nhóm doanh thu từ 10 triệu đồng.

Bước 5: Tạo subtotal bằng GROUPING SETS

Báo cáo có thể trả chi tiết cửa hàng-ngày và tổng ngày trong một truy vấn. GROUPING phân biệt dòng subtotal với store_id thật sự NULL.

SELECT o.order_date::date AS sale_date, o.store_id,
       GROUPING(o.store_id) AS is_daily_total,
       SUM(oi.quantity * oi.unit_price - oi.discount_amount) AS net_revenue
FROM orders o JOIN order_items oi ON oi.order_id=o.order_id
WHERE o.status='completed'
GROUP BY GROUPING SETS ((o.order_date::date,o.store_id),(o.order_date::date))
ORDER BY sale_date, is_daily_total, store_id;

Kết quả mong đợi: Chi tiết cửa hàng và một dòng tổng cho mỗi ngày.

Đối chiếu báo cáo bằng hai đường tính

Một cách mạnh để kiểm tra aggregate là tính cùng chỉ số bằng hai con đường độc lập. Ví dụ tổng net_revenue từ view ngày phải bằng tổng trực tiếp từ order_items hợp lệ. Nếu hai truy vấn chia sẻ toàn bộ CTE, chúng có thể cùng mắc một lỗi và vẫn khớp.

Đối chiếu nên thực hiện ở nhiều cấp: toàn kỳ, theo ngày và chọn một cửa hàng. Tổng toàn kỳ khớp nhưng một cửa hàng sai có thể do phân bổ nhầm khóa; kiểm tra theo cấp giúp thu hẹp nguyên nhân.

Phân biệt chỉ số cộng được và không cộng được

Doanh thu ngày có thể cộng thành doanh thu tháng. Số khách distinct theo ngày không thể cộng trực tiếp thành số khách distinct tháng vì một khách mua nhiều ngày. AOV cũng không nên lấy trung bình đơn giản của AOV cửa hàng nếu quy mô đơn khác nhau.

Khi thiết kế mart, ghi chú tính cộng được của từng metric. additive có thể cộng mọi chiều, semi-additive chỉ cộng một số chiều và non-additive như tỷ lệ cần tính lại từ tử số, mẫu số.

Xử lý hoàn tiền và hủy đơn

Bài dùng completed để đơn giản, nhưng doanh nghiệp cần quyết định refund ghi âm ở ngày hoàn hay điều chỉnh ngày bán. Hai cách ảnh hưởng xu hướng ngày và đối soát tài chính khác nhau. Không có hàm SUM nào tự trả lời câu hỏi đó.

Tốt nhất giữ gross sales, discount, refund và net sales thành các measure riêng. Dashboard có thể trình bày doanh thu thuần, trong khi reconciliation vẫn giải thích được đường đi của từng thành phần.

Bảng kết quả mẫu và cách đọc KPI

Giả sử cửa hàng 1 trong ngày 20/9 có hai đơn, ba dòng sản phẩm, bốn sản phẩm bán ra, gross 1.000.000 đồng và discount 100.000 đồng. Báo cáo đúng phải trả order_count 2, units_sold 4 và net_revenue 900.000 đồng. COUNT(*) bằng 3 không phải số đơn.

Việc viết trước một dòng kết quả mong đợi giúp người học biết truy vấn cần chứng minh điều gì. Đây là hình thức test dựa trên ví dụ và rất hiệu quả với aggregate.

SELECT DATE '2026-09-20' sale_date,1::bigint store_id,
       2::bigint expected_orders,4::bigint expected_units,
       1000000::numeric expected_gross,100000::numeric expected_discount,
       900000::numeric expected_net;

Aggregate có điều kiện trong cùng báo cáo

FILTER giúp tính completed revenue, cancelled orders và high-value orders trong một lần nhóm. So với nhiều CASE bên trong SUM, FILTER thường dễ đọc hơn khi PostgreSQL được sử dụng.

Chỉ số có điều kiện phải ghi rõ mẫu số. Tỷ lệ hủy là cancelled orders chia tất cả đơn hay chỉ đơn đã chốt? Hai định nghĩa tạo con số khác và đều có thể hợp lý trong ngữ cảnh khác.

SELECT store_id,
 COUNT(*) total_orders,
 COUNT(*) FILTER(WHERE status='cancelled') cancelled_orders,
 COUNT(*) FILTER(WHERE total_amount>=10000000) high_value_orders,
 ROUND(100.0*COUNT(*) FILTER(WHERE status='cancelled')/NULLIF(COUNT(*),0),2) cancel_rate_pct
FROM orders GROUP BY store_id;

Tổng hợp theo thời gian đúng cách

date_trunc('week', order_date) phụ thuộc timezone phiên. Đặt timezone hoặc chuyển rõ trước khi nhóm. Tuần bắt đầu thứ Hai trong PostgreSQL; doanh nghiệp có thể dùng lịch tài chính khác và cần bảng calendar.

Không tạo month bằng EXTRACT(month) đơn lẻ vì tháng 9 của nhiều năm sẽ bị gộp. Dùng date_trunc('month') hoặc nhóm cả year và month.

SET TIME ZONE 'Asia/Ho_Chi_Minh';
SELECT date_trunc('week',order_date)::date week_start,
       store_id,SUM(total_amount) revenue
FROM orders WHERE status='completed'
GROUP BY week_start,store_id
ORDER BY week_start,store_id;

Kiểm tra tính cộng được của chỉ số

order_count có thể cộng từ cửa hàng lên toàn hệ thống nếu mỗi order chỉ thuộc một store. customer_count distinct không thể cộng vì một khách có thể mua nhiều cửa hàng. Muốn khách toàn hệ thống phải tính distinct lại từ dữ liệu cấp thấp hơn hoặc dùng kỹ thuật ước lượng.

Data mart nên lưu cả tử số và mẫu số cho tỷ lệ. Lưu AOV mà bỏ revenue và order_count khiến tổng hợp cấp cao hơn dễ sai.

SELECT sale_date,
       SUM(net_revenue) total_revenue,
       SUM(order_count) total_orders,
       SUM(net_revenue)/NULLIF(SUM(order_count),0) correct_aov,
       AVG(average_order_value) unweighted_aov
FROM daily_store_sales GROUP BY sale_date;

7. Chương trình hoàn chỉnh

View chính dùng CTE để tính line metrics, sau đó tổng hợp tất cả KPI ở đúng grain. Công thức được viết một lần giúp dashboard và API sử dụng cùng định nghĩa.

Không ORDER BY trong view nếu người tiêu dùng có thể chọn thứ tự khác. Dashboard thêm ORDER BY khi truy vấn view.

CREATE OR REPLACE VIEW daily_store_sales AS
WITH line_metrics AS (
  SELECT o.order_id, o.customer_id, o.store_id,
         o.order_date::date AS sale_date, oi.quantity,
         oi.quantity * oi.unit_price AS gross_revenue,
         oi.discount_amount,
         oi.quantity * oi.unit_price - oi.discount_amount AS net_revenue
  FROM orders o JOIN order_items oi ON oi.order_id=o.order_id
  WHERE o.status='completed'
)
SELECT sale_date, store_id,
       COUNT(DISTINCT order_id) AS order_count,
       COUNT(DISTINCT customer_id) AS customer_count,
       SUM(quantity) AS units_sold,
       SUM(gross_revenue) AS gross_revenue,
       SUM(discount_amount) AS discount_amount,
       SUM(net_revenue) AS net_revenue,
       SUM(net_revenue)/NULLIF(COUNT(DISTINCT order_id),0) AS average_order_value
FROM line_metrics
GROUP BY sale_date, store_id;

8. Kiểm tra kết quả

Tổng net_revenue của view phải bằng tổng công thức trên dữ liệu completed nguồn. Tổng order_count theo cửa hàng bằng số đơn distinct nếu mỗi đơn chỉ thuộc một cửa hàng.

Chọn một đơn nhiều dòng và xác nhận order_count chỉ tăng một. Kiểm tra đẳng thức gross_revenue - discount_amount = net_revenue ở từng nhóm.

SELECT
 (SELECT SUM(net_revenue) FROM daily_store_sales) AS mart_total,
 (SELECT SUM(oi.quantity*oi.unit_price-oi.discount_amount)
  FROM orders o JOIN order_items oi ON oi.order_id=o.order_id
  WHERE o.status='completed') AS source_total;

SELECT * FROM daily_store_sales
WHERE ABS(gross_revenue-discount_amount-net_revenue) > 0.01;

9. Lỗi thường gặp

• COUNT(*) thành số đơn: Sau JOIN, COUNT(*) là số dòng sản phẩm.
• Đưa điều kiện aggregate vào WHERE: Dùng HAVING cho điều kiện trên SUM hoặc COUNT.
• Nhóm quá chi tiết: Thêm product_id sẽ đổi grain và tạo nhiều dòng hơn.
• AVG unit_price thành AOV: AOV phải dựa trên doanh thu và số đơn.
• Cộng subtotal với detail: Dòng GROUPING SETS phải được nhận diện trước khi cộng tiếp.

10. Đưa vào thực tế

Với bảng lớn, materialized view hoặc bảng mart được nạp incremental có thể phù hợp hơn view tính lại. Khóa mart gồm sale_date, store_id và job ghi bằng upsert hoặc thay partition ngày.

Định nghĩa KPI phải nằm trong data catalog: completed có bao gồm refunded không, discount thuộc dòng hay đơn, timezone nào xác định ngày. Những quyết định này quan trọng hơn bản thân hàm SUM.

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

1. Thêm gross margin khi có product_cost.
2. Tạo báo cáo doanh thu theo category nhưng vẫn đếm đơn đúng.
3. So sánh COUNT(*), COUNT(order_id) và COUNT(DISTINCT order_id).
4. Tìm cửa hàng có ít nhất 100 đơn bằng HAVING.
5. Tạo báo cáo tuần bằng date_trunc.

12. Project mở rộng

Xây mart_monthly_store_sales từ view ngày và so sánh với tổng hợp trực tiếp từ nguồn. Hai cách phải cho cùng doanh thu; khác biệt là tín hiệu grain hoặc logic lọc chưa nhất quán.

13. Tổng kết

GROUP BY không chỉ là cú pháp gom dòng. Người làm dữ liệu phải xác định grain, chọn đúng mẫu số và chứng minh tổng sau tổng hợp khớp nguồn.

Sau khi có báo cáo ngày, bài JOIN sẽ đi sâu vào cách nối năm bảng mà không làm doanh thu tăng do fan-out.

14. Tài liệu tham khảo

• Aggregate Functions
• GROUPING SETS
• SELECT

TechData.AI - Leading the Future.
Tham khảo các khoá học theo link: https://techdata.ai/techdata-ai-course/
Hoàng Minh.