CodeGym /コース /SQL SELF /スケジュールによるレポートの自動生成

スケジュールによるレポートの自動生成

SQL SELF

レベル 60 , レッスン 0

使用可能

小さいデータベースを扱っているときは、手動でクエリやプロシージャを実行してレポートを作るのも全然アリ。でも、現実の世界ではDBが大きくなって、繰り返しの作業は全部自動化したくなるよね。例えば、毎日売上レポートを作ってって頼まれたらどう？クエリが2分かかるとしても、1年で12時間以上も無駄にしちゃう。自動プロシージャに任せて、その間コーヒーでも飲んでた方が絶対いいよ！

自動化のメリット：

手作業を減らせる。
レポート作成の定期性を確保できる（例：毎日、毎週のレポート）。
ヒューマンエラーのリスクを最小限にできる。
レポートの信頼性アップ：いつも決まった条件で作られるから安心。

レポート自動生成の基本ステップ

レポートを自動で作るには、ざっくりこんな流れになる：

PL/pgSQLでレポートを作るプロシージャを作成。
必要なら結果のログを設定。
スケジューラでプロシージャを定期実行する。

じゃあ、順番にやってみよう！

レポート生成用プロシージャの作成

まずは、今日の全注文の売上合計を計算して、ログテーブルに保存するシンプルなプロシージャを作ってみる。ログ用テーブルはもうあるとしよう（名前はsales_report_logにしとく）：

CREATE TABLE sales_report_log (
    report_date DATE NOT NULL,
    total_sales NUMERIC NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

次に、PL/pgSQLでプロシージャを作成：

CREATE OR REPLACE FUNCTION generate_daily_sales_report()
RETURNS VOID AS $$
BEGIN
    -- 今日の売上合計を計算
    INSERT INTO sales_report_log (report_date, total_sales)
    SELECT CURRENT_DATE, SUM(order_total)
    FROM orders
    WHERE order_date = CURRENT_DATE;

    RAISE NOTICE 'レポート（%）が正常に作成された', CURRENT_DATE;
END;
$$ LANGUAGE plpgsql;

ここでやってること：

SUM()集計関数でordersテーブルから売上合計を計算。
レポート日（report_date）は常に今日（CURRENT_DATE）。
結果はsales_report_logテーブルに保存。
RAISE NOTICEでデバッグ用メッセージを出してる：レポートが正常に作成されたよってお知らせ。

プロシージャのテスト

このプロシージャを自動化する前に、手動でテストするのがオススメ。関数を実行してみよう：

SELECT generate_daily_sales_report();

次に、sales_report_logテーブルの中身を確認：

SELECT * FROM sales_report_log;

今日の日付と正しい売上合計の行が見えたら、おめでとう！関数はちゃんと動いてるよ！

PostgreSQLでのタスク自動化

DBに自動で何かさせたいときってあるよね：レポートを作ったり、古いデータを消したり、集計を定期更新したり。PostgreSQLならpg_cron拡張や、外部のタスクスケジューラ（システムのcronやTask Scheduler）で実現できる。

Linuxなら、pg_cronが一番便利。SQLをPostgreSQLの中で直接スケジューリングできて、shellやスクリプトに頼らなくてOK。

pg_cronのインストール方法（XXは自分のPostgreSQLバージョンに置き換えてね）：

sudo apt install postgresql-XX-cron

インストール後、設定ファイルで有効化。postgresql.confを開いて、次の行を追加：

shared_preload_libraries = 'pg_cron'

その後、PostgreSQLを再起動して、DBで拡張を有効化：

CREATE EXTENSION pg_cron;

これでタスクをスケジューリングできる。例えば、generate_daily_sales_report()を毎日0時に実行：

SELECT cron.schedule(
    'daily_sales_report',
    '0 0 * * *',
    $$ SELECT generate_daily_sales_report(); $$
);

ここで：

'daily_sales_report' — タスク名；
'0 0 * * *' — cron形式のスケジュール（この場合は毎日0時）；
$$の間のSQL — 実行されるコード。

全タスクを確認したいときは：

SELECT * FROM cron.job;

WindowsやmacOSの場合、pg_cronは（Windowsでは）使えないか、（macOSでは）ソースからビルドが必要。面倒なので、ほとんどの場合はシステムのスケジューラを使った方が楽。

やり方はこんな感じ：

必要なコマンドを書いたSQLファイルを作成：

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

psqlでファイルを実行：

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

このコマンドの実行をタスクスケジューラに追加：
- Linux/macOSならcrontab -eで：
```
0 0 * * * psql -h localhost -U postgres -d your_database -f /path/to/script.sql
```
- WindowsならTask Schedulerで、psql.exeを必要なパラメータ付きで実行するタスクを作成。

Linuxならpg_cronが便利でPostgreSQLに統合されてる。
WindowsやMacなら、システムスケジューラ（cronやTask Scheduler）でpsql経由でSQLを実行するのが賢い。

これで、PostgreSQLのどんなタスクもラクラク自動化できるよ！

自動レポートの例

地域別の毎日レポート

例えば、各地域ごとの売上レポートを自動で作りたい場合。関数を拡張すればOK：

CREATE OR REPLACE FUNCTION generate_regional_sales_report()
RETURNS VOID AS $$
BEGIN
    INSERT INTO regional_sales_report_log (region, report_date, total_sales)
    SELECT region, CURRENT_DATE, SUM(order_total)
    FROM orders
    WHERE order_date = CURRENT_DATE
    GROUP BY region;

    RAISE NOTICE '地域別レポート（%）が正常に作成された', CURRENT_DATE;
END;
$$ LANGUAGE plpgsql;

月次レポート

同じように、月ごとのレポートを作るプロシージャも簡単。クエリのフィルタを変えるだけ：

WHERE order_date BETWEEN date_trunc('month', CURRENT_DATE)
                     AND date_trunc('month', CURRENT_DATE) + interval '1 month - 1 day';

よくあるミスとその防止策

レポート自動生成でありがちなトラブル：

関数のシンタックスエラー：自動化前に必ず手動テストしよう。
タスクの実行頻度：頻繁すぎるとDBに負荷がかかる。スケジュールは賢く設定しよう。
データの重複：1日に何度もレポートが走ると重複することも。ユニークキーで重複防止しよう。

このレクチャーで、PostgreSQLでのレポート自動生成のやり方が分かったはず。これで分析作業を効率化して、大事なことにもっと時間を使えるよ…例えばバグ探しとか、コード書きとか、SQLクエリの完璧さを夢見るとかね。

前へ前のレッスン

次へ次のレッスン

TO VIEW ALL COMMENTS OR TO MAKE A COMMENT,
GO TO FULL VERSION