【PL/SQL】集計バッチ設計｜日次集計・再集計・検算

売上、注文数、在庫、アクセスログのような大量データを毎回明細から集計していると、画面表示や帳票出力が重くなります。そこで使うのが、日次や月次の集計結果をあらかじめ保存しておくサマリーテーブルです。ただし、サマリーテーブルは作るだけでは足りません。どの粒度で集計するか、いつ再集計するか、元データとどう検算するかまで決めないと、速いけれど信用できない数字になります。

この記事では、PL/SQLでサマリーテーブルと集計バッチを設計する方法を、実務の運用目線で整理します。日次集計、差分集計、再集計、検算、ジョブ履歴、失敗時の復旧まで扱います。差分抽出の考え方は PL/SQLの差分抽出・増分処理、ジョブ履歴の残し方は PL/SQLのジョブ実行履歴テーブル設計と組み合わせると設計しやすくなります。

この記事で扱うこと

サマリーテーブルが必要になる場面
マテリアライズドビューと手組み集計バッチの使い分け
日次サマリーテーブルの設計
DELETE INSERT方式とMERGE方式
差分集計と再集計範囲の決め方
元データとの検算SQL
集計バッチのジョブ履歴とリカバリ

サマリーテーブルが必要になる場面
マテリアライズドビューと手組み集計バッチの違い
集計粒度を先に決める
日次サマリーテーブルを設計する
集計対象期間を固定する
DELETE INSERT方式で再作成する
MERGE方式で差分更新する
差分集計では影響日を抽出する
影響キーだけ再集計する
検算SQLを必ず用意する
ジョブ履歴に処理件数を残す
失敗時はサマリーを中途半端に残さない
再集計の入口を用意する
やってはいけない集計バッチ
設計チェックリスト
まとめ

サマリーテーブルが必要になる場面

サマリーテーブルは、明細テーブルを毎回集計すると遅い場合に使います。たとえば、注文明細を日別、商品別、店舗別に集計してダッシュボードに出す場合、毎アクセスで数千万件をGROUP BYするのは現実的ではありません。一方で、サマリーテーブルを作ると、参照は速くなりますが、更新タイミングと整合性の管理が必要になります。

画面表示を速くしたいダッシュボードや一覧画面で毎回重い集計を走らせないようにします。

帳票の数字を固定したい日次や月次で確定した数字を、後から同じ値で参照できるようにします。

再計算範囲を限定したい全期間を毎回集計せず、変更があった日だけ再集計します。

検算可能にしたい明細合計とサマリー合計を比較し、数字のズレを検知できるようにします。

重要なのは、サマリーテーブルをキャッシュではなく検算できる派生データとして扱うことです。元データが正、サマリーは再作成可能、という前提にしておくと、障害時や仕様変更時に復旧しやすくなります。

マテリアライズドビューと手組み集計バッチの違い

Oracleにはマテリアライズドビューがあります。集計結果を保存し、条件が合えばクエリリライトやリフレッシュ機能を使えるため、定型的な集計には強力です。一方で、業務上の補正、エラー隔離、再集計承認、検算ログ、外部連携との整合などを細かく制御したい場合は、手組みのサマリーテーブルとPL/SQLバッチの方が扱いやすいことがあります。

マテリアライズドビューが向くケースSQL定義で表せる集計を、Oracleのリフレッシュ機構に任せたい場合です。

手組みサマリーが向くケース再集計範囲、作業証跡、検算、エラー隔離、業務承認を細かく制御したい場合です。

併用もできる標準集計はマテリアライズドビュー、業務固有の補正や検算が必要な集計は手組みサマリーに分けられます。

既存のマテリアライズドビュー解説は Oracleのマテリアライズドビュー完全ガイド、PL/SQL側のキャッシュ戦略は Result Cacheとマテリアライズドビュー併用戦略も参考になります。この記事では、手組みサマリーテーブルを安全に運用する設計に絞ります。

集計粒度を先に決める

サマリーテーブル設計で最初に決めるのは、集計粒度です。日別なのか、月別なのか、商品別なのか、店舗別なのかを曖昧にしたまま作ると、後から列追加や再集計が増えます。粒度は、検索条件、画面の集計軸、帳票の出力単位から逆算します。

日次粒度日別推移、日次締め、日次バッチの検算に向いています。

月次粒度月次帳票や締め処理に向いていますが、月中の補正再計算範囲に注意します。

商品・店舗粒度分析軸として便利ですが、粒度を増やすほど行数が増えます。

集計条件の固定キャンセルを含むか、税抜か税込か、返品をどう扱うかを列定義や仕様に残します。

迷ったときは、最初から多軸の巨大サマリーを作るより、用途ごとに小さく作る方が安全です。1つのサマリーテーブルにすべての要求を詰め込むと、更新条件も検算条件も複雑になります。

日次サマリーテーブルを設計する

ここでは、注文明細から日別、商品別、店舗別の売上サマリーを作る例で考えます。サマリーテーブルには、集計キー、集計値、元データの範囲、集計バッチID、再集計日時を持たせます。

sales-daily-summary-ddl.sql

CREATE TABLE sales_daily_summary (
  summary_date       DATE         NOT NULL,
  store_id           NUMBER       NOT NULL,
  item_id            NUMBER       NOT NULL,
  order_count        NUMBER       DEFAULT 0 NOT NULL,
  sales_amount       NUMBER       DEFAULT 0 NOT NULL,
  discount_amount    NUMBER       DEFAULT 0 NOT NULL,
  return_amount      NUMBER       DEFAULT 0 NOT NULL,
  source_min_id      NUMBER,
  source_max_id      NUMBER,
  source_rows        NUMBER       DEFAULT 0 NOT NULL,
  batch_id           NUMBER       NOT NULL,
  recalculated_at    TIMESTAMP    DEFAULT SYSTIMESTAMP NOT NULL,
  CONSTRAINT pk_sales_daily_summary
    PRIMARY KEY (summary_date, store_id, item_id)
);

source_rows、source_min_id、source_max_idは必須ではありませんが、検算や調査に役立ちます。サマリーだけを見ると正しそうでも、どの範囲の明細から作った数字なのか分からないと、障害調査が難しくなります。

集計対象期間を固定する

集計バッチでは、処理中に明細が増えることを前提にします。そのため、今回の集計対象期間を最初に固定します。差分処理と同じく、処理中にSYSTIMESTAMPを何度も参照して対象範囲を変えるのは避けます。

summary-batch-range.sql

DECLARE
  v_batch_id  NUMBER := summary_batch_seq.NEXTVAL;
  v_from_date DATE   := DATE '2026-06-01';
  v_to_date   DATE   := DATE '2026-06-02';
BEGIN
  INSERT INTO summary_batch_log (
    batch_id,
    summary_name,
    from_date,
    to_date,
    status,
    started_at
  ) VALUES (
    v_batch_id,
    'SALES_DAILY_SUMMARY',
    v_from_date,
    v_to_date,
    'RUNNING',
    SYSTIMESTAMP
  );

  -- 以降は v_from_date 以上、v_to_date 未満だけを処理する
END;
/

日次集計なら、summary_date >= from_dateかつsummary_date < to_dateのように、上限を未満で扱うと境界が明確になります。期間条件の考え方は、差分抽出や補正スクリプトと同じです。

DELETE INSERT方式で再作成する

サマリーを安全に作る基本形は、対象期間の既存サマリーを削除してから、元明細から再作成する方式です。差分だけ足し引きする方式より処理量は増えますが、ロジックが単純で、再集計や検算がしやすくなります。

delete-insert-summary.sql

DELETE FROM sales_daily_summary
WHERE summary_date >= :from_date
  AND summary_date <  :to_date;

INSERT INTO sales_daily_summary (
  summary_date,
  store_id,
  item_id,
  order_count,
  sales_amount,
  discount_amount,
  return_amount,
  source_min_id,
  source_max_id,
  source_rows,
  batch_id,
  recalculated_at
)
SELECT
  TRUNC(o.order_date) AS summary_date,
  o.store_id,
  d.item_id,
  COUNT(DISTINCT o.order_id) AS order_count,
  SUM(CASE WHEN o.status <> 'CANCELLED' THEN d.amount ELSE 0 END) AS sales_amount,
  SUM(NVL(d.discount_amount, 0)) AS discount_amount,
  SUM(CASE WHEN o.status = 'RETURNED' THEN d.amount ELSE 0 END) AS return_amount,
  MIN(d.order_detail_id) AS source_min_id,
  MAX(d.order_detail_id) AS source_max_id,
  COUNT(*) AS source_rows,
  :batch_id,
  SYSTIMESTAMP
FROM orders o
JOIN order_details d
  ON d.order_id = o.order_id
WHERE o.order_date >= :from_date
  AND o.order_date <  :to_date
GROUP BY
  TRUNC(o.order_date),
  o.store_id,
  d.item_id;

この方式では、対象期間だけを消して作り直します。処理途中で失敗した場合はROLLBACKすれば元に戻せるため、同一トランザクション内で削除と挿入を完結させるのが基本です。トランザクション境界は PL/SQLのトランザクション設計と合わせて押さえておきましょう。

MERGE方式で差分更新する

対象範囲が広い場合や、更新対象キーが限定できる場合は、MERGEでサマリーを更新する方法もあります。ただし、削除された明細やキャンセル変更のように、既存サマリーから減算が必要なケースは難しくなります。そのため、MERGE方式は、影響キーを正しく抽出できる場合に限定するのが安全です。

merge-summary.sql

MERGE INTO sales_daily_summary s
USING (
  SELECT
    TRUNC(o.order_date) AS summary_date,
    o.store_id,
    d.item_id,
    COUNT(DISTINCT o.order_id) AS order_count,
    SUM(CASE WHEN o.status <> 'CANCELLED' THEN d.amount ELSE 0 END) AS sales_amount,
    SUM(NVL(d.discount_amount, 0)) AS discount_amount,
    SUM(CASE WHEN o.status = 'RETURNED' THEN d.amount ELSE 0 END) AS return_amount,
    MIN(d.order_detail_id) AS source_min_id,
    MAX(d.order_detail_id) AS source_max_id,
    COUNT(*) AS source_rows
  FROM orders o
  JOIN order_details d
    ON d.order_id = o.order_id
  WHERE o.order_date >= :from_date
    AND o.order_date <  :to_date
  GROUP BY TRUNC(o.order_date), o.store_id, d.item_id
) x
ON (
  s.summary_date = x.summary_date
  AND s.store_id = x.store_id
  AND s.item_id = x.item_id
)
WHEN MATCHED THEN
  UPDATE SET
    s.order_count = x.order_count,
    s.sales_amount = x.sales_amount,
    s.discount_amount = x.discount_amount,
    s.return_amount = x.return_amount,
    s.source_min_id = x.source_min_id,
    s.source_max_id = x.source_max_id,
    s.source_rows = x.source_rows,
    s.batch_id = :batch_id,
    s.recalculated_at = SYSTIMESTAMP
WHEN NOT MATCHED THEN
  INSERT (
    summary_date, store_id, item_id,
    order_count, sales_amount, discount_amount, return_amount,
    source_min_id, source_max_id, source_rows,
    batch_id, recalculated_at
  )
  VALUES (
    x.summary_date, x.store_id, x.item_id,
    x.order_count, x.sales_amount, x.discount_amount, x.return_amount,
    x.source_min_id, x.source_max_id, x.source_rows,
    :batch_id, SYSTIMESTAMP
  );

MERGEは便利ですが、対象範囲に存在しなくなったサマリー行を消す処理は別途必要です。集計キーが消える可能性がある場合は、DELETE INSERT方式の方が単純です。MERGEの条件設計は PL/SQLのMERGE文とUPSERT も参考になります。

差分集計では影響日を抽出する

差分集計で重要なのは、変更された行だけを見るのではなく、変更の影響を受ける集計キーを特定することです。たとえば、注文日が変更された場合、変更前の日付と変更後の日付の両方を再集計する必要があります。単純に新しい日付だけ再集計すると、古い日付のサマリーに数字が残ります。

affected-summary-key-ddl.sql

CREATE GLOBAL TEMPORARY TABLE tmp_affected_summary_key (
  summary_date DATE   NOT NULL,
  store_id     NUMBER NOT NULL,
  item_id      NUMBER NOT NULL
) ON COMMIT DELETE ROWS;

ON COMMIT DELETE ROWSの一時表は、同一トランザクション内で影響キーを集め、削除、再作成、検算まで使い切る前提です。途中でCOMMITすると影響キーが消えるため、処理を分割したい場合は通常テーブルにbatch_id付きで保存する方式を選びます。

collect-affected-keys.sql

INSERT INTO tmp_affected_summary_key(summary_date, store_id, item_id)
SELECT DISTINCT
  TRUNC(o.order_date),
  o.store_id,
  d.item_id
FROM orders o
JOIN order_details d
  ON d.order_id = o.order_id
WHERE o.updated_at >= :from_ts
  AND o.updated_at <  :to_ts;

-- 注文日や店舗が変更される業務では、変更前キーも履歴から入れる
INSERT INTO tmp_affected_summary_key(summary_date, store_id, item_id)
SELECT DISTINCT
  TRUNC(h.old_order_date),
  h.old_store_id,
  h.item_id
FROM order_change_history h
WHERE h.changed_at >= :from_ts
  AND h.changed_at <  :to_ts;

差分集計では、影響キーを集めてから、そのキーに該当するサマリーを再作成します。これは、変更分を足し引きするより安全な方法です。差分抽出のウォーターマーク設計は差分抽出・増分処理の設計の考え方をそのまま使えます。

影響キーだけ再集計する

影響キーを集めたら、そのキーに該当するサマリーだけ削除して再作成します。日付範囲全体を再集計するより軽く、単純な差分加算より正確にできます。

rebuild-affected-summary.sql

DELETE FROM sales_daily_summary s
WHERE EXISTS (
  SELECT 1
  FROM tmp_affected_summary_key k
  WHERE k.summary_date = s.summary_date
    AND k.store_id = s.store_id
    AND k.item_id = s.item_id
);

INSERT INTO sales_daily_summary (
  summary_date,
  store_id,
  item_id,
  order_count,
  sales_amount,
  discount_amount,
  return_amount,
  source_min_id,
  source_max_id,
  source_rows,
  batch_id,
  recalculated_at
)
SELECT
  k.summary_date,
  k.store_id,
  k.item_id,
  COUNT(DISTINCT o.order_id),
  SUM(CASE WHEN o.status <> 'CANCELLED' THEN d.amount ELSE 0 END),
  SUM(NVL(d.discount_amount, 0)),
  SUM(CASE WHEN o.status = 'RETURNED' THEN d.amount ELSE 0 END),
  MIN(d.order_detail_id),
  MAX(d.order_detail_id),
  COUNT(d.order_detail_id),
  :batch_id,
  SYSTIMESTAMP
FROM tmp_affected_summary_key k
LEFT JOIN orders o
  ON TRUNC(o.order_date) = k.summary_date
 AND o.store_id = k.store_id
LEFT JOIN order_details d
  ON d.order_id = o.order_id
 AND d.item_id = k.item_id
GROUP BY
  k.summary_date,
  k.store_id,
  k.item_id;

LEFT JOINにしているのは、明細が0件になったキーも再作成対象として扱うためです。このときCOUNT(*)を使うと、明細が存在しないキーでも1件として数えてしまうため、COUNT(d.order_detail_id)で実明細数を数えます。ただし、0件のサマリー行を残すか削除するかは業務要件で決めます。画面で0表示が必要なら残し、不要なら0件行を後続で削除します。

検算SQLを必ず用意する

サマリーテーブルの価値は、速いことだけではありません。元明細とサマリーの数字が一致していることを説明できる必要があります。そのため、集計バッチには検算SQLを必ず用意します。

summary-reconcile.sql

WITH detail_sum AS (
  SELECT
    TRUNC(o.order_date) AS summary_date,
    o.store_id,
    d.item_id,
    COUNT(DISTINCT o.order_id) AS order_count,
    SUM(CASE WHEN o.status <> 'CANCELLED' THEN d.amount ELSE 0 END) AS sales_amount,
    SUM(NVL(d.discount_amount, 0)) AS discount_amount,
    SUM(CASE WHEN o.status = 'RETURNED' THEN d.amount ELSE 0 END) AS return_amount
  FROM orders o
  JOIN order_details d
    ON d.order_id = o.order_id
  WHERE o.order_date >= :from_date
    AND o.order_date <  :to_date
  GROUP BY TRUNC(o.order_date), o.store_id, d.item_id
)
SELECT
  COALESCE(d.summary_date, s.summary_date) AS summary_date,
  COALESCE(d.store_id, s.store_id) AS store_id,
  COALESCE(d.item_id, s.item_id) AS item_id,
  NVL(d.order_count, 0) - NVL(s.order_count, 0) AS diff_order_count,
  NVL(d.sales_amount, 0) - NVL(s.sales_amount, 0) AS diff_sales_amount,
  NVL(d.discount_amount, 0) - NVL(s.discount_amount, 0) AS diff_discount_amount,
  NVL(d.return_amount, 0) - NVL(s.return_amount, 0) AS diff_return_amount
FROM detail_sum d
FULL OUTER JOIN sales_daily_summary s
  ON s.summary_date = d.summary_date
 AND s.store_id = d.store_id
 AND s.item_id = d.item_id
WHERE NVL(d.order_count, 0) <> NVL(s.order_count, 0)
   OR NVL(d.sales_amount, 0) <> NVL(s.sales_amount, 0)
   OR NVL(d.discount_amount, 0) <> NVL(s.discount_amount, 0)
   OR NVL(d.return_amount, 0) <> NVL(s.return_amount, 0);

検算SQLは、差分がある行だけを返す形にしておくと運用しやすくなります。0件なら正常、1件以上なら異常としてジョブを失敗扱いにできます。金額に丸めが入る場合は、完全一致ではなく許容誤差を決めます。

ジョブ履歴に処理件数を残す

集計バッチでは、何件削除し、何件挿入し、検算結果が何件だったかを残します。単に成功・失敗だけでは、数字が変わった理由を後から追えません。

summary-batch-log-ddl.sql

CREATE TABLE summary_batch_log (
  batch_id          NUMBER PRIMARY KEY,
  summary_name      VARCHAR2(100) NOT NULL,
  from_date         DATE NOT NULL,
  to_date           DATE NOT NULL,
  status            VARCHAR2(20) NOT NULL,
  deleted_rows      NUMBER,
  inserted_rows     NUMBER,
  reconciled_errors NUMBER,
  started_at        TIMESTAMP NOT NULL,
  finished_at       TIMESTAMP,
  error_message     VARCHAR2(2000)
);

summary-batch-finish.sql

UPDATE summary_batch_log
SET
  status = 'SUCCESS',
  deleted_rows = :deleted_rows,
  inserted_rows = :inserted_rows,
  reconciled_errors = 0,
  finished_at = SYSTIMESTAMP
WHERE batch_id = :batch_id;

COMMIT;

本番運用では、処理件数の急増や急減も異常検知の材料になります。昨日は10万件、今日は100件という場合、正常終了でも集計対象条件を疑うべきです。進捗や監視への出し方は DBMS_APPLICATION_INFOと運用観測性と相性がよいです。

失敗時はサマリーを中途半端に残さない

集計バッチが失敗したときに一番困るのは、サマリーが半分だけ更新された状態でコミットされることです。DELETEとINSERT、検算、履歴更新の境界を決め、失敗時にどこまで戻すかを明確にします。

summary-refresh-block.sql

DECLARE
  v_deleted_rows      NUMBER := 0;
  v_inserted_rows     NUMBER := 0;
  v_reconcile_errors  NUMBER := 0;
BEGIN
  DELETE FROM sales_daily_summary
  WHERE summary_date >= :from_date
    AND summary_date <  :to_date;

  v_deleted_rows := SQL%ROWCOUNT;

  INSERT INTO sales_daily_summary (
    summary_date,
    store_id,
    item_id,
    order_count,
    sales_amount,
    discount_amount,
    return_amount,
    source_min_id,
    source_max_id,
    source_rows,
    batch_id,
    recalculated_at
  )
  SELECT
    TRUNC(o.order_date),
    o.store_id,
    d.item_id,
    COUNT(DISTINCT o.order_id),
    SUM(CASE WHEN o.status <> 'CANCELLED' THEN d.amount ELSE 0 END),
    SUM(NVL(d.discount_amount, 0)),
    SUM(CASE WHEN o.status = 'RETURNED' THEN d.amount ELSE 0 END),
    MIN(d.order_detail_id),
    MAX(d.order_detail_id),
    COUNT(d.order_detail_id),
    :batch_id,
    SYSTIMESTAMP
  FROM orders o
  JOIN order_details d
    ON d.order_id = o.order_id
  WHERE o.order_date >= :from_date
    AND o.order_date <  :to_date
  GROUP BY TRUNC(o.order_date), o.store_id, d.item_id;

  v_inserted_rows := SQL%ROWCOUNT;

  SELECT COUNT(*)
    INTO v_reconcile_errors
    FROM summary_reconcile_error_work;

  IF v_reconcile_errors > 0 THEN
    RAISE_APPLICATION_ERROR(
      -20981,
      'サマリー検算で差分があります。errors=' || v_reconcile_errors
    );
  END IF;

  UPDATE summary_batch_log
  SET status = 'SUCCESS',
      deleted_rows = v_deleted_rows,
      inserted_rows = v_inserted_rows,
      reconciled_errors = 0,
      finished_at = SYSTIMESTAMP
  WHERE batch_id = :batch_id;

  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END;
/

この例では、失敗時に本処理をロールバックします。失敗ログを確実に残したい場合は、本処理のロールバック後に別トランザクションでジョブ履歴を更新します。エラーコードを体系化するなら PL/SQLの業務エラーコード設計も参考になります。

再集計の入口を用意する

サマリーは必ず再集計が必要になります。仕様変更、補正、遅延連携、過去データの修正、検算エラーなどがあるためです。そのため、日次バッチとは別に、任意期間を再集計できる入口を用意しておきます。

recalculate-summary-procedure.sql

CREATE OR REPLACE PROCEDURE recalc_sales_daily_summary(
  p_from_date DATE,
  p_to_date   DATE,
  p_reason    VARCHAR2
) AS
BEGIN
  IF p_from_date IS NULL OR p_to_date IS NULL OR p_from_date >= p_to_date THEN
    RAISE_APPLICATION_ERROR(-20982, '再集計期間が不正です。');
  END IF;

  INSERT INTO summary_recalc_request (
    request_id,
    summary_name,
    from_date,
    to_date,
    reason,
    requested_by,
    requested_at,
    status
  ) VALUES (
    summary_recalc_request_seq.NEXTVAL,
    'SALES_DAILY_SUMMARY',
    p_from_date,
    p_to_date,
    p_reason,
    SYS_CONTEXT('USERENV','SESSION_USER'),
    SYSTIMESTAMP,
    'REQUESTED'
  );
END;
/

再集計は、誰でも自由に実行できるようにするのではなく、理由と範囲を記録してから実行します。特に月次締め後の再集計は、業務承認や帳票再出力と関係するため、証跡が重要です。本番データ補正後にサマリーを作り直す場合は本番データ補正スクリプトの作り方と連動させると安全です。