PostgreSQLウィンドウ関数MAX()で複雑な分析を簡単に！実例で学ぶ完全マスター

「各社員の給与と部門内最高給与を同時に表示したい」 「商品ごとに過去最高価格を横に並べて表示したい」 「GROUP BYすると他のカラムが消えちゃう…」

こんな悩み、ありませんか？

実は、ウィンドウ関数のMAX()を使えば、これらの問題が魔法のように解決します！

通常のMAX()とは違い、行を集約せずに各行に最大値を付け加えることができるんです。この記事を読めば、データ分析のレベルが一段階上がること間違いなし！

---

ウィンドウ関数のMAX()を簡単に理解する

通常のMAX vs ウィンドウ関数のMAX

まず、違いを見てみましょう：

-- サンプルテーブル：社員データ
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    salary INTEGER,
    hire_date DATE
);

INSERT INTO employees (name, department, salary, hire_date) VALUES
('田中太郎', '営業部', 450000, '2020-04-01'),
('佐藤花子', '営業部', 520000, '2019-07-15'),
('鈴木一郎', '開発部', 680000, '2018-01-10'),
('高橋美咲', '開発部', 590000, '2021-03-20'),
('山田次郎', '人事部', 420000, '2022-06-01');

通常のMAX（行が集約される）：

SELECT department, MAX(salary) AS 最高給与
FROM employees
GROUP BY department;

-- 結果：
-- 営業部 | 520000
-- 開発部 | 680000
-- 人事部 | 420000
-- 個人の情報は消えてしまう！

ウィンドウ関数のMAX（全行が残る）：

SELECT 
    name,
    department,
    salary,
    MAX(salary) OVER (PARTITION BY department) AS 部門内最高給与
FROM employees;

-- 結果：
-- 田中太郎 | 営業部 | 450000 | 520000
-- 佐藤花子 | 営業部 | 520000 | 520000
-- 鈴木一郎 | 開発部 | 680000 | 680000
-- 高橋美咲 | 開発部 | 590000 | 680000
-- 山田次郎 | 人事部 | 420000 | 420000

**魔法みたいでしょう？**各社員の情報を保ちながら、部門の最高給与も表示できました！

---

基本構文：OVER句の使い方

基本パターン

MAX(カラム) OVER (
    PARTITION BY グループ化カラム  -- 省略可
    ORDER BY ソートカラム          -- 省略可
    ROWS/RANGE フレーム指定        -- 省略可
)

パターン1：全体の最大値

-- 全社員中の最高給与を各行に表示
SELECT 
    name,
    salary,
    MAX(salary) OVER () AS 全体最高給与
FROM employees;

-- 結果：全員に680000が表示される

パターン2：グループごとの最大値（PARTITION BY）

-- 部門ごとの最高給与
SELECT 
    name,
    department,
    salary,
    MAX(salary) OVER (PARTITION BY department) AS 部門最高給与,
    salary - MAX(salary) OVER (PARTITION BY department) AS 最高給与との差
FROM employees
ORDER BY department, salary DESC;

パターン3：累積最大値（ORDER BY）

-- 入社日順での累積最高給与（その時点での最高給与）
SELECT 
    name,
    hire_date,
    salary,
    MAX(salary) OVER (ORDER BY hire_date) AS その時点の最高給与
FROM employees
ORDER BY hire_date;

-- 結果：
-- 鈴木一郎 | 2018-01-10 | 680000 | 680000
-- 佐藤花子 | 2019-07-15 | 520000 | 680000
-- 田中太郎 | 2020-04-01 | 450000 | 680000
-- 高橋美咲 | 2021-03-20 | 590000 | 680000
-- 山田次郎 | 2022-06-01 | 420000 | 680000

---

実践例1：売上分析での活用

月次売上の過去最高を追跡

-- 月次売上テーブル
CREATE TABLE monthly_sales (
    month DATE,
    category VARCHAR(50),
    sales_amount DECIMAL(10,2)
);

INSERT INTO monthly_sales VALUES
('2024-01-01', '家電', 1500000),
('2024-02-01', '家電', 1800000),
('2024-03-01', '家電', 1600000),
('2024-04-01', '家電', 2100000),
('2024-01-01', '衣類', 800000),
('2024-02-01', '衣類', 950000),
('2024-03-01', '衣類', 1100000),
('2024-04-01', '衣類', 900000);

-- 各月の売上と過去最高売上を比較
SELECT 
    month,
    category,
    sales_amount AS 当月売上,
    MAX(sales_amount) OVER (
        PARTITION BY category 
        ORDER BY month
        ROWS UNBOUNDED PRECEDING
    ) AS 過去最高売上,
    CASE 
        WHEN sales_amount = MAX(sales_amount) OVER (
            PARTITION BY category 
            ORDER BY month
            ROWS UNBOUNDED PRECEDING
        ) THEN '★最高記録更新！'
        ELSE ''
    END AS 備考
FROM monthly_sales
ORDER BY category, month;

前年同月比での最大値

-- 過去3年間の同月最高売上
SELECT 
    month,
    category,
    sales_amount,
    MAX(sales_amount) OVER (
        PARTITION BY category, EXTRACT(MONTH FROM month)
    ) AS 同月最高売上,
    ROUND(100.0 * sales_amount / NULLIF(MAX(sales_amount) OVER (
        PARTITION BY category, EXTRACT(MONTH FROM month)
    ), 0), 2) AS 最高比率
FROM monthly_sales
ORDER BY month, category;

---

実践例2：在庫管理での活用

商品ごとの価格変動追跡

-- 価格履歴テーブル
CREATE TABLE price_history (
    product_id INTEGER,
    price_date DATE,
    price DECIMAL(10,2)
);

INSERT INTO price_history VALUES
(1, '2024-01-01', 10000),
(1, '2024-02-01', 11000),
(1, '2024-03-01', 9500),
(1, '2024-04-01', 12000),
(2, '2024-01-01', 5000),
(2, '2024-02-01', 5500),
(2, '2024-03-01', 5200);

-- 各時点での過去最高価格と現在価格を比較
SELECT 
    product_id,
    price_date,
    price AS 現在価格,
    MAX(price) OVER (
        PARTITION BY product_id 
        ORDER BY price_date
    ) AS 過去最高価格,
    MAX(price) OVER (
        PARTITION BY product_id
    ) AS 全期間最高価格,
    ROUND(100.0 * price / MAX(price) OVER (PARTITION BY product_id), 2) AS 最高値比
FROM price_history
ORDER BY product_id, price_date;

---

実践例3：ランキングとの組み合わせ

部門内での給与ランキングと最高値

-- 複数のウィンドウ関数を組み合わせる
SELECT 
    name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS 部門内順位,
    MAX(salary) OVER (PARTITION BY department) AS 部門最高給与,
    ROUND(100.0 * salary / MAX(salary) OVER (PARTITION BY department), 2) AS 最高給与比,
    MAX(salary) OVER () AS 全社最高給与,
    MAX(salary) OVER () - salary AS 全社最高との差額
FROM employees
ORDER BY department, salary DESC;

トップN分析

-- 各カテゴリのトップ3の商品とその最高売上
WITH ranked_products AS (
    SELECT 
        product_name,
        category,
        total_sales,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY total_sales DESC) AS rn,
        MAX(total_sales) OVER (PARTITION BY category) AS category_max
    FROM product_sales
)
SELECT 
    product_name,
    category,
    total_sales,
    category_max AS カテゴリ最高売上,
    ROUND(100.0 * total_sales / category_max, 2) AS 最高売上比
FROM ranked_products
WHERE rn <= 3;

---

フレーム指定：範囲を細かく制御

ROWS vs RANGE

-- 直近3レコードでの最大値（ROWS）
SELECT 
    date,
    value,
    MAX(value) OVER (
        ORDER BY date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS 直近3日間最大
FROM daily_data;

-- 期間指定での最大値（RANGE）
SELECT 
    date,
    value,
    MAX(value) OVER (
        ORDER BY date
        RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW
    ) AS 過去7日間最大
FROM daily_data;

実用例：移動最大値

-- 株価の52週最高値
CREATE TABLE stock_prices (
    date DATE,
    symbol VARCHAR(10),
    closing_price DECIMAL(10,2)
);

SELECT 
    date,
    symbol,
    closing_price,
    MAX(closing_price) OVER (
        PARTITION BY symbol
        ORDER BY date
        RANGE BETWEEN INTERVAL '52 weeks' PRECEDING AND CURRENT ROW
    ) AS 52週最高値,
    ROUND(100.0 * closing_price / NULLIF(
        MAX(closing_price) OVER (
            PARTITION BY symbol
            ORDER BY date
            RANGE BETWEEN INTERVAL '52 weeks' PRECEDING AND CURRENT ROW
        ), 0), 2) AS 最高値比
FROM stock_prices
WHERE date >= CURRENT_DATE - INTERVAL '2 years';

---

パフォーマンス最適化

インデックスの活用

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

-- ウィンドウ関数のパフォーマンス改善
EXPLAIN ANALYZE
SELECT 
    name,
    department,
    salary,
    MAX(salary) OVER (PARTITION BY department) AS dept_max
FROM employees;

大量データでの注意点

-- CTEで事前集計してからウィンドウ関数
WITH pre_aggregated AS (
    SELECT 
        department,
        MAX(salary) AS dept_max
    FROM employees
    GROUP BY department
)
SELECT 
    e.*,
    pa.dept_max
FROM employees e
JOIN pre_aggregated pa ON e.department = pa.department;

-- 場合によってはJOINの方が速いことも

---

よくある使用例とテンプレート

1. 累積最大値（ハイウォーターマーク）

-- システムの同時接続数の過去最高を追跡
SELECT 
    timestamp,
    concurrent_users,
    MAX(concurrent_users) OVER (ORDER BY timestamp) AS 過去最高同時接続数
FROM system_metrics;

2. グループ内比較

-- 店舗ごとの日次売上と月間最高売上
SELECT 
    store_id,
    date,
    daily_sales,
    MAX(daily_sales) OVER (
        PARTITION BY store_id, DATE_TRUNC('month', date)
    ) AS 月間最高売上
FROM store_sales;

3. 異常値検出

-- 平均の3倍を超える異常値を検出
SELECT 
    *,
    CASE 
        WHEN value > 3 * AVG(value) OVER (PARTITION BY category) THEN '異常値'
        WHEN value = MAX(value) OVER (PARTITION BY category) THEN '最大値'
        ELSE '正常'
    END AS status
FROM measurements;

---

NULLの扱い

MAX()はNULLを無視

-- NULLは自動的に無視される
SELECT 
    name,
    bonus,
    MAX(bonus) OVER () AS 最高ボーナス
FROM employees;
-- bonusにNULLがあっても、NULL以外の最大値が返される

COALESCEと組み合わせ

-- NULLを0として扱いたい場合
SELECT 
    name,
    bonus,
    MAX(COALESCE(bonus, 0)) OVER (PARTITION BY department) AS 部門最高ボーナス
FROM employees;

---

まとめ：ウィンドウ関数MAXで分析力アップ！

今日マスターしたポイント：

✅ ウィンドウ関数MAX = 行を保ったまま最大値を付加 ✅ OVER() だけで全体の最大値 ✅ PARTITION BY でグループごとの最大値 ✅ ORDER BY で累積最大値 ✅ ROWS/RANGE で範囲を細かく制御 ✅ 通常のGROUP BYより柔軟で強力 ✅ 他のウィンドウ関数と組み合わせ可能

最初は「GROUP BY で十分じゃない？」と思うかもしれません。

でも、ウィンドウ関数のMAX()を使いこなせば、複雑なサブクエリやJOINが不要になり、SQLがシンプルで読みやすくなります。

今日から、あなたのSQL分析スキルは確実にレベルアップしました！

データの海から、価値ある洞察を見つけ出しましょう！

---

次のステップにおすすめ：

• PostgreSQLウィンドウ関数の全関数マスター
• LAG/LEADで時系列分析を極める
• CTEとウィンドウ関数の組み合わせテクニック