100本ノック / SQL / データ分析のためのSQL入門100本ノック

SQLのJOINで工場・部品・検査記録を横断分析する

2020-01-10

SQLのJOINで工場・部品・検査記録を横断分析する

SQL 100本ノック第5章（No.041〜No.050）：JOINによるテーブル結合

本記事は「データ分析のための SQL 入門 100本ノック」シリーズの 第5章 です。第4章（No.031〜040）では日付・文字列・計算を学びました。本章では JOIN（テーブル結合） を使い、工場マスタ・ライン・部品マスタ・検査記録を横断的に集計・分析します。データ不整合の自動検出方法も実践します。

[!NOTE] 本資料は、数理工房 (もしくは代表である和山個人) が過去に企業研修において使用した notebook を企業様の許可を得て再構成・編集のうえ公開しています。掲載データはすべて架空のものであり、実在する企業・工場・数値とは一切関係ありません。

はじめに：この記事で扱う製造業の実務課題

自動車部品メーカー IT・データ管理部の渡辺さんが対応している課題です。

製造実績 DB のデータ整合性確認と横断分析（月次定例作業）
  1. 検査記録 DB の factory_id / part_code が各マスタテーブルに存在するか確認
  2. 工場マスタ × 検査実績 を結合してライン別・工場別の生産 KPI を集計
  3. 部品マスタ × 検査実績 を結合して部品別の不良損失額を計算
  4. 検査実績のない部品・工場を特定してマスタ整備の優先順位を決める

現在はマスタ Excel と実績 CSV を VLOOKUP で突き合わせていますが、データが増えるほど処理が重くなり、対応漏れが発生しています。

SQL の JOIN を使えば、複数テーブルの突き合わせが 1クエリ で完結し、不整合データの自動検出も可能になります。

現場でよくある状況

場面	現状の課題	SQL JOIN で解決できること
工場別の実績集計	工場コード → 工場名の変換を VLOOKUP で手作業	`INNER JOIN factories` で工場名を自動付与
部品の損失額計算	単価テーブルと実績テーブルを別々に管理	`JOIN parts` で単価を結合して損失額を計算
未検査部品の特定	マスタと実績を見比べて手動で確認	`LEFT JOIN ... WHERE IS NULL` で自動抽出
不正コードの検出	実績データのコードがマスタに存在するか目視確認	`LEFT JOIN ... WHERE IS NULL` で自動検出
3テーブル以上の結合	複数の VLOOKUP を組み合わせて複雑化	複数 `JOIN` を連鎖して1クエリで解決

JOIN は製造業の データ品質管理 と KPI 横断分析 の核心スキルです。

なぜこの問題は判断が難しいのか

JOIN の初学者がつまずきやすい4つのポイントを整理します。

1. INNER JOIN と LEFT JOIN の使い分け

-- INNER JOIN: 両テーブルに一致するデータのみ取得（不一致は除外）
SELECT * FROM inspections INNER JOIN parts ON inspections.part_code = parts.part_code;

-- LEFT JOIN: 左テーブルの全行を保持（右テーブルに一致がなければ NULL）
SELECT * FROM parts LEFT JOIN inspections ON parts.part_code = inspections.part_code;

INNER JOIN は「確実に存在するデータだけで集計したい」場合に使います。 LEFT JOIN は「存在しないデータを検出したい（マスタ整備・品質管理）」場合に使います。

2. 結合キーの重複による行数増加

結合先テーブルに同じキーが複数存在すると、結合後の行数が増加します。必ず COUNT(*) で結合前後の件数を確認する習慣が重要です。

3. NULL の判定は `IS NULL` を使う

LEFT JOIN で一致しなかった行の結合列は NULL になります。 WHERE joined_col = NULL は常に FALSE です。必ず WHERE joined_col IS NULL を使います。

4. 結合前後の件数検証

期待した件数と異なる場合、結合キーの重複・NULL・マスタ不整合のいずれかが原因です。 SELECT COUNT(*) FROM ... で段階的に確認します。

今回扱うノックの全体像

No.	タイトル	製造業での活用場面
041	INNER JOINでテーブルを結合する	検査記録 × 工場マスタの基本結合
042	LEFT JOINで片方のテーブルを基準に結合する	全工場を基準に検査実績ゼロ工場を把握
043	RIGHT JOINの考え方を理解する	LEFT JOIN との等価関係を把握
044	工場マスタと検査記録を結合する	工場名・地域情報を付与した実績集計
045	部品マスタと検査記録を結合する	単価情報を付与した損失額計算
046	複数テーブルを結合する	工場+ライン+部品+検査記録の4テーブル横断分析
047	結合キーの重複に注意する	品質基準テーブルの重複による行数増加の把握
048	結合後の件数増加を確認する	JOIN 前後の件数検証でデータ品質を確保
049	検査実績のない部品を抽出する	未検査部品の特定とマスタ整備優先度の決定
050	部品マスタに存在しない検査記録を検出する	不正コードの検出とデータ品質管理

Python 環境の準備

import subprocess, sys
res = subprocess.run(["sw_vers", "-productVersion"], capture_output=True, text=True)
print(f"macOS : {res.stdout.strip()}")
print(f"Python: {sys.version}")

macOS : 26.3
Python: 3.13.1 (main, Dec  3 2024, 17:59:52) [Clang 16.0.0 (clang-1600.0.26.4)]

import sqlite3
import numpy as np
import polars as pl
import matplotlib
import matplotlib.pyplot as plt
from matplotlib.patches import Patch

matplotlib.rcParams['font.family'] = 'Hiragino Maru Gothic Pro'
%config InlineBackend.figure_format = 'svg'
np.random.seed(42)

print(f"sqlite3   : {sqlite3.sqlite_version}")
print(f"polars    : {pl.__version__}")
print(f"numpy     : {np.__version__}")
print(f"matplotlib: {matplotlib.__version__}")
print()
print("ライブラリ読み込み完了")

sqlite3   : 3.47.2
polars    : 1.42.1
numpy     : 2.5.1
matplotlib: 3.11.0

ライブラリ読み込み完了

架空データの作成

想定シナリオ: 自動車部品メーカー製造実績 DB / データ管理部 テーブル構成: 5テーブル

テーブル名	件数	説明
`factories`	5件	工場マスタ（F05: 仙台工場は検査実績なし）
`lines`	8件	ラインマスタ（工場コードを FK として保持）
`parts`	8件	部品マスタ（BRK-002 / ELC-002 / SUS-002 は検査実績なし）
`quality_specs`	11件	品質基準テーブル（一部部品は複数基準を保持 = 重複キー）
`inspections`	73件	検査記録（F05 の実績なし + 不正 part_code 3件含む）

JOIN デモ用の設計ポイント:

factories F05（仙台工場）に検査実績なし → No.042 LEFT JOIN で浮かび上がる
parts の3部品（BRK-002 / ELC-002 / SUS-002）に検査実績なし → No.049 で抽出
inspections 3件に part_code = 'UNKNOWN-999'（不正コード）→ No.050 で検出
quality_specs は part_code が一部重複（基準改定前後）→ No.047 で確認

# ─────────────────────────────────────────────────────────────────────────
# SQL ヘルパー関数
# ─────────────────────────────────────────────────────────────────────────
def q(conn, sql):
    '''SQL を実行して Polars DataFrame で結果を表示する'''
    print('── SQL ─────────────────────────────────────────')
    for line in sql.strip().split('\n'):
        print(f'  {line}')
    print('───────────────────────────────────────────────')
    cur = conn.execute(sql.strip())
    rows = cur.fetchall()
    cols = [d[0] for d in cur.description]
    data = {col: [row[i] for row in rows] for i, col in enumerate(cols)}
    df = pl.DataFrame(data)
    print(df)
    print(f'↳ {len(rows)} 行取得')
    return df

# ─────────────────────────────────────────────────────────────────────────
# インメモリ DB 作成
# ─────────────────────────────────────────────────────────────────────────
conn = sqlite3.connect(':memory:')

conn.execute('''
CREATE TABLE factories (
    factory_id   TEXT    PRIMARY KEY,
    factory_name TEXT    NOT NULL,
    city         TEXT    NOT NULL,
    region       TEXT    NOT NULL,
    capacity     INTEGER NOT NULL
)''')

conn.execute('''
CREATE TABLE lines (
    line_code          TEXT    PRIMARY KEY,
    factory_id         TEXT    NOT NULL,
    line_name          TEXT    NOT NULL,
    capacity_per_shift INTEGER NOT NULL
)''')

conn.execute('''
CREATE TABLE parts (
    part_code     TEXT    PRIMARY KEY,
    part_name     TEXT    NOT NULL,
    category      TEXT    NOT NULL,
    unit_price    INTEGER NOT NULL,
    supplier_code TEXT    NOT NULL
)''')

conn.execute('''
CREATE TABLE quality_specs (
    spec_id        INTEGER PRIMARY KEY,
    part_code      TEXT    NOT NULL,
    spec_type      TEXT    NOT NULL,
    max_dr_pct     REAL    NOT NULL,
    effective_from TEXT    NOT NULL
)''')

conn.execute('''
CREATE TABLE inspections (
    id              INTEGER PRIMARY KEY,
    inspection_date TEXT    NOT NULL,
    factory_id      TEXT    NOT NULL,
    line_code       TEXT    NOT NULL,
    part_code       TEXT    NOT NULL,
    shift           TEXT    NOT NULL,
    production_qty  INTEGER NOT NULL,
    defect_qty      INTEGER NOT NULL,
    inspector_code  TEXT    NOT NULL
)''')

# ── マスタデータ ───────────────────────────────────────────────────────────
conn.executemany('INSERT INTO factories VALUES (?,?,?,?,?)', [
    ('F01', '東京工場',   '東京都', '関東', 2000),
    ('F02', '大阪工場',   '大阪府', '関西', 1800),
    ('F03', '名古屋工場', '愛知県', '中部', 1600),
    ('F04', '福岡工場',   '福岡県', '九州', 1200),
    ('F05', '仙台工場',   '宮城県', '東北', 1000),  # 検査実績なし（JOIN デモ）
])

conn.executemany('INSERT INTO lines VALUES (?,?,?,?)', [
    ('LINE-A1', 'F01', 'エンジン部品ライン',   400),
    ('LINE-A2', 'F01', 'ブレーキ部品ライン',   300),
    ('LINE-B1', 'F02', 'エンジン部品ライン',   380),
    ('LINE-B2', 'F02', '電装部品ライン',       200),
    ('LINE-C1', 'F03', 'サスペンションライン', 150),
    ('LINE-C2', 'F03', 'ブレーキ部品ライン',   280),
    ('LINE-D1', 'F04', 'エンジン部品ライン',   350),
    ('LINE-E1', 'F05', '電装部品ライン',       180),  # F05 ライン（実績なし）
])

conn.executemany('INSERT INTO parts VALUES (?,?,?,?,?)', [
    ('ENG-001', 'ピストンリング',     'エンジン部品',    1200, 'SUP-01'),
    ('ENG-002', 'クランクシャフト',   'エンジン部品',    8500, 'SUP-01'),
    ('BRK-001', 'ブレーキパッド',     'ブレーキ部品',     950, 'SUP-02'),
    ('BRK-002', 'ブレーキキャリパー', 'ブレーキ部品',    4200, 'SUP-02'),  # 実績なし
    ('ELC-001', 'オルタネータ',       '電装部品',        6800, 'SUP-03'),
    ('ELC-002', 'スタータモータ',     '電装部品',        3500, 'SUP-03'),  # 実績なし
    ('SUS-001', 'ショックアブソーバ', 'サスペンション',  2800, 'SUP-04'),
    ('SUS-002', 'コイルスプリング',   'サスペンション',  1800, 'SUP-04'),  # 実績なし
])

conn.executemany('INSERT INTO quality_specs VALUES (?,?,?,?,?)', [
    (1,  'ENG-001', '標準基準', 2.00, '2024-01-01'),
    (2,  'ENG-001', '厳格基準', 1.50, '2024-03-01'),  # 重複キー
    (3,  'ENG-002', '標準基準', 1.80, '2024-01-01'),
    (4,  'ENG-002', '厳格基準', 1.20, '2024-03-01'),  # 重複キー
    (5,  'BRK-001', '標準基準', 2.50, '2024-01-01'),
    (6,  'BRK-001', '厳格基準', 2.00, '2024-03-01'),  # 重複キー
    (7,  'BRK-002', '標準基準', 3.00, '2024-01-01'),
    (8,  'ELC-001', '標準基準', 3.50, '2024-01-01'),
    (9,  'ELC-001', '厳格基準', 3.00, '2024-03-01'),  # 重複キー
    (10, 'ELC-002', '標準基準', 3.00, '2024-01-01'),
    (11, 'SUS-001', '標準基準', 2.80, '2024-01-01'),
    # SUS-002 には基準なし
])

# ── 検査記録データ生成（7ライン × 5日 × 2シフト = 70件 + 不正3件 = 73件）──
np.random.seed(42)
LINE_CONFIG = {
    'LINE-A1': ('F01', 'ENG-001', 'INS-001', 460, 0.018),
    'LINE-A2': ('F01', 'BRK-001', 'INS-002', 300, 0.020),
    'LINE-B1': ('F02', 'ENG-001', 'INS-003', 380, 0.019),
    'LINE-B2': ('F02', 'ELC-001', 'INS-004', 200, 0.030),
    'LINE-C1': ('F03', 'SUS-001', 'INS-005', 150, 0.025),
    'LINE-C2': ('F03', 'BRK-001', 'INS-006', 280, 0.021),
    'LINE-D1': ('F04', 'ENG-002', 'INS-007', 350, 0.022),
    # LINE-E1（F05）は生成しない
}
DATES  = ['2024-01-10', '2024-01-17', '2024-01-24', '2024-02-07', '2024-02-14']
SHIFTS = ['早番', '遅番']

records = []
rid = 1
for date in DATES:
    for line_code, (factory_id, part_code, inspector, base_prod, base_dr) in LINE_CONFIG.items():
        for shift in SHIFTS:
            prod = int(np.clip(
                np.random.normal(base_prod, base_prod * 0.05),
                base_prod * 0.85, base_prod * 1.15
            ))
            dr = base_dr + np.random.normal(0, base_dr * 0.15)
            dr = max(dr, 0.005)
            defect = max(1, round(prod * dr))
            records.append((rid, date, factory_id, line_code, part_code, shift,
                             prod, defect, inspector))
            rid += 1

# 不正 part_code 3件（No.050 デモ用）
for extra in [
    (rid,   '2024-01-15', 'F01', 'LINE-A1', 'UNKNOWN-999', '早番', 450,  8, 'INS-001'),
    (rid+1, '2024-01-22', 'F02', 'LINE-B1', 'UNKNOWN-999', '遅番', 375, 12, 'INS-003'),
    (rid+2, '2024-02-10', 'F03', 'LINE-C1', 'UNKNOWN-999', '早番', 148,  9, 'INS-005'),
]:
    records.append(extra)

conn.executemany('INSERT INTO inspections VALUES (?,?,?,?,?,?,?,?,?)', records)
conn.commit()

print('データベース作成完了')
for tbl in ['factories', 'lines', 'parts', 'quality_specs', 'inspections']:
    n = conn.execute(f'SELECT COUNT(*) FROM {tbl}').fetchone()[0]
    print(f'  {tbl:<16}: {n} 件')
print()
note = conn.execute(
    "SELECT COUNT(*) FROM inspections WHERE part_code='UNKNOWN-999'"
).fetchone()[0]
print(f'  ※ inspections のうち不正 part_code 件数: {note}')

データベース作成完了
  factories       : 5 件
  lines           : 8 件
  parts           : 8 件
  quality_specs   : 11 件
  inspections     : 73 件

  ※ inspections のうち不正 part_code 件数: 3

# ── データ概要グラフ（工場別 検査件数 / 加重平均不良率）─────────────────────
rows = conn.execute('''
    SELECT f.factory_id, f.factory_name,
           COUNT(i.id)                                       AS n_insp,
           COALESCE(SUM(i.production_qty), 0)               AS total_prod,
           COALESCE(SUM(i.defect_qty), 0)                   AS total_defect
    FROM   factories f
    LEFT   JOIN inspections i
           ON  f.factory_id = i.factory_id
           AND i.part_code  != 'UNKNOWN-999'
    GROUP  BY f.factory_id, f.factory_name
    ORDER  BY f.factory_id
''').fetchall()

fac_ids   = [r[0] for r in rows]
fac_names = [r[1] for r in rows]
n_insps   = [r[2] for r in rows]
t_prod    = [r[3] for r in rows]
t_def     = [r[4] for r in rows]
dr_vals   = [d / p * 100 if p > 0 else 0.0 for d, p in zip(t_def, t_prod)]
# F05（仙台）はグレー
COLORS = ['#4878CF', '#6ACC65', '#D65F5F', '#B47CC7', '#AAAAAA']

fig, axes = plt.subplots(1, 2, figsize=(13, 5))

for ax, vals, ylabel, title, fmt in [
    (axes[0], n_insps, '検査件数',       '工場別 検査件数（2024年1〜2月）',       '{:.0f}'),
    (axes[1], dr_vals, '加重平均不良率（%）', '工場別 加重平均不良率（2024年1〜2月）', '{:.2f}%'),
]:
    bars = ax.bar(range(len(fac_ids)), vals, color=COLORS, alpha=0.85,
                  edgecolor='black', linewidth=0.4)
    for bar, v in zip(bars, vals):
        label = fmt.format(v)
        ax.text(bar.get_x() + bar.get_width() / 2, bar.get_height() + 0.02,
                label, ha='center', va='bottom', fontsize=9)
    ax.set_title(title, fontsize=12, pad=10)
    ax.set_xlabel('工場', fontsize=10)
    ax.set_ylabel(ylabel, fontsize=10)
    ax.set_xticks(range(len(fac_ids)))
    ax.set_xticklabels(
        [f'{fid}\n{fn}' for fid, fn in zip(fac_ids, fac_names)], fontsize=8
    )
    ax.grid(axis='y', alpha=0.3)

legend_h = [Patch(color='#AAAAAA', alpha=0.85, label='F05: 仙台工場（検査実績なし）')]
axes[0].legend(handles=legend_h, fontsize=8, loc='upper right')

plt.tight_layout()
plt.show()
print('データ概要グラフ表示完了（SVG 1/2）')

svg

データ概要グラフ表示完了（SVG 1/2）

No.041：INNER JOINでテーブルを結合する

実務での意味

INNER JOIN は 2つのテーブルの共通部分（両方に一致するデータ）を取得します。製造業での主な活用場面：

検査記録に工場名・地域を付与して可読性の高いレポートを作成
部品マスタと実績を結合して損失額を計算
有効なマスタコードを持つ実績レコードだけを集計対象にする

分析・モデル化の考え方

\text{INNER JOIN}(A,\, B,\, \text{key}) = \{\,(a,\, b) \mid a \in A,\; b \in B,\; a.\text{key} = b.\text{key}\,\}

結合キーが 片方のテーブルに存在しない行は自動的に除外されます。これは WHERE 句を使った旧来のカンマ結合とは異なり、意図が明確です。

-- 現代的な書き方（推奨）
SELECT * FROM A INNER JOIN B ON A.key = B.key;
-- 旧来の書き方（非推奨）
SELECT * FROM A, B WHERE A.key = B.key;

Python で確認する

# No.041: INNER JOIN — 検査記録 × 工場マスタ

print('=== INNER JOIN: 検査記録に工場情報を付与（先頭10件）===')
q(conn, '''
SELECT i.id,
       i.inspection_date,
       i.factory_id,
       f.factory_name,
       f.region,
       i.line_code,
       i.production_qty,
       i.defect_qty
FROM   inspections i
INNER  JOIN factories f ON i.factory_id = f.factory_id
ORDER  BY i.id
LIMIT  10
''')

print()
print('=== INNER JOIN 後の全件数確認 ===')
q(conn, '''
SELECT COUNT(*) AS inner_join_count
FROM   inspections i
INNER  JOIN factories f ON i.factory_id = f.factory_id
''')

=== INNER JOIN: 検査記録に工場情報を付与（先頭10件）===
── SQL ─────────────────────────────────────────
  SELECT i.id,
         i.inspection_date,
         i.factory_id,
         f.factory_name,
         f.region,
         i.line_code,
         i.production_qty,
         i.defect_qty
  FROM   inspections i
  INNER  JOIN factories f ON i.factory_id = f.factory_id
  ORDER  BY i.id
  LIMIT  10
───────────────────────────────────────────────
shape: (10, 8)
┌─────┬───────────────┬────────────┬──────────────┬────────┬───────────┬──────────────┬────────────┐
│ id  ┆ inspection_da ┆ factory_id ┆ factory_name ┆ region ┆ line_code ┆ production_q ┆ defect_qty │
│ --- ┆ te            ┆ ---        ┆ ---          ┆ ---    ┆ ---       ┆ ty           ┆ ---        │
│ i64 ┆ ---           ┆ str        ┆ str          ┆ str    ┆ str       ┆ ---          ┆ i64        │
│     ┆ str           ┆            ┆              ┆        ┆           ┆ i64          ┆            │
╞═════╪═══════════════╪════════════╪══════════════╪════════╪═══════════╪══════════════╪════════════╡
│ 1   ┆ 2024-01-10    ┆ F01        ┆ 東京工場     ┆ 関東   ┆ LINE-A1   ┆ 471          ┆ 8          │
│ 2   ┆ 2024-01-10    ┆ F01        ┆ 東京工場     ┆ 関東   ┆ LINE-A1   ┆ 474          ┆ 10         │
│ 3   ┆ 2024-01-10    ┆ F01        ┆ 東京工場     ┆ 関東   ┆ LINE-A2   ┆ 296          ┆ 6          │
│ 4   ┆ 2024-01-10    ┆ F01        ┆ 東京工場     ┆ 関東   ┆ LINE-A2   ┆ 323          ┆ 7          │
│ 5   ┆ 2024-01-10    ┆ F02        ┆ 大阪工場     ┆ 関西   ┆ LINE-B1   ┆ 371          ┆ 8          │
│ 6   ┆ 2024-01-10    ┆ F02        ┆ 大阪工場     ┆ 関西   ┆ LINE-B1   ┆ 371          ┆ 7          │
│ 7   ┆ 2024-01-10    ┆ F02        ┆ 大阪工場     ┆ 関西   ┆ LINE-B2   ┆ 202          ┆ 4          │
│ 8   ┆ 2024-01-10    ┆ F02        ┆ 大阪工場     ┆ 関西   ┆ LINE-B2   ┆ 182          ┆ 5          │
│ 9   ┆ 2024-01-10    ┆ F03        ┆ 名古屋工場   ┆ 中部   ┆ LINE-C1   ┆ 142          ┆ 4          │
│ 10  ┆ 2024-01-10    ┆ F03        ┆ 名古屋工場   ┆ 中部   ┆ LINE-C1   ┆ 143          ┆ 3          │
└─────┴───────────────┴────────────┴──────────────┴────────┴───────────┴──────────────┴────────────┘
↳ 10 行取得

=== INNER JOIN 後の全件数確認 ===
── SQL ─────────────────────────────────────────
  SELECT COUNT(*) AS inner_join_count
  FROM   inspections i
  INNER  JOIN factories f ON i.factory_id = f.factory_id
───────────────────────────────────────────────
shape: (1, 1)
┌──────────────────┐
│ inner_join_count │
│ ---              │
│ i64              │
╞══════════════════╡
│ 73               │
└──────────────────┘
↳ 1 行取得

shape: (1, 1)

inner_join_count
i64
73

結果の読み取り

factory_name と region が検査記録に付与され、可読性が大幅に向上しています
INNER JOIN の全件数は 73件（inspections の全件数と同じ）です。すべての検査記録に有効な factory_id が存在することを確認できます
不正 part_code = 'UNKNOWN-999' の3件も factory_id は正常なため除外されていません。部品マスタとの結合（No.045, No.050）で不整合を検出します

No.042：LEFT JOINで片方のテーブルを基準に結合する

実務での意味

LEFT JOIN は 左テーブルの全行を保持し、右テーブルに一致がなければ NULL を補完します。製造業での主な活用場面：

全工場の一覧を表示し、検査実績がない工場を NULL で可視化
全部品の一覧を表示し、未検査の部品を特定
マスタに存在するすべての項目を網羅的に確認する「全数チェック」

分析・モデル化の考え方

\text{LEFT JOIN}(A, B) \supseteq \text{INNER JOIN}(A, B)

LEFT JOIN の結果は常に INNER JOIN の結果を包含します。左テーブルに存在するが右テーブルに一致しない行が追加されます。

状況	INNER JOIN	LEFT JOIN
A と B に一致する行	✅ 出力	✅ 出力
A にのみ存在する行	❌ 除外	✅ 出力（B 側は NULL）
B にのみ存在する行	❌ 除外	❌ 除外

Python で確認する

# No.042: LEFT JOIN — 全工場を基準に検査実績を集計（F05 が 0 件で表示される）

print('=== LEFT JOIN: 全工場の検査実績件数（F05 は NULL / 0 になる）===')
q(conn, '''
SELECT f.factory_id,
       f.factory_name,
       f.region,
       f.capacity,
       COUNT(i.id)                           AS inspection_count,
       COALESCE(SUM(i.production_qty), 0)    AS total_prod,
       COALESCE(SUM(i.defect_qty), 0)        AS total_defect
FROM   factories f
LEFT   JOIN inspections i ON f.factory_id = i.factory_id
GROUP  BY f.factory_id, f.factory_name, f.region, f.capacity
ORDER  BY inspection_count DESC
''')

=== LEFT JOIN: 全工場の検査実績件数（F05 は NULL / 0 になる）===
── SQL ─────────────────────────────────────────
  SELECT f.factory_id,
         f.factory_name,
         f.region,
         f.capacity,
         COUNT(i.id)                           AS inspection_count,
         COALESCE(SUM(i.production_qty), 0)    AS total_prod,
         COALESCE(SUM(i.defect_qty), 0)        AS total_defect
  FROM   factories f
  LEFT   JOIN inspections i ON f.factory_id = i.factory_id
  GROUP  BY f.factory_id, f.factory_name, f.region, f.capacity
  ORDER  BY inspection_count DESC
───────────────────────────────────────────────
shape: (5, 7)
┌────────────┬──────────────┬────────┬──────────┬──────────────────┬────────────┬──────────────┐
│ factory_id ┆ factory_name ┆ region ┆ capacity ┆ inspection_count ┆ total_prod ┆ total_defect │
│ ---        ┆ ---          ┆ ---    ┆ ---      ┆ ---              ┆ ---        ┆ ---          │
│ str        ┆ str          ┆ str    ┆ i64      ┆ i64              ┆ i64        ┆ i64          │
╞════════════╪══════════════╪════════╪══════════╪══════════════════╪════════════╪══════════════╡
│ F01        ┆ 東京工場     ┆ 関東   ┆ 2000     ┆ 21               ┆ 8046       ┆ 157          │
│ F02        ┆ 大阪工場     ┆ 関西   ┆ 1800     ┆ 21               ┆ 6158       ┆ 140          │
│ F03        ┆ 名古屋工場   ┆ 中部   ┆ 1600     ┆ 21               ┆ 4395       ┆ 101          │
│ F04        ┆ 福岡工場     ┆ 九州   ┆ 1200     ┆ 10               ┆ 3466       ┆ 78           │
│ F05        ┆ 仙台工場     ┆ 東北   ┆ 1000     ┆ 0                ┆ 0          ┆ 0            │
└────────────┴──────────────┴────────┴──────────┴──────────────────┴────────────┴──────────────┘
↳ 5 行取得

shape: (5, 7)

factory_id	factory_name	region	capacity	inspection_count	total_prod	total_defect
str	str	str	i64	i64	i64	i64
”F01"	"東京工場"	"関東”	2000	21	8046	157
”F02"	"大阪工場"	"関西”	1800	21	6158	140
”F03"	"名古屋工場"	"中部”	1600	21	4395	101
”F04"	"福岡工場"	"九州”	1200	10	3466	78
”F05"	"仙台工場"	"東北”	1000	0	0	0

結果の読み取り

F05（仙台工場） は inspection_count = 0、total_prod = 0 と表示されます。 INNER JOIN では F05 行自体が消えていましたが、LEFT JOIN で浮かび上がります
COALESCE(SUM(...), 0) を使うことで NULL を 0 に変換しています。 F05 の合計値が NULL のままでは後続の計算でエラーになる場合があります
検査実績のない工場が判明したことで、「稼働開始前か」「データ連携漏れか」を調査するきっかけになります。データ品質管理の出発点となる重要な確認です

No.043：RIGHT JOINの考え方を理解する

実務での意味

RIGHT JOIN は LEFT JOIN の逆方向版で、右テーブルの全行を保持します。 A RIGHT JOIN B は B LEFT JOIN A と等価です。

実務では RIGHT JOIN より LEFT JOIN（テーブル順を入れ替える）が好まれます。理由：LEFT JOIN は「何を基準にするか」が直感的に分かりやすいためです。

分析・モデル化の考え方

A \text{ RIGHT JOIN } B \equiv B \text{ LEFT JOIN } A

どちらを使っても同じ結果が得られます。コーディング規約として「常に LEFT JOIN を使う」チームも多くあります。

書き方	保持される側	除外される側
`A LEFT JOIN B`	A の全行	B のみの行
`A RIGHT JOIN B`	B の全行	A のみの行
`A FULL OUTER JOIN B`	両方の全行	なし（SQLite は非対応）

Python で確認する

# No.043: RIGHT JOIN — No.042 の LEFT JOIN と同じ結果になることを確認

print('=== RIGHT JOIN（inspections を左、factories を右）===')
df_right = q(conn, '''
SELECT f.factory_id,
       f.factory_name,
       COUNT(i.id) AS inspection_count
FROM   inspections i
RIGHT  JOIN factories f ON i.factory_id = f.factory_id
GROUP  BY f.factory_id, f.factory_name
ORDER  BY f.factory_id
''')

print()
print('=== 等価な LEFT JOIN（テーブル順を入れ替え）===')
df_left = q(conn, '''
SELECT f.factory_id,
       f.factory_name,
       COUNT(i.id) AS inspection_count
FROM   factories f
LEFT   JOIN inspections i ON f.factory_id = i.factory_id
GROUP  BY f.factory_id, f.factory_name
ORDER  BY f.factory_id
''')

right_vals = sorted(df_right['inspection_count'].to_list())
left_vals  = sorted(df_left['inspection_count'].to_list())
print()
print(f'RIGHT JOIN 結果件数: {len(df_right)}')
print(f'LEFT  JOIN 結果件数: {len(df_left)}')
print(f'両者は等価: {right_vals == left_vals}')

=== RIGHT JOIN（inspections を左、factories を右）===
── SQL ─────────────────────────────────────────
  SELECT f.factory_id,
         f.factory_name,
         COUNT(i.id) AS inspection_count
  FROM   inspections i
  RIGHT  JOIN factories f ON i.factory_id = f.factory_id
  GROUP  BY f.factory_id, f.factory_name
  ORDER  BY f.factory_id
───────────────────────────────────────────────
shape: (5, 3)
┌────────────┬──────────────┬──────────────────┐
│ factory_id ┆ factory_name ┆ inspection_count │
│ ---        ┆ ---          ┆ ---              │
│ str        ┆ str          ┆ i64              │
╞════════════╪══════════════╪══════════════════╡
│ F01        ┆ 東京工場     ┆ 21               │
│ F02        ┆ 大阪工場     ┆ 21               │
│ F03        ┆ 名古屋工場   ┆ 21               │
│ F04        ┆ 福岡工場     ┆ 10               │
│ F05        ┆ 仙台工場     ┆ 0                │
└────────────┴──────────────┴──────────────────┘
↳ 5 行取得

=== 等価な LEFT JOIN（テーブル順を入れ替え）===
── SQL ─────────────────────────────────────────
  SELECT f.factory_id,
         f.factory_name,
         COUNT(i.id) AS inspection_count
  FROM   factories f
  LEFT   JOIN inspections i ON f.factory_id = i.factory_id
  GROUP  BY f.factory_id, f.factory_name
  ORDER  BY f.factory_id
───────────────────────────────────────────────
shape: (5, 3)
┌────────────┬──────────────┬──────────────────┐
│ factory_id ┆ factory_name ┆ inspection_count │
│ ---        ┆ ---          ┆ ---              │
│ str        ┆ str          ┆ i64              │
╞════════════╪══════════════╪══════════════════╡
│ F01        ┆ 東京工場     ┆ 21               │
│ F02        ┆ 大阪工場     ┆ 21               │
│ F03        ┆ 名古屋工場   ┆ 21               │
│ F04        ┆ 福岡工場     ┆ 10               │
│ F05        ┆ 仙台工場     ┆ 0                │
└────────────┴──────────────┴──────────────────┘
↳ 5 行取得

RIGHT JOIN 結果件数: 5
LEFT  JOIN 結果件数: 5
両者は等価: True

結果の読み取り

RIGHT JOIN と LEFT JOIN（テーブル順を入れ替えた）の結果が完全に一致することが確認できます
SQLite 3.39 以降で RIGHT JOIN がサポートされましたが、多くの現場ではLEFT JOIN に統一して可読性を保っています
FULL OUTER JOIN（両テーブルの全行を保持）は SQLite では非サポートです。必要な場合は LEFT JOIN UNION ALL RIGHT JOIN で近似できます

No.044：工場マスタと検査記録を結合する

実務での意味

工場マスタ（工場名・地域・キャパシティ）と検査記録（生産数・不良数）を JOIN することで、地域・工場単位の KPI サマリーを自動生成できます。

製造業での活用例：

経営会議向けの工場別月次生産レポートの自動化
地域別（関東 / 関西 / 中部 / 九州）の不良率比較
キャパシティ（capacity）と実際の生産数を比較した稼働率分析

分析・モデル化の考え方

不良損失額を計算する際、工場別のコスト格差（人件費・設備費）は factories テーブルの拡張で対応できます。

\text{稼働率} = \frac{\text{実績生産数}}{\text{工場キャパシティ} \times \text{稼働日数}}

Python で確認する

# No.044: 工場マスタ × 検査記録 — 工場別 KPI 集計（不正コード除外）

print('=== 工場別 KPI サマリー（INNER JOIN + 不正コード除外）===')
q(conn, '''
SELECT f.factory_id,
       f.factory_name,
       f.region,
       f.capacity,
       COUNT(i.id)                                                    AS n_records,
       SUM(i.production_qty)                                          AS total_prod,
       SUM(i.defect_qty)                                              AS total_defect,
       ROUND(SUM(i.defect_qty) * 100.0 / SUM(i.production_qty), 2)  AS dr_pct,
       ROUND(SUM(i.production_qty) * 1.0 / f.capacity, 1)           AS prod_per_capacity
FROM   factories f
INNER  JOIN inspections i ON f.factory_id = i.factory_id
WHERE  i.part_code != 'UNKNOWN-999'
GROUP  BY f.factory_id, f.factory_name, f.region, f.capacity
ORDER  BY dr_pct DESC
''')

=== 工場別 KPI サマリー（INNER JOIN + 不正コード除外）===
── SQL ─────────────────────────────────────────
  SELECT f.factory_id,
         f.factory_name,
         f.region,
         f.capacity,
         COUNT(i.id)                                                    AS n_records,
         SUM(i.production_qty)                                          AS total_prod,
         SUM(i.defect_qty)                                              AS total_defect,
         ROUND(SUM(i.defect_qty) * 100.0 / SUM(i.production_qty), 2)  AS dr_pct,
         ROUND(SUM(i.production_qty) * 1.0 / f.capacity, 1)           AS prod_per_capacity
  FROM   factories f
  INNER  JOIN inspections i ON f.factory_id = i.factory_id
  WHERE  i.part_code != 'UNKNOWN-999'
  GROUP  BY f.factory_id, f.factory_name, f.region, f.capacity
  ORDER  BY dr_pct DESC
───────────────────────────────────────────────
shape: (4, 9)
┌────────────┬─────────────┬────────┬──────────┬───┬────────────┬────────────┬────────┬────────────┐
│ factory_id ┆ factory_nam ┆ region ┆ capacity ┆ … ┆ total_prod ┆ total_defe ┆ dr_pct ┆ prod_per_c │
│ ---        ┆ e           ┆ ---    ┆ ---      ┆   ┆ ---        ┆ ct         ┆ ---    ┆ apacity    │
│ str        ┆ ---         ┆ str    ┆ i64      ┆   ┆ i64        ┆ ---        ┆ f64    ┆ ---        │
│            ┆ str         ┆        ┆          ┆   ┆            ┆ i64        ┆        ┆ f64        │
╞════════════╪═════════════╪════════╪══════════╪═══╪════════════╪════════════╪════════╪════════════╡
│ F04        ┆ 福岡工場    ┆ 九州   ┆ 1200     ┆ … ┆ 3466       ┆ 78         ┆ 2.25   ┆ 2.9        │
│ F02        ┆ 大阪工場    ┆ 関西   ┆ 1800     ┆ … ┆ 5783       ┆ 128        ┆ 2.21   ┆ 3.2        │
│ F03        ┆ 名古屋工場  ┆ 中部   ┆ 1600     ┆ … ┆ 4247       ┆ 92         ┆ 2.17   ┆ 2.7        │
│ F01        ┆ 東京工場    ┆ 関東   ┆ 2000     ┆ … ┆ 7596       ┆ 149        ┆ 1.96   ┆ 3.8        │
└────────────┴─────────────┴────────┴──────────┴───┴────────────┴────────────┴────────┴────────────┘
↳ 4 行取得

shape: (4, 9)

factory_id	factory_name	region	capacity	n_records	total_prod	total_defect	dr_pct	prod_per_capacity
str	str	str	i64	i64	i64	i64	f64	f64
”F04"	"福岡工場"	"九州”	1200	10	3466	78	2.25	2.9
”F02"	"大阪工場"	"関西”	1800	20	5783	128	2.21	3.2
”F03"	"名古屋工場"	"中部”	1600	20	4247	92	2.17	2.7
”F01"	"東京工場"	"関東”	2000	20	7596	149	1.96	3.8

結果の読み取り

region が付与されたことで、関東 vs 関西 vs 中部 vs 九州の比較が可能になります
prod_per_capacity（生産数 / 工場キャパシティ）はキャパシティ活用度の目安です。値が高いラインは増産要求への余裕が少なく、設備投資の優先度が上がります
F05（仙台）は INNER JOIN のため結果に表示されません。全工場を表示したい場合は No.042 の LEFT JOIN を使います

No.045：部品マスタと検査記録を結合する

実務での意味

部品マスタ（部品名・カテゴリ・単価）と検査記録を JOIN することで、部品別の「不良損失額」や「生産額」を1クエリで計算できます。

製造業での活用例：

改善投資の優先順位付け（損失額の大きい部品から着手）
カテゴリ別（エンジン / ブレーキ / 電装 / サスペンション）の KPI 比較
単価 × 不良数による ROI 試算

分析・モデル化の考え方

\text{部品別不良損失額} = \sum_{i} \text{defect\_qty}_i \times \text{unit\_price}

部品マスタの単価は JOIN 後に各行に適用されるため、 SUM(i.defect_qty * p.unit_price) で正しく計算できます。

Python で確認する

# No.045: 部品マスタ × 検査記録 — 部品別損失額ランキング

print('=== 部品別 生産額・不良損失額（INNER JOIN）===')
q(conn, '''
SELECT p.part_code,
       p.part_name,
       p.category,
       p.unit_price,
       COUNT(i.id)                                                    AS n_records,
       SUM(i.production_qty)                                          AS total_prod,
       SUM(i.defect_qty)                                              AS total_defect,
       ROUND(SUM(i.defect_qty) * 100.0 / SUM(i.production_qty), 2)  AS dr_pct,
       SUM(i.production_qty * p.unit_price)                          AS production_value,
       SUM(i.defect_qty    * p.unit_price)                           AS defect_loss
FROM   parts p
INNER  JOIN inspections i ON p.part_code = i.part_code
GROUP  BY p.part_code, p.part_name, p.category, p.unit_price
ORDER  BY defect_loss DESC
''')

=== 部品別 生産額・不良損失額（INNER JOIN）===
── SQL ─────────────────────────────────────────
  SELECT p.part_code,
         p.part_name,
         p.category,
         p.unit_price,
         COUNT(i.id)                                                    AS n_records,
         SUM(i.production_qty)                                          AS total_prod,
         SUM(i.defect_qty)                                              AS total_defect,
         ROUND(SUM(i.defect_qty) * 100.0 / SUM(i.production_qty), 2)  AS dr_pct,
         SUM(i.production_qty * p.unit_price)                          AS production_value,
         SUM(i.defect_qty    * p.unit_price)                           AS defect_loss
  FROM   parts p
  INNER  JOIN inspections i ON p.part_code = i.part_code
  GROUP  BY p.part_code, p.part_name, p.category, p.unit_price
  ORDER  BY defect_loss DESC
───────────────────────────────────────────────


shape: (5, 10)
┌───────────┬────────────┬────────────┬───────────┬───┬───────────┬────────┬───────────┬───────────┐
│ part_code ┆ part_name  ┆ category   ┆ unit_pric ┆ … ┆ total_def ┆ dr_pct ┆ productio ┆ defect_lo │
│ ---       ┆ ---        ┆ ---        ┆ e         ┆   ┆ ect       ┆ ---    ┆ n_value   ┆ ss        │
│ str       ┆ str        ┆ str        ┆ ---       ┆   ┆ ---       ┆ f64    ┆ ---       ┆ ---       │
│           ┆            ┆            ┆ i64       ┆   ┆ i64       ┆        ┆ i64       ┆ i64       │
╞═══════════╪════════════╪════════════╪═══════════╪═══╪═══════════╪════════╪═══════════╪═══════════╡
│ ENG-002   ┆ クランクシ ┆ エンジン部 ┆ 8500      ┆ … ┆ 78        ┆ 2.25   ┆ 29461000  ┆ 663000    │
│           ┆ ャフト     ┆ 品         ┆           ┆   ┆           ┆        ┆           ┆           │
│ ELC-001   ┆ オルタネー ┆ 電装部品   ┆ 6800      ┆ … ┆ 59        ┆ 2.96   ┆ 13545600  ┆ 401200    │
│           ┆ タ         ┆            ┆           ┆   ┆           ┆        ┆           ┆           │
│ ENG-001   ┆ ピストンリ ┆ エンジン部 ┆ 1200      ┆ … ┆ 159       ┆ 1.9    ┆ 10047600  ┆ 190800    │
│           ┆ ング       ┆ 品         ┆           ┆   ┆           ┆        ┆           ┆           │
│ BRK-001   ┆ ブレーキパ ┆ ブレーキ部 ┆ 950       ┆ … ┆ 116       ┆ 1.99   ┆ 5547050   ┆ 110200    │
│           ┆ ッド       ┆ 品         ┆           ┆   ┆           ┆        ┆           ┆           │
│ SUS-001   ┆ ショックア ┆ サスペンシ ┆ 2800      ┆ … ┆ 35        ┆ 2.46   ┆ 3981600   ┆ 98000     │
│           ┆ ブソーバ   ┆ ョン       ┆           ┆   ┆           ┆        ┆           ┆           │
└───────────┴────────────┴────────────┴───────────┴───┴───────────┴────────┴───────────┴───────────┘
↳ 5 行取得

shape: (5, 10)

part_code	part_name	category	unit_price	n_records	total_prod	total_defect	dr_pct	production_value	defect_loss
str	str	str	i64	i64	i64	i64	f64	i64	i64
”ENG-002"	"クランクシャフト"	"エンジン部品”	8500	10	3466	78	2.25	29461000	663000
”ELC-001"	"オルタネータ"	"電装部品”	6800	10	1992	59	2.96	13545600	401200
”ENG-001"	"ピストンリング"	"エンジン部品”	1200	20	8373	159	1.9	10047600	190800
”BRK-001"	"ブレーキパッド"	"ブレーキ部品”	950	20	5839	116	1.99	5547050	110200
”SUS-001"	"ショックアブソーバ"	"サスペンション”	2800	10	1422	35	2.46	3981600	98000

結果の読み取り

defect_loss（不良損失額）上位の部品が改善投資の最優先ターゲットです。単価が高い部品（ENG-002: ¥8,500 / ELC-001: ¥6,800）は不良数が少なくても損失額が大きくなります
BRK-002 / ELC-002 / SUS-002 の3部品は inspections に一致行がないため INNER JOIN の結果に表示されません。（左は parts なので LEFT JOIN で表示可）
「不良数ランキング」と「損失額ランキング」が異なる場合、損失額ベースの優先順位を使うことで経営的なインパクトを正しく評価できます

No.046：複数テーブルを結合する

実務での意味

工場・ライン・部品・検査記録の 4テーブルを一度に JOIN することで、「どの工場の、どのラインで、どの部品の、不良率が高いか」を 1クエリで把握できます。

製造業での活用例：

経営会議向けの多軸 KPI ダッシュボード用データの生成
工場 × ライン × 部品カテゴリのクロス集計
ライン名称やキャパシティ情報を実績データに自動付与

分析・モデル化の考え方

複数テーブルの JOIN は、結合の順序を意識して記述します。

FROM   inspections i
INNER  JOIN factories f ON i.factory_id = f.factory_id
INNER  JOIN lines l     ON i.line_code  = l.line_code
INNER  JOIN parts p     ON i.part_code  = p.part_code

DB エンジンが実行順序を最適化するため、結合順序はクエリの意図（最初に絞り込みたいテーブルを先に）を優先します。

Python で確認する

# No.046: 4テーブル INNER JOIN — 工場 × ライン × 部品 の KPI 集計

print('=== 4テーブル結合: 工場 × ライン × 部品カテゴリ 集計 ===')
q(conn, '''
SELECT f.factory_name,
       f.region,
       l.line_name,
       p.part_name,
       p.category,
       COUNT(i.id)                                                    AS n_records,
       SUM(i.production_qty)                                          AS total_prod,
       SUM(i.defect_qty)                                              AS total_defect,
       ROUND(SUM(i.defect_qty) * 100.0 / SUM(i.production_qty), 2)  AS dr_pct
FROM   inspections i
INNER  JOIN factories f ON i.factory_id = f.factory_id
INNER  JOIN lines l     ON i.line_code  = l.line_code
INNER  JOIN parts p     ON i.part_code  = p.part_code
GROUP  BY f.factory_id, l.line_code, p.part_code
ORDER  BY f.factory_id, dr_pct DESC
''')

=== 4テーブル結合: 工場 × ライン × 部品カテゴリ 集計 ===
── SQL ─────────────────────────────────────────
  SELECT f.factory_name,
         f.region,
         l.line_name,
         p.part_name,
         p.category,
         COUNT(i.id)                                                    AS n_records,
         SUM(i.production_qty)                                          AS total_prod,
         SUM(i.defect_qty)                                              AS total_defect,
         ROUND(SUM(i.defect_qty) * 100.0 / SUM(i.production_qty), 2)  AS dr_pct
  FROM   inspections i
  INNER  JOIN factories f ON i.factory_id = f.factory_id
  INNER  JOIN lines l     ON i.line_code  = l.line_code
  INNER  JOIN parts p     ON i.part_code  = p.part_code
  GROUP  BY f.factory_id, l.line_code, p.part_code
  ORDER  BY f.factory_id, dr_pct DESC
───────────────────────────────────────────────
shape: (7, 9)
┌────────────┬────────┬────────────┬────────────┬───┬───────────┬────────────┬────────────┬────────┐
│ factory_na ┆ region ┆ line_name  ┆ part_name  ┆ … ┆ n_records ┆ total_prod ┆ total_defe ┆ dr_pct │
│ me         ┆ ---    ┆ ---        ┆ ---        ┆   ┆ ---       ┆ ---        ┆ ct         ┆ ---    │
│ ---        ┆ str    ┆ str        ┆ str        ┆   ┆ i64       ┆ i64        ┆ ---        ┆ f64    │
│ str        ┆        ┆            ┆            ┆   ┆           ┆            ┆ i64        ┆        │
╞════════════╪════════╪════════════╪════════════╪═══╪═══════════╪════════════╪════════════╪════════╡
│ 東京工場   ┆ 関東   ┆ エンジン部 ┆ ピストンリ ┆ … ┆ 10        ┆ 4582       ┆ 90         ┆ 1.96   │
│            ┆        ┆ 品ライン   ┆ ング       ┆   ┆           ┆            ┆            ┆        │
│ 東京工場   ┆ 関東   ┆ ブレーキ部 ┆ ブレーキパ ┆ … ┆ 10        ┆ 3014       ┆ 59         ┆ 1.96   │
│            ┆        ┆ 品ライン   ┆ ッド       ┆   ┆           ┆            ┆            ┆        │
│ 大阪工場   ┆ 関西   ┆ 電装部品ラ ┆ オルタネー ┆ … ┆ 10        ┆ 1992       ┆ 59         ┆ 2.96   │
│            ┆        ┆ イン       ┆ タ         ┆   ┆           ┆            ┆            ┆        │
│ 大阪工場   ┆ 関西   ┆ エンジン部 ┆ ピストンリ ┆ … ┆ 10        ┆ 3791       ┆ 69         ┆ 1.82   │
│            ┆        ┆ 品ライン   ┆ ング       ┆   ┆           ┆            ┆            ┆        │
│ 名古屋工場 ┆ 中部   ┆ サスペンシ ┆ ショックア ┆ … ┆ 10        ┆ 1422       ┆ 35         ┆ 2.46   │
│            ┆        ┆ ョンライン ┆ ブソーバ   ┆   ┆           ┆            ┆            ┆        │
│ 名古屋工場 ┆ 中部   ┆ ブレーキ部 ┆ ブレーキパ ┆ … ┆ 10        ┆ 2825       ┆ 57         ┆ 2.02   │
│            ┆        ┆ 品ライン   ┆ ッド       ┆   ┆           ┆            ┆            ┆        │
│ 福岡工場   ┆ 九州   ┆ エンジン部 ┆ クランクシ ┆ … ┆ 10        ┆ 3466       ┆ 78         ┆ 2.25   │
│            ┆        ┆ 品ライン   ┆ ャフト     ┆   ┆           ┆            ┆            ┆        │
└────────────┴────────┴────────────┴────────────┴───┴───────────┴────────────┴────────────┴────────┘
↳ 7 行取得

shape: (7, 9)

factory_name	region	line_name	part_name	category	n_records	total_prod	total_defect	dr_pct
str	str	str	str	str	i64	i64	i64	f64
”東京工場"	"関東"	"エンジン部品ライン"	"ピストンリング"	"エンジン部品”	10	4582	90	1.96
”東京工場"	"関東"	"ブレーキ部品ライン"	"ブレーキパッド"	"ブレーキ部品”	10	3014	59	1.96
”大阪工場"	"関西"	"電装部品ライン"	"オルタネータ"	"電装部品”	10	1992	59	2.96
”大阪工場"	"関西"	"エンジン部品ライン"	"ピストンリング"	"エンジン部品”	10	3791	69	1.82
”名古屋工場"	"中部"	"サスペンションライン"	"ショックアブソーバ"	"サスペンション”	10	1422	35	2.46
”名古屋工場"	"中部"	"ブレーキ部品ライン"	"ブレーキパッド"	"ブレーキ部品”	10	2825	57	2.02
”福岡工場"	"九州"	"エンジン部品ライン"	"クランクシャフト"	"エンジン部品”	10	3466	78	2.25

結果の読み取り

4テーブルが結合され、工場名 / 地域 / ライン名 / 部品名 / カテゴリが付与された可読性の高いサマリーが1クエリで生成できます
同一工場内でもラインによって不良率が異なることが確認できます。これは工程設計・作業標準・設備状態の差異を反映しています
n_records が均等（各ライン × 5日 × 2シフト = 10件）なので、 dr_pct の比較が公平な条件で行えています

No.047：結合キーの重複に注意する

実務での意味

結合先テーブルに同じキーが複数存在すると、結合後の行数が期待より増加します（ファンアウト）。

製造業での典型例：

品質基準テーブルを「基準改訂履歴」として運用している場合、同じ部品コードに「標準基準（2024Q1）」と「厳格基準（2024Q2）」の2行が存在
部品マスタをこの品質基準テーブルに JOIN すると、行数が増加する

分析・モデル化の考え方

quality_specs テーブルでは5部品が2行（基準改訂前後）を持ちます。

parts 行数	INNER JOIN 後	増加理由
8行	11行	ENG-001/002, BRK-001, ELC-001 が各2行にファンアウト

\text{JOIN 後の行数} = \sum_{k \in \text{keys}} |A_k| \times |B_k|

ここで $|A_k|$ , $|B_k|$ は各テーブルにおけるキー $k$ の出現回数です。

Python で確認する

# No.047: 結合キーの重複 — parts × quality_specs で行数増加を確認

print('=== parts（8件）× quality_specs（11件）の INNER JOIN ===')
q(conn, '''
SELECT p.part_code,
       p.part_name,
       p.unit_price,
       qs.spec_type,
       qs.max_dr_pct,
       qs.effective_from
FROM   parts p
INNER  JOIN quality_specs qs ON p.part_code = qs.part_code
ORDER  BY p.part_code, qs.effective_from
''')

print()
print('=== どの部品が何基準持つか（重複の確認）===')
q(conn, '''
SELECT p.part_code,
       p.part_name,
       COUNT(qs.spec_id) AS spec_count
FROM   parts p
LEFT   JOIN quality_specs qs ON p.part_code = qs.part_code
GROUP  BY p.part_code, p.part_name
ORDER  BY spec_count DESC, p.part_code
''')

=== parts（8件）× quality_specs（11件）の INNER JOIN ===
── SQL ─────────────────────────────────────────
  SELECT p.part_code,
         p.part_name,
         p.unit_price,
         qs.spec_type,
         qs.max_dr_pct,
         qs.effective_from
  FROM   parts p
  INNER  JOIN quality_specs qs ON p.part_code = qs.part_code
  ORDER  BY p.part_code, qs.effective_from
───────────────────────────────────────────────
shape: (11, 6)
┌───────────┬────────────────────┬────────────┬───────────┬────────────┬────────────────┐
│ part_code ┆ part_name          ┆ unit_price ┆ spec_type ┆ max_dr_pct ┆ effective_from │
│ ---       ┆ ---                ┆ ---        ┆ ---       ┆ ---        ┆ ---            │
│ str       ┆ str                ┆ i64        ┆ str       ┆ f64        ┆ str            │
╞═══════════╪════════════════════╪════════════╪═══════════╪════════════╪════════════════╡
│ BRK-001   ┆ ブレーキパッド     ┆ 950        ┆ 標準基準  ┆ 2.5        ┆ 2024-01-01     │
│ BRK-001   ┆ ブレーキパッド     ┆ 950        ┆ 厳格基準  ┆ 2.0        ┆ 2024-03-01     │
│ BRK-002   ┆ ブレーキキャリパー ┆ 4200       ┆ 標準基準  ┆ 3.0        ┆ 2024-01-01     │
│ ELC-001   ┆ オルタネータ       ┆ 6800       ┆ 標準基準  ┆ 3.5        ┆ 2024-01-01     │
│ ELC-001   ┆ オルタネータ       ┆ 6800       ┆ 厳格基準  ┆ 3.0        ┆ 2024-03-01     │
│ …         ┆ …                  ┆ …          ┆ …         ┆ …          ┆ …              │
│ ENG-001   ┆ ピストンリング     ┆ 1200       ┆ 標準基準  ┆ 2.0        ┆ 2024-01-01     │
│ ENG-001   ┆ ピストンリング     ┆ 1200       ┆ 厳格基準  ┆ 1.5        ┆ 2024-03-01     │
│ ENG-002   ┆ クランクシャフト   ┆ 8500       ┆ 標準基準  ┆ 1.8        ┆ 2024-01-01     │
│ ENG-002   ┆ クランクシャフト   ┆ 8500       ┆ 厳格基準  ┆ 1.2        ┆ 2024-03-01     │
│ SUS-001   ┆ ショックアブソーバ ┆ 2800       ┆ 標準基準  ┆ 2.8        ┆ 2024-01-01     │
└───────────┴────────────────────┴────────────┴───────────┴────────────┴────────────────┘
↳ 11 行取得

=== どの部品が何基準持つか（重複の確認）===
── SQL ─────────────────────────────────────────
  SELECT p.part_code,
         p.part_name,
         COUNT(qs.spec_id) AS spec_count
  FROM   parts p
  LEFT   JOIN quality_specs qs ON p.part_code = qs.part_code
  GROUP  BY p.part_code, p.part_name
  ORDER  BY spec_count DESC, p.part_code
───────────────────────────────────────────────
shape: (8, 3)
┌───────────┬────────────────────┬────────────┐
│ part_code ┆ part_name          ┆ spec_count │
│ ---       ┆ ---                ┆ ---        │
│ str       ┆ str                ┆ i64        │
╞═══════════╪════════════════════╪════════════╡
│ BRK-001   ┆ ブレーキパッド     ┆ 2          │
│ ELC-001   ┆ オルタネータ       ┆ 2          │
│ ENG-001   ┆ ピストンリング     ┆ 2          │
│ ENG-002   ┆ クランクシャフト   ┆ 2          │
│ BRK-002   ┆ ブレーキキャリパー ┆ 1          │
│ ELC-002   ┆ スタータモータ     ┆ 1          │
│ SUS-001   ┆ ショックアブソーバ ┆ 1          │
│ SUS-002   ┆ コイルスプリング   ┆ 0          │
└───────────┴────────────────────┴────────────┘
↳ 8 行取得

shape: (8, 3)

part_code	part_name	spec_count
str	str	i64
”BRK-001"	"ブレーキパッド”	2
”ELC-001"	"オルタネータ”	2
”ENG-001"	"ピストンリング”	2
”ENG-002"	"クランクシャフト”	2
”BRK-002"	"ブレーキキャリパー”	1
”ELC-002"	"スタータモータ”	1
”SUS-001"	"ショックアブソーバ”	1
”SUS-002"	"コイルスプリング”	0

結果の読み取り

parts（8件）と quality_specs（11件）を INNER JOIN すると 11件になります。 ENG-001, ENG-002, BRK-001, ELC-001 の各部品が2行（標準基準・厳格基準）持っているためです
SUS-002 は quality_specs に基準がないため INNER JOIN で除外されます（1行 → 0行）
実務上の対処：最新基準だけを使いたい場合は WHERE effective_from = MAX(...) のサブクエリや、JOIN 前に quality_specs を絞り込む必要があります

No.048：結合後の件数増加を確認する

実務での意味

JOIN を実行したあと、件数が期待通りか確認する習慣はデータエンジニアリングの基本です。

製造業での重要性：

集計クエリで行数増加に気づかないまま SUM を計算すると、二重計上が発生
月次レポートの数値が突然増えた場合、JOIN キーの重複が原因であることが多い
CI/CD パイプラインで COUNT(*) のアサーションを入れて品質担保する

分析・モデル化の考え方

JOIN 前後の件数を段階的に確認する手順：

結合前の件数: SELECT COUNT(*) FROM parts → 8件
INNER JOIN 後: SELECT COUNT(*) FROM parts INNER JOIN quality_specs ... → 11件（増加）
LEFT JOIN 後: SELECT COUNT(*) FROM parts LEFT JOIN quality_specs ... → 12件（SUS-002 の NULL 行を含む）
重複キーの特定: GROUP BY key HAVING COUNT(*) > 1

Python で確認する

# No.048: 結合後の件数増加を段階的に確認する

parts_n = conn.execute('SELECT COUNT(*) FROM parts').fetchone()[0]
qs_n    = conn.execute('SELECT COUNT(*) FROM quality_specs').fetchone()[0]
inner_n = conn.execute('''
    SELECT COUNT(*) FROM parts
    INNER JOIN quality_specs ON parts.part_code = quality_specs.part_code
''').fetchone()[0]
left_n  = conn.execute('''
    SELECT COUNT(*) FROM parts
    LEFT  JOIN quality_specs ON parts.part_code = quality_specs.part_code
''').fetchone()[0]

print('=== JOIN 前後の件数比較 ===')
print(f'  parts         : {parts_n} 件')
print(f'  quality_specs : {qs_n} 件')
print(f'  INNER JOIN 後 : {inner_n} 件  ← parts より {inner_n - parts_n} 件増加（重複キー）')
print(f'  LEFT  JOIN 後 : {left_n} 件  ← INNER より {left_n - inner_n} 件増加（NULL 行: SUS-002）')

# quality_specs で重複キーを持つ部品を特定
print()
print('=== quality_specs 内で重複する part_code ===')
q(conn, '''
SELECT part_code, COUNT(*) AS spec_count
FROM   quality_specs
GROUP  BY part_code
HAVING COUNT(*) > 1
ORDER  BY spec_count DESC, part_code
''')

=== JOIN 前後の件数比較 ===
  parts         : 8 件
  quality_specs : 11 件
  INNER JOIN 後 : 11 件  ← parts より 3 件増加（重複キー）
  LEFT  JOIN 後 : 12 件  ← INNER より 1 件増加（NULL 行: SUS-002）

=== quality_specs 内で重複する part_code ===
── SQL ─────────────────────────────────────────
  SELECT part_code, COUNT(*) AS spec_count
  FROM   quality_specs
  GROUP  BY part_code
  HAVING COUNT(*) > 1
  ORDER  BY spec_count DESC, part_code
───────────────────────────────────────────────
shape: (4, 2)
┌───────────┬────────────┐
│ part_code ┆ spec_count │
│ ---       ┆ ---        │
│ str       ┆ i64        │
╞═══════════╪════════════╡
│ BRK-001   ┆ 2          │
│ ELC-001   ┆ 2          │
│ ENG-001   ┆ 2          │
│ ENG-002   ┆ 2          │
└───────────┴────────────┘
↳ 4 行取得

shape: (4, 2)

part_code	spec_count
str	i64
”BRK-001”	2
”ELC-001”	2
”ENG-001”	2
”ENG-002”	2

結果の読み取り

INNER JOIN 後は parts の8件から11件に増加しています。 quality_specs で重複キーを持つ4部品（ENG-001/002, BRK-001, ELC-001）が原因です
LEFT JOIN 後はさらに1件増えて12件。SUS-002 が NULL 行として追加されます
対応策: JOIN 後に COUNT(*) で件数を確認し、意図しない増加があれば GROUP BY ... HAVING COUNT(*) > 1 で重複キーを特定してください
集計クエリ（SUM / AVG）を実行する前に必ず件数確認を行う習慣をつけましょう

No.049：検査実績のない部品を抽出する

実務での意味

LEFT JOIN + WHERE IS NULL パターンは**「存在しないものを検出する」** 最も強力な SQL テクニックの一つです。

製造業での活用例：

部品マスタに登録されているが、今期ゼロ生産の部品（製造中止? 段取り漏れ?）
品質基準が登録されていない部品（リスク管理の空白地帯）
稼働しているはずのラインから実績が届いていない日（センサー異常? データ連携エラー?）

分析・モデル化の考え方

\text{未検査部品} = \text{parts} - \text{INNER JOIN}(\text{parts},\, \text{inspections})

SELECT p.*
FROM   parts p
LEFT   JOIN inspections i ON p.part_code = i.part_code
WHERE  i.id IS NULL   -- ← 右テーブルに一致がなかった行のみ

WHERE i.id IS NULL で「LEFT JOIN で NULL になった行 = 実績のない部品」を抽出します。

Python で確認する

# No.049: 検査実績のない部品を LEFT JOIN + WHERE IS NULL で抽出

# ① 全部品の検査件数（LEFT JOIN + GROUP BY）
print('=== ① 全部品の検査件数（LEFT JOIN でゼロ件の部品も表示）===')
df_parts_join = q(conn, '''
SELECT p.part_code,
       p.part_name,
       p.category,
       p.unit_price,
       COUNT(i.id) AS inspection_count
FROM   parts p
LEFT   JOIN inspections i ON p.part_code = i.part_code
GROUP  BY p.part_code, p.part_name, p.category, p.unit_price
ORDER  BY p.part_code
''')

print()
print('=== ② 検査実績のない部品のみ（WHERE i.id IS NULL）===')
q(conn, '''
SELECT p.part_code,
       p.part_name,
       p.category,
       p.unit_price,
       p.supplier_code
FROM   parts p
LEFT   JOIN inspections i ON p.part_code = i.part_code
WHERE  i.id IS NULL
ORDER  BY p.part_code
''')

=== ① 全部品の検査件数（LEFT JOIN でゼロ件の部品も表示）===
── SQL ─────────────────────────────────────────
  SELECT p.part_code,
         p.part_name,
         p.category,
         p.unit_price,
         COUNT(i.id) AS inspection_count
  FROM   parts p
  LEFT   JOIN inspections i ON p.part_code = i.part_code
  GROUP  BY p.part_code, p.part_name, p.category, p.unit_price
  ORDER  BY p.part_code
───────────────────────────────────────────────
shape: (8, 5)
┌───────────┬────────────────────┬────────────────┬────────────┬──────────────────┐
│ part_code ┆ part_name          ┆ category       ┆ unit_price ┆ inspection_count │
│ ---       ┆ ---                ┆ ---            ┆ ---        ┆ ---              │
│ str       ┆ str                ┆ str            ┆ i64        ┆ i64              │
╞═══════════╪════════════════════╪════════════════╪════════════╪══════════════════╡
│ BRK-001   ┆ ブレーキパッド     ┆ ブレーキ部品   ┆ 950        ┆ 20               │
│ BRK-002   ┆ ブレーキキャリパー ┆ ブレーキ部品   ┆ 4200       ┆ 0                │
│ ELC-001   ┆ オルタネータ       ┆ 電装部品       ┆ 6800       ┆ 10               │
│ ELC-002   ┆ スタータモータ     ┆ 電装部品       ┆ 3500       ┆ 0                │
│ ENG-001   ┆ ピストンリング     ┆ エンジン部品   ┆ 1200       ┆ 20               │
│ ENG-002   ┆ クランクシャフト   ┆ エンジン部品   ┆ 8500       ┆ 10               │
│ SUS-001   ┆ ショックアブソーバ ┆ サスペンション ┆ 2800       ┆ 10               │
│ SUS-002   ┆ コイルスプリング   ┆ サスペンション ┆ 1800       ┆ 0                │
└───────────┴────────────────────┴────────────────┴────────────┴──────────────────┘
↳ 8 行取得

=== ② 検査実績のない部品のみ（WHERE i.id IS NULL）===
── SQL ─────────────────────────────────────────
  SELECT p.part_code,
         p.part_name,
         p.category,
         p.unit_price,
         p.supplier_code
  FROM   parts p
  LEFT   JOIN inspections i ON p.part_code = i.part_code
  WHERE  i.id IS NULL
  ORDER  BY p.part_code
───────────────────────────────────────────────
shape: (3, 5)
┌───────────┬────────────────────┬────────────────┬────────────┬───────────────┐
│ part_code ┆ part_name          ┆ category       ┆ unit_price ┆ supplier_code │
│ ---       ┆ ---                ┆ ---            ┆ ---        ┆ ---           │
│ str       ┆ str                ┆ str            ┆ i64        ┆ str           │
╞═══════════╪════════════════════╪════════════════╪════════════╪═══════════════╡
│ BRK-002   ┆ ブレーキキャリパー ┆ ブレーキ部品   ┆ 4200       ┆ SUP-02        │
│ ELC-002   ┆ スタータモータ     ┆ 電装部品       ┆ 3500       ┆ SUP-03        │
│ SUS-002   ┆ コイルスプリング   ┆ サスペンション ┆ 1800       ┆ SUP-04        │
└───────────┴────────────────────┴────────────────┴────────────┴───────────────┘
↳ 3 行取得

shape: (3, 5)

part_code	part_name	category	unit_price	supplier_code
str	str	str	i64	str
”BRK-002"	"ブレーキキャリパー"	"ブレーキ部品”	4200	”SUP-02"
"ELC-002"	"スタータモータ"	"電装部品”	3500	”SUP-03"
"SUS-002"	"コイルスプリング"	"サスペンション”	1800	”SUP-04”

# No.049 可視化：部品別 検査件数（未検査部品を赤でハイライト）
rows_p  = df_parts_join.to_dicts()
p_codes = [r['part_code'] for r in rows_p]
counts  = [r['inspection_count'] for r in rows_p]
bar_col = ['#D65F5F' if c == 0 else '#4878CF' for c in counts]

fig, ax = plt.subplots(figsize=(10, 5))
bars = ax.bar(range(len(p_codes)), counts, color=bar_col, alpha=0.85,
              edgecolor='black', linewidth=0.4)
for bar, v in zip(bars, counts):
    ax.text(bar.get_x() + bar.get_width() / 2, bar.get_height() + 0.3,
            str(v), ha='center', va='bottom', fontsize=9)
ax.set_xticks(range(len(p_codes)))
ax.set_xticklabels(p_codes, rotation=30, ha='right', fontsize=9)
ax.set_title('部品別 検査件数（LEFT JOINで全部品を網羅）', fontsize=12, pad=10)
ax.set_xlabel('部品コード', fontsize=10)
ax.set_ylabel('検査件数', fontsize=10)
ax.grid(axis='y', alpha=0.3)
legend_h = [
    Patch(color='#4878CF', alpha=0.85, label='検査実績あり'),
    Patch(color='#D65F5F', alpha=0.85, label='検査実績なし（要確認）'),
]
ax.legend(handles=legend_h, fontsize=9, loc='upper right')
plt.tight_layout()
plt.show()
print('部品別検査件数グラフ表示完了（SVG 2/2）')

svg

部品別検査件数グラフ表示完了（SVG 2/2）

結果の読み取り

BRK-002 / ELC-002 / SUS-002 の3部品が検査件数ゼロ（赤バー）として浮かび上がります。グラフを見るだけで「今期未検査の部品一覧」が瞬時に把握できます
これらの部品は今期のラインに割り当てられていない可能性があります。「製造中止か」「段取り変更中か」「データ連携漏れか」を現場に確認するトリガーになります
WHERE i.id IS NULL（NOT EXISTS パターン）は、SQLite / PostgreSQL / MySQL 共通で使えます。 NOT IN (SELECT ...) でも同じ結果が得られますが、NULL を含むと動作が変わるため LEFT JOIN + IS NULL の方が安全です

No.050：部品マスタに存在しない検査記録を検出する

実務での意味

No.049 の逆パターンです。今度は**「実績データが参照するマスタコードが存在しない」** = 孤立した（オーファン）レコードを検出します。

製造業での活用例：

手入力ミスや旧コードで登録された検査記録（UNKNOWN-999 など）
マスタ削除後も残った実績データ（削除保護の欠如）
外部システムからのデータ連携ミスで紛れ込んだ不明コード

分析・モデル化の考え方

\text{孤立レコード} = \text{inspections} - \text{INNER JOIN}(\text{inspections},\, \text{parts})

SELECT i.*
FROM   inspections i
LEFT   JOIN parts p ON i.part_code = p.part_code
WHERE  p.part_code IS NULL   -- ← parts に一致がなかった = 不正コード

No.049 と同じ LEFT JOIN + WHERE IS NULL パターンですが、 JOINの方向（どちらが基準か）が逆なことに注目してください。

Python で確認する

# No.050: 部品マスタに存在しない検査記録（不正 part_code）を検出

print('=== 不正 part_code の検出（LEFT JOIN + WHERE p.part_code IS NULL）===')
q(conn, '''
SELECT i.id,
       i.inspection_date,
       i.factory_id,
       i.line_code,
       i.part_code          AS orphaned_part_code,
       i.shift,
       i.production_qty,
       i.defect_qty,
       p.part_name          AS part_name_check
FROM   inspections i
LEFT   JOIN parts p ON i.part_code = p.part_code
WHERE  p.part_code IS NULL
ORDER  BY i.id
''')

print()
print('=== 不正コードの集計（どの工場・ラインから来たか）===')
q(conn, '''
SELECT i.factory_id, i.line_code,
       COUNT(*) AS orphaned_count,
       i.part_code AS orphaned_code
FROM   inspections i
LEFT   JOIN parts p ON i.part_code = p.part_code
WHERE  p.part_code IS NULL
GROUP  BY i.factory_id, i.line_code, i.part_code
ORDER  BY orphaned_count DESC
''')

=== 不正 part_code の検出（LEFT JOIN + WHERE p.part_code IS NULL）===
── SQL ─────────────────────────────────────────
  SELECT i.id,
         i.inspection_date,
         i.factory_id,
         i.line_code,
         i.part_code          AS orphaned_part_code,
         i.shift,
         i.production_qty,
         i.defect_qty,
         p.part_name          AS part_name_check
  FROM   inspections i
  LEFT   JOIN parts p ON i.part_code = p.part_code
  WHERE  p.part_code IS NULL
  ORDER  BY i.id
───────────────────────────────────────────────
shape: (3, 9)
┌─────┬──────────────┬────────────┬───────────┬───┬───────┬─────────────┬────────────┬─────────────┐
│ id  ┆ inspection_d ┆ factory_id ┆ line_code ┆ … ┆ shift ┆ production_ ┆ defect_qty ┆ part_name_c │
│ --- ┆ ate          ┆ ---        ┆ ---       ┆   ┆ ---   ┆ qty         ┆ ---        ┆ heck        │
│ i64 ┆ ---          ┆ str        ┆ str       ┆   ┆ str   ┆ ---         ┆ i64        ┆ ---         │
│     ┆ str          ┆            ┆           ┆   ┆       ┆ i64         ┆            ┆ null        │
╞═════╪══════════════╪════════════╪═══════════╪═══╪═══════╪═════════════╪════════════╪═════════════╡
│ 71  ┆ 2024-01-15   ┆ F01        ┆ LINE-A1   ┆ … ┆ 早番  ┆ 450         ┆ 8          ┆ null        │
│ 72  ┆ 2024-01-22   ┆ F02        ┆ LINE-B1   ┆ … ┆ 遅番  ┆ 375         ┆ 12         ┆ null        │
│ 73  ┆ 2024-02-10   ┆ F03        ┆ LINE-C1   ┆ … ┆ 早番  ┆ 148         ┆ 9          ┆ null        │
└─────┴──────────────┴────────────┴───────────┴───┴───────┴─────────────┴────────────┴─────────────┘
↳ 3 行取得

=== 不正コードの集計（どの工場・ラインから来たか）===
── SQL ─────────────────────────────────────────
  SELECT i.factory_id, i.line_code,
         COUNT(*) AS orphaned_count,
         i.part_code AS orphaned_code
  FROM   inspections i
  LEFT   JOIN parts p ON i.part_code = p.part_code
  WHERE  p.part_code IS NULL
  GROUP  BY i.factory_id, i.line_code, i.part_code
  ORDER  BY orphaned_count DESC
───────────────────────────────────────────────
shape: (3, 4)
┌────────────┬───────────┬────────────────┬───────────────┐
│ factory_id ┆ line_code ┆ orphaned_count ┆ orphaned_code │
│ ---        ┆ ---       ┆ ---            ┆ ---           │
│ str        ┆ str       ┆ i64            ┆ str           │
╞════════════╪═══════════╪════════════════╪═══════════════╡
│ F01        ┆ LINE-A1   ┆ 1              ┆ UNKNOWN-999   │
│ F02        ┆ LINE-B1   ┆ 1              ┆ UNKNOWN-999   │
│ F03        ┆ LINE-C1   ┆ 1              ┆ UNKNOWN-999   │
└────────────┴───────────┴────────────────┴───────────────┘
↳ 3 行取得

shape: (3, 4)

factory_id	line_code	orphaned_count	orphaned_code
str	str	i64	str
”F01"	"LINE-A1”	1	”UNKNOWN-999"
"F02"	"LINE-B1”	1	”UNKNOWN-999"
"F03"	"LINE-C1”	1	”UNKNOWN-999”

結果の読み取り

part_code = 'UNKNOWN-999' の3件が検出されました。part_name_check が NULL です。これらは parts マスタに存在しない不正なコードを持つレコードです
不正コードの発生元は F01/F02/F03 の3工場にまたがっています。手入力ミスよりデータ連携バグの可能性が高い（特定ラインからではなく分散して発生）
対応手順:
1. orphaned_count が多い工場・ラインのデータ担当者に連絡
2. 正しい part_code を特定して UPDATE または DELETE で修正
3. 再発防止として外部キー制約（FOREIGN KEY）を追加する
LEFT JOIN + WHERE IS NULL パターンは実務の データ品質チェッククエリ として定期実行（cron / Airflow）に組み込むと効果的です

対象ノックを通して見える実務上の示唆

No.041〜050 で学んだ JOIN を通じて、以下の製造業実務課題が SQL で解決できます。

実務課題	SQL パターン
工場名・地域を実績に付与	`INNER JOIN factories`
単価 × 不良数で損失額を計算	`INNER JOIN parts` + `SUM(defect * price)`
全工場を網羅してゼロ件工場を把握	`LEFT JOIN factories ... COALESCE(...)`
3テーブル以上の横断 KPI	`JOIN factories JOIN lines JOIN parts`
品質基準の重複による誤集計防止	JOIN 前後の `COUNT(*)` 確認
未検査部品・未稼働ラインの特定	`LEFT JOIN ... WHERE IS NULL`
不正コードの検出と修正	`LEFT JOIN parts ... WHERE p.part_code IS NULL`

VLOOKUP → SQL JOIN の移行で、毎月の突き合わせ作業が 1クエリ × 数秒 に短縮できます。

実務導入する場合に必要なこと

1. 外部キー制約の整備

SQLite では PRAGMA foreign_keys = ON; を実行しないと外部キー制約が無効です。本番 DB（PostgreSQL / MySQL）では FOREIGN KEY を設定し、不正コードの混入をDB レベルで防止します。

2. インデックスの設定

JOIN の結合キー（factory_id, part_code, line_code）にはインデックスを設定すると大量データでの JOIN が高速化されます。 CREATE INDEX idx_insp_part ON inspections(part_code);

3. LEFT JOIN + WHERE IS NULL の定期実行

データ品質チェック用クエリ（No.049, No.050）を定期ジョブに組み込み、不正コードや未検査部品が発生したら即座にアラートを送る仕組みを作ります。

4. JOIN 結合後の件数アサーション

ETL パイプラインや月次集計バッチで、COUNT(*) によるアサーションを入れ、期待件数を超えた場合に処理を中断・アラートする設計を推奨します。

まとめ

本章では、SQL の JOIN を使って製造実績 DB の複数テーブルを横断分析しました。

ノック	主な SQL	製造業での活用ポイント
No.041	`INNER JOIN`	工場名・単価を実績に付与した基本結合
No.042	`LEFT JOIN`	全工場基準で検査実績ゼロ工場を把握
No.043	`RIGHT JOIN`	LEFT JOIN との等価性を理解
No.044	`JOIN factories`	工場別 KPI（不良率・稼働率）集計
No.045	`JOIN parts`	部品別損失額ランキング
No.046	`JOIN × 3 連鎖`	工場+ライン+部品+検査記録の横断分析
No.047	重複キーの把握	品質基準テーブルの重複による行数増加
No.048	件数アサーション	JOIN 前後の `COUNT(*)` で品質担保
No.049	`LEFT JOIN + WHERE IS NULL`	未検査部品の自動特定
No.050	`LEFT JOIN + WHERE IS NULL`	不正コードの自動検出

次章（第6章: サブクエリと CTE）では、 JOIN と組み合わせてさらに複雑な分析クエリを構築する方法を学びます。

法人向けのご相談

製造業のデータ分析・SQL 教育・DX 推進について、以下よりお気軽にご相談ください。

📩 お問い合わせ: surikobo.co.jp/contact まずはお気軽にご相談ください。

SQLのJOINで工場・部品・検査記録を横断分析する

SQLのJOINで工場・部品・検査記録を横断分析する

SQL 100本ノック 第5章（No.041〜No.050）：JOINによるテーブル結合

はじめに：この記事で扱う製造業の実務課題

現場でよくある状況

なぜこの問題は判断が難しいのか

1. INNER JOIN と LEFT JOIN の使い分け

2. 結合キーの重複による行数増加

3. NULL の判定は IS NULL を使う

4. 結合前後の件数検証

今回扱うノックの全体像

Python 環境の準備

架空データの作成

No.041：INNER JOINでテーブルを結合する

実務での意味

分析・モデル化の考え方

Python で確認する

結果の読み取り

No.042：LEFT JOINで片方のテーブルを基準に結合する

実務での意味

分析・モデル化の考え方

Python で確認する

結果の読み取り

No.043：RIGHT JOINの考え方を理解する

実務での意味

分析・モデル化の考え方

Python で確認する

結果の読み取り

No.044：工場マスタと検査記録を結合する

実務での意味

分析・モデル化の考え方

Python で確認する

結果の読み取り

No.045：部品マスタと検査記録を結合する

実務での意味

分析・モデル化の考え方

Python で確認する

結果の読み取り

No.046：複数テーブルを結合する

実務での意味

分析・モデル化の考え方

Python で確認する

結果の読み取り

No.047：結合キーの重複に注意する

実務での意味

分析・モデル化の考え方

Python で確認する

結果の読み取り

No.048：結合後の件数増加を確認する

実務での意味

分析・モデル化の考え方

Python で確認する

結果の読み取り

No.049：検査実績のない部品を抽出する

実務での意味

分析・モデル化の考え方

Python で確認する

結果の読み取り

No.050：部品マスタに存在しない検査記録を検出する

実務での意味

分析・モデル化の考え方

Python で確認する

結果の読み取り

対象ノックを通して見える実務上の示唆

実務導入する場合に必要なこと

1. 外部キー制約の整備

2. インデックスの設定

3. LEFT JOIN + WHERE IS NULL の定期実行

4. JOIN 結合後の件数アサーション

まとめ

法人向けのご相談

Related Posts

製造ラインのデータ品質管理・SQL設計・BI連携を実務に落とし込む

SQL応用分析テクニックで製造業の意思決定を深掘りする

受注KPI・顧客分析・在庫リスク評価をSQLで自動化する

SQLウィンドウ関数で製造ラインの月次トレンドを多角的に分析する

サブクエリ・CTEで製造ラインの異常検知とKPI集計を自動化する

SQL 100本ノック第5章（No.041〜No.050）：JOINによるテーブル結合

3. NULL の判定は `IS NULL` を使う