【SQL】DISTINCT完全ガイド｜重複排除・GROUP BYとの違い・複数列・COUNT(DISTINCT)・NULLの扱い・重複行DELETE・パフォーマンスまで

-- 従業員テーブルから所属部署の一覧を取得（重複なし）
SELECT DISTINCT department
FROM employees;
-- 結果: 営業部 / 開発部 / 総務部（各1行）

-- DISTINCT なし（重複あり）
SELECT department
FROM employees;
-- 結果: 営業部 / 営業部 / 開発部 / 営業部 / 総務部 / 開発部 ...（全行）

-- ALL は明示的に書いてもデフォルトと同じ（重複をそのまま返す）
SELECT ALL department FROM employees;     -- 全行返す（デフォルト動作）
SELECT DISTINCT department FROM employees; -- 重複排除して返す

-- 部署と役職の一意な組み合わせを取得
SELECT DISTINCT department, role
FROM employees;
-- 結果:
-- 営業部  | マネージャー
-- 営業部  | メンバー     ← department は同じでも role が異なるので別行
-- 開発部  | マネージャー
-- 開発部  | メンバー

-- DISTINCT で重複排除
SELECT DISTINCT department FROM employees;

-- GROUP BY で同じ結果
SELECT department FROM employees GROUP BY department;

-- どちらも結果は同じ。集約関数を使わないなら DISTINCT の方がシンプル

-- 部署ごとの人数を数える（DISTINCT では不可能）
SELECT department, COUNT(*) AS member_count
FROM employees
GROUP BY department;
-- 結果:
-- 営業部 | 15
-- 開発部 | 22
-- 総務部 | 8

-- 重複が2件以上ある行だけ抽出（HAVING）
SELECT department, role, COUNT(*) AS cnt
FROM employees
GROUP BY department, role
HAVING COUNT(*) > 1;

-- 従業員テーブルの部署数を数える（重複を排除してカウント）
SELECT COUNT(DISTINCT department) AS dept_count
FROM employees;
-- 結果: 3（営業部/開発部/総務部）

-- DISTINCT なしの COUNT（全行をカウント）
SELECT COUNT(department) AS total_rows
FROM employees;
-- 結果: 45（NULL以外の全行数）

-- 部署ごとに「何種類の役職があるか」を数える
SELECT
    department,
    COUNT(DISTINCT role) AS role_types,
    COUNT(*)             AS total_members
FROM employees
GROUP BY department;
-- 結果:
-- 営業部 | 3 | 15  ← 3種類の役職に15人
-- 開発部 | 4 | 22
-- 総務部 | 2 | 8

-- 注文テーブルで、重複する金額を排除して合計（あまり使わない）
SELECT SUM(DISTINCT amount) FROM orders;
-- 1000円の注文が3件あっても 1000 を1回だけ合計する

-- 実務で使う場面：JOIN で行が膨らんだとき
SELECT
    c.customer_id,
    c.customer_name,
    SUM(DISTINCT o.amount) AS unique_total  -- JOIN重複排除
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.customer_id, c.customer_name;

-- 各部署で最も給与が高い従業員を1人だけ取得
SELECT DISTINCT ON (department)
    department,
    name,
    salary
FROM employees
ORDER BY department, salary DESC;
-- 結果: 部署ごとに給与が最も高い1名ずつが返る

-- ORDER BY で先頭に来る行が選ばれる
-- ORDER BY を省略するとどの行が選ばれるか不定

-- 各部署で最も給与が高い従業員を1人だけ取得
SELECT department, name, salary
FROM (
    SELECT
        department,
        name,
        salary,
        ROW_NUMBER() OVER (
            PARTITION BY department
            ORDER BY salary DESC
        ) AS rn
    FROM employees
) ranked
WHERE rn = 1;

-- department に NULL がある場合
-- データ: 営業部, 開発部, NULL, NULL, 営業部

SELECT DISTINCT department FROM employees;
-- 結果: 営業部 / 開発部 / NULL（NULLは1行にまとまる）

-- COUNT(DISTINCT) は NULL を無視する
SELECT COUNT(DISTINCT department) FROM employees;
-- 結果: 2（営業部と開発部のみ。NULLはカウント外）

-- NULLもカウントしたい場合
SELECT COUNT(DISTINCT COALESCE(department, '(未設定)')) FROM employees;
-- 結果: 3（営業部 / 開発部 / (未設定)）

-- 同じ name + email の重複行のうち、id が大きい方を削除
DELETE FROM employees
WHERE id IN (
    SELECT id FROM (
        SELECT
            id,
            ROW_NUMBER() OVER (
                PARTITION BY name, email
                ORDER BY id           -- 最小IDを残す
            ) AS rn
        FROM employees
    ) ranked
    WHERE rn > 1
);

-- PostgreSQL: ctid（物理行ID）を使って重複を削除
DELETE FROM employees
WHERE ctid NOT IN (
    SELECT MIN(ctid)
    FROM employees
    GROUP BY name, email
);

-- CTE で直接 DELETE（SQL Server はCTEを更新可能）
WITH ranked AS (
    SELECT
        id,
        ROW_NUMBER() OVER (
            PARTITION BY name, email
            ORDER BY id
        ) AS rn
    FROM employees
)
DELETE FROM ranked WHERE rn > 1;

-- Oracle: ROWID を使って重複の古い行を残す
DELETE FROM employees
WHERE ROWID NOT IN (
    SELECT MIN(ROWID)
    FROM employees
    GROUP BY name, email
);

-- Step1: 重複排除したデータを退避
CREATE TABLE employees_clean AS
SELECT DISTINCT * FROM employees;
-- Oracle: CREATE TABLE ... AS SELECT
-- PostgreSQL: CREATE TABLE ... AS SELECT
-- SQL Server: SELECT ... INTO employees_clean FROM (SELECT DISTINCT ...)

-- Step2: 元テーブルを空にする
TRUNCATE TABLE employees;

-- Step3: 退避データを挿入
INSERT INTO employees SELECT * FROM employees_clean;

-- Step4: 一時テーブルを削除
DROP TABLE employees_clean;

-- テーブル作成時に設定
CREATE TABLE employees (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    email       VARCHAR(255) NOT NULL UNIQUE,  -- メールアドレスの重複を禁止
    name        VARCHAR(100) NOT NULL,
    department  VARCHAR(100)
);

-- 既存テーブルに追加
ALTER TABLE employees
ADD CONSTRAINT uq_employees_email UNIQUE (email);

-- 複合UNIQUE（name + department の組み合わせで一意）
ALTER TABLE employees
ADD CONSTRAINT uq_emp_name_dept UNIQUE (name, department);

-- NG: JOINで重複が発生 → DISTINCTで消している
SELECT DISTINCT c.customer_id, c.name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;
-- 注文が複数ある顧客が重複する → DISTINCT で消している

-- OK: EXISTS で書き換え（重複が発生しない）
SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);
-- JOINせずにEXISTSで存在チェックするため重複しない

-- MySQL: EXPLAIN で確認
EXPLAIN SELECT DISTINCT department FROM employees;
-- Extra に "Using temporary" や "Using filesort" が出るかチェック

-- PostgreSQL: EXPLAIN ANALYZE
EXPLAIN ANALYZE SELECT DISTINCT department FROM employees;
-- HashAggregate か Sort + Unique が使われているか確認