【Oracle】分析関数（ウィンドウ関数）の使い方完全ガイド｜LAG・LEAD・FIRST_VALUE・NTILE・累計・移動平均まで解説

-- 集計関数（GROUP BY）: 部門ごとに1行に集約される
SELECT dept, SUM(sales_amt) AS total_sales
FROM monthly_sales
GROUP BY dept;
-- → 2行（営業1部・営業2部それぞれ1行）

-- 分析関数（OVER句）: 全12行を保ったまま各行に合計を付与する
SELECT dept, yr_month, sales_amt,
       SUM(sales_amt) OVER (PARTITION BY dept) AS dept_total
FROM monthly_sales;
-- → 12行（全行を保持。dept_totalには部門合計が付く）

CREATE TABLE monthly_sales (
    dept      VARCHAR2(20),   -- 部門名
    yr_month  VARCHAR2(7),    -- 年月 (例: 2025-01)
    sales_amt NUMBER          -- 売上金額
);

INSERT INTO monthly_sales VALUES ('営業1部', '2025-01', 1200000);
INSERT INTO monthly_sales VALUES ('営業1部', '2025-02',  980000);
INSERT INTO monthly_sales VALUES ('営業1部', '2025-03', 1450000);
INSERT INTO monthly_sales VALUES ('営業1部', '2025-04', 1600000);
INSERT INTO monthly_sales VALUES ('営業1部', '2025-05', 1380000);
INSERT INTO monthly_sales VALUES ('営業1部', '2025-06', 1700000);
INSERT INTO monthly_sales VALUES ('営業2部', '2025-01',  900000);
INSERT INTO monthly_sales VALUES ('営業2部', '2025-02', 1100000);
INSERT INTO monthly_sales VALUES ('営業2部', '2025-03',  850000);
INSERT INTO monthly_sales VALUES ('営業2部', '2025-04', 1200000);
INSERT INTO monthly_sales VALUES ('営業2部', '2025-05', 1350000);
INSERT INTO monthly_sales VALUES ('営業2部', '2025-06', 1500000);
COMMIT;

分析関数名(引数) OVER (
    [PARTITION BY パーティション列, ...]   -- グループ分けの単位（省略で全行が1グループ）
    [ORDER BY ソート列 [ASC|DESC], ...]    -- ウィンドウ内のソート順（省略可）
    [ROWS | RANGE BETWEEN 開始 AND 終了]  -- ウィンドウ（行範囲）の指定（省略可）
)

SELECT
    dept,
    yr_month,
    sales_amt,
    ROW_NUMBER() OVER (PARTITION BY dept ORDER BY sales_amt DESC) AS row_num,
    RANK()       OVER (PARTITION BY dept ORDER BY sales_amt DESC) AS rank_num,
    DENSE_RANK() OVER (PARTITION BY dept ORDER BY sales_amt DESC) AS dense_rank
FROM monthly_sales
ORDER BY dept, sales_amt DESC;

LAG (列名 [, 遡る行数 [, デフォルト値]]) OVER (PARTITION BY ... ORDER BY ...)
LEAD(列名 [, 進む行数 [, デフォルト値]]) OVER (PARTITION BY ... ORDER BY ...)
-- 行数のデフォルト: 1（直前/直後）
-- デフォルト値のデフォルト: NULL（範囲外のときに返す値）

SELECT
    dept,
    yr_month,
    sales_amt,
    LAG(sales_amt, 1, 0) OVER (PARTITION BY dept ORDER BY yr_month) AS prev_month,
    LEAD(sales_amt, 1, 0) OVER (PARTITION BY dept ORDER BY yr_month) AS next_month,
    sales_amt
        - LAG(sales_amt, 1) OVER (PARTITION BY dept ORDER BY yr_month) AS mom_diff
FROM monthly_sales
ORDER BY dept, yr_month;

SELECT
    dept,
    yr_month,
    sales_amt,
    FIRST_VALUE(sales_amt) OVER (
        PARTITION BY dept ORDER BY yr_month
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS first_month_sales,
    LAST_VALUE(sales_amt) OVER (
        PARTITION BY dept ORDER BY yr_month
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  -- 全行を見るため明示必須
    ) AS last_month_sales
FROM monthly_sales
ORDER BY dept, yr_month;

SELECT
    dept,
    yr_month,
    sales_amt,
    NTH_VALUE(sales_amt, 2) OVER (
        PARTITION BY dept ORDER BY sales_amt DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS second_highest
FROM monthly_sales
ORDER BY dept, yr_month;
-- 営業1部の second_highest は全行で 1,600,000（2位の売上）が付く

SELECT
    dept,
    yr_month,
    sales_amt,
    NTILE(3) OVER (PARTITION BY dept ORDER BY sales_amt) AS tile
FROM monthly_sales
ORDER BY dept, tile, sales_amt;
-- tile=1: 下位1/3（売上が低い月）
-- tile=2: 中位1/3
-- tile=3: 上位1/3（売上が高い月）

SELECT
    dept,
    yr_month,
    sales_amt,
    ROUND(CUME_DIST()    OVER (PARTITION BY dept ORDER BY sales_amt), 2) AS cum_dist,
    ROUND(PERCENT_RANK() OVER (PARTITION BY dept ORDER BY sales_amt), 2) AS pct_rank
FROM monthly_sales
ORDER BY dept, sales_amt;

SELECT
    dept,
    yr_month,
    sales_amt,
    SUM(sales_amt) OVER (
        PARTITION BY dept
        ORDER BY yr_month
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  -- 先頭から現在行まで
    ) AS cumulative_sales
FROM monthly_sales
ORDER BY dept, yr_month;

SELECT
    dept,
    yr_month,
    sales_amt,
    ROUND(
        AVG(sales_amt) OVER (
            PARTITION BY dept
            ORDER BY yr_month
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW  -- 2ヶ月前〜現在の3行
        )
    ) AS moving_avg_3m
FROM monthly_sales
ORDER BY dept, yr_month;

SELECT
    dept,
    yr_month,
    sales_amt,
    LAG(sales_amt) OVER (PARTITION BY dept ORDER BY yr_month) AS prev_sales,
    ROUND(
        (sales_amt - LAG(sales_amt) OVER (PARTITION BY dept ORDER BY yr_month))
        / LAG(sales_amt) OVER (PARTITION BY dept ORDER BY yr_month) * 100,
        1
    ) AS mom_change_pct    -- 前月比増減率（%）
FROM monthly_sales
ORDER BY dept, yr_month;

SELECT dept, yr_month, sales_amt, rn AS rank_in_dept
FROM (
    SELECT
        dept, yr_month, sales_amt,
        ROW_NUMBER() OVER (PARTITION BY dept ORDER BY sales_amt DESC) AS rn
    FROM monthly_sales
)
WHERE rn <= 3
ORDER BY dept, rn;

-- 重複がある仮定のテーブルから、同一（dept, yr_month）で最新のROWIDを持つ行のみ残す
DELETE FROM monthly_sales
WHERE ROWID NOT IN (
    SELECT MAX(ROWID)
    FROM monthly_sales
    GROUP BY dept, yr_month
);

-- ROW_NUMBER を使った重複確認（削除前に内容を確認したい場合）
SELECT dept, yr_month, sales_amt, ROWID
FROM (
    SELECT dept, yr_month, sales_amt, ROWID,
           ROW_NUMBER() OVER (
               PARTITION BY dept, yr_month
               ORDER BY ROWID DESC
           ) AS rn
    FROM monthly_sales
)
WHERE rn > 1;  -- rn > 1 が重複行（最初の1件以外）

SELECT
    dept,
    yr_month,
    sales_amt,
    LAG(sales_amt, 12) OVER (PARTITION BY dept ORDER BY yr_month) AS same_month_prev_yr,
    ROUND(
        (sales_amt - LAG(sales_amt, 12) OVER (PARTITION BY dept ORDER BY yr_month))
        / LAG(sales_amt, 12) OVER (PARTITION BY dept ORDER BY yr_month) * 100,
        1
    ) AS yoy_change_pct    -- 前年同月比増減率（%）
FROM monthly_sales
ORDER BY dept, yr_month;
-- 2026年以降のデータが追加されると、12ヶ月前の値が自動的に参照される