把分析數據記錄到獨立的資料表

想像一下，你正在做一份一週銷售報告。計算都做好了，客戶也很滿意。但一個月後，有人問你：「可以再給我看一下那份報告嗎？」如果你沒事先把數據存下來，就只能手動重算，或是只能說「沒辦法」。這不只麻煩，還可能影響你的信譽。

記錄分析數據可以解決幾個重要問題：

• 保存歷史紀錄：你可以把關鍵指標（像是收入、訂單數量）按時段記錄下來。
• 稽核與診斷：如果哪裡出錯，隨時可以查當時記錄了哪些數據。
• 數據比較：加上時間戳記後，你可以分析指標的變化。
• 數據重用：存下來的指標還可以用在其他分析任務。

核心想法：log_analytics 資料表

要記錄分析數據，我們會建立一個專用的資料表，來存所有關鍵指標。每個新結果就是一筆新資料。為了更好理解，先從最基本的情境開始。

資料表結構範例

在 log_analytics 資料表裡，我們會存報告的數據。結構如下（DDL — Data Definition Language）：

CREATE TABLE log_analytics (
    log_id SERIAL PRIMARY KEY, -- 唯一紀錄 ID
    report_name TEXT NOT NULL, -- 報告或指標名稱
    report_date DATE DEFAULT CURRENT_DATE, -- 報告對應的日期
    category TEXT, -- 數據分類（例如地區、產品）
    metric_value NUMERIC NOT NULL, -- 指標數值
    created_at TIMESTAMP DEFAULT NOW() -- 記錄的日期時間
);

• log_id：這筆資料的主鍵。
• report_name：報告或指標名稱，例如 "Weekly Sales"。
• report_date：指標對應的日期。例如如果是 10 月 1 日的銷售，這裡就是 2023-10-01。
• category：幫助分組數據，例如按地區。
• metric_value：報告指標的數值。
• created_at：記錄的時間戳記。

在 log_analytics 寫入數據的範例

假設我們算出「北區」10 月的收入。要怎麼存這個數值？

INSERT INTO log_analytics (report_name, report_date, category, metric_value)
VALUES ('Monthly Revenue', '2023-10-01', 'North', 15000.75);

結果：

建立 logging 用的 procedure

當然，我們不可能每週或每月都手動寫入數據。所以要用 procedure 來自動化。

來寫一個簡單的收入 logging procedure：

CREATE OR REPLACE FUNCTION log_monthly_revenue(category TEXT, revenue NUMERIC)
RETURNS VOID AS $$
BEGIN
    INSERT INTO log_analytics (report_name, report_date, category, metric_value)
    VALUES ('Monthly Revenue', CURRENT_DATE, category, revenue);
END;
$$ LANGUAGE plpgsql;

這個 log_monthly_revenue procedure 會接收兩個參數：

• category：數據分類，例如地區。
• revenue：收入數值

呼叫這個 function 來寫入收入：

SELECT log_monthly_revenue('North', 15000.75);

結果會跟直接用 INSERT 一樣。

log 結構的進階想法

有時候關鍵指標不只一個。來看看怎麼記錄多個指標，比如訂單數量或平均客單價。

更新一下資料表結構：

CREATE TABLE log_analytics_extended (
    log_id SERIAL PRIMARY KEY,
    report_name TEXT NOT NULL,
    report_date DATE DEFAULT CURRENT_DATE,
    category TEXT,
    metric_values JSONB NOT NULL, -- 用 JSONB 格式存多個指標
    created_at TIMESTAMP DEFAULT NOW()
);

這裡的重點是用 JSONB 來存多個指標在同一欄位。

寫入進階資料表的範例

假設要同時存三個指標：收入、訂單數、平均客單價。範例如下：

INSERT INTO log_analytics_extended (report_name, category, metric_values)
VALUES (
    'Monthly Revenue',
    'North',
    '{"revenue": 15000.75, "orders": 45, "avg_check": 333.35}'::jsonb
);

結果：

log 應用範例：收入分析

假設我們想知道 10 月所有地區的總收入。查詢如下：

SELECT SUM((metric_values->>'revenue')::NUMERIC) AS total_revenue
FROM log_analytics_extended
WHERE report_date BETWEEN '2023-10-01' AND '2023-10-31';

log 應用範例：地區趨勢

分析各地區收入變化：

SELECT category, report_date, (metric_values->>'revenue')::NUMERIC AS revenue
FROM log_analytics_extended
ORDER BY category, report_date;

常見錯誤處理

記錄分析數據時，常會犯幾個錯誤。來討論一下怎麼避免。

• 錯誤：忘記填 category 或日期。建議在資料表設預設值，例如 DEFAULT CURRENT_DATE。
• 錯誤：資料重複。為了避免重複，可以加唯一索引：

  CREATE UNIQUE INDEX unique_log_entry
  ON log_analytics (report_name, report_date, category);
  

• 錯誤：指標計算時除以零。一定要檢查除數！用 NULLIF：

  SELECT revenue / NULLIF(order_count, 0) AS avg_check FROM orders;
  

實際專案應用

記錄分析數據在很多領域都很有用：

• 零售：追蹤商品分類的收入和銷售。
• 服務：分析 server 或 app 的負載。
• 金融：控管交易和支出。

這些數據不只讓你能解釋發生了什麼，還能根據 log 做決策。現在你已經知道怎麼在 PostgreSQL 裡記錄分析數據的歷史啦。很棒，接下來還有更多實用知識等你學！