分析データのログを別テーブルに保存する方法

例えば、1週間の売上レポートを作ってるとするよ。計算も終わって、クライアントも満足。でも1ヶ月後に「そのレポート、もう一度見せてくれる？」って言われたらどうする？もし事前にデータを保存してなかったら、手作業で復元するか「できません」って答えるしかない。これってめっちゃ不便だし、評判にも影響するかも。

分析データのログを取ることで、いくつか大事なことができるようになる：

• 履歴の保存：特定期間の重要なメトリクス（例えば売上や注文数）を記録できる。
• 監査・診断：何か問題があった時、どんなデータが記録されてたか確認できる。
• データ比較：タイムスタンプを追加することで、指標の変化を時系列で分析できる。
• データの再利用：保存したメトリクスを他の分析タスクでも使える。

基本アイデア：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);

結果：

ログ用プロシージャの作成

もちろん、毎週や毎月手動でデータを入れるのは無理だよね。だからプロシージャで自動化しよう。

収益データをログするシンプルなプロシージャを作ってみよう：

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プロシージャは2つのパラメータを受け取る：

• category：データのカテゴリ、例えば地域。
• revenue：収益の値

この関数を使って収益を記録するにはこうする：

SELECT log_monthly_revenue('North', 15000.75);

結果はINSERTで入れた時と同じになるよ。

ログ構造の追加アイデア

時には、重要なメトリクスが1つじゃなくて複数ある場合もあるよね。例えば注文数や平均チェックも記録したい場合を考えてみよう。

テーブル構造をアップデートしよう：

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

ここでのポイントは、複数のメトリクスを1つのフィールドにJSONB型で保存できること。

拡張テーブルへの記録例

例えば、収益・注文数・平均チェックの3つのメトリクスを同時に保存したい場合はこう：

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

結果：

ログ活用例：収益分析

例えば、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';

ログ活用例：地域ごとのトレンド

地域ごとの収益の変化を分析するには：

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

よくあるミスの対処法

分析データのログを取るとき、いくつかミスしやすいポイントがある。対策も一緒に見てみよう。

• ミス：カテゴリや日付を指定し忘れる。テーブルで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;
  

実際のプロジェクトでの活用

分析データのログは色んな分野で役立つよ：

• リテール：商品カテゴリごとの売上や収益のトラッキング。
• サービス：サーバーやアプリの負荷分析。
• ファイナンス：トランザクションや支出の管理。

こういうデータがあれば、「何が起きたか」を説明できるだけじゃなく、ログから見えることをもとに意思決定もできるよ。これでPostgreSQLで分析データの履歴を記録する方法が分かったね。いい感じ！これからもっと役立つ知識を学んでいこう！