範例：計算最近 3 個月訂單的平均消費金額

這堂課我們來看一個很實用的例子。

平均消費金額（average check）是一個指標，代表客戶每次購物平均會花多少錢。這是很重要的商業指標，可以：

• 分析消費能力的變化，
• 找出銷售趨勢，
• 評估行銷活動的成效。

任務說明

假設我們有一個資料庫，裡面有個 orders 表，存放訂單。我們的目標：

1. 計算最近三個月的訂單平均消費金額。
2. 用 procedure 自動化這個計算。
3. 把結果存到另一個表，方便之後分析。

擴充資料庫：orders 表結構

先來確認我們有需要的資料表。orders 表的結構大概長這樣：

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 — 訂單的唯一識別碼。
• customer_id — 下訂單的客戶。
• order_date — 訂單成立的日期。
• total_amount — 訂單總金額。

舉個例子，先加幾筆資料進去，這樣才有東西可以玩：

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);

手動計算平均消費金額

在自動化之前，先寫個基本查詢，算出最近 3 個月的平均消費金額。我們會用 CURRENT_DATE 跟 AVG() 這個 function 來算平均。

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

這裡發生了什麼事：

• AVG(total_amount) — 聚合函數，算 total_amount 的平均值。
• CURRENT_DATE - INTERVAL '3 months' — 找出最近三個月的訂單。
• ROUND(..., 2) — 結果四捨五入到小數點後兩位。

查詢結果大概會長這樣：

用 procedure 自動化

接下來我們要寫一個 procedure，自動做這個計算，然後把結果寫進另一個 log 表。先來建一個存 log 的表。

建立 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 — 記錄的日期和時間。
• metric_name — 指標名稱（這裡是 "averagecheck_3_months"）。
• metric_value — 算出來的指標值。

寫 procedure

現在來寫一個 procedure，它會：

1. 算出最近三個月的平均消費金額。
2. 把結果存到 log_analytics 表。

CREATE OR REPLACE FUNCTION calculate_average_check()
RETURNS VOID AS $$
DECLARE
    avg_check NUMERIC(10, 2);
BEGIN
    -- 步驟 1：計算平均消費金額
    SELECT ROUND(AVG(total_amount), 2)
    INTO avg_check
    FROM orders
    WHERE order_date >= (CURRENT_DATE - INTERVAL '3 months');

    -- 步驟 2：寫 log
    INSERT INTO log_analytics (metric_name, metric_value)
    VALUES ('average_check_3_months', avg_check);

    -- 除錯訊息（可選）
    RAISE NOTICE '平均消費金額: %', avg_check;
END;
$$ LANGUAGE plpgsql;

現在你可以直接呼叫這個 function，它會自動把結果寫進 log_analytics：

SELECT calculate_average_check();

用排程器自動化

上一堂課我們已經安裝過排程器。如果你用 Linux，就是 pg_cron 這個 extension；如果你用 Windows 或 macOS，應該是用系統的排程器（cron 或 Task Scheduler）。現在都準備好了，來把 procedure 加進排程。

如果你在 Linux 用 pg_cron，記得要在對的資料庫啟用這個 extension：

CREATE EXTENSION IF NOT EXISTS pg_cron;

（提醒一下：pg_cron 的安裝跟 shared_preload_libraries 的設定，上次課程已經講過了。）

現在可以排每天凌晨執行 calculate_average_check()：

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

說明：

• 'daily_avg_check' — 任務名稱；
• '0 0 * * *' — cron 表達式，每天 00:00 執行；
• $$ 裡面是要執行的 SQL。

如果你用 Windows 或 macOS，pg_cron 在這些系統不能用（Windows 完全不行，macOS 要自己編譯）。但你已經設定好系統排程器了，剩下就是連 SQL 檔案。

1. 先建立一個查詢檔案：

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

2. 用 psql 定時執行這個檔案：

   • 在 Linux/macOS:

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

     （加到 crontab -e）
   • 在 Windows Task Scheduler:
     • 指定 psql.exe 的路徑。
     • 參數填：
       -U postgres -d your_database -f "C:\path\to\script.sql"

這樣不管你用哪個系統，procedure 都會自動定時執行，把平均消費金額寫進 log_analytics 表。如果你不確定自己用哪種方式，可以回去看上一堂課，裡面有各平台的安裝和設定教學。

檢查和分析結果

來看看成果吧。查詢 log_analytics 表的資料：

SELECT * FROM log_analytics ORDER BY log_date DESC;

範例結果：

現在我們有所有平均消費金額的 log！這些資料可以拿來做報表，或分析指標隨時間的變化。

常見錯誤與避免方法

寫平均消費金額的分析 procedure，常常會遇到幾個典型錯誤。

其中一個是忘記處理空結果。如果最近三個月沒有訂單，AVG() 會回傳 NULL，這樣寫 log 可能會出問題。要避免這種情況，可以用 COALESCE()：

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

還有一個錯誤是 orders 表裡有不正確的資料。比如訂單金額是負數，或日期不對。建議定期檢查資料，或在資料庫層加限制（像 CHECK (total_amount > 0)）。

恭喜你，現在你有一個完整的 procedure，可以自動計算最近三個月的平均消費金額，還會把結果存下來方便分析。這只是 PostgreSQL 和 PL/pgSQL 幫你自動化分析任務的其中一個例子。下堂課我們會繼續講更進階的分析情境。下次見囉！