Ghi log dữ liệu phân tích vào các bảng riêng biệt

Giả sử bạn đang làm báo cáo doanh số trong tuần. Tính toán xong, khách hàng vui vẻ. Nhưng một tháng sau, người ta hỏi: "Bạn có thể cho xem lại báo cáo đó không?" Nếu bạn không lưu dữ liệu từ trước, thì hoặc là phải phục hồi thủ công, hoặc trả lời "không thể". Cực kỳ bất tiện và còn ảnh hưởng đến uy tín của bạn nữa.

Ghi log dữ liệu phân tích giải quyết mấy vấn đề quan trọng:

• Lưu lịch sử: bạn ghi lại các chỉ số chính (ví dụ: doanh thu, số lượng đơn hàng) theo từng giai đoạn.
• Audit và chẩn đoán: nếu có gì sai, luôn có thể kiểm tra lại dữ liệu đã ghi log.
• So sánh dữ liệu: thêm dấu thời gian, bạn có thể phân tích sự thay đổi của chỉ số theo thời gian.
• Tái sử dụng dữ liệu: các chỉ số đã lưu có thể dùng cho các bài toán phân tích khác.

Ý tưởng chính: bảng log_analytics

Để ghi log dữ liệu phân tích, mình tạo một bảng riêng để lưu tất cả các chỉ số quan trọng. Mỗi kết quả mới là một dòng mới trong bảng. Để dễ hiểu hơn, bắt đầu với kịch bản cơ bản nhé.

Ví dụ cấu trúc bảng

Trong bảng log_analytics mình sẽ lưu dữ liệu về các báo cáo. Đây là cấu trúc (DDL — Data Definition Language):

CREATE TABLE log_analytics (
    log_id SERIAL PRIMARY KEY, -- Định danh duy nhất cho bản ghi
    report_name TEXT NOT NULL, -- Tên báo cáo hoặc chỉ số
    report_date DATE DEFAULT CURRENT_DATE, -- Ngày liên quan đến báo cáo
    category TEXT, -- Nhóm dữ liệu (ví dụ: khu vực, sản phẩm)
    metric_value NUMERIC NOT NULL, -- Giá trị chỉ số
    created_at TIMESTAMP DEFAULT NOW() -- Ngày giờ ghi log
);

• log_id: định danh chính của bản ghi.
• report_name: tên báo cáo hoặc chỉ số, ví dụ "Weekly Sales".
• report_date: ngày liên quan đến chỉ số. Ví dụ, nếu là doanh số ngày 1 tháng 10 thì ở đây sẽ là 2023-10-01.
• category: giúp nhóm dữ liệu, ví dụ theo khu vực.
• metric_value: giá trị số cho chỉ số báo cáo.
• created_at: dấu thời gian ghi log.

Ví dụ ghi dữ liệu vào log_analytics

Giả sử bạn đã tính doanh thu tháng 10 cho khu vực "Bắc". Làm sao lưu giá trị này?

INSERT INTO log_analytics (report_name, report_date, category, metric_value)
VALUES ('Monthly Revenue', '2023-10-01', 'North', 15000.75);

Kết quả:

Tạo procedure để ghi log

Chứ mỗi tuần/tháng lại tự tay ghi dữ liệu thì chịu rồi. Nên mình tự động hóa bằng procedure nhé.

Tạo một procedure đơn giản để ghi log doanh thu:

CREATE OR REPLACE FUNCTION log_monthly_revenue(category TEXT, revenue NUMERIC)
RETURNS VOID AS $$
BEGIN
    INSERT INTO log_analytics (report_name, report_date, category, metric_value)
    VALUES ('Monthly Revenue', CURRENT_DATE, category, revenue);
END;
$$ LANGUAGE plpgsql;

Giờ procedure log_monthly_revenue nhận hai tham số:

• category: nhóm dữ liệu, ví dụ khu vực.
• revenue: giá trị doanh thu

Gọi function này để ghi doanh thu như sau:

SELECT log_monthly_revenue('North', 15000.75);

Kết quả sẽ giống như khi insert bằng INSERT.

Ý tưởng mở rộng cho cấu trúc log

Đôi khi chỉ số chính không chỉ có một mà là nhiều cái cùng lúc. Xem thử cách lưu thêm các chỉ số như số lượng đơn hàng hoặc giá trị trung bình đơn nhé.

Cập nhật cấu trúc bảng:

CREATE TABLE log_analytics_extended (
    log_id SERIAL PRIMARY KEY,
    report_name TEXT NOT NULL,
    report_date DATE DEFAULT CURRENT_DATE,
    category TEXT,
    metric_values JSONB NOT NULL, -- Lưu chỉ số dạng JSONB
    created_at TIMESTAMP DEFAULT NOW()
);

Điểm mới ở đây là dùng kiểu JSONB để lưu nhiều chỉ số trong một trường.

Ví dụ ghi vào bảng mở rộng

Giả sử cần lưu cùng lúc ba chỉ số: doanh thu, số đơn hàng và giá trị trung bình đơn. Ví dụ query:

INSERT INTO log_analytics_extended (report_name, category, metric_values)
VALUES (
    'Monthly Revenue',
    'North',
    '{"revenue": 15000.75, "orders": 45, "avg_check": 333.35}'::jsonb
);

Kết quả:

Ví dụ dùng log: phân tích doanh thu

Giả sử muốn biết tổng doanh thu của tất cả khu vực trong tháng 10. Query như sau:

SELECT SUM((metric_values->>'revenue')::NUMERIC) AS total_revenue
FROM log_analytics_extended
WHERE report_date BETWEEN '2023-10-01' AND '2023-10-31';

Ví dụ dùng log: xu hướng theo khu vực

Phân tích sự thay đổi doanh thu theo khu vực:

SELECT category, report_date, (metric_values->>'revenue')::NUMERIC AS revenue
FROM log_analytics_extended
ORDER BY category, report_date;

Xử lý lỗi phổ biến

Khi ghi log dữ liệu phân tích, dễ gặp mấy lỗi này. Cùng bàn cách tránh nhé.

• Lỗi: quên điền category hoặc ngày. Nên đặt giá trị mặc định trong bảng, ví dụ DEFAULT CURRENT_DATE.
• Lỗi: trùng bản ghi. Để tránh trùng, thêm unique index:

  CREATE UNIQUE INDEX unique_log_entry
  ON log_analytics (report_name, report_date, category);
  

• Lỗi: tính chỉ số bị chia cho 0. Luôn kiểm tra mẫu số! Dùng NULLIF:

  SELECT revenue / NULLIF(order_count, 0) AS avg_check FROM orders;
  

Ứng dụng trong dự án thực tế

Ghi log dữ liệu phân tích cực kỳ hữu ích cho nhiều lĩnh vực:

• Retail: theo dõi doanh thu và bán hàng theo nhóm sản phẩm.
• Dịch vụ: phân tích tải server hoặc ứng dụng.
• Tài chính: kiểm soát giao dịch và chi tiêu.

Những dữ liệu này giúp bạn không chỉ giải thích được chuyện gì đã xảy ra mà còn ra quyết định dựa trên những gì thấy trong log. Giờ bạn đã biết cách ghi lại lịch sử dữ liệu phân tích trong PostgreSQL rồi nhé. Tuyệt vời, còn nhiều kiến thức hay phía trước nữa!