Tự động tạo báo cáo theo lịch

Khi bạn làm việc với database nhỏ, việc tự tay chạy query hoặc procedure để tạo báo cáo cũng không có gì to tát. Nhưng ngoài đời thực, database sẽ phình to đến mức mọi task lặp lại đều nên tự động hóa. Thử tưởng tượng mỗi ngày bạn bị nhờ làm báo cáo doanh số. Dù query chỉ mất hai phút, một năm bạn sẽ tốn hơn 12 tiếng chỉ để chạy nó. Thà ngồi uống cà phê còn hơn, để procedure tự động lo hết cho bạn.

Tự động hóa sẽ giúp bạn:

• Giảm bớt thao tác tay chân.
• Đảm bảo báo cáo luôn đều đặn (ví dụ: báo cáo hàng ngày, hàng tuần).
• Giảm tối đa nguy cơ lỗi do con người.
• Tăng độ tin cậy cho báo cáo: luôn được tạo đúng theo tham số đã định.

Các bước chính để tự động tạo báo cáo

Việc tự động chạy báo cáo gồm các bước sau:

1. Tạo procedure bằng PL/pgSQL để sinh báo cáo.
2. Cấu hình log kết quả (nếu cần).
3. Dùng task scheduler để chạy procedure theo lịch.

Giờ mình sẽ làm từng bước nhé!

Tạo procedure để sinh báo cáo

Đầu tiên, mình sẽ tạo một procedure đơn giản tính tổng doanh thu của tất cả đơn hàng trong ngày và lưu kết quả vào bảng log. Bảng log của mình đã có sẵn (gọi là sales_report_log):

CREATE TABLE sales_report_log (
    report_date DATE NOT NULL,
    total_sales NUMERIC NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Bây giờ tạo procedure bằng PL/pgSQL nhé:

CREATE OR REPLACE FUNCTION generate_daily_sales_report()
RETURNS VOID AS $$
BEGIN
    -- Tính tổng doanh thu trong ngày hiện tại
    INSERT INTO sales_report_log (report_date, total_sales)
    SELECT CURRENT_DATE, SUM(order_total)
    FROM orders
    WHERE order_date = CURRENT_DATE;

    RAISE NOTICE 'Báo cáo cho % đã được tạo thành công', CURRENT_DATE;
END;
$$ LANGUAGE plpgsql;

Ở đây có gì:

• Mình dùng hàm tổng hợp SUM() để tính tổng doanh thu từ bảng orders.
• Ngày báo cáo (report_date) luôn là ngày hiện tại (CURRENT_DATE).
• Kết quả được lưu vào bảng sales_report_log.
• Dòng RAISE NOTICE để debug: nó báo là báo cáo đã tạo thành công.

Test procedure

Trước khi tự động hóa procedure này, luôn nên test thủ công trước. Chạy function nhé:

SELECT generate_daily_sales_report();

Giờ kiểm tra nội dung bảng sales_report_log:

SELECT * FROM sales_report_log;

Nếu bạn thấy dòng có ngày hiện tại và tổng doanh thu đúng — chúc mừng, function của bạn chạy ngon rồi!

Tự động hóa task trong PostgreSQL

Đôi khi database nên tự làm gì đó: chạy báo cáo, dọn dữ liệu cũ hoặc cập nhật aggregate theo lịch. PostgreSQL cho phép làm việc này bằng extension pg_cron hoặc dùng task scheduler ngoài — như cron của hệ thống hoặc Task Scheduler.

Nếu bạn dùng Linux, chọn pg_cron là ngon nhất. Extension này chạy SQL trực tiếp trong PostgreSQL, không cần shell hay script ngoài.

Cài pg_cron như sau (nhớ thay XX bằng version PostgreSQL của bạn):

sudo apt install postgresql-XX-cron

Sau khi cài, phải bật nó trong config. Mở postgresql.conf và thêm dòng này:

shared_preload_libraries = 'pg_cron'

Rồi restart PostgreSQL và kích hoạt extension trong database của bạn:

CREATE EXTENSION pg_cron;

Bây giờ có thể lên lịch cho task. Ví dụ, chạy function generate_daily_sales_report() mỗi ngày lúc nửa đêm:

SELECT cron.schedule(
    'daily_sales_report',
    '0 0 * * *',
    $$ SELECT generate_daily_sales_report(); $$
);

Ở đây:

• 'daily_sales_report' — tên task;
• '0 0 * * *' — lịch kiểu cron (ở đây là mỗi ngày lúc 00:00);
• SQL giữa $$ — code sẽ được chạy.

Muốn xem tất cả task đã lên lịch, dùng:

SELECT * FROM cron.job;

Nếu bạn dùng Windows hoặc macOS, thì pg_cron hoặc là không hỗ trợ (trên Windows), hoặc phải tự build từ source (trên macOS). Khá phiền, nên đa số trường hợp dùng task scheduler của hệ thống sẽ dễ hơn.

Làm như này nhé:

1. Tạo file SQL với lệnh cần chạy:

echo "SELECT generate_daily_sales_report();" > /path/to/script.sql

2. Dùng psql để chạy file đó:

psql -h localhost -U postgres -d your_database -f /path/to/script.sql

3. Thêm lệnh này vào task scheduler:

   • Trên Linux/macOS: qua crontab -e:

     0 0 * * * psql -h localhost -U postgres -d your_database -f /path/to/script.sql
     

   • Trên Windows: dùng Task Scheduler, tạo task chạy psql.exe với tham số cần thiết.

• Nếu bạn dùng Linux, cứ dùng pg_cron — tiện và tích hợp sẵn trong PostgreSQL.
• Nếu bạn dùng Windows hoặc Mac, nên dùng task scheduler hệ thống (cron hoặc Task Scheduler) và chạy SQL qua psql.

Như vậy bạn có thể tự động hóa mọi task trong PostgreSQL mà không phải vất vả gì cả.

Ví dụ về báo cáo tự động

1. Báo cáo hàng ngày theo vùng

Giả sử bạn muốn tự động tạo báo cáo doanh thu cho từng vùng. Có thể mở rộng function như sau:

CREATE OR REPLACE FUNCTION generate_regional_sales_report()
RETURNS VOID AS $$
BEGIN
    INSERT INTO regional_sales_report_log (region, report_date, total_sales)
    SELECT region, CURRENT_DATE, SUM(order_total)
    FROM orders
    WHERE order_date = CURRENT_DATE
    GROUP BY region;

    RAISE NOTICE 'Báo cáo vùng cho % đã được tạo thành công', CURRENT_DATE;
END;
$$ LANGUAGE plpgsql;

2. Báo cáo hàng tháng

Tương tự, bạn có thể tạo procedure để sinh báo cáo theo tháng. Chỉ cần đổi filter trong query:

WHERE order_date BETWEEN date_trunc('month', CURRENT_DATE)
                     AND date_trunc('month', CURRENT_DATE) + interval '1 month - 1 day';

Lỗi thường gặp và cách tránh

Khi tự động tạo báo cáo có thể gặp mấy vấn đề sau:

• Lỗi cú pháp trong function: luôn test function thủ công trước khi tự động hóa.
• Tần suất chạy task: nếu chạy quá thường xuyên có thể làm database quá tải. Hãy lên lịch hợp lý.
• Trùng dữ liệu: nếu báo cáo chạy nhiều lần/ngày, có thể bị trùng. Dùng khóa unique để tránh lặp.

Bài giảng này đã chỉ bạn cách cấu hình tự động tạo báo cáo trong PostgreSQL. Giờ bạn có thể tối ưu quy trình phân tích, dành thời gian cho việc quan trọng hơn... như debug bug, viết code hoặc mơ về việc hoàn hảo hóa query SQL của mình.