Skip to content
Published on

SQL完全攻略2025：開発者面接に出るSQL — JOINからWindow Function、クエリ最適化まで

🇰🇷KO🇺🇸EN🇯🇵JA
Split View
Authors

はじめに：2025年（ねん）でもSQLが最強（さいきょう）な理由（りゆう）

Stack Overflow 2024 Developer SurveyでSQLは最（もっと）も使（つか）われている言語（げんご）TOP3に入（はい）り、開発者（かいはつしゃ）が最（もっと）も学（まな）びたいスキルの上位（じょうい）にランクインしました。PostgreSQLは2023-2024年（ねん）連続（れんぞく）でDB-Engines年間（ねんかん）DBMSに選（えら）ばれ、AI/LLM時代（じだい）でもデータを扱（あつか）う根本技術（こんぽんぎじゅつ）はSQLです。

なぜSQLが面接（めんせつ）で重要（じゅうよう）なのか：

  • バックエンドポジション面接（めんせつ）の80%以上（いじょう）でSQL問題（もんだい）が出題（しゅつだい）
  • データエンジニア、フルスタック、DevOpsまでSQL必須（ひっす）
  • 実務（じつむ）でORMだけ使（つか）ってパフォーマンス問題（もんだい）に遭遇（そうぐう）すれば結局（けっきょく）SQLで解決（かいけつ）
  • Window FunctionとCTEは最近（さいきん）の面接（めんせつ）トレンドの核心（かくしん）
┌──────────────────────────────────────────────────┐
│       SQLが依然として最強な理由 (2025)             │
├──────────────────────────────────────────────────┤
│  Stack Overflow  → 使用言語 TOP 3                │
│  PostgreSQL      → 2年連続 年間DBMS              │
│  AI/LLM          → Text-to-SQL コア技術          │
│  クラウド         → Aurora, AlloyDB, Neon         │
│  面接頻度         → バックエンド面接 80%+ 出題    │
└──────────────────────────────────────────────────┘

この記事（きじ）ではJOINの全種類（ぜんしゅるい）からWindow Function、インデクシング、クエリ最適化（さいてきか）、トランザクションまで、面接（めんせつ）に必要（ひつよう）なSQLの全（すべ）てをカバーします。

1. JOIN完全攻略（かんぜんこうりゃく）

1.1 サンプルテーブル

-- 従業員テーブル
CREATE TABLE employees (
    id    INT PRIMARY KEY,
    name  VARCHAR(50),
    dept_id INT
);

INSERT INTO employees VALUES
(1, 'Alice',   10),
(2, 'Bob',     20),
(3, 'Charlie', 10),
(4, 'Diana',   30),
(5, 'Eve',     NULL);

-- 部署テーブル
CREATE TABLE departments (
    id    INT PRIMARY KEY,
    name  VARCHAR(50)
);

INSERT INTO departments VALUES
(10, 'Engineering'),
(20, 'Marketing'),
(40, 'HR');

1.2 INNER JOIN

両方（りょうほう）のテーブルでマッチする行（ぎょう）のみ返（かえ）します。

SELECT e.name AS employee, d.name AS department
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

┌──────────┬─────────────┐
│ employee │ department  │
├──────────┼─────────────┤
│ Alice    │ Engineering │
│ Bob      │ Marketing   │
│ Charlie  │ Engineering │
└──────────┴─────────────┘
-- Diana(dept_id=30)とEve(dept_id=NULL)はマッチなしで除外
-- HR(id=40)もマッチする従業員がなく除外

1.3 LEFT JOIN

左（ひだり）テーブルの全行（ぜんぎょう）＋マッチする右（みぎ）の行（ぎょう）（なければNULL）。

SELECT e.name AS employee, d.name AS department
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

┌──────────┬─────────────┐
│ employee │ department  │
├──────────┼─────────────┤
│ Alice    │ Engineering │
│ Bob      │ Marketing   │
│ Charlie  │ Engineering │
│ Diana    │ NULL        │
│ Eve      │ NULL        │
└──────────┴─────────────┘

1.4 RIGHT JOIN

右（みぎ）テーブルの全行（ぜんぎょう）＋マッチする左（ひだり）の行（ぎょう）（なければNULL）。

SELECT e.name AS employee, d.name AS department
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;

┌──────────┬─────────────┐
│ employee │ department  │
├──────────┼─────────────┤
│ Alice    │ Engineering │
│ Bob      │ Marketing   │
│ Charlie  │ Engineering │
│ NULL     │ HR          │
└──────────┴─────────────┘

1.5 FULL OUTER JOIN

両方（りょうほう）のテーブルの全行（ぜんぎょう）を返（かえ）します。マッチがなければNULL。

SELECT e.name AS employee, d.name AS department
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.id;

1.6 CROSS JOIN

2つのテーブルの全（すべ）ての組（く）み合（あ）わせ（デカルト積（せき））を返（かえ）します。

SELECT e.name, d.name
FROM employees e
CROSS JOIN departments d;
-- 5人 x 3部署 = 15行

1.7 SELF JOIN

同（おな）じテーブルを自分自身（じぶんじしん）と結合（けつごう）します。

-- 管理者-従業員関係の検索
SELECT
    s.name AS employee,
    m.name AS manager
FROM staff s
LEFT JOIN staff m ON s.manager_id = m.id;

┌──────────┬─────────┐
│ employee │ manager │
├──────────┼─────────┤
│ CEO      │ NULL    │
│ CTO      │ CEO     │
│ Alice    │ CTO     │
│ Bob      │ CTO     │
└──────────┴─────────┘

2. 集約（しゅうやく）とグループ化（か）

2.1 GROUP BYとHAVING

-- 部署別の従業員数と平均給与
SELECT
    dept_id,
    COUNT(*) AS emp_count,
    AVG(salary) AS avg_salary,
    MAX(salary) AS max_salary,
    MIN(salary) AS min_salary
FROM employees
GROUP BY dept_id
HAVING COUNT(*) >= 2
ORDER BY avg_salary DESC;

核心（かくしん）ルール： SELECTに集約関数（しゅうやくかんすう）でないカラムがあれば、必（かなら）ずGROUP BYに含（ふく）める必要（ひつよう）があります。

2.2 ROLLUP

SELECT
    COALESCE(dept_name, '合計') AS department,
    COALESCE(job_title, '小計') AS job,
    COUNT(*) AS emp_count,
    SUM(salary) AS total_salary
FROM employees e
JOIN departments d ON e.dept_id = d.id
GROUP BY ROLLUP(dept_name, job_title);

3. Window Functions

3.1 Window Functionとは

集約（しゅうやく）しながらも個別（こべつ）の行（ぎょう）を保持（ほじ）する関数（かんすう）です。GROUP BYと異（こと）なり行（ぎょう）を減（へ）らしません。

-- GROUP BY: 行削減（部署ごとに1行）
SELECT dept_id, AVG(salary) FROM employees GROUP BY dept_id;

-- Window Function: 行保持（全従業員 + 部署平均）
SELECT
    name,
    dept_id,
    salary,
    AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg
FROM employees;

3.2 ROW_NUMBER, RANK, DENSE_RANK

SELECT
    name,
    dept_id,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
    RANK()       OVER (ORDER BY salary DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

┌─────────┬─────────┬────────┬─────────┬──────┬────────────┐
│  name   │ dept_id │ salary │ row_num │ rank │ dense_rank │
├─────────┼─────────┼────────┼─────────┼──────┼────────────┤
│ Alice   │   10    │ 120000 │    1    │  1   │     1      │
│ Charlie │   10    │ 120000 │    2    │  1   │     1      │
│ Bob     │   20    │ 100000 │    3    │  3   │     2      │
│ Diana   │   30    │  80000 │    4    │  4   │     3      │
│ Eve     │   20    │  80000 │    5    │  4   │     3      │
└─────────┴─────────┴────────┴─────────┴──────┴────────────┘

ROW_NUMBER: 常に一意な番号（同点でも異なる番号）
RANK:       同点は同じ番号、次はスキップ (1, 1, 3)
DENSE_RANK: 同点は同じ番号、次は連続   (1, 1, 2)

3.3 LAGとLEAD

前（まえ）/次（つぎ）の行（ぎょう）の値（あたい）を参照（さんしょう）します。

-- 前月比の売上変化
SELECT
    month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY month) AS prev_month,
    revenue - LAG(revenue, 1) OVER (ORDER BY month) AS change,
    LEAD(revenue, 1) OVER (ORDER BY month) AS next_month
FROM monthly_sales;

┌───────┬─────────┬────────────┬────────┬────────────┐
│ month │ revenue │ prev_month │ change │ next_month │
├───────┼─────────┼────────────┼────────┼────────────┤
│ 01    │  50000  │   NULL     │  NULL  │   60000    │
│ 02    │  60000  │   50000    │  10000 │   45000    │
│ 03    │  45000  │   60000    │ -15000 │   70000    │
│ 04    │  70000  │   45000    │  25000 │   NULL     │
└───────┴─────────┴────────────┴────────┴────────────┘

3.4 累積合計（るいせきごうけい）（Running Total）

SELECT
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

3.5 部署別（ぶしょべつ）TOP N検索（けんさく）

-- 各部署で給与TOP 3
WITH ranked AS (
    SELECT
        name, dept_id, salary,
        ROW_NUMBER() OVER (
            PARTITION BY dept_id ORDER BY salary DESC
        ) AS rn
    FROM employees
)
SELECT name, dept_id, salary
FROM ranked
WHERE rn <= 3;

4. サブクエリ vs CTE

4.1 サブクエリ

-- 相関サブクエリ: 自部署の平均より給与が高い従業員
SELECT name, salary, dept_id
FROM employees e1
WHERE salary > (
    SELECT AVG(salary) FROM employees e2
    WHERE e2.dept_id = e1.dept_id
);

4.2 CTE (Common Table Expression)

WITH dept_avg AS (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY dept_id
),
high_earners AS (
    SELECT e.name, e.salary, e.dept_id, d.avg_salary
    FROM employees e
    JOIN dept_avg d ON e.dept_id = d.dept_id
    WHERE e.salary > d.avg_salary
)
SELECT * FROM high_earners ORDER BY salary DESC;

4.3 比較（ひかく）

┌──────────────┬───────────────┬───────────────┬────────────────┐
│    項目       │  サブクエリ    │     CTE       │  派生テーブル   │
├──────────────┼───────────────┼───────────────┼────────────────┤
│ 可読性       │ 低い（ネスト） │ 高い          │ 普通           │
│ 再利用       │ 不可          │ 同一クエリ内   │ 不可           │
│ 再帰対応     │ 不可          │ 可能(WITH REC)│ 不可           │
│ パフォーマンス│ DB依存        │ DB依存        │ インラインビュー│
│ デバッグ     │ 困難          │ 容易          │ 普通           │
└──────────────┴───────────────┴───────────────┴────────────────┘

5. 再帰（さいき）CTE

5.1 組織図（そしきず）ツリー探索（たんさく）

CREATE TABLE org (
    id         INT PRIMARY KEY,
    name       VARCHAR(50),
    manager_id INT REFERENCES org(id)
);

-- 再帰CTEでCEOから全組織図を探索
WITH RECURSIVE org_tree AS (
    -- 基底条件: CEO（マネージャーなし）
    SELECT id, name, manager_id, 0 AS depth, name::TEXT AS path
    FROM org WHERE manager_id IS NULL

    UNION ALL

    -- 再帰条件: 部下の探索
    SELECT o.id, o.name, o.manager_id,
           t.depth + 1,
           t.path || ' > ' || o.name
    FROM org o
    JOIN org_tree t ON o.manager_id = t.id
)
SELECT
    REPEAT('  ', depth) || name AS hierarchy,
    depth, path
FROM org_tree ORDER BY path;

6. インデクシング

6.1 B-Treeインデックス

最（もっと）も一般的（いっぱんてき）なインデックス。等号（とうごう）(=)、範囲比較（はんいひかく）、ソート、LIKE 'prefix%'に効果的（こうかてき）です。

CREATE INDEX idx_employees_name ON employees(name);

-- 効果的なクエリ
SELECT * FROM employees WHERE name = 'Alice';          -- 等号
SELECT * FROM employees WHERE name BETWEEN 'A' AND 'M'; -- 範囲
SELECT * FROM employees ORDER BY name;                  -- ソート

6.2 GIN (Generalized Inverted Index)

配列（はいれつ）、JSONB、全文検索（ぜんぶんけんさく）に適（てき）しています。

CREATE INDEX idx_products_tags ON products USING GIN (tags);
SELECT * FROM products WHERE tags @> '["electronics"]';

6.3 複合（ふくごう）インデックス

-- カラム順序が重要!
CREATE INDEX idx_emp_dept_salary ON employees(dept_id, salary);

-- 効果的: dept_idのみ、またはdept_id + salary両方
SELECT * FROM employees WHERE dept_id = 10;
SELECT * FROM employees WHERE dept_id = 10 AND salary > 50000;

-- 非効果的: salaryのみ（最初のカラムをスキップ）
-- SELECT * FROM employees WHERE salary > 50000;

複合インデックスの順序ルール (Leftmost Prefix):

インデックス: (A, B, C)

WHERE A = ?                        → 使用 O
WHERE A = ? AND B = ?              → 使用 O
WHERE A = ? AND B = ? AND C = ?    → 使用 O
WHERE B = ?                        → 使用 X (Aなし)
WHERE B = ? AND C = ?              → 使用 X (Aなし)
WHERE A = ? AND C = ?              → Aのみ使用 (Bスキップ)

7. EXPLAIN ANALYZE

7.1 実行計画（じっこうけいかく）の読（よ）み方（かた）

EXPLAIN ANALYZE
SELECT e.name, d.name AS dept
FROM employees e
JOIN departments d ON e.dept_id = d.id
WHERE e.salary > 50000;

7.2 主要（しゅよう）スキャンタイプ

┌──────────────────┬──────────────────────────────────────┐
│ スキャンタイプ    │ 説明                                  │
├──────────────────┼──────────────────────────────────────┤
│ Seq Scan         │ テーブル全体の順次スキャン              │
│ Index Scan       │ インデックスで条件行を見つけテーブル参照│
│ Index Only Scan  │ インデックスのみで結果返却              │
│ Bitmap Index Scan│ インデックスでビットマップ作成          │
│ Bitmap Heap Scan │ ビットマップ基盤のテーブル参照          │
└──────────────────┴──────────────────────────────────────┘

7.3 遅（おそ）いクエリ診断（しんだん）フロー

┌──────────────────────────────────────────────────┐
│          クエリ最適化診断フロー                    │
├──────────────────────────────────────────────────┤
│ 1. EXPLAIN ANALYZE実行                            │
│    |                                              │
│ 2. 大きなテーブルでSeq Scanがあるか？             │
│    → インデックス追加を検討                        │
│    |                                              │
│ 3. 予想rowsと実際rowsの差が大きいか？             │
│    → ANALYZEを実行して統計を更新                   │
│    |                                              │
│ 4. 大量データでNested Loopが使われているか？       │
│    → Hash JoinやMerge Joinがより適切かも           │
│    |                                              │
│ 5. Sortがディスクで実行されているか？              │
│    → work_mem増加またはインデックス追加             │
└──────────────────────────────────────────────────┘

8. クエリ最適化（さいてきか）

8.1 N+1問題（もんだい）

-- 悪い例: 各注文ごとに別クエリ（アプリケーションから）
-- 良い例: JOINで一度に
SELECT o.id, o.total, oi.product_name, oi.quantity
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
WHERE o.user_id = 1;

8.2 カバリングインデックス

インデックスだけでクエリを完了（かんりょう）し、テーブルアクセスを回避（かいひ）します。

CREATE INDEX idx_emp_dept_name_salary
ON employees(dept_id, name, salary);

-- Index Only Scan可能（テーブルアクセス不要）
SELECT name, salary FROM employees WHERE dept_id = 10;

8.3 パーシャルインデックス

-- アクティブユーザーのみインデクシング
CREATE INDEX idx_active_users
ON users(email)
WHERE status = 'active';

8.4 最適化（さいてきか）ティップス

-- 1. EXISTS vs IN: 大量データではEXISTSが通常高速
SELECT * FROM orders o WHERE EXISTS (
    SELECT 1 FROM customers c
    WHERE c.id = o.customer_id AND c.country = 'JP'
);

-- 2. LIMITで不要なスキャンを防止
SELECT * FROM logs ORDER BY created_at DESC LIMIT 100;

-- 3. UNION ALL vs UNION: 重複除去不要ならALL使用
-- 4. サブクエリ内の不要なORDER BY除去
-- 5. SELECT *より必要なカラムのみ指定

9. トランザクション分離（ぶんり）レベル

9.1 4つの分離（ぶんり）レベル

┌──────────────────┬───────────┬──────────────┬──────────────┬─────────────┐
│ 分離レベル        │Dirty Read │Non-Repeatable│Phantom Read  │パフォーマンス│
│                  │           │    Read      │              │             │
├──────────────────┼───────────┼──────────────┼──────────────┼─────────────┤
│ Read Uncommitted │ 可能      │ 可能         │ 可能          │ 最速        │
│ Read Committed   │ 防止      │ 可能         │ 可能          │ 速い        │
│ Repeatable Read  │ 防止      │ 防止         │ 可能          │ 普通        │
│ Serializable     │ 防止      │ 防止         │ 防止          │ 最遅        │
└──────────────────┴───────────┴──────────────┴──────────────┴─────────────┘

PostgreSQLデフォルト: Read Committed
MySQL InnoDBデフォルト: Repeatable Read

9.2 異常現象（いじょうげんしょう）の説明（せつめい）

Dirty Read: コミットされていないデータを読（よ）む

-- Transaction A
UPDATE accounts SET balance = 0 WHERE id = 1;
-- (まだCOMMITしていない)

-- Transaction B (Read Uncommitted)
SELECT balance FROM accounts WHERE id = 1;
-- balance = 0 (コミットされていない値!)

-- Transaction A
ROLLBACK;
-- 実際にはbalanceが元の値に復元される

Non-Repeatable Read: 同（おな）じクエリを2回（かい）実行（じっこう）して結果（けっか）が異（こと）なる

Phantom Read: 同（おな）じ条件（じょうけん）クエリで行数（ぎょうすう）が変（か）わる

9.3 MVCC

PostgreSQLはMVCCを使用（しよう）して読（よ）み取（と）りと書（か）き込（こ）みが互（たが）いにブロッキングしません。

┌──────────────────────────────────────────────────┐
│                    MVCC原理                       │
├──────────────────────────────────────────────────┤
│  Transaction 100 (Read):                         │
│  「xmin 100以下のバージョンのみ参照」             │
│                                                  │
│  Transaction 101 (Write):                        │
│  「新バージョンをxmin=101で作成、                 │
│   既存行にxmax=101をマーク」                      │
│                                                  │
│  → Transaction 100は依然として旧バージョンを参照  │
│  → 読み取りと書き込みが互いにブロックしない        │
└──────────────────────────────────────────────────┘

10. PostgreSQL固有機能（こゆうきのう）

10.1 JSONBクエリ

CREATE TABLE products (
    id   SERIAL PRIMARY KEY,
    name TEXT,
    data JSONB
);

-- JSONBクエリ
SELECT name, data->>'brand' AS brand
FROM products
WHERE data->'specs'->>'ram' = '16';

-- 配列包含検索
SELECT * FROM products WHERE data->'tags' @> '"mobile"';

10.2 配列（はいれつ）演算子（えんざんし）

CREATE TABLE articles (
    id   SERIAL PRIMARY KEY,
    title TEXT,
    tags  TEXT[]
);

-- 配列包含検索
SELECT * FROM articles WHERE tags @> ARRAY['sql'];
SELECT * FROM articles WHERE 'react' = ANY(tags);

10.3 LATERAL JOIN

-- 各部署の最新注文3件を取得
SELECT d.name, recent.*
FROM departments d
CROSS JOIN LATERAL (
    SELECT o.id, o.amount, o.created_at
    FROM orders o
    WHERE o.dept_id = d.id
    ORDER BY o.created_at DESC
    LIMIT 3
) recent;

10.4 Materialized View

CREATE MATERIALIZED VIEW monthly_sales_summary AS
SELECT
    DATE_TRUNC('month', order_date) AS month,
    dept_id,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount
FROM orders
GROUP BY DATE_TRUNC('month', order_date), dept_id;

CREATE INDEX idx_monthly_sales ON monthly_sales_summary(month, dept_id);
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales_summary;

11. 実践問題（じっせんもんだい）30選（せん）

Easy (1-10)

問題（もんだい）1. 全従業員（ぜんじゅうぎょういん）の名前（なまえ）と所属部署名（しょぞくぶしょめい）を検索（けんさく）してください。部署（ぶしょ）のない従業員（じゅうぎょういん）も含（ふく）めます。

回答を見る
SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

問題（もんだい）2. 部署別（ぶしょべつ）の従業員数（じゅうぎょういんすう）を求（もと）めてください。

回答を見る
SELECT d.name, COUNT(e.id) AS emp_count
FROM departments d
LEFT JOIN employees e ON d.id = e.dept_id
GROUP BY d.name
ORDER BY emp_count DESC;

問題（もんだい）3. 全体平均（ぜんたいへいきん）より給与（きゅうよ）が高（たか）い従業員（じゅうぎょういん）を検索（けんさく）してください。

回答を見る
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

問題（もんだい）4. 重複（ちょうふく）したメールアドレスを持（も）つユーザーを見（み）つけてください。

回答を見る
SELECT email, COUNT(*) AS cnt
FROM users GROUP BY email HAVING COUNT(*) > 1;

問題（もんだい）5. 注文（ちゅうもん）が一件（いっけん）もない顧客（こきゃく）を見（み）つけてください。

回答を見る
SELECT c.name FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;

問題（もんだい）6. 最新（さいしん）の注文（ちゅうもん）5件（けん）を検索（けんさく）してください。

回答を見る
SELECT * FROM orders ORDER BY created_at DESC LIMIT 5;

問題（もんだい）7. 部署別（ぶしょべつ）平均給与（へいきんきゅうよ）が60000以上（いじょう）の部署（ぶしょ）のみ表示（ひょうじ）してください。

回答を見る
SELECT dept_id, AVG(salary) AS avg_salary
FROM employees GROUP BY dept_id HAVING AVG(salary) >= 60000;

問題（もんだい）8. 2番目（ばんめ）に高（たか）い給与（きゅうよ）を求（もと）めてください。

回答を見る
SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;

問題（もんだい）9. NULL値（あたい）をデフォルト値（あたい）に置換（ちかん）して検索（けんさく）してください。

回答を見る
SELECT name, COALESCE(phone, 'N/A') AS phone FROM employees;

問題（もんだい）10. 名前（なまえ）が'A'で始（はじ）まる従業員（じゅうぎょういん）を検索（けんさく）してください。

回答を見る
SELECT * FROM employees WHERE name LIKE 'A%';

Medium (11-20)

問題（もんだい）11. 各部署（かくぶしょ）で給与（きゅうよ）が最（もっと）も高（たか）い従業員（じゅうぎょういん）を求（もと）めてください。

回答を見る
WITH ranked AS (
    SELECT name, dept_id, salary,
           RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rk
    FROM employees
)
SELECT name, dept_id, salary FROM ranked WHERE rk = 1;

問題（もんだい）12. 月別（つきべつ）売上（うりあげ）と前月比増減率（ぜんげつひぞうげんりつ）を求（もと）めてください。

回答を見る
SELECT
    month, revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY month))::NUMERIC
        / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100, 2
    ) AS growth_pct
FROM monthly_sales;

問題（もんだい）13. 3日（にち）以上（いじょう）連続（れんぞく）ログインしたユーザーを見（み）つけてください。

回答を見る
WITH login_groups AS (
    SELECT user_id, login_date,
           login_date - ROW_NUMBER() OVER (
               PARTITION BY user_id ORDER BY login_date
           )::INT * INTERVAL '1 day' AS grp
    FROM logins
)
SELECT user_id, COUNT(*) AS consecutive_days
FROM login_groups
GROUP BY user_id, grp
HAVING COUNT(*) >= 3;

問題（もんだい）14. 累積合計（るいせきごうけい）を求（もと）めてください。

回答を見る
SELECT order_date, amount,
       SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

問題（もんだい）15. ピボットテーブル：部署別（ぶしょべつ）/四半期別（しはんきべつ）売上（うりあげ）を行（ぎょう）から列（れつ）に変換（へんかん）してください。

回答を見る
SELECT dept_name,
    SUM(CASE WHEN quarter = 1 THEN revenue ELSE 0 END) AS q1,
    SUM(CASE WHEN quarter = 2 THEN revenue ELSE 0 END) AS q2,
    SUM(CASE WHEN quarter = 3 THEN revenue ELSE 0 END) AS q3,
    SUM(CASE WHEN quarter = 4 THEN revenue ELSE 0 END) AS q4
FROM quarterly_sales GROUP BY dept_name;

問題（もんだい）16. 再帰（さいき）CTEでフィボナッチ数列（すうれつ）を生成（せいせい）してください。

回答を見る
WITH RECURSIVE fib AS (
    SELECT 1 AS n, 0::BIGINT AS a, 1::BIGINT AS b
    UNION ALL
    SELECT n + 1, b, a + b FROM fib WHERE n < 20
)
SELECT n, a AS fibonacci FROM fib;

問題（もんだい）17. 部署内（ぶしょない）での給与（きゅうよ）ランキングをつけてください。

回答を見る
SELECT name, dept_id, salary,
       DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dept_rank
FROM employees;

問題（もんだい）18. 最（もっと）も売（う）れた商品（しょうひん）TOP 5とカテゴリを求（もと）めてください。

回答を見る
SELECT p.name, p.category, SUM(oi.quantity) AS total_sold
FROM order_items oi
JOIN products p ON oi.product_id = p.id
GROUP BY p.name, p.category
ORDER BY total_sold DESC LIMIT 5;

問題（もんだい）19. LATERAL JOINで各（かく）カテゴリの最新（さいしん）商品（しょうひん）3つを求（もと）めてください。

回答を見る
SELECT c.name AS category, p.*
FROM categories c
CROSS JOIN LATERAL (
    SELECT id, name, price, created_at
    FROM products WHERE category_id = c.id
    ORDER BY created_at DESC LIMIT 3
) p;

問題（もんだい）20. 3ヶ月（かげつ）移動平均（いどうへいきん）を求（もと）めてください。

回答を見る
SELECT month, revenue,
       AVG(revenue) OVER (
           ORDER BY month
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS moving_avg_3m
FROM monthly_sales;

Hard (21-30)

問題（もんだい）21. JSONBフィールドで特定条件（とくていじょうけん）フィルタリングしてください。

回答を見る
SELECT id, name, data->>'brand' AS brand
FROM products
WHERE (data->'specs'->>'ram')::INT >= 16
  AND data->'tags' @> '"electronics"';

問題（もんだい）22. ギャップ分析（ぶんせき）：連続（れんぞく）IDの欠番（けつばん）を見（み）つけてください。

回答を見る
WITH id_range AS (
    SELECT generate_series(MIN(id), MAX(id)) AS expected_id FROM orders
)
SELECT expected_id AS missing_id FROM id_range
WHERE expected_id NOT IN (SELECT id FROM orders);

問題（もんだい）23. 重複行（ちょうふくぎょう）の削除（さくじょ）（最新（さいしん）のみ保持（ほじ））。

回答を見る
DELETE FROM users WHERE id NOT IN (
    SELECT DISTINCT ON (email) id FROM users ORDER BY email, created_at DESC
);

問題（もんだい）24. GROUP BYなしで割合計算（わりあいけいさん）。

回答を見る
SELECT status, COUNT(*) AS cnt,
       ROUND(COUNT(*)::NUMERIC / SUM(COUNT(*)) OVER () * 100, 2) AS percentage
FROM orders GROUP BY status;

問題（もんだい）25. 累積（るいせき）ユニークユーザー数（すう）を求（もと）めてください。

回答を見る
WITH daily_new AS (
    SELECT user_id, MIN(login_date) AS first_date FROM logins GROUP BY user_id
)
SELECT first_date, COUNT(*) AS new_users,
       SUM(COUNT(*)) OVER (ORDER BY first_date) AS cumulative_users
FROM daily_new GROUP BY first_date ORDER BY first_date;

問題（もんだい）26. 最長連続売上増加期間（さいちょうれんぞくうりあげぞうかきかん）を求（もと）めてください。

回答を見る
WITH changes AS (
    SELECT month, revenue,
        CASE WHEN revenue > LAG(revenue) OVER (ORDER BY month)
            THEN 0 ELSE 1 END AS is_break
    FROM monthly_sales
),
groups AS (
    SELECT *, SUM(is_break) OVER (ORDER BY month) AS grp FROM changes
)
SELECT grp, MIN(month) AS start_month, MAX(month) AS end_month,
       COUNT(*) AS streak_length
FROM groups GROUP BY grp ORDER BY streak_length DESC LIMIT 1;

問題（もんだい）27. 再帰（さいき）CTEでBOM（部品表（ぶひんひょう））展開（てんかい）。

回答を見る
WITH RECURSIVE bom AS (
    SELECT parent_id, child_id, quantity, 1 AS level, quantity AS total_qty
    FROM bill_of_materials WHERE parent_id = 'PRODUCT_A'
    UNION ALL
    SELECT b.parent_id, b.child_id, b.quantity,
           bom.level + 1, bom.total_qty * b.quantity
    FROM bill_of_materials b
    JOIN bom ON b.parent_id = bom.child_id
    WHERE bom.level < 10
)
SELECT child_id AS component, SUM(total_qty) AS total_needed
FROM bom GROUP BY child_id ORDER BY total_needed DESC;

問題（もんだい）28. 時間（じかん）間隔（かんかく）の重（かさ）なり検出（けんしゅつ）（スケジュール衝突（しょうとつ））。

回答を見る
SELECT a.id AS meeting_a, b.id AS meeting_b
FROM meetings a
JOIN meetings b ON a.id < b.id
    AND a.room_id = b.room_id
    AND a.start_time < b.end_time
    AND a.end_time > b.start_time;

問題（もんだい）29. 中央値（ちゅうおうち）（メディアン）を求（もと）めてください。

回答を見る
-- PostgreSQL専用:
SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY salary) AS median
FROM employees;

問題（もんだい）30. グラフ最短経路（さいたんけいろ）（再帰（さいき）CTE）。

回答を見る
WITH RECURSIVE paths AS (
    SELECT src, dst, cost, ARRAY[src, dst] AS path, 1 AS hops
    FROM edges WHERE src = 'A'
    UNION ALL
    SELECT p.src, e.dst, p.cost + e.cost, p.path || e.dst, p.hops + 1
    FROM paths p
    JOIN edges e ON p.dst = e.src
    WHERE NOT e.dst = ANY(p.path) AND p.hops < 10
)
SELECT dst, MIN(cost) AS min_cost, path
FROM paths WHERE dst = 'Z'
GROUP BY dst, path ORDER BY min_cost LIMIT 1;

12. 面接質問（めんせつしつもん）15選（せん）

基礎（きそ）(1-5)

Q1. INNER JOINとLEFT JOINの違（ちが）いは？

回答を見る

INNER JOINは両方（りょうほう）のテーブルでマッチする行（ぎょう）のみ返（かえ）します。LEFT JOINは左（ひだり）テーブルの全行（ぜんぎょう）を返（かえ）し、右（みぎ）テーブルにマッチがなければNULLで埋（う）めます。

Q2. GROUP BYとHAVINGの違（ちが）いは？

回答を見る

GROUP BYは行（ぎょう）をグループにまとめます。HAVINGはグループ化（か）された結果（けっか）に条件（じょうけん）を適用（てきよう）します。WHEREはグループ化前（まえ）に個別行（こべつぎょう）をフィルタリングし、HAVINGはグループ化後（ご）にグループをフィルタリングします。

Q3. WHERE句（く）でインデックスが使（つか）われない場合（ばあい）は？

回答を見る

(1)カラムに関数（かんすう）を適用（てきよう）した場合（ばあい）。(2)暗黙的（あんもくてき）な型変換（かたへんかん）。(3)先頭（せんとう）ワイルドカードのLIKE。(4)OR条件（じょうけん）がインデックスと合（あ）わない場合（ばあい）。(5)NOT IN、否定条件（ひていじょうけん）。(6)オプティマイザがフルスキャンの方（ほう）が効率的（こうりつてき）と判断（はんだん）した場合（ばあい）。

Q4. NULLに対（たい）する比較演算（ひかくえんざん）の結果（けっか）は？

回答を見る

NULLとの全（すべ）ての比較演算結果（ひかくえんざんけっか）はNULL(UNKNOWN)です。NULL = NULLはTRUEではなくNULLです。NULL確認（かくにん）にはIS NULLまたはIS NOT NULLを使（つか）う必要（ひつよう）があります。

Q5. DELETE, TRUNCATE, DROPの違（ちが）いは？

回答を見る

DELETE: 行単位（ぎょうたんい）の削除（さくじょ）、WHERE可能（かのう）、ロールバック可能（かのう）。TRUNCATE: 全行即時削除（ぜんぎょうそくじさくじょ）、条件不可（じょうけんふか）、DDLなので通常（つうじょう）ロールバック不可（ふか）（PostgreSQLは可能（かのう））。DROP: テーブル自体（じたい）を削除（さくじょ）。

中級（ちゅうきゅう）(6-10)

Q6. Window FunctionがGROUP BYと異（こと）なる点（てん）は？

回答を見る

GROUP BYはグループごとに1行（ぎょう）に縮小（しゅくしょう）します。Window Functionは集約（しゅうやく）しながらも個別行（こべつぎょう）を保持（ほじ）します。各従業員行（じゅうぎょういんぎょう）に部署平均給与（ぶしょへいきんきゅうよ）を一緒（いっしょ）に表示（ひょうじ）できます。

Q7. CTEとサブクエリのどちらを選（えら）びますか？

回答を見る

CTEは可読性（かどくせい）が良（よ）く、同（おな）じクエリ内（ない）で再利用可能（さいりようかのう）で、再帰（さいき）クエリをサポートします。PostgreSQL 12+ではCTEはデフォルトでインライン化（か）されるためパフォーマンス差（さ）はほぼありません。

Q8. 複合（ふくごう）インデックスのカラム順序（じゅんじょ）が重要（じゅうよう）な理由（りゆう）は？

回答を見る

B-Tree複合（ふくごう）インデックスはLeftmost Prefixルールに従（したが）います。(A, B, C)インデックスはA、A+B、A+B+C条件（じょうけん）で使用可能（しようかのう）ですが、BだけやCだけでは使用（しよう）できません。

Q9. EXPLAIN ANALYZEで注意（ちゅうい）すべき指標（しひょう）は？

回答を見る

(1)actual rows vs rows: 大（おお）きな差（さ）は統計（とうけい）の不正確（ふせいかく）さを示（しめ）す。(2)大（おお）きなテーブルのSeq Scan: インデックス必要（ひつよう）を示唆（しさ）。(3)Sort Method: disk = work_mem不足（ぶそく）。(4)大量（たいりょう）データのNested Loop: Hash Join検討（けんとう）。

Q10. トランザクション分離（ぶんり）レベルの選択基準（せんたくきじゅん）は？

回答を見る

ほとんどのOLTPではRead Committedが適切（てきせつ）です（PostgreSQLデフォルト）。財務（ざいむ）/決済（けっさい）など正確性（せいかくせい）が重要（じゅうよう）な場合（ばあい）はSerializable。レポート/分析（ぶんせき）ではRepeatable Read。分離（ぶんり）レベルが高（たか）いほど並行性（へいこうせい）が低下（ていか）するため要件（ようけん）に合（あ）わせて選択（せんたく）します。

上級（じょうきゅう）(11-15)

Q11. カバリングインデックスとは何（なに）か、なぜ重要（じゅうよう）ですか？

回答を見る

カバリングインデックスはクエリに必要（ひつよう）な全（すべ）てのカラムがインデックスに含（ふく）まれている場合（ばあい）で、Index Only Scanが可能（かのう）になります。テーブル本体（ほんたい）へのアクセスが不要（ふよう）でI/Oが大幅（おおはば）に減少（げんしょう）します。

Q12. MVCCの動作原理（どうさげんり）を説明（せつめい）してください。

回答を見る

MVCCは各行（かくぎょう）の複数（ふくすう）バージョンを維持（いじ）します。トランザクション開始時（かいしじ）にスナップショット時点（じてん）を記録（きろく）し、それ以降（いこう）の変更（へんこう）は見（み）えません。書（か）き込（こ）みは新（あたら）しいバージョンを作成（さくせい）し、読（よ）み取（と）りは自分（じぶん）のスナップショットに合（あ）うバージョンを読（よ）みます。古（ふる）いバージョンはVACUUMで整理（せいり）されます。

Q13. パーシャルインデックスと式（しき）インデックスの活用例（かつようれい）は？

回答を見る

パーシャルインデックスは頻繁（ひんぱん）に検索（けんさく）する条件（じょうけん）の部分集合（ぶぶんしゅうごう）のみインデクシングします。例（れい）：アクティブユーザーのみ WHERE status = 'active'。式（しき）インデックスは関数結果（かんすうけっか）をインデクシングします。例（れい）：LOWER(email)インデックスで大文字小文字無視検索（むしけんさく）の最適化（さいてきか）。

Q14. PostgreSQLでJSONBがJSONより有利（ゆうり）な理由（りゆう）は？

回答を見る

JSONBはバイナリ形式（けいしき）で保存（ほぞん）されパースオーバーヘッドがなく、GINインデックスをサポートし、包含（ほうがん）(@>)、存在（そんざい）(?)、パス(@?)演算子（えんざんし）が使用可能（しようかのう）です。JSONはテキストそのまま保存（ほぞん）で挿入（そうにゅう）は速（はや）いですが毎回（まいかい）パースが必要（ひつよう）です。

Q15. 大規模（だいきぼ）テーブルのマイグレーション戦略（せんりゃく）は？

回答を見る

(1)オンラインDDL：ALTER TABLE ... ADD COLUMNはPostgreSQLでデフォルト値（ち）があれば即座（そくざ）に完了（かんりょう）。(2)バッチ更新（こうしん）：全体（ぜんたい）UPDATEの代（か）わりにバッチで分割実行（ぶんかつじっこう）。(3)テーブルパーティショニングで作業範囲（さぎょうはんい）を縮小（しゅくしょう）。(4)pg_repackでテーブル再構成（さいこうせい）。(5)レプリカで先（さき）にテスト。無停止運用（むていしうんよう）が核心（かくしん）です。

13. クイズ5選（せん）

クイズ1

次（つぎ）のクエリの結果行数（けっかぎょうすう）は？

SELECT * FROM A CROSS JOIN B;
-- A: 5行, B: 3行

A) 3 B) 5 C) 8 D) 15

回答を見る

D) 15。CROSS JOINはデカルト積（せき）で、5 x 3 = 15行（ぎょう）を返（かえ）します。

クイズ2

ROW_NUMBER, RANK, DENSE_RANKで同点時（どうてんじ）、次（つぎ）の順位（じゅんい）がスキップされるのは？

A) ROW_NUMBER B) RANK C) DENSE_RANK D) 全部（ぜんぶ）スキップ

回答を見る

B) RANK。RANKは同点（どうてん）に同（おな）じ順位（じゅんい）を付与（ふよ）し、次（つぎ）をスキップします(1, 1, 3)。DENSE_RANKはスキップしません(1, 1, 2)。

クイズ3

複合（ふくごう）インデックス(dept_id, salary, name)がある時（とき）、このインデックスを使用（しよう）できないクエリは？

A) WHERE dept_id = 10 B) WHERE dept_id = 10 AND salary > 50000 C) WHERE salary > 50000 D) WHERE dept_id = 10 AND salary > 50000 AND name = 'Alice'

回答を見る

C) Leftmost Prefixルールにより、最初（さいしょ）のカラム(dept_id)なしで2番目（ばんめ）のカラム(salary)だけではインデックスを使用（しよう）できません。

クイズ4

Phantom Readを防止（ぼうし）する最小（さいしょう）の分離（ぶんり）レベルは？

A) Read Uncommitted B) Read Committed C) Repeatable Read D) Serializable

回答を見る

D) Serializable。標準（ひょうじゅん）SQLではSerializableのみPhantom Readを防止（ぼうし）します。ただしPostgreSQLのRepeatable Readは内部的（ないぶてき）にMVCCスナップショットを使用（しよう）してPhantom Readも防止（ぼうし）します。

クイズ5

次（つぎ）の結果（けっか）でdept_avgの値（あたい）は？

SELECT name, salary,
       AVG(salary) OVER () AS dept_avg
FROM employees;
-- employees: Alice 100, Bob 200, Charlie 300

A) 各行（かくぎょう）で異（こと）なる値（あたい） B) 全行（ぜんぎょう）で200 C) NULL D) エラー

回答を見る

B) OVER ()はパーティションなしで全（ぜん）データに対（たい）する集約（しゅうやく）です。(100 + 200 + 300) / 3 = 200が全行（ぜんぎょう）に表示（ひょうじ）されます。

14. 参考資料（さんこうしりょう）

公式（こうしき）ドキュメント

書籍（しょせき）

  • SQL Performance Explained - Markus Winand
  • PostgreSQL 14 Internals - Egor Rogov
  • The Art of SQL - Stephane Faroult
  • High Performance MySQL - Baron Schwartz et al.
  • SQL Antipatterns - Bill Karwin

オンライン資料（しりょう）

ツール

ブログ

Discuss on Twitter • View on GitHub