Dashboard với SQL

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

• Viết SQL cho từng loại widget: KPI, xu hướng, phân rã, top N.
• Tránh lỗi join làm phồng số liệu.
• Giữ widget nhất quán bằng cách dùng chung một lớp dữ liệu.
• Tối ưu truy vấn cho dashboard chạy thường xuyên.
• Quyết định nhịp làm mới và cách xử lý múi giờ.

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

Mỗi widget là một truy vấn trả về tập nhỏ, ổn định. Dashboard tốt dùng một lớp dữ liệu chung (view, CTE hoặc bảng tổng hợp) để mọi widget cùng định nghĩa, thay vì mỗi widget tự viết lại logic.

Với dataset bán lẻ (customers, products, orders, order_items), các widget cần:

Điểm mấu chốt: cùng khoảng thời gian, cùng bộ lọc trạng thái, cùng định nghĩa doanh thu ở mọi widget. Khác nhau một điều kiện là dashboard tự mâu thuẫn.

KPI so kỳ trước

Một truy vấn, hai cột thời gian:

WITH doanh_thu AS (
  SELECT
    o.order_date,
    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 NOT IN ('CANCELLED', 'RETURNED')
  GROUP BY o.order_date
)
SELECT
  SUM(doanh_thu) FILTER (WHERE order_date >= CURRENT_DATE - INTERVAL '30 days') AS ky_nay,
  SUM(doanh_thu) FILTER (WHERE order_date >= CURRENT_DATE - INTERVAL '60 days'
                         AND order_date <  CURRENT_DATE - INTERVAL '30 days') AS ky_truoc
FROM doanh_thu;

Hai mốc thời gian dạng nửa mở (>= và <) để không đếm trùng ngày biên. Kết quả trả về hai số, giao diện tự tính phần trăm thay đổi.

Xu hướng theo ngày

SELECT
  date_trunc('day', o.order_date)::date AS ngay,
  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 NOT IN ('CANCELLED', 'RETURNED')
  AND o.order_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY 1
ORDER BY 1;

Lưu ý:

• date_trunc trả về timestamp; ép về date để nhãn trục sạch.
• Ngày không có đơn sẽ biến mất khỏi kết quả. Biểu đồ đường sẽ nối tắt qua khoảng trống. Nếu cần trục liên tục, dùng generate_series và LEFT JOIN.
• Dashboard nên trả đủ dải ngày với giá trị 0 thay vì để giao diện tự suy đoán.

Bổ sung dải ngày liên tục:

WITH ngay AS (
  SELECT generate_series(
    CURRENT_DATE - INTERVAL '89 days',
    CURRENT_DATE,
    INTERVAL '1 day'
  )::date AS ngay
),
doanh_thu AS (
  SELECT o.order_date::date AS ngay,
         SUM(oi.quantity * oi.unit_price) AS tien
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  WHERE o.status NOT IN ('CANCELLED', 'RETURNED')
  GROUP BY 1
)
SELECT n.ngay, COALESCE(d.tien, 0) AS doanh_thu
FROM ngay n
LEFT JOIN doanh_thu d USING (ngay)
ORDER BY 1;

Phân rã và top N

-- Doanh thu theo thành phố
SELECT c.city, SUM(oi.quantity * oi.unit_price) AS doanh_thu
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN customers c ON c.id = o.customer_id
WHERE o.status NOT IN ('CANCELLED', 'RETURNED')
  AND o.order_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY 1
ORDER BY 2 DESC;

-- Top 10 sản phẩm
SELECT p.name, SUM(oi.quantity) AS so_luong,
       SUM(oi.quantity * oi.unit_price) AS doanh_thu
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
JOIN products p ON p.id = oi.product_id
WHERE o.status NOT IN ('CANCELLED', 'RETURNED')
  AND o.order_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY 1
ORDER BY 3 DESC
LIMIT 10;

Lỗi join làm phồng số liệu

Nếu join thêm bảng một-nhiều (ví dụ join cả order_items và một bảng khuyến mãi theo đơn), mỗi dòng đơn bị nhân lên và doanh thu tăng giả. Cách xử lý:

• Tổng hợp ở đúng mức hạt (grain) trước khi join.
• Dùng CTE riêng cho từng phép tính rồi join theo khóa duy nhất.
• Nếu cần đếm đơn, dùng COUNT(DISTINCT o.id) thay COUNT(*).

Ví dụ tổng hợp trước:

WITH don AS (
  SELECT o.id, o.customer_id, SUM(oi.quantity * oi.unit_price) AS tien
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  GROUP BY o.id, o.customer_id
)
SELECT c.city, SUM(d.tien)
FROM don d
JOIN customers c ON c.id = d.customer_id
GROUP BY 1;

Hiệu năng và làm mới

Dashboard chạy truy vấn mỗi lần tải trang. Với dữ liệu lớn:

• Chỉ mục: orders(order_date), orders(status), order_items(order_id), order_items(product_id).
• Tổng hợp sẵn: materialized view theo ngày cho các widget xu hướng; làm mới theo lịch (ví dụ mỗi 15 phút hoặc mỗi giờ).
• Giới hạn: mọi truy vấn widget phải có LIMIT hoặc khoảng ngày cố định.
• Cache: cache kết quả ngắn (1–5 phút) cho widget không cần thời gian thực.

Nhịp làm mới nên khớp nhịp quyết định, không khớp nhịp "có thể". Dashboard xem hằng ngày không cần cập nhật mỗi giây; dashboard giám sát sự cố thì cần gần thời gian thực.

Múi giờ

Một lỗi âm thầm: máy chủ lưu UTC, người xem ở Việt Nam (UTC+7). "Hôm nay" trên dashboard có thể bắt đầu lúc 7 giờ sáng theo giờ Việt Nam.

Cách xử lý:

• Lưu UTC trong cơ sở dữ liệu.
• Chuyển đổi ở lớp truy vấn: order_date AT TIME ZONE 'Asia/Ho_Chi_Minh'.
• Ghi rõ múi giờ trên dashboard ("giờ Việt Nam").
• Chốt một múi giờ cho toàn bộ widget, không trộn.

Thực hành

1. Viết truy vấn KPI doanh thu 7 ngày so 7 ngày trước.
2. Viết xu hướng 30 ngày có đủ ngày trống bằng generate_series.
3. Viết top 5 thành phố theo doanh thu.
4. Tìm một truy vấn trong dự án của bạn có nguy cơ phồng số do join.
5. Chọn nhịp làm mới cho dashboard bán hàng và dashboard giám sát lỗi.

Lỗi thường gặp

Tóm tắt

• Dùng một lớp dữ liệu chung cho mọi widget.
• KPI so kỳ trước dùng hai cửa sổ thời gian nửa mở.
• Xu hướng cần đủ dải ngày, kể cả ngày 0.
• Tổng hợp trước khi join để tránh phồng số.
• Chỉ mục, tổng hợp sẵn, cache và múi giờ là bốn điểm hiệu năng chính.