Ví dụ: tính trung bình hóa đơn đơn hàng trong 3 tháng gần nhất

Trong bài này tụi mình sẽ xem một ví dụ thực tế khá thú vị nha.

Trung bình hóa đơn — là chỉ số cho biết trung bình một khách hàng chi bao nhiêu cho một lần mua. Đây là một trong những chỉ số kinh doanh quan trọng nhất, giúp bạn:

• phân tích sự thay đổi sức mua của khách,
• phát hiện xu hướng bán hàng,
• đánh giá hiệu quả các chiến dịch marketing.

Đặt vấn đề

Giả sử tụi mình có một database với bảng orders, nơi lưu các đơn hàng. Mục tiêu của tụi mình:

1. Tính trung bình hóa đơn cho các đơn hàng trong 3 tháng gần nhất.
2. Tự động hóa việc tính này bằng procedure.
3. Lưu kết quả vào một bảng riêng để phân tích sau.

Mở rộng database: cấu trúc bảng orders

Đầu tiên, hãy chắc là tụi mình có bảng với đủ dữ liệu cần thiết. Đây là cấu trúc bảng orders có thể như sau:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount NUMERIC(10, 2) NOT NULL
);

• order_id — mã đơn hàng duy nhất.
• customer_id — khách hàng đã đặt đơn.
• order_date — ngày đặt đơn.
• total_amount — tổng số tiền của đơn hàng.

Làm ví dụ, tụi mình thêm vài dòng vào bảng để có dữ liệu test:

INSERT INTO orders (customer_id, order_date, total_amount)
VALUES
    (1, '2023-07-15', 100.00),
    (2, '2023-08-10', 200.50),
    (3, '2023-09-01', 150.75),
    (1, '2023-09-20', 300.00),
    (4, '2023-09-25', 250.00),
    (5, '2023-10-05', 450.00);

Tính trung bình hóa đơn thủ công

Trước khi tự động hóa, tụi mình viết truy vấn cơ bản để tính trung bình hóa đơn trong 3 tháng gần nhất. Sẽ dùng ngày hiện tại (CURRENT_DATE) và hàm AVG() để tính trung bình.

SELECT ROUND(AVG(total_amount), 2) AS avg_check
FROM orders
WHERE order_date >= (CURRENT_DATE - INTERVAL '3 months');

Ở đây có gì:

• AVG(total_amount) — hàm tổng hợp tính trung bình total_amount.
• CURRENT_DATE - INTERVAL '3 months' — lọc các đơn hàng trong 3 tháng gần nhất.
• ROUND(..., 2) — làm tròn kết quả đến 2 số thập phân.

Kết quả truy vấn sẽ kiểu như này:

Tự động hóa bằng procedure

Giờ nhiệm vụ là tạo procedure tự động tính toán này, và log kết quả vào một bảng riêng. Đầu tiên tạo bảng lưu log analytics.

Tạo bảng log_analytics

CREATE TABLE log_analytics (
    log_id SERIAL PRIMARY KEY,
    log_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    metric_name VARCHAR(50),
    metric_value NUMERIC(10, 2)
);

• log_date — ngày giờ ghi log.
• metric_name — tên chỉ số (ở đây là "averagecheck_3_months").
• metric_value — giá trị chỉ số đã tính.

Tạo procedure

Giờ tụi mình viết procedure sẽ:

1. Tính trung bình hóa đơn trong 3 tháng gần nhất.
2. Lưu kết quả vào bảng log_analytics.

CREATE OR REPLACE FUNCTION calculate_average_check()
RETURNS VOID AS $$
DECLARE
    avg_check NUMERIC(10, 2);
BEGIN
    -- Bước 1: Tính trung bình hóa đơn
    SELECT ROUND(AVG(total_amount), 2)
    INTO avg_check
    FROM orders
    WHERE order_date >= (CURRENT_DATE - INTERVAL '3 months');

    -- Bước 2: Ghi log kết quả
    INSERT INTO log_analytics (metric_name, metric_value)
    VALUES ('average_check_3_months', avg_check);

    -- In thông tin debug (tùy chọn)
    RAISE NOTICE 'Trung bình hóa đơn: %', avg_check;
END;
$$ LANGUAGE plpgsql;

Bây giờ bạn có thể gọi function này, nó sẽ tự động ghi kết quả vào bảng log_analytics:

SELECT calculate_average_check();

Tự động hóa bằng scheduler

Bài trước tụi mình đã cài scheduler rồi. Nếu bạn dùng Linux — đó là extension pg_cron; nếu dùng Windows hoặc macOS — chắc bạn đã setup chạy qua scheduler hệ thống (cron hoặc Task Scheduler). Giờ mọi thứ sẵn sàng, cùng gắn procedure vào lịch chạy.

Nếu bạn dùng Linux và pg_cron nhớ bật extension này trong database cần thiết:

CREATE EXTENSION IF NOT EXISTS pg_cron;

(Nhắc lại: cài pg_cron và cấu hình shared_preload_libraries đã nói ở bài trước rồi nhé.)

Giờ bạn có thể lên lịch chạy function calculate_average_check() — ví dụ, mỗi ngày lúc 0h:

SELECT cron.schedule(
    'daily_avg_check',
    '0 0 * * *',
    $$ SELECT calculate_average_check(); $$
);

Giải thích:

• 'daily_avg_check' — tên job;
• '0 0 * * *' — biểu thức cron chạy lúc 00:00 mỗi ngày;
• lệnh trong $$ — SQL sẽ được thực thi.

Nếu bạn dùng Windows hoặc macOS, pg_cron không chạy được (Windows — không hỗ trợ, macOS — phải build thủ công). Nhưng bạn đã setup scheduler hệ thống rồi — chỉ cần gắn SQL file vào.

1. Tạo file với truy vấn:

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

2. Dùng psql để chạy file theo lịch:

   • Trên Linux/macOS:

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

     (được thêm qua crontab -e)
   • Trên Windows Task Scheduler:
     • Chỉ đường dẫn tới psql.exe.
     • Tham số:
       -U postgres -d your_database -f "C:\path\to\script.sql"

Như vậy, dù bạn dùng hệ điều hành nào, procedure sẽ tự động chạy và log trung bình hóa đơn vào bảng log_analytics đều đặn. Nếu chưa chắc mình dùng cách nào, quay lại bài trước — ở đó có hướng dẫn cài và cấu hình scheduler cho từng nền tảng.

Kiểm tra và phân tích kết quả

Xem thử tụi mình đã làm được gì. Lấy dữ liệu từ bảng log_analytics:

SELECT * FROM log_analytics ORDER BY log_date DESC;

Ví dụ kết quả:

Giờ tụi mình đã có log tất cả các lần tính trung bình hóa đơn! Dữ liệu này có thể dùng để tạo báo cáo hoặc phân tích sự thay đổi chỉ số theo thời gian.

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

Làm việc với procedure phân tích tính trung bình hóa đơn có thể gặp vài lỗi phổ biến.

Một lỗi là quên xử lý kết quả rỗng. Nếu 3 tháng gần nhất không có đơn hàng, hàm AVG() sẽ trả về NULL, có thể gây lỗi khi log. Để tránh, dùng COALESCE():

SELECT ROUND(COALESCE(AVG(total_amount), 0), 2) AS avg_check

Lỗi nữa là dữ liệu bảng orders không hợp lệ. Ví dụ, số tiền âm hoặc ngày không hợp lệ. Nên thường xuyên kiểm tra dữ liệu hoặc thêm constraint ở database (ví dụ, CHECK (total_amount > 0)).

Chúc mừng, giờ bạn đã có procedure hoàn chỉnh tự động tính trung bình hóa đơn 3 tháng gần nhất và lưu kết quả để phân tích sau. Đây chỉ là một ví dụ nhỏ về cách PostgreSQL và PL/pgSQL giúp tự động hóa các bài toán phân tích. Bài sau tụi mình sẽ tiếp tục với các kịch bản phân tích phức tạp hơn. Hẹn gặp lại!