【SQL】MAX関数完全ガイド｜GROUP BY・最大値の行取得・日付MAX・GREATEST・NULL対策・ウィンドウ関数・HAVINGまで

-- 商品テーブルの最高価格を取得
SELECT MAX(price) AS max_price FROM products;
-- 結果: 24,990

-- WHERE で絞り込んでから最大値
SELECT MAX(price) AS max_electronics_price
FROM products
WHERE category = '家電';

-- 最新の注文日を取得
SELECT MAX(order_date) AS latest_order FROM orders;
-- 結果: 2024-03-31

-- 顧客の最終ログイン日
SELECT MAX(login_at) AS last_login FROM users WHERE user_id = 101;

-- 文字列は辞書順（照合順序）で最大値を判定
SELECT MAX(name) FROM employees;
-- 照合順序に依存: 'Z' が先か 'あ' が先かは COLLATION による

-- アルファベットなら 'Z...' が最大
SELECT MAX(product_code) FROM products;
-- 結果: 'Z-999'（辞書順で最後のコード）

-- カテゴリごとの最高価格
SELECT
    category,
    MAX(price)      AS max_price,
    MIN(price)      AS min_price,
    MAX(price) - MIN(price) AS price_range
FROM products
GROUP BY category
ORDER BY max_price DESC;
-- 結果:
-- 家電    | 249,990 | 1,980 | 248,010
-- 家具    | 89,990  | 5,980 | 84,010
-- 食品    | 4,980   | 198   | 4,782

-- 各部署で最後に入社した日付
SELECT
    department,
    MAX(hire_date) AS latest_hire
FROM employees
GROUP BY department;

-- 最高価格の商品を取得
SELECT * FROM products
WHERE price = (SELECT MAX(price) FROM products);
-- 最大値が複数行に存在する場合はすべて返る

-- カテゴリが「家電」の中で最高価格の商品
SELECT * FROM products
WHERE category = '家電'
  AND price = (SELECT MAX(price) FROM products WHERE category = '家電');

-- カテゴリごとに最高価格の商品を1件ずつ取得
SELECT category, product_name, price
FROM (
    SELECT
        category,
        product_name,
        price,
        ROW_NUMBER() OVER (
            PARTITION BY category
            ORDER BY price DESC
        ) AS rn
    FROM products
) ranked
WHERE rn = 1;
-- 結果: カテゴリごとに最高価格の1件が返る
-- 同額があっても1件（ORDER BY の2番目の列で制御可能）

-- RANK を使うと同率1位が複数あれば全て返る
SELECT category, product_name, price
FROM (
    SELECT
        category,
        product_name,
        price,
        RANK() OVER (
            PARTITION BY category
            ORDER BY price DESC
        ) AS rnk
    FROM products
) ranked
WHERE rnk = 1;

-- 顧客ごとの最終注文日と最終注文金額
SELECT
    c.customer_id,
    c.name,
    latest.order_date  AS last_order_date,
    latest.amount      AS last_order_amount
FROM customers c
JOIN orders latest
    ON c.customer_id = latest.customer_id
   AND latest.order_date = (
       SELECT MAX(o.order_date)
       FROM orders o
       WHERE o.customer_id = c.customer_id
   );

-- AUTO_INCREMENT の最大IDが最新レコード
SELECT * FROM logs WHERE id = (SELECT MAX(id) FROM logs);

-- グループごとの最新ログ
SELECT * FROM logs l
WHERE l.id = (
    SELECT MAX(l2.id)
    FROM logs l2
    WHERE l2.user_id = l.user_id
);

-- MAX: price 列の全行から最大値（集約関数）
SELECT MAX(price) FROM products;
-- 結果: 1行（全体の最高価格）

-- GREATEST: 同一行の複数列から最大値（行ごとに計算）
SELECT
    product_name,
    GREATEST(price_a, price_b, price_c) AS highest_price
FROM price_comparison;
-- 結果: 行ごとに price_a/price_b/price_c の最大値
-- MySQL / PostgreSQL / Oracle で使用可能

-- SQL Server では GREATEST は 2022+ のみ。代替:
SELECT
    product_name,
    (SELECT MAX(v) FROM (VALUES (price_a), (price_b), (price_c)) AS t(v)) AS highest_price
FROM price_comparison;

-- 各行に全体最大価格と差分を表示
SELECT
    product_name,
    price,
    MAX(price) OVER ()             AS max_price,
    MAX(price) OVER () - price     AS diff_from_max
FROM products;
-- 結果:
-- テレビ   | 49,990 | 249,990 | 200,000
-- 冷蔵庫   | 89,990 | 249,990 | 160,000
-- エアコン | 249,990| 249,990 | 0

-- カテゴリ別の最高価格を各行に付与
SELECT
    category,
    product_name,
    price,
    MAX(price) OVER (PARTITION BY category) AS category_max
FROM products;
-- 結果:
-- 家電 | テレビ   | 49,990 | 249,990
-- 家電 | エアコン | 249,990| 249,990
-- 食品 | 米       |  2,980 |   4,980

-- 日付順に売上の最大値を更新しながら表示
SELECT
    sale_date,
    amount,
    MAX(amount) OVER (ORDER BY sale_date) AS running_max
FROM sales
ORDER BY sale_date;
-- 結果:
-- 2024-01-01 | 10,000 | 10,000
-- 2024-01-02 | 15,000 | 15,000  ← 更新
-- 2024-01-03 |  8,000 | 15,000  ← 維持
-- 2024-01-04 | 20,000 | 20,000  ← 更新

-- データ: price = 100, 200, NULL, 300
SELECT MAX(price) FROM products;
-- 結果: 300（NULLは無視される）

-- 0件の場合
SELECT MAX(price) FROM products WHERE 1 = 0;
-- 結果: NULL（最大値なし）

-- NULL を特定の値に変換
SELECT COALESCE(MAX(price), 0) AS max_price
FROM products WHERE 1 = 0;
-- 結果: 0

-- カテゴリ別の最高価格が50,000以上のカテゴリだけ抽出
SELECT
    category,
    MAX(price) AS max_price
FROM products
GROUP BY category
HAVING MAX(price) >= 50000
ORDER BY max_price DESC;

-- 最大値と最小値の差が大きいカテゴリ
SELECT
    category,
    MAX(price) AS max_price,
    MIN(price) AS min_price,
    MAX(price) - MIN(price) AS price_range
FROM products
GROUP BY category
HAVING MAX(price) - MIN(price) > 10000;

-- ステータス履歴テーブルから最新のステータスを取得
SELECT
    o.order_id,
    o.order_date,
    sh.status AS current_status
FROM orders o
JOIN status_history sh
    ON o.order_id = sh.order_id
   AND sh.changed_at = (
       SELECT MAX(sh2.changed_at)
       FROM status_history sh2
       WHERE sh2.order_id = o.order_id
   );

-- 現在の最大IDを確認
SELECT MAX(id) AS current_max_id FROM employees;

-- 欠番を特定（IDが連続していない箇所）
SELECT a.id + 1 AS gap_start
FROM employees a
WHERE NOT EXISTS (
    SELECT 1 FROM employees b WHERE b.id = a.id + 1
)
AND a.id < (SELECT MAX(id) FROM employees);

SELECT
    customer_id,
    MAX(CASE WHEN category = '家電'   THEN order_date END) AS latest_electronics,
    MAX(CASE WHEN category = '食品'   THEN order_date END) AS latest_food,
    MAX(CASE WHEN category = '衣料品' THEN order_date END) AS latest_clothing
FROM orders
GROUP BY customer_id;