NEW ARTICLE
【完全ガイド】PLS-00201: identifier must be declared の原因と解決方法｜ロール経由権限の罠・DBMS_LOCK・シノニム 徹底解説
  1. HOME
  2. その他
  3. 【完全ガイド】Oracle 分析関数 (OVER/PARTITION BY) 徹底解説｜LEAD・LAG・累計・移動平均・ランキングまで

【完全ガイド】Oracle 分析関数 (OVER/PARTITION BY) 徹底解説｜LEAD・LAG・累計・移動平均・ランキングまで

【完全ガイド】Oracle 分析関数 (OVER/PARTITION BY) 徹底解説｜LEAD・LAG・累計・移動平均・ランキングまで

Oracle SQL の最強の武器、分析関数（Analytic Functions）:

-- 部署ごとの給与ランキング + 平均 + 前後の給与 + 累計
SELECT 
  employee_id, department_id, salary,
  ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank,
  AVG(salary) OVER (PARTITION BY department_id) AS dept_avg,
  LAG(salary) OVER (PARTITION BY department_id ORDER BY salary) AS prev_sal,
  LEAD(salary) OVER (PARTITION BY department_id ORDER BY salary) AS next_sal,
  SUM(salary) OVER (PARTITION BY department_id ORDER BY salary 
                    ROWS UNBOUNDED PRECEDING) AS running_total
FROM employees;

1つの SQL で:

  • ランキング
  • 部署平均
  • 前の給与
  • 次の給与
  • 累計

これらを全行で計算。GROUP BY では不可能な、モダン SQL の真骨頂です。

分析関数は Oracle 8i で導入され、その後 SQL 標準にも採用された機能。しかし、日本語圏では体系的な解説が少なく、多くの開発者が使いこなせていないのが現状:

  • GROUP BY との違いを明確に理解していない
  • PARTITION BY と GROUP BY の混同
  • Window Frame（ROWS/RANGE） を知らない
  • LEAD/LAG を使わず自己 JOIN で複雑化
  • 累計・移動平均をアプリ側でループ処理
  • 前月比・前年比を PL/SQL で書いてしまう
  • RANGE UNBOUNDED PRECEDING のデフォルトの罠
  • ORDER BY の順序による結果の変化

さらに、実務では:

  • KPI ダッシュボードでの必須スキル
  • BI ツール（Tableau/Power BI） の代替
  • ETL パイプラインの効率化
  • 時系列分析の必要性
  • ランキング系機能の実装
  • Rails / Java / Python からの活用
  • パフォーマンス（自己 JOIN より高速）

など、モダン Oracle 開発の必須スキルです。

本記事では、Oracle 分析関数の完全ガイドを、リファレンスとして実用的に整理します。GROUP BY との違い、OVER 句の3要素、ランキング系・集計系・順序系・統計系関数、Window Frame の詳細、実践パターン10選、パフォーマンス、Rails/Java/Python 対応、実践シナリオ、FAQまで完全網羅。この1本で Oracle 分析関数を根本から使いこなせるようになります。

目次

結論：OVER 句が全て

時間がない方向けに、最速の理解を示します。

分析関数 vs 集約関数

項目集約関数（GROUP BY）分析関数（OVER）
戻り値グループごとに1行各行を保持
他の列GROUP BY にないと不可自由に選択可
キーワードGROUP BYOVER
用途集計集計 + 各行への追加情報

OVER 句の構文

<関数>(<引数>) OVER (
  [PARTITION BY <列>]      -- グループ分け
  [ORDER BY <列>]           -- 順序付け
  [<Window Frame>]          -- 対象範囲（ROWS/RANGE）
)

5大カテゴリ

カテゴリ関数
ランキングROW_NUMBER, RANK, DENSE_RANK, NTILE, PERCENT_RANK
集計SUM, AVG, COUNT, MIN, MAX (OVER)
順序LEAD, LAG, FIRST_VALUE, LAST_VALUE, NTH_VALUE
統計PERCENTILE_CONT, PERCENTILE_DISC, RATIO_TO_REPORT
その他LISTAGG, CUME_DIST

覚えるべき Top 5 パターン

-- ① ランキング
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY sal DESC)

-- ② 累計
SUM(sales) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING)

-- ③ 移動平均（7日）
AVG(sales) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

-- ④ 前月比
sales - LAG(sales) OVER (ORDER BY month)

-- ⑤ 部署内シェア
sales / SUM(sales) OVER (PARTITION BY dept) * 100

詳細は以下で解説します。

まず理解する：分析関数の本質

GROUP BY との根本的な違い

-- ❌ GROUP BY: 各行の情報が失われる
SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id;
-- department_id, COUNT の1行のみ

-- ✅ 分析関数: 各行を保持しつつ集計
SELECT employee_id, department_id, 
       COUNT(*) OVER (PARTITION BY department_id) AS dept_count
FROM employees;
-- 全社員の行 + 各部署の人数

実行順序での位置

FROM → WHERE → GROUP BY → HAVING → SELECT → 
      → 分析関数評価 → ORDER BY → OFFSET/FETCH

GROUP BY より後、ORDER BY より前。GROUP BY と分析関数を組み合わせる場合、集計結果を分析関数が使う。

GROUP BY と分析関数の組み合わせ

-- 月次売上と前月比
SELECT month, total_sales,
       LAG(total_sales) OVER (ORDER BY month) AS prev_month,
       total_sales - LAG(total_sales) OVER (ORDER BY month) AS diff
FROM (
  SELECT TO_CHAR(order_date, 'YYYY-MM') month,
         SUM(amount) total_sales
  FROM orders
  GROUP BY TO_CHAR(order_date, 'YYYY-MM')
);

GROUP BY で集計 → 分析関数で行間比較。強力な組み合わせ。

GROUP BY 関連の詳細は ORA-00979: not a GROUP BY expression の記事も参照してください。

OVER 句の3要素

PARTITION BY（グループ分け）

GROUP BY と似た概念、しかし各行が保持される:

-- 部署ごとに独立したウィンドウ
AVG(salary) OVER (PARTITION BY department_id)
  • PARTITION なし: 全行を1つのウィンドウとして扱う
  • PARTITION あり: 部署ごとに独立して計算

ORDER BY（順序付け）

ウィンドウ内の順序:

-- 給与順で累計
SUM(salary) OVER (ORDER BY salary)
  • 順序に依存する関数（RANK, LAG, LEAD, 累計等）で必須
  • 順序不要の関数（AVG 全体, COUNT 全体等）では省略可

Window Frame（対象範囲）

同じウィンドウ内でどの範囲を計算対象にするか:

-- 現在行と過去6行の平均（7日移動平均）
AVG(sales) OVER (ORDER BY date 
                 ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

Frame の指定方法:

  • ROWS: 物理的な行数
  • RANGE: 論理的な値の範囲

Window Frame の詳細

ROWS vs RANGE

-- ROWS: 物理的な N 行
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
-- 現在行 + 前の2行 = 3行

-- RANGE: 論理的な値の範囲
RANGE BETWEEN INTERVAL '2' DAY PRECEDING AND CURRENT ROW
-- 現在の日付から2日前まで

Frame の指定方法

-- 開始位置
UNBOUNDED PRECEDING  -- パーティション先頭
N PRECEDING          -- 現在行から N 行前
CURRENT ROW          -- 現在行

-- 終了位置
CURRENT ROW          -- 現在行
N FOLLOWING          -- 現在行から N 行後
UNBOUNDED FOLLOWING  -- パーティション末尾

よく使う Frame パターン

-- 累計（先頭から現在まで）
ROWS UNBOUNDED PRECEDING
-- または
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

-- 移動平均（過去 N 行 + 現在行）
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW  -- 7日移動平均

-- 中心移動平均（前後 N 行 + 現在行）
ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING  -- 7行中心

-- 全体
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

デフォルト Frame の罠

ORDER BY があって Frame 省略時:

SUM(x) OVER (ORDER BY d)
-- 暗黙的に:
SUM(x) OVER (ORDER BY d 
             RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

RANGE がデフォルト、期待通りでない場合あり:

  • 同じ ORDER BY 値は同一ウィンドウ扱い
  • ROWS で明示が推奨
-- ❌ 意図と違う可能性
SUM(x) OVER (ORDER BY d)

-- ✅ 明示
SUM(x) OVER (ORDER BY d ROWS UNBOUNDED PRECEDING)

【カテゴリ①】ランキング系関数

ROW_NUMBER()

連番（同順位でも別番号）:

SELECT employee_id, salary,
       ROW_NUMBER() OVER (PARTITION BY department_id 
                          ORDER BY salary DESC) rn
FROM employees;
-- 部署ごとに給与順で 1, 2, 3, ...

RANK()

同順位は同ランク、次はスキップ（1, 2, 2, 4, …）:

RANK() OVER (ORDER BY salary DESC)

DENSE_RANK()

同順位は同ランク、次は連続（1, 2, 2, 3, …）:

DENSE_RANK() OVER (ORDER BY salary DESC)

NTILE(N)

N 個のバケットに分割:

-- 給与を4分位に
SELECT employee_id, salary,
       NTILE(4) OVER (ORDER BY salary) quartile
FROM employees;
-- 1, 2, 3, 4 のいずれか

四分位、十分位、パーセンタイル分割に。

PERCENT_RANK()

相対順位（0〜1）:

PERCENT_RANK() OVER (ORDER BY salary)
-- (rank - 1) / (total_rows - 1)
-- 0.0 = 最低, 1.0 = 最高

詳細比較

ROWNUM や RANK/DENSE_RANK の詳細比較は Oracle ROWNUM vs ROW_NUMBER() vs RANK() の記事も参照してください。

【カテゴリ②】集計系関数（OVER 版）

SUM, AVG, COUNT, MIN, MAX

通常の集約関数 + OVER 句で分析関数化:

-- 部署内総給与
SUM(salary) OVER (PARTITION BY department_id)

-- 全社員平均給与
AVG(salary) OVER ()   -- OVER () で全体

-- 部署内人数
COUNT(*) OVER (PARTITION BY department_id)

-- 部署内最高給与
MAX(salary) OVER (PARTITION BY department_id)

-- 部署内最低給与
MIN(salary) OVER (PARTITION BY department_id)

累計

-- 給与順の累計
SUM(salary) OVER (ORDER BY salary ROWS UNBOUNDED PRECEDING)

-- 部署内累計
SUM(salary) OVER (PARTITION BY department_id 
                  ORDER BY salary 
                  ROWS UNBOUNDED PRECEDING)

移動平均

-- 7日移動平均
AVG(daily_sales) OVER (ORDER BY sale_date 
                       ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

-- 3日中心移動平均
AVG(daily_sales) OVER (ORDER BY sale_date 
                       ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)

部署内シェア

-- 各社員の給与が部署合計の何%か
SELECT employee_id, salary,
       ROUND(salary / SUM(salary) OVER (PARTITION BY department_id) * 100, 2) 
         AS pct_of_dept
FROM employees;

ゼロ除算の注意は ORA-01476: divisor is equal to zero の記事も参照してください。

【カテゴリ③】順序系関数

LAG（前の行の値）

前の行の値を取得:

LAG(<列>, <offset>, <default>) OVER (
  [PARTITION BY <列>] 
  ORDER BY <列>
)

例:

-- 前月の売上
SELECT month, sales,
       LAG(sales) OVER (ORDER BY month) AS prev_month_sales,
       LAG(sales, 12, 0) OVER (ORDER BY month) AS prev_year_sales
FROM monthly_sales;
  • LAG(sales): 1つ前の値
  • LAG(sales, 12, 0): 12個前の値、なければ 0

LEAD（次の行の値）

次の行の値を取得:

LEAD(<列>, <offset>, <default>) OVER (...)

例:

-- 次のイベント時刻
SELECT event_time,
       LEAD(event_time) OVER (ORDER BY event_time) AS next_event
FROM events;

FIRST_VALUE / LAST_VALUE

ウィンドウ内の最初/最後の値:

SELECT employee_id, salary, department_id,
       FIRST_VALUE(salary) OVER (
         PARTITION BY department_id ORDER BY salary DESC
       ) AS top_salary,
       LAST_VALUE(salary) OVER (
         PARTITION BY department_id ORDER BY salary DESC
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS bottom_salary
FROM employees;

LAST_VALUE の落とし穴: デフォルト Frame では**現在行が「最後」**になる。UNBOUNDED FOLLOWING を明示する必要。

NTH_VALUE

N 番目の値:

NTH_VALUE(salary, 3) OVER (
  PARTITION BY department_id ORDER BY salary DESC
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS third_highest

RESPECT / IGNORE NULLS

NULL を無視:

LAG(sales IGNORE NULLS) OVER (ORDER BY date)
-- NULL の行をスキップして前の非 NULL 値

【カテゴリ④】統計系関数

RATIO_TO_REPORT

全体に対する比率:

SELECT employee_id, salary,
       RATIO_TO_REPORT(salary) OVER (PARTITION BY department_id) AS ratio
FROM employees;
-- salary / SUM(salary) OVER (PARTITION BY department_id) と同じ

PERCENTILE_CONT

連続パーセンタイル（中央値、四分位など）:

-- 中央値
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) 
       OVER (PARTITION BY department_id) AS median_salary
FROM employees;

-- 四分位（25%, 50%, 75%）
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY salary) AS q1
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY salary) AS q3

PERCENTILE_DISC

離散パーセンタイル（実データの値のみ）:

PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary)
  • PERCENTILE_CONT: 補間して連続値
  • PERCENTILE_DISC: 実データから最も近い値

CUME_DIST

累積分布（0〜1）:

CUME_DIST() OVER (ORDER BY salary)
-- ある値以下の行数 / 全行数

LISTAGG（集約関数版もあり）

文字列連結:

-- 部署ごとの社員名リスト
SELECT department_id,
       LISTAGG(employee_name, ', ') 
         WITHIN GROUP (ORDER BY employee_name) 
         OVER (PARTITION BY department_id) AS employees
FROM employees;

実践パターン 10選

パターン① 前月比・前年比

SELECT TO_CHAR(order_date, 'YYYY-MM') month,
       SUM(amount) sales,
       LAG(SUM(amount)) OVER (ORDER BY TO_CHAR(order_date, 'YYYY-MM')) prev,
       ROUND(
         (SUM(amount) - LAG(SUM(amount)) OVER (ORDER BY TO_CHAR(order_date, 'YYYY-MM')))
         * 100.0 / NULLIF(LAG(SUM(amount)) OVER (ORDER BY TO_CHAR(order_date, 'YYYY-MM')), 0),
         2
       ) AS growth_pct
FROM orders
GROUP BY TO_CHAR(order_date, 'YYYY-MM');

パターン② 累計売上

SELECT sale_date, daily_sales,
       SUM(daily_sales) OVER (ORDER BY sale_date 
                              ROWS UNBOUNDED PRECEDING) AS cumulative
FROM daily_sales;

パターン③ 7日移動平均

SELECT sale_date, daily_sales,
       AVG(daily_sales) OVER (ORDER BY sale_date 
                              ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7
FROM daily_sales;

パターン④ 部署別 TOP 3

SELECT * FROM (
  SELECT employee_id, department_id, salary,
         ROW_NUMBER() OVER (PARTITION BY department_id 
                            ORDER BY salary DESC) AS rn
  FROM employees
) WHERE rn <= 3;

パターン⑤ 差分計算

-- 各行と前の行の差
SELECT event_time, value,
       value - LAG(value) OVER (ORDER BY event_time) AS diff
FROM metrics;

パターン⑥ ページビュー間の滞在時間

SELECT session_id, page, view_time,
       LEAD(view_time) OVER (PARTITION BY session_id ORDER BY view_time) 
         - view_time AS duration_on_page
FROM page_views;

パターン⑦ 部署内シェア

SELECT employee_id, salary,
       ROUND(salary * 100.0 / SUM(salary) OVER (PARTITION BY department_id), 2) 
         AS pct_of_dept,
       ROUND(salary * 100.0 / SUM(salary) OVER (), 2) 
         AS pct_of_total
FROM employees;

パターン⑧ ギャップ検出

-- 連続する ID の欠番検出
SELECT id, LEAD(id) OVER (ORDER BY id) - id - 1 AS gap
FROM t
WHERE LEAD(id) OVER (ORDER BY id) - id > 1;

パターン⑨ 累計割合

-- パレート分析（80/20）
SELECT product_id, sales,
       SUM(sales) OVER (ORDER BY sales DESC ROWS UNBOUNDED PRECEDING) 
         / SUM(sales) OVER () * 100 AS cumulative_pct
FROM products
ORDER BY sales DESC;

パターン⑩ セッション判定

-- 前のイベントから30分以上経過なら新セッション
SELECT user_id, event_time,
       CASE WHEN event_time - LAG(event_time) OVER (
              PARTITION BY user_id ORDER BY event_time
            ) > INTERVAL '30' MINUTE 
            OR LAG(event_time) OVER (
              PARTITION BY user_id ORDER BY event_time
            ) IS NULL
            THEN 1 ELSE 0 END AS new_session_flag
FROM events;

パフォーマンス

分析関数のコスト

  • 1回のスキャンでパーティション毎に処理
  • 自己 JOIN より高速
  • ソート必要（PARTITION BY / ORDER BY）

最適化のコツ

1. インデックス活用:

-- PARTITION BY / ORDER BY のカラムにインデックス
CREATE INDEX emp_dept_sal ON employees(department_id, salary);

2. WHERE で早期絞り込み:

-- ❌ 全件を分析
SELECT ... ROW_NUMBER() OVER (...) FROM huge_table;

-- ✅ WHERE で絞ってから
SELECT ... ROW_NUMBER() OVER (...) 
FROM huge_table 
WHERE created_at > SYSDATE - 30;

3. パーティション サイズ:

  • 小さすぎる: オーバーヘッド
  • 大きすぎる: メモリ圧迫

4. Frame の明示:

-- 明示的なほうがオプティマイザに優しい
SUM(x) OVER (ORDER BY d ROWS UNBOUNDED PRECEDING)

Rails / Java / Python 対応

Rails ActiveRecord

# 生 SQL 経由
User.select("*, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn")
    .from("(#{User.all.to_sql}) users")
    .where("rn <= 3")

# 分析関数を使うクエリ
class SalesReport
  def self.monthly_growth
    ActiveRecord::Base.connection.select_all(<<-SQL)
      SELECT month, sales,
             LAG(sales) OVER (ORDER BY month) AS prev_sales,
             sales - LAG(sales) OVER (ORDER BY month) AS diff
      FROM monthly_sales
    SQL
  end
end

Rails 8 系の詳細は Rails 8 アップグレードガイドの記事、find/find_by/where の記事、includes/preload/eager_load の記事も参照してください。

Java (JDBC)

String sql = """
    SELECT employee_id, salary,
           RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) rank
    FROM employees
    """;

try (Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery(sql)) {
    while (rs.next()) {
        int empId = rs.getInt("employee_id");
        int rank = rs.getInt("rank");
        // 処理
    }
}

Python (oracledb)

cursor.execute("""
    SELECT sale_date, daily_sales,
           AVG(daily_sales) OVER (ORDER BY sale_date 
                                  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) 
             AS ma7
    FROM daily_sales
""")

for row in cursor:
    print(row)

Pandas 経由

import pandas as pd
import oracledb

conn = oracledb.connect(...)
df = pd.read_sql("SELECT ...", conn)

# Pandas でも実行可能だが、Oracle 側で計算のほうが高速

実践シナリオ

シナリオ1：KPI ダッシュボード

-- 月次 KPI（成長率含む）
SELECT month, revenue, customers, orders,
       LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
       ROUND((revenue - LAG(revenue) OVER (ORDER BY month)) 
             * 100.0 / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 2) 
         AS growth_pct,
       AVG(revenue) OVER (ORDER BY month 
                          ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) 
         AS ma3_revenue,
       SUM(revenue) OVER (ORDER BY month ROWS UNBOUNDED PRECEDING) 
         AS cumulative_revenue
FROM monthly_kpi
ORDER BY month;

シナリオ2：営業ランキング

SELECT sales_rep, department, monthly_sales,
       RANK() OVER (ORDER BY monthly_sales DESC) AS overall_rank,
       RANK() OVER (PARTITION BY department 
                    ORDER BY monthly_sales DESC) AS dept_rank,
       DENSE_RANK() OVER (PARTITION BY department 
                          ORDER BY monthly_sales DESC) AS dept_dense_rank,
       NTILE(4) OVER (ORDER BY monthly_sales DESC) AS performance_quartile
FROM sales_data
WHERE month = TRUNC(SYSDATE, 'MM');

シナリオ3：Cohort 分析

-- ユーザーの初回購入からの経過月別 LTV
SELECT 
  cohort_month,
  months_since_first_purchase,
  SUM(revenue) AS revenue,
  SUM(SUM(revenue)) OVER (
    PARTITION BY cohort_month 
    ORDER BY months_since_first_purchase 
    ROWS UNBOUNDED PRECEDING
  ) AS cumulative_ltv
FROM (
  SELECT customer_id,
         TO_CHAR(MIN(order_date) OVER (PARTITION BY customer_id), 'YYYY-MM') 
           AS cohort_month,
         MONTHS_BETWEEN(order_date, 
                        MIN(order_date) OVER (PARTITION BY customer_id)) 
           AS months_since_first_purchase,
         amount AS revenue
  FROM orders
)
GROUP BY cohort_month, months_since_first_purchase
ORDER BY cohort_month, months_since_first_purchase;

シナリオ4：異常検知

-- 平均から標準偏差 3σ 以上乖離
SELECT sale_date, sales,
       AVG(sales) OVER (ORDER BY sale_date 
                        ROWS BETWEEN 29 PRECEDING AND 1 PRECEDING) AS ma30,
       STDDEV(sales) OVER (ORDER BY sale_date 
                           ROWS BETWEEN 29 PRECEDING AND 1 PRECEDING) AS sd30,
       CASE WHEN ABS(sales - AVG(sales) OVER (ORDER BY sale_date 
                                              ROWS BETWEEN 29 PRECEDING AND 1 PRECEDING))
                 > 3 * STDDEV(sales) OVER (ORDER BY sale_date 
                                           ROWS BETWEEN 29 PRECEDING AND 1 PRECEDING)
            THEN 'ANOMALY' ELSE 'NORMAL' END AS status
FROM daily_sales;

シナリオ5：セッション分析

-- Web セッション（30分区切り）
WITH session_flags AS (
  SELECT user_id, event_time,
         CASE WHEN event_time - LAG(event_time) OVER (
                PARTITION BY user_id ORDER BY event_time
              ) > INTERVAL '30' MINUTE OR LAG(event_time) OVER (
                PARTITION BY user_id ORDER BY event_time
              ) IS NULL THEN 1 ELSE 0 END AS new_session
  FROM events
)
SELECT user_id, event_time,
       SUM(new_session) OVER (PARTITION BY user_id 
                              ORDER BY event_time 
                              ROWS UNBOUNDED PRECEDING) AS session_id
FROM session_flags;

シナリオ6：Retention Rate

SELECT cohort_month, week_since_cohort,
       users_active,
       FIRST_VALUE(users_active) OVER (
         PARTITION BY cohort_month ORDER BY week_since_cohort
       ) AS cohort_size,
       ROUND(users_active * 100.0 / FIRST_VALUE(users_active) OVER (
         PARTITION BY cohort_month ORDER BY week_since_cohort
       ), 2) AS retention_pct
FROM cohort_data
ORDER BY cohort_month, week_since_cohort;

シナリオ7：MERGE 文と分析関数

-- 重複除去 MERGE
MERGE INTO target dst
USING (
  SELECT * FROM (
    SELECT src.*, 
           ROW_NUMBER() OVER (
             PARTITION BY unique_key ORDER BY updated_at DESC
           ) AS rn
    FROM staging src
  ) WHERE rn = 1  -- 最新版のみ
) src ON (dst.id = src.id)
WHEN MATCHED THEN UPDATE SET dst.value = src.value
WHEN NOT MATCHED THEN INSERT VALUES (src.id, src.value);

MERGE の詳細は Oracle MERGE 文 使い方の記事、UNIQUE 制約は ORA-00001: unique constraint violated の記事も参照してください。

シナリオ8：時系列予測（単純移動平均）

-- 過去30日の平均を予測値に
SELECT sale_date, sales,
       AVG(sales) OVER (ORDER BY sale_date 
                        ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) 
         AS forecast_next_day
FROM daily_sales
ORDER BY sale_date;

シナリオ9：Docker Oracle での実行

docker exec -it oracle-xe sqlplus scott/tiger <<EOF
SELECT ename, sal, 
       RANK() OVER (ORDER BY sal DESC) rnk
FROM emp;
EOF

Docker 関連は docker daemon 接続エラーの記事、Docker no space left on device の記事も参照してください。

シナリオ10：Rails でのランキング API

class ProductRankingController < ApplicationController
  def index
    sql = <<-SQL
      SELECT product_id, product_name, sales,
             RANK() OVER (ORDER BY sales DESC) AS overall_rank,
             RANK() OVER (PARTITION BY category ORDER BY sales DESC) 
               AS category_rank
      FROM products
      WHERE created_at > SYSDATE - 30
    SQL
    render json: ActiveRecord::Base.connection.select_all(sql)
  end
end

Solid Queue の詳細は Solid Queue 使い方の記事も参照してください。

予防のベストプラクティス

1. 常に Frame を明示

-- ❌ デフォルトに依存
SUM(x) OVER (ORDER BY d)

-- ✅ 明示
SUM(x) OVER (ORDER BY d ROWS UNBOUNDED PRECEDING)

2. ORDER BY を意識

順序依存関数（RANK, LAG, LEAD, 累計）では ORDER BY 必須。

3. NULL の扱い

-- 明示的な NULL 処理
LAG(sales IGNORE NULLS) OVER (ORDER BY d)
ORDER BY sales DESC NULLS LAST

4. インデックス設計

CREATE INDEX ON t(partition_col, order_col);

5. WHERE で絞ってから

-- 分析関数は全件対象
-- WHERE で絞ってから分析
FROM (SELECT ... FROM t WHERE ...) sub

6. サブクエリで rn <= N

-- TOP-N per group
SELECT * FROM (
  SELECT ..., ROW_NUMBER() OVER (...) rn FROM t
) WHERE rn <= 3;

7. デバッグ時は分解

-- 順番に確認
SELECT ..., 
       LAG(x) OVER (ORDER BY d) prev_x,
       x - LAG(x) OVER (ORDER BY d) diff
FROM t;

8. LAST_VALUE の Frame 明示

LAST_VALUE(x) OVER (
  ORDER BY d 
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
-- Frame 明示しないと現在行が「最後」

9. 分析関数と GROUP BY の使い分け

  • GROUP BY: 集計結果のみ必要
  • 分析関数: 各行 + 集計値必要

10. パフォーマンステスト

EXPLAIN PLAN FOR SELECT ... OVER (...);
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

EXPLAIN PLAN 関連はパフォーマンス系記事、パラメータ確認は Oracle パラメータ確認（V$PARAMETER）の記事も参照してください。

トラブルシューティング

期待通りの結果にならない

Frame の確認:

-- ROWS vs RANGE
-- デフォルトは RANGE

全て同じ値になる

PARTITION BY 忘れ、またはORDER BY 忘れ:

-- ✅ ちゃんと指定
SUM(x) OVER (PARTITION BY g ORDER BY d ROWS UNBOUNDED PRECEDING)

LAG/LEAD で NULL

最初/最後の行は NULL:

-- デフォルト値指定
LAG(x, 1, 0) OVER (ORDER BY d)

パフォーマンスが遅い

  • 大きすぎるパーティション
  • インデックスなし
  • PGA 不足

PGA 関連は ORA-04030: process memory の記事も参照してください。

順序が保証されない

ORDER BY タイブレーク:

ORDER BY salary DESC, employee_id

PostgreSQL / SQL Server との違い

  • Oracle: 分析関数の Frame は ROWS/RANGE
  • PostgreSQL: 概ね同じ、一部構文違い
  • SQL Server: 概ね同じ

よくある質問（FAQ）

Q1. 分析関数と Window Function

同じ意味。SQL 標準は “Window Function”、Oracle 内では “Analytic Function”。

Q2. HAVING で分析関数使える？

使えない（HAVING は GROUP BY 後）:

-- ❌
GROUP BY ... HAVING RANK() OVER (...) < 5

-- ✅ サブクエリ化
SELECT * FROM (
  SELECT ..., RANK() OVER (...) rnk FROM ...
) WHERE rnk < 5

Q3. WHERE で分析関数使える？

使えない（WHERE は分析関数評価前）:

-- ❌
WHERE ROW_NUMBER() OVER (...) = 1

-- ✅ サブクエリ化

Q4. GROUP BY と分析関数の組み合わせ

GROUP BY で集計 → 分析関数で行間比較:

SELECT month, SUM(sales),
       LAG(SUM(sales)) OVER (ORDER BY month)
FROM t GROUP BY month;

Q5. LAST_VALUE の罠

デフォルト Frame で最後が現在行になる:

-- ✅ Frame 明示
LAST_VALUE(x) OVER (
  ORDER BY d
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

Q6. PARTITION BY と GROUP BY の違い

  • PARTITION BY: 各行保持、分析関数
  • GROUP BY: 集計結果のみ

Q7. NULL の扱い

LAG(x IGNORE NULLS) OVER (...)
NTH_VALUE(x, 3) IGNORE NULLS OVER (...)

Q8. ネストできる？

-- ❌ 分析関数の入れ子は不可
SUM(RANK() OVER (...)) OVER (...)

-- ✅ サブクエリ化

Q9. パフォーマンス vs 自己 JOIN

分析関数の方が高速（1回のスキャン）。

Q10. Rails での使い方

select + 生 SQL 文字列で分析関数を使う。

Q11. Materialized View で

CREATE MATERIALIZED VIEW mv AS
SELECT ..., RANK() OVER (...) FROM ...;

キャッシュとして活用。

Q12. Autonomous DB での対応

完全対応。自動チューニングの恩恵。

参考リンク

Oracle 公式

まとめ

Oracle 分析関数の要点を再整理します。

集約関数 vs 分析関数

GROUP BY: グループごとに1行、他列は選択不可
OVER:     各行を保持、他列も自由

OVER 句の3要素

<関数>() OVER (
  [PARTITION BY]  -- グループ分け
  [ORDER BY]      -- 順序
  [Frame]         -- 対象範囲
)

5大カテゴリ

カテゴリ関数
ランキングROW_NUMBER, RANK, DENSE_RANK, NTILE
集計SUM, AVG, COUNT, MIN, MAX (OVER)
順序LEAD, LAG, FIRST_VALUE, LAST_VALUE
統計PERCENTILE_CONT, RATIO_TO_REPORT
文字列LISTAGG

Frame の指定

-- 累計
ROWS UNBOUNDED PRECEDING

-- 移動平均（過去N日）
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW

-- 中心移動平均
ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING

-- 全体
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

10大実践パターン

#パターン使う関数
①前月比・前年比LAG
②累計SUM OVER UNBOUNDED PRECEDING
③移動平均AVG OVER ROWS BETWEEN
④部署別 TOP NROW_NUMBER + サブクエリ
⑤差分計算LAG
⑥滞在時間LEAD
⑦部署内シェアSUM OVER PARTITION BY
⑧ギャップ検出LEAD
⑨累計割合SUM + SUM OVER ()
⑩セッション判定LAG + SUM

主な関数

-- 順位
ROW_NUMBER() OVER (PARTITION BY d ORDER BY s DESC)
RANK() OVER (ORDER BY s DESC)
DENSE_RANK() OVER (ORDER BY s DESC)
NTILE(4) OVER (ORDER BY s)

-- 前後
LAG(s, 1, 0) OVER (ORDER BY d)
LEAD(s) OVER (ORDER BY d)
FIRST_VALUE(s) OVER (ORDER BY d)
LAST_VALUE(s) OVER (ORDER BY d ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

-- 集計
SUM(s) OVER (PARTITION BY d)
AVG(s) OVER (ORDER BY d ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

-- 統計
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY s) OVER (PARTITION BY d)
RATIO_TO_REPORT(s) OVER (PARTITION BY d)

予防のベストプラクティス

  • 常に Frame を明示
  • ORDER BY を意識
  • NULL の扱い（IGNORE NULLS）
  • インデックス設計
  • WHERE で絞ってから
  • サブクエリで rn <= N
  • LAST_VALUE の Frame 明示
  • 分析関数と GROUP BY の使い分け

事故防止

  • Frame デフォルトは RANGE（意図と違う可能性）
  • HAVING/WHERE で分析関数不可
  • 順序保証のためタイブレーク
  • PGA 消費に注意（大量ソート）

これらの知識は、Oracle での KPI ダッシュボード・レポート作成・ETL・時系列分析・BI 代替・Rails / Java / Python 開発・データサイエンスなど、あらゆる場面で活用できます。本記事をブックマークしておけば、Oracle 分析関数を確実に使いこなせるようになります。

本記事は2026年6月時点の情報をもとに、Oracle Database 19c〜23ai での動作確認・公式ドキュメントに基づき作成しています。Oracle のバージョンにより挙動が異なる場合があるため、最新の情報は Oracle 公式ドキュメント（docs.oracle.com）もあわせてご確認ください。