例：直近3ヶ月の注文の平均チェックを計算する

このレクチャーでは、面白い実践的な例を見ていくよ。

平均チェックっていうのは、1回の購入でお客さんが平均いくら使うかを示すメトリクスだよ。これはビジネスの主要なメトリクスの1つで、こんなことができる：

• 購買力の変化を分析する
• 売上のトレンドを見つける
• マーケティングキャンペーンの効果を評価する

課題の設定

例えば、ordersテーブルがあるデータベースがあるとしよう。ここに注文が保存されてる。目標は：

1. 直近3ヶ月で作成された注文の平均チェックを計算する
2. この計算をプロシージャで自動化する
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 — 注文のユニーク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()関数を使って平均を出すよ。

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' — 直近3ヶ月の注文を抽出する
• ROUND(..., 2) — 結果を小数点2桁に丸める

クエリの結果はこんな感じになるよ：

プロシージャで自動化しよう

次の目標は、この計算を自動でやってくれるプロシージャを作って、結果を別テーブルにログとして保存すること。まずは分析ログ用のテーブルを作ろう。

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 — 計算されたメトリクスの値

プロシージャを作成する

次に、こんなことをするプロシージャを書こう：

1. 直近3ヶ月の平均チェックを計算する
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: 結果をログに保存
    INSERT INTO log_analytics (metric_name, metric_value)
    VALUES ('average_check_3_months', avg_check);

    -- デバッグ用の情報を出力（オプション）
    RAISE NOTICE '平均チェック: %', avg_check;
END;
$$ LANGUAGE plpgsql;

この関数を呼び出せば、自動的にlog_analyticsテーブルに結果が記録されるよ：

SELECT calculate_average_check();

タスクスケジューラで自動化する

前回のレクチャーでタスクスケジューラをセットアップしたよね。Linuxならpg_cron拡張、WindowsやmacOSならシステムのスケジューラ（cronやTask Scheduler）を使ったはず。準備ができたら、プロシージャをスケジュールに組み込もう。

Linuxでpg_cronを使う場合は、必要なデータベースで拡張が有効になってるか確認してね：

CREATE EXTENSION IF NOT EXISTS pg_cron;

（ちなみに、pg_cron自体のインストールやshared_preload_librariesの設定は前回やったよね。）

これで、例えば毎日0時にcalculate_average_check()を実行するようにスケジュールできる：

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

説明：

• 'daily_avg_check' — タスク名
• '0 0 * * *' — 毎日0時に実行するcron式
• $$内のコマンド — 実行される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"

こんな感じで、どのOSでもプロシージャが自動で実行されて、平均チェックがlog_analyticsテーブルに定期的に記録されるよ。どの方法を使ってるかわからなかったら、前回のレクチャーを見返してみて。各プラットフォームのセットアップ方法が書いてあるから。

結果の確認と分析

どんな感じになったか見てみよう。log_analyticsテーブルからデータを取得：

SELECT * FROM log_analytics ORDER BY log_date DESC;

結果例：

これで平均チェックの計算履歴が全部ログとして残る！このデータを使ってレポートを作ったり、メトリクスの変化を分析したりできるよ。

よくあるミスとその回避法

平均チェックの分析プロシージャを使うとき、いくつかありがちなミスがあるんだ。

1つは、結果が空の場合を考慮し忘れること。直近3ヶ月に注文がなかったら、AVG()はNULLを返すから、ログ記録時に問題になることがある。これを防ぐにはCOALESCE()を使おう：

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

もう1つは、ordersテーブルに変なデータが入ってる場合。例えば、注文金額がマイナスだったり、日付が変だったり。データを定期的にチェックしたり、DBレベルで制約（例：CHECK (total_amount > 0)）をつけるのがおすすめ。

おめでとう！これで直近3ヶ月の平均チェックを自動で計算して、あとで分析できるように保存するプロシージャが完成したよ。これはPostgreSQLとPL/pgSQLで分析タスクを自動化する方法の一例にすぎない。次回はもっと複雑な分析シナリオをやっていくから、またね！