用 PL/pgSQL 製作分析報表

分析報表就是把資料有系統地整理出來，幫助大家做決策。舉例來說：

• 經理想知道上個月的營收是多少。
• 分析師在找市場趨勢。
• 工程師在監控 app 的效能。

想像你是個大餐廳的主廚。你想知道哪些菜最常被點，就需要一份報表。PostgreSQL 就像你的食譜跟訂單資料庫，而 PL/pgSQL（procedures）就是你廚房裡自動幫你分析訂單的小幫手。

分析報表的基本流程

分析報表就是一種把資料做 aggregation、filter、sort、整理，最後得到有用資訊的工具。通常報表的結構會有這幾個步驟：

1. 資料準備：從資料表撈資料、做過濾跟前處理。
2. 資料彙總：算指標（像平均消費、總銷售額等等）。
3. 格式化：把資料整理成好讀的樣子。
4. 輸出結果：把報表給使用者看，或寫進資料表存起來。

這些步驟都可以用 PL/pgSQL 的 procedures 來實作。

寫一個分析報表的 procedure

我們來看個基本範例。假設有個 orders 資料表，裡面存著訂單資料：

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

我們的任務：做一份指定月份的銷售總額報表。也就是說，我們想看到：

• 月份。
• 這個月的總銷售額。

procedure 的架構

這是我們的小 procedure 計畫（別怕，PL/pgSQL 寫起來沒那麼恐怖）：

1. 接收一個輸入參數——月份。
2. 從 orders 表撈出這個月的資料。
3. 算出總銷售額。
4. 回傳結果。

procedure 實作

範例 code：

CREATE OR REPLACE FUNCTION monthly_sales_report(p_month DATE)
RETURNS TABLE (
    month DATE,
    total_sales NUMERIC(10, 2)
) AS $$
BEGIN
    -- 撈出指定月份的資料並做彙總
    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. 輸入參數：p_month——日期。我們用它來過濾月份。
2. RETURN QUERY：這個超方便，可以直接從 procedure 回傳資料。
3. DATE_TRUNC：把 order_date 四捨五入到月初。
4. SUM：aggregate function，算所有訂單的總和。
5. GROUP BY：依月份分組，因為我們是做月報。

現在我們可以這樣呼叫 function：

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

你會拿到類似這樣的結果：

這個 function 算是基礎款。來點進階的吧！

做更進階的報表

現在假設我們想要把銷售額依客戶拆開。也就是說，報表要顯示：

• 客戶
• 月份
• 這個客戶這個月的訂單總額

來改一下 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;

現在呼叫 procedure：

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

結果可能會像這樣：

用臨時表

有時候做複雜報表時，用臨時表會很方便。比如說你要處理中間結果。

CREATE OR REPLACE FUNCTION temp_table_example(p_month DATE)
RETURNS VOID AS $$
BEGIN
    -- 建一個臨時表
    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);

    -- 可以對這個表做進一步運算或處理
    -- 比如說，列出這個月訂單金額前三名的客戶
    RAISE NOTICE '這個月 % 的前三大客戶：', p_month;
    FOR record IN
        SELECT customer_id, total_sales
        FROM temp_sales
        ORDER BY total_sales DESC
        LIMIT 3
    LOOP
        RAISE NOTICE '客戶: %, 金額: %', record.customer_id, record.total_sales;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

這裡的臨時表 temp_sales 就是用來存中間結果。

小技巧

1. 優化：用 index 讓查詢更快。
2. 除以零的 bug：記得檢查除數，不然報表會爆掉。
3. 日期格式化：用 TO_CHAR 這類 function 讓輸出更好看。

希望你沒打太多哈欠啦！接下來還有更難更有趣的題目，別鬆懈喔！