PostgreSQL ウィンドウ関数完全マスター！分析クエリが劇的に簡単になる魔法の関数

「部署ごとの順位を付けたい」 「前月比を計算したい」 「累計を表示しながら個別データも見たい」

これまでサブクエリや自己結合で 複雑に書いていたクエリが、 ウィンドウ関数なら1行で解決します！

ウィンドウ関数を使えば、 GROUP BYせずに集計できる。 これが革命的なんです。

この記事を読めば、 売上分析からランキング作成まで、 実務で即使える30パターンをマスターできます！

---

ウィンドウ関数の基本概念

通常の集計関数との違い

GROUP BY（通常の集計）：

-- 部署ごとの平均給与（部署単位に集約される）
SELECT department, AVG(salary)
FROM employees
GROUP BY department;

-- 結果：3行（部署数分）
-- 営業部 | 450000
-- 開発部 | 520000
-- 総務部 | 380000

ウィンドウ関数：

-- 各社員の給与と部署平均を同時に表示
SELECT 
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) as dept_avg
FROM employees;

-- 結果：全社員分の行数（例：10行）
-- 田中 | 営業部 | 400000 | 450000
-- 鈴木 | 営業部 | 500000 | 450000
-- 山田 | 開発部 | 550000 | 520000
-- ...各社員のデータが残る！

ウィンドウ関数の構文

関数() OVER (
    [PARTITION BY カラム]  -- グループ分け
    [ORDER BY カラム]      -- 並び順
    [ROWS/RANGE 範囲]      -- 対象行の範囲
)

---

主要なウィンドウ関数一覧

ランキング関数

関数	説明	同順位の扱い
ROW_NUMBER()	連番	1,2,3,4,5…
RANK()	順位（ギャップあり）	1,2,2,4,5…
DENSE_RANK()	順位（ギャップなし）	1,2,2,3,4…
PERCENT_RANK()	パーセンタイル順位	0～1の値
NTILE(n)	n分割	n個のグループに分割

集計関数

関数	説明	使用例
SUM()	合計	累計売上
AVG()	平均	移動平均
COUNT()	カウント	累積件数
MAX()/MIN()	最大/最小	これまでの最高値

値アクセス関数

関数	説明	使用例
LAG()	前の行の値	前月比較
LEAD()	次の行の値	翌日予測
FIRST_VALUE()	最初の値	期初からの変化
LAST_VALUE()	最後の値	期末値

---

実践例1：ランキング作成

売上ランキング（全体・部署別）

-- サンプルデータ
CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    employee_name VARCHAR(50),
    department VARCHAR(50),
    sales_amount DECIMAL(10,2),
    sales_date DATE
);

-- 全体ランキングと部署内ランキングを同時に計算
SELECT 
    employee_name,
    department,
    sales_amount,
    -- 全体順位
    RANK() OVER (ORDER BY sales_amount DESC) as overall_rank,
    -- 部署内順位
    RANK() OVER (PARTITION BY department ORDER BY sales_amount DESC) as dept_rank,
    -- 全体での位置（パーセンタイル）
    ROUND(PERCENT_RANK() OVER (ORDER BY sales_amount DESC) * 100, 1) as percentile
FROM sales
WHERE sales_date >= '2024-01-01'
ORDER BY overall_rank;

-- 結果例：
-- 山田太郎 | 営業部 | 1500000 | 1 | 1 | 0.0
-- 鈴木花子 | 営業部 | 1200000 | 2 | 2 | 10.5
-- 田中一郎 | 開発部 | 1100000 | 3 | 1 | 21.0

トップN分析

-- 各部署の上位3名を抽出
WITH ranked_sales AS (
    SELECT 
        employee_name,
        department,
        sales_amount,
        ROW_NUMBER() OVER (
            PARTITION BY department 
            ORDER BY sales_amount DESC
        ) as rank_in_dept
    FROM sales
    WHERE sales_date >= '2024-01-01'
)
SELECT * FROM ranked_sales
WHERE rank_in_dept <= 3
ORDER BY department, rank_in_dept;

---

実践例2：累計・移動集計

累計売上の計算

-- 日付順の累計売上
SELECT 
    sales_date,
    sales_amount,
    -- 累計（最初から現在行まで）
    SUM(sales_amount) OVER (
        ORDER BY sales_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as cumulative_sales,
    -- 過去7日間の売上合計（移動合計）
    SUM(sales_amount) OVER (
        ORDER BY sales_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as rolling_7day_sales
FROM sales
ORDER BY sales_date;

移動平均の計算

-- 3日移動平均と標準偏差
SELECT 
    sales_date,
    sales_amount,
    -- 3日移動平均
    ROUND(AVG(sales_amount) OVER (
        ORDER BY sales_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ), 2) as moving_avg_3days,
    -- 標準偏差（ばらつき）
    ROUND(STDDEV(sales_amount) OVER (
        ORDER BY sales_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ), 2) as moving_stddev
FROM sales
WHERE department = '営業部'
ORDER BY sales_date;

---

実践例3：前期比・成長率

前月比・前年比の計算

-- 月次売上の前月比・前年同月比
WITH monthly_sales AS (
    SELECT 
        DATE_TRUNC('month', sales_date) as month,
        SUM(sales_amount) as total_sales
    FROM sales
    GROUP BY DATE_TRUNC('month', sales_date)
)
SELECT 
    month,
    total_sales,
    -- 前月の売上
    LAG(total_sales, 1) OVER (ORDER BY month) as prev_month_sales,
    -- 前月比（%）
    ROUND(
        (total_sales - LAG(total_sales, 1) OVER (ORDER BY month)) 
        / LAG(total_sales, 1) OVER (ORDER BY month) * 100, 
    1) as mom_growth_rate,
    -- 前年同月の売上
    LAG(total_sales, 12) OVER (ORDER BY month) as prev_year_sales,
    -- 前年同月比（%）
    ROUND(
        (total_sales - LAG(total_sales, 12) OVER (ORDER BY month))
        / LAG(total_sales, 12) OVER (ORDER BY month) * 100,
    1) as yoy_growth_rate
FROM monthly_sales
ORDER BY month;

連続増加・減少の検出

-- 3ヶ月連続で売上が増加している社員を検出
WITH sales_trends AS (
    SELECT 
        employee_name,
        sales_date,
        sales_amount,
        -- 前月の売上
        LAG(sales_amount, 1) OVER (
            PARTITION BY employee_name 
            ORDER BY sales_date
        ) as prev_sales,
        -- 成長フラグ
        CASE 
            WHEN sales_amount > LAG(sales_amount, 1) OVER (
                PARTITION BY employee_name ORDER BY sales_date
            ) THEN 1 
            ELSE 0 
        END as is_growing
    FROM sales
)
SELECT 
    employee_name,
    COUNT(*) as growth_months
FROM sales_trends
WHERE is_growing = 1
GROUP BY employee_name
HAVING COUNT(*) >= 3;

---

実践例4：グループ内での位置づけ

四分位数・中央値の計算

-- 給与の四分位数
SELECT 
    employee_name,
    department,
    salary,
    -- 四分位（4グループに分割）
    NTILE(4) OVER (ORDER BY salary) as quartile,
    -- 部署内での四分位
    NTILE(4) OVER (
        PARTITION BY department 
        ORDER BY salary
    ) as dept_quartile,
    -- 中央値との差
    salary - PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) 
        OVER (PARTITION BY department) as diff_from_median
FROM employees;

最初と最後の値

-- 期初と期末の在庫数
SELECT 
    product_id,
    inventory_date,
    quantity,
    -- 月初の在庫
    FIRST_VALUE(quantity) OVER (
        PARTITION BY product_id, DATE_TRUNC('month', inventory_date)
        ORDER BY inventory_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as month_start_qty,
    -- 月末の在庫
    LAST_VALUE(quantity) OVER (
        PARTITION BY product_id, DATE_TRUNC('month', inventory_date)
        ORDER BY inventory_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as month_end_qty
FROM inventory
ORDER BY product_id, inventory_date;

---

高度な使い方

ROWS vs RANGE の違い

-- ROWSとRANGEの違いを理解する
CREATE TABLE test_data (
    id INT,
    value INT,
    category VARCHAR(10)
);

INSERT INTO test_data VALUES 
(1, 10, 'A'),
(2, 20, 'A'),
(3, 20, 'A'),  -- 同じ値
(4, 30, 'A');

-- ROWS：物理的な行数でカウント
SELECT 
    id, value,
    SUM(value) OVER (
        ORDER BY value
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) as sum_rows
FROM test_data;
-- 結果：前後1行ずつの合計

-- RANGE：同じ値を1グループとして扱う
SELECT 
    id, value,
    SUM(value) OVER (
        ORDER BY value
        RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) as sum_range
FROM test_data;
-- 結果：値が同じ行はまとめて計算

FILTER句との組み合わせ

-- 条件付きウィンドウ関数（PostgreSQL 9.4+）
SELECT 
    department,
    employee_name,
    salary,
    -- 部署全体の平均
    AVG(salary) OVER (PARTITION BY department) as dept_avg,
    -- 高給者（50万以上）の平均
    AVG(salary) FILTER (WHERE salary >= 500000) 
        OVER (PARTITION BY department) as high_earner_avg,
    -- 自分より給与が高い人数
    COUNT(*) FILTER (WHERE salary > 400000) 
        OVER (PARTITION BY department) as higher_salary_count
FROM employees;

---

パフォーマンス最適化

インデックスの活用

-- ウィンドウ関数用のインデックス
CREATE INDEX idx_sales_dept_amount 
ON sales(department, sales_amount DESC);

-- PARTITION BY + ORDER BY に合わせたインデックス
CREATE INDEX idx_sales_date_amount 
ON sales(sales_date, sales_amount);

-- 実行計画の確認
EXPLAIN (ANALYZE, BUFFERS)
SELECT 
    employee_name,
    RANK() OVER (ORDER BY sales_amount DESC)
FROM sales;

CTEを使った最適化

-- 複雑なウィンドウ関数はCTEで分割
WITH base_data AS (
    -- まず必要なデータを絞る
    SELECT * FROM sales
    WHERE sales_date >= '2024-01-01'
),
ranked_data AS (
    -- ランキング計算
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY sales_amount DESC) as rn
    FROM base_data
)
-- 最終的な結果
SELECT * FROM ranked_data
WHERE rn <= 10;

---

よくあるユースケース集

1. 在庫管理：入出庫の累計

SELECT 
    transaction_date,
    product_id,
    CASE 
        WHEN transaction_type = 'IN' THEN quantity 
        ELSE -quantity 
    END as quantity_change,
    SUM(CASE 
        WHEN transaction_type = 'IN' THEN quantity 
        ELSE -quantity 
    END) OVER (
        PARTITION BY product_id 
        ORDER BY transaction_date
    ) as running_inventory
FROM inventory_transactions;

2. 顧客分析：RFM分析

-- Recency, Frequency, Monetary分析
WITH customer_metrics AS (
    SELECT 
        customer_id,
        MAX(order_date) as last_order_date,
        COUNT(*) as order_count,
        SUM(order_amount) as total_amount
    FROM orders
    GROUP BY customer_id
)
SELECT 
    customer_id,
    -- Recency（最近度）スコア
    NTILE(5) OVER (ORDER BY last_order_date DESC) as recency_score,
    -- Frequency（頻度）スコア
    NTILE(5) OVER (ORDER BY order_count DESC) as frequency_score,
    -- Monetary（金額）スコア
    NTILE(5) OVER (ORDER BY total_amount DESC) as monetary_score
FROM customer_metrics;

3. 時系列分析：異常値検出

-- 移動平均から大きく外れた値を検出
WITH stats AS (
    SELECT 
        measurement_time,
        value,
        AVG(value) OVER (
            ORDER BY measurement_time
            ROWS BETWEEN 10 PRECEDING AND 10 FOLLOWING
        ) as moving_avg,
        STDDEV(value) OVER (
            ORDER BY measurement_time
            ROWS BETWEEN 10 PRECEDING AND 10 FOLLOWING
        ) as moving_stddev
    FROM sensor_data
)
SELECT 
    measurement_time,
    value,
    moving_avg,
    CASE 
        WHEN ABS(value - moving_avg) > 2 * moving_stddev 
        THEN '異常値'
        ELSE '正常'
    END as status
FROM stats;

---

エラーと対処法

よくあるエラー

-- エラー1：PARTITION BYを忘れる
SELECT 
    name,
    AVG(salary) OVER ()  -- 全体平均になってしまう
FROM employees;

-- 正しい：
SELECT 
    name,
    AVG(salary) OVER (PARTITION BY department)
FROM employees;

-- エラー2：ORDER BYが必要な関数で忘れる
SELECT 
    LAG(value) OVER ()  -- エラー：ORDER BY必須
FROM data;

-- 正しい：
SELECT 
    LAG(value) OVER (ORDER BY date)
FROM data;

---

まとめ：ウィンドウ関数チートシート

ランキング：

ROW_NUMBER() OVER (ORDER BY col)
RANK() OVER (ORDER BY col)
DENSE_RANK() OVER (ORDER BY col)

累計・移動集計：

SUM(col) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING)
AVG(col) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

前後の値：

LAG(col, 1) OVER (ORDER BY date)
LEAD(col, 1) OVER (ORDER BY date)

グループ内位置：

FIRST_VALUE(col) OVER (PARTITION BY group ORDER BY date)
LAST_VALUE(col) OVER (PARTITION BY group ORDER BY date)

---

ウィンドウ関数をマスターすれば、 複雑な分析クエリが驚くほどシンプルになります！

まずは基本的なRANK()とSUM() OVERから始めて、 徐々に高度な関数に挑戦していきましょう。

---