【PL/SQL】分析関数（Analytic Function）を活用した集計ロジックの簡素化

カーソル処理や一時表を多用して段階集計を作り込むよりも、分析関数を用いて「結果セットの各行に、同一パーティション内の集約・順位・前後関係を付与する」発想へ転換すると、SQL一発でロジックを簡素化できる。PL/SQLのループや状態変数を置き換え、読みやすさと実行効率を両立させるためには、PARTITION BYとORDER BY、そしてウィンドウフレーム（ROWS/RANGE）の三点を軸に設計するとよい。本稿では、典型的な業務要件を分析関数に落とし込む具体例を通じて、段階的集計、前後比較、トップN、重複排除、移動統計、欠損補完、パーセンタイルまでを実務視点で解説する。

累積集計と部分和で段階集計を置き換える

日次・月次の累積や小計・総計は、SUM OVERのパーティションと順序付けで一行に同居させる。従来の自己結合やPL/SQLカウンタは不要になり、明細と累積を同時に可視化できる。

-- 顧客別・購入日の累積金額と日次小計・総計を同時に出力
SELECT
  customer_id,
  purchase_dt,
  amount,
  SUM(amount) OVER (
    PARTITION BY customer_id
    ORDER BY purchase_dt
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS run_amount,
  SUM(amount) OVER (PARTITION BY customer_id, TRUNC(purchase_dt)) AS daily_sum,
  SUM(amount) OVER (PARTITION BY customer_id)                    AS cust_total
FROM app.sales
WHERE purchase_dt >= DATE '2025-01-01';

LAG/LEADで前回値との差分と変化点を検出する

前レコードの値比較や差分はLAG/LEADで一行に並べる。変化点検出や連番途切れの検知、日次増分の算出などがループなしで表現できる。

-- 口座残高の前回残高、増減、増減率、変化フラグ
SELECT
  acct_id,
  txn_dt,
  balance,
  LAG(balance) OVER (PARTITION BY acct_id ORDER BY txn_dt) AS prev_bal,
  balance - LAG(balance) OVER (PARTITION BY acct_id ORDER BY txn_dt) AS diff_bal,
  CASE
    WHEN LAG(balance) OVER (PARTITION BY acct_id ORDER BY txn_dt) IS NULL THEN 0
    WHEN LAG(balance) OVER (PARTITION BY acct_id ORDER BY txn_dt) = 0 THEN NULL
    ELSE (balance - LAG(balance) OVER (PARTITION BY acct_id ORDER BY txn_dt))
         / NULLIF(LAG(balance) OVER (PARTITION BY acct_id ORDER BY txn_dt),0)
  END AS diff_rate,
  CASE WHEN balance != LAG(balance) OVER (PARTITION BY acct_id ORDER BY txn_dt) THEN 1 ELSE 0 END AS changed
FROM app.ledger;

ROW_NUMBER/DENSE_RANKでトップNや最新行を抽出する

グループ内の上位N件や最新1件は、解析関数で順位を付与して外側でフィルタする。相関サブクエリよりも可読性が高く、最適化も受けやすい。

-- 顧客ごとの最新注文（購入日時の最大行）を取得
SELECT customer_id, order_id, order_ts, amount
FROM (
  SELECT
    o.*,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_ts DESC) AS rn
  FROM app.orders o
)
WHERE rn = 1;

-- 店舗ごとの売上トップ3商品
SELECT *
FROM (
  SELECT
    store_id, product_id, sales_amt,
    DENSE_RANK() OVER (PARTITION BY store_id ORDER BY sales_amt DESC) AS rnk
  FROM app.store_sales
)
WHERE rnk <= 3;

重複排除と代表行抽出をウィンドウ関数で明示する

ユニークキーが欠落した取り込みデータから代表行を一意に選ぶ場合も、順位付けで単純化できる。DELETEの代わりに対象を確定するSELECTをまず設計し、後段でMERGE/DELETEに接続する。

-- 同一顧客・同一日で複数行ある場合に金額が大きい行のみを代表にする
WITH ranked AS (
  SELECT
    t.*,
    ROW_NUMBER() OVER (PARTITION BY customer_id, TRUNC(purchase_dt) ORDER BY amount DESC, rowid) AS rn
  FROM app.stage_sales t
)
SELECT * FROM ranked WHERE rn = 1;

移動平均・移動合計・中心化ウィンドウの設計

期間ベースの移動統計はwindow frameで定義する。ROWSは物理行数、RANGEはソートキーの値範囲でウィンドウが決まるため、基準列の性質に応じて選択する。タイムスタンプのギャップが大きい実データでは、RANGE INTERVALを使うと自然な振る舞いになる。

-- 5行移動平均（行数基準）
SELECT
  product_id,
  sales_dt,
  sales,
  AVG(sales) OVER (
    PARTITION BY product_id
    ORDER BY sales_dt
    ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
  ) AS ma5_rows
FROM app.daily_sales;

-- 7日間の移動合計（日数基準）。同日複数行も自然に含む
SELECT
  product_id,
  sales_dt,
  sales,
  SUM(sales) OVER (
    PARTITION BY product_id
    ORDER BY sales_dt
    RANGE BETWEEN INTERVAL '6' DAY PRECEDING AND CURRENT ROW
  ) AS sum7d
FROM app.daily_sales;

条件付き集計と比率を一度に付与する

WHEREで絞り込まずに明細を保ったまま条件付き集計や比率、構成比を同一行に並べると、レポートや検証用の中間結果をワンステップで得られる。

-- カテゴリ別の売上、小計、全体比率、カテゴリ内比率
SELECT
  category,
  product_id,
  sales_amt,
  SUM(sales_amt) OVER (PARTITION BY category)                        AS cat_sum,
  SUM(sales_amt) OVER ()                                             AS grand_sum,
  ROUND( 100 * sales_amt / NULLIF(SUM(sales_amt) OVER (),0), 2)      AS pct_of_total,
  ROUND( 100 * sales_amt / NULLIF(SUM(sales_amt) OVER (PARTITION BY category),0), 2) AS pct_in_cat,
  SUM(CASE WHEN sales_amt >= 1000 THEN sales_amt ELSE 0 END)
    OVER (PARTITION BY category)                                     AS cat_sum_ge1k
FROM app.prod_sales;

ギャップ&アイランド問題を分析関数で解く

連続する期間を島としてまとめる典型問題は、LAGと「開始点フラグ」の累積和で連番を振ると容易に解ける。PL/SQLの走査・状態遷移は不要になる。

-- 連続在庫（連日在庫あり）を島としてまとめ、島ごとの開始・終了と日数を算出
WITH base AS (
  SELECT
    item_id,
    inv_dt,
    qty,
    CASE
      WHEN LAG(inv_dt) OVER (PARTITION BY item_id ORDER BY inv_dt) = inv_dt - 1 THEN 0
      ELSE 1
    END AS is_start
  FROM app.inventory_daily
  WHERE qty > 0
),
grp AS (
  SELECT
    item_id,
    inv_dt,
    qty,
    SUM(is_start) OVER (PARTITION BY item_id ORDER BY inv_dt) AS grp_id
  FROM base
)
SELECT
  item_id,
  MIN(inv_dt) AS start_dt,
  MAX(inv_dt) AS end_dt,
  COUNT(*)    AS days,
  SUM(qty)    AS total_qty
FROM grp
GROUP BY item_id, grp_id;

欠損の前方補完や直近値参照を窓関数で表現する

センサ値の欠損を直近の実測値で補う前方補完は、LAST_VALUEにIGNORE NULLSを併用すると純SQLで安定して実装できる。

-- 時系列のNULLを直前の非NULL値で埋める前方補完
SELECT
  device_id,
  ts,
  reading,
  LAST_VALUE(reading IGNORE NULLS)
    OVER (PARTITION BY device_id ORDER BY ts
          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS reading_ffill
FROM app.sensor_readings;

パーセンタイル・順位統計で外れ値やSLAを診断する

遅延時間や応答時間のSLA検証には、PERCENTILE_CONTやPERCENT_RANKを用いると閾値可視化が容易になる。分析バージョンを使えば、明細と同時に分位点を行へ投影できる。

-- サービス別の遅延分布。行と同じパーティションでp95を同居
SELECT
  service,
  latency_ms,
  PERCENT_RANK()  OVER (PARTITION BY service ORDER BY latency_ms) AS pr,
  PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY latency_ms)
    OVER (PARTITION BY service) AS p95
FROM app.request_latency
WHERE req_ts >= SYSTIMESTAMP - INTERVAL '7' DAY;

文字列集約・キー内ソートを分析版LISTAGGで同居させる

ユニークなキー内で時系列順に項目を並べた説明列を付与したい場合、LISTAGGの分析版を用いると、明細を保ったまま説明列を持たせられる。

-- 注文明細の品目一覧を注文行すべてに同居させる
SELECT
  order_id,
  line_no,
  item_name,
  LISTAGG(item_name, ',') WITHIN GROUP (ORDER BY line_no)
    OVER (PARTITION BY order_id) AS items_summary
FROM app.order_lines;

設計と性能の要点：パーティション鍵・順序列・フレームの整合

分析関数は「どの行を仲間とみなすか」「どの順序で時間軸を走らせるか」「どこからどこまでを窓とするか」の三点が一致して初めて意図通りに機能する。パーティション鍵は結合列と同じ選択性で設計し、必要ならパーティションキーとORDER BY列に複合索引を用意する。ROWSは行数で厳密に制御でき、RANGEは同値や多重レコードを自然に含むため、要件に応じて使い分ける。ウィンドウ関数はもとのSELECTに溶け込むため、PL/SQLへデータを持ち上げるよりもコンテキストスイッチが少なく、I/OとCPUの両面で有利である。複数の分析式を併用する際は、同一のPARTITION BY/ORDER BYを共有できるように式変形をそろえると、ソートの再利用で実行計画が安定する。

PL/SQLループから分析関数への書き換えテンプレート

売上明細に対する顧客別の累積金額、前回差分、カテゴリ内順位を一度に付与し、フィルタ条件で「累積が一定額を超えた最初の行」を抽出する例を示す。従来はループと状態変数、暫定表が必要だった処理をSQLで完結できる。

WITH enriched AS (
  SELECT
    s.*,
    SUM(amount) OVER (PARTITION BY customer_id ORDER BY sales_dt
                      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS run_amt,
    amount - LAG(amount) OVER (PARTITION BY customer_id ORDER BY sales_dt) AS diff_amt,
    DENSE_RANK() OVER (PARTITION BY customer_id, category ORDER BY amount DESC) AS rnk_in_cat
  FROM app.sales s
)
SELECT *
FROM (
  SELECT
    e.*,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY sales_dt) AS rn
  FROM enriched e
  WHERE run_amt >= 100000
)
WHERE rn = 1;

まとめ

分析関数は、明細を失わずに「その行が属する集合の性質」を同居させる表現力を提供し、PL/SQLの逐次処理や一時表の多用を大幅に削減する。PARTITION BYで集合を定義し、ORDER BYで時間や優先を与え、ROWS/RANGEで窓の広さを制御するという三点を設計の基準に据えれば、累積や前後比較、トップN、重複排除、移動統計、欠損補完、パーセンタイルまでが一貫した文法で記述できる。実装ではインデックスとフレームの整合、同一ソートの再利用、そしてSQL一発化の方針を守ることで、読みやすさと性能の両立が現場の標準として定着する。