PL/pgSQLでアナリティクスレポートを作る

アナリティクスレポートっていうのは、データを体系的にまとめて、意思決定を助けてくれるものだよ。例えば：

• マネージャーは先月の売上がどれくらいだったか見たい。
• アナリストは市場のトレンドを探してる。
• 開発者はアプリのパフォーマンスをモニタリングしてる。

イメージしてみて、君が巨大なレストランのシェフだとする。どの料理が一番注文されてるか知りたいよね？そのためにはレポートが必要。PostgreSQLはレシピと注文のデータベースで、PL/pgSQL（プロシージャ）はキッチンで分析を自動化してくれるアシスタントみたいなもんだ。

アナリティクスレポート作成の基本

アナリティクスレポートは、データを集計・フィルタ・ソート・並べ替えて、役立つ情報を引き出すためのツールだよ。だいたい、レポートの流れはこんな感じ：

1. データの準備：テーブルから情報を取り出して、フィルタや前処理をする。
2. データの集計：メトリクス（平均注文額、売上合計など）を計算する。
3. フォーマット：見やすい形にデータを並べる。
4. 結果の出力：ユーザーにレポートを見せたり、保存用にテーブルに書き込んだりする。

これらのステップは全部、PL/pgSQLのプロシージャで実装できるよ。

アナリティクスレポート用プロシージャの作成

じゃあ、基本的なアナリティクスレポートを作る例を見てみよう。たとえば、ordersっていう注文データのテーブルがあるとする：

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

やりたいこと：指定した月の売上合計レポートを作る。つまり、こういうのが見たい：

• 月
• その月の売上合計

プロシージャの構成

プロシージャの流れはこんな感じ（怖がらなくてOK、PL/pgSQLのプログラミングは全然痛くないよ）：

1. 入力パラメータとして月を受け取る。
2. ordersテーブルからその月のデータを選ぶ。
3. 売上合計を計算する。
4. 結果を返す。

プロシージャの実装

コード例：

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：これが魔法みたいなやつで、プロシージャから直接データを返せる。
3. DATE_TRUNC：order_dateを月の最初の日に切り捨てるために使う。
4. SUM：注文の合計金額を計算する集計関数。
5. GROUP BY：月ごとにデータをまとめる。レポートは月単位だからね。

これで関数を呼び出せるようになる：

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

結果はこんな感じ：

この関数がベースになる。じゃあ、もうちょっとレベルアップしよう！

もっと複雑なレポートを作る

今度は、売上を顧客ごとに分けてみたいとする。つまり、レポートにはこういうのが欲しい：

• 顧客
• 月
• その顧客の月間注文合計

プロシージャを変更しよう

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;

これでプロシージャを呼び出すと：

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

    -- このテーブルで追加の計算や操作をする
    -- 例えば、注文合計が多いトップ3の顧客を表示
    RAISE NOTICE '月 % のトップ3顧客:', 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は中間結果を保存するために使ってるよ。

お役立ちTips

1. 最適化：データ取得を速くするためにインデックスを使おう。
2. ゼロ割りエラー：割り算のときは割る数がゼロじゃないか必ずチェックしよう。レポートが「死ぬ」からね。
3. 日付フォーマット：TO_CHARみたいな関数で見やすく表示しよう。

あんまり退屈しなかったらいいな！これからもっと難しくて面白い課題が待ってるから、油断しないでね！

1

アンケート/クイズ

アナリティクス用プロシージャ、レベル 59、レッスン 4

使用不可

アナリティクス用プロシージャ

アナリティクス用プロシージャ

オープン