Xây dựng báo cáo phân tích với PL/pgSQL

Báo cáo phân tích là các biểu diễn dữ liệu có hệ thống giúp đưa ra quyết định. Ví dụ:

• Quản lý muốn xem doanh thu tháng trước là bao nhiêu.
• Analyst tìm kiếm xu hướng trên thị trường.
• Dev theo dõi hiệu suất của app.

Hãy tưởng tượng bạn là một đầu bếp trưởng quản lý một nhà hàng siêu to. Để biết món nào được gọi nhiều nhất, bạn cần báo cáo. PostgreSQL lúc này là database công thức và đơn hàng của bạn, còn PL/pgSQL (procedure) là trợ lý bếp giúp bạn tự động hóa việc phân tích đơn hàng.

Cơ bản về xây dựng báo cáo phân tích

Báo cáo phân tích là công cụ để tổng hợp, lọc, sắp xếp và tổ chức dữ liệu nhằm lấy thông tin hữu ích. Thường thì cấu trúc báo cáo gồm các bước sau:

1. Chuẩn bị dữ liệu: lấy thông tin từ bảng, lọc và xử lý sơ bộ.
2. Tổng hợp dữ liệu: tính toán các chỉ số (giá trị trung bình, tổng doanh số, v.v.).
3. Định dạng: sắp xếp dữ liệu cho dễ nhìn.
4. Xuất kết quả: trình bày báo cáo cho user hoặc ghi vào bảng để lưu trữ.

Mỗi bước này đều có thể làm bằng procedure PL/pgSQL.

Tạo procedure cho báo cáo phân tích

Cùng xem ví dụ cơ bản tạo báo cáo phân tích. Giả sử bạn có bảng orders lưu dữ liệu đơn hàng:

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

Nhiệm vụ: tạo báo cáo tổng doanh số trong tháng chỉ định. Tức là bạn muốn thấy:

• Tháng.
• Tổng doanh số tháng đó.

Cấu trúc procedure

Kế hoạch cho procedure của chúng ta (đừng lo, code PL/pgSQL không cắn ai đâu):

1. Nhận tham số đầu vào — tháng.
2. Lấy dữ liệu tháng đó từ bảng orders.
3. Tính tổng doanh số.
4. Trả về kết quả.

Code procedure

Ví dụ code:

CREATE OR REPLACE FUNCTION monthly_sales_report(p_month DATE)
RETURNS TABLE (
    month DATE,
    total_sales NUMERIC(10, 2)
) AS $$
BEGIN
    -- Lấy dữ liệu tháng chỉ định và tổng hợp lại
    RETURN QUERY
    SELECT 
        DATE_TRUNC('month', o.order_date) AS month,
        SUM(o.total_amount) AS total_sales
    FROM orders o
    WHERE DATE_TRUNC('month', o.order_date) = DATE_TRUNC('month', p_month)
    GROUP BY 1;
END;
$$ LANGUAGE plpgsql;

1. Tham số đầu vào: p_month — kiểu date. Dùng để lọc dữ liệu theo tháng.
2. RETURN QUERY: cái này siêu tiện, trả dữ liệu trực tiếp từ procedure.
3. DATE_TRUNC: dùng để làm tròn order_date về đầu tháng.
4. SUM: hàm tổng hợp để tính tổng đơn hàng.
5. GROUP BY: gom nhóm theo tháng vì báo cáo theo tháng.

Bây giờ bạn có thể gọi function này:

SELECT * FROM monthly_sales_report('2023-08-01');

Và sẽ ra kiểu như này:

Function này là nền tảng. Giờ thử nâng cấp nhé!

Tạo báo cáo phức tạp hơn

Giờ tưởng tượng bạn muốn chia doanh số theo khách hàng. Tức là báo cáo sẽ hiển thị:

• Khách hàng
• Tháng
• Tổng đơn hàng của khách đó trong tháng

Sửa procedure

CREATE OR REPLACE FUNCTION customer_monthly_report(p_month DATE)
RETURNS TABLE (
    customer_id INT,
    month DATE,
    total_sales NUMERIC(10, 2)
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        o.customer_id,
        DATE_TRUNC('month', o.order_date) AS month,
        SUM(o.total_amount) AS total_sales
    FROM orders o
    WHERE DATE_TRUNC('month', o.order_date) = DATE_TRUNC('month', p_month)
    GROUP BY o.customer_id, DATE_TRUNC('month', o.order_date);
END;
$$ LANGUAGE plpgsql;

Gọi procedure này:

SELECT * FROM customer_monthly_report('2023-08-01');

Kết quả có thể như sau:

Dùng bảng tạm (temporary table)

Đôi khi khi làm báo cáo phức tạp, dùng bảng tạm rất tiện. Ví dụ, nếu cần xử lý dữ liệu trung gian.

CREATE OR REPLACE FUNCTION temp_table_example(p_month DATE)
RETURNS VOID AS $$
BEGIN
    -- Tạo bảng tạm
    CREATE TEMP TABLE temp_sales AS
    SELECT
        customer_id,
        DATE_TRUNC('month', order_date) AS month,
        SUM(total_amount) AS total_sales
    FROM orders
    WHERE DATE_TRUNC('month', order_date) = DATE_TRUNC('month', p_month)
    GROUP BY customer_id, DATE_TRUNC('month', order_date);

    -- Làm thêm tính toán hoặc thao tác với bảng này
    -- Ví dụ: in ra top-3 khách hàng có tổng đơn hàng cao nhất
    RAISE NOTICE 'Top-3 khách hàng tháng %:', p_month;
    FOR record IN
        SELECT customer_id, total_sales
        FROM temp_sales
        ORDER BY total_sales DESC
        LIMIT 3
    LOOP
        RAISE NOTICE 'Khách hàng: %, Tổng: %', record.customer_id, record.total_sales;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

Ở đây bảng tạm temp_sales dùng để lưu kết quả trung gian.

Mẹo nhỏ hữu ích

1. Tối ưu hóa: dùng index để tăng tốc truy vấn.
2. Lỗi chia cho 0: luôn kiểm tra mẫu số để khỏi "toang" báo cáo.
3. Định dạng ngày tháng: dùng function như TO_CHAR để xuất ra cho đẹp.

Hy vọng bạn không buồn ngủ quá! Phía trước còn nhiều bài toán thú vị và khó hơn, đừng lơ là nhé!