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

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

2020-01-13

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

SQL 100本ノック第6章（No.051〜No.060）：サブクエリとCTE

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

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

製造現場の品質管理では、次のような問いが日常的に発生します。

「全ラインの平均不良率を超えているのはどのラインか？」
「メンテナンスを実施した日と生産実績はどう対応しているか？」
「月次・ライン別の損失コストを一度に集計したい」

こうした問いに対して、サブクエリ と CTE（Common Table Expression） を使うと、
単一の SELECT 文の中で段階的な分析を実現でき、定期レポートの自動化にも直結します。

機能	SQL 構文	製造業での活用例
スカラサブクエリ	`WHERE x > (SELECT AVG(x) ...)`	全体平均を超えたラインを検出
FROM サブクエリ	`FROM (SELECT ...) AS sub`	集計済み中間テーブルをさらにフィルタ
SELECT サブクエリ	`SELECT (SELECT ...)`	各行に全体平均・ベンチマーク値を付加
IN サブクエリ	`WHERE x IN (SELECT ...)`	条件に合うラインコードを動的に特定
EXISTS	`WHERE EXISTS (SELECT 1 ...)`	メンテナンス実施日の生産データを抽出
相関サブクエリ	`WHERE x > (SELECT AVG(x) WHERE line = outer.line)`	ライン別平均との比較
CTE（WITH 句）	`WITH cte AS (...)`	複数段階の集計を見通しよく整理

現場でよくある状況

工場の品質管理担当者が毎朝行う「朝礼前の品質確認」を想像してください。

昨日の各ラインの不良数を確認する
自ラインの過去平均と比較して、異常水準でないか判断する
メンテナンスを実施したラインの不良率が改善しているか確認する
月次で損失コストを算出し、上長へ報告する

これら 4 つのステップは、それぞれ WHERE サブクエリ → 相関サブクエリ → EXISTS → CTE という
SQL の構造に対応しています。本章ではこれらを順番に習得します。

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

単純な SELECT + WHERE で済まない理由は、「比較の基準値 = 動的」 であることです。

\text{異常判定} = \text{当日不良数} > \underbrace{\text{過去平均不良数（ライン別）}}_{\text{毎月・毎期間変化する}}

「全社平均」でなく「ライン別の動的平均」と比較する必要があり、
これを実現するには外側のクエリと内側のクエリが連動する 相関サブクエリ が必要です。

さらに複数段階の集計（日別 → 月別 → 損失コスト換算）を一つの SQL に整理するには、
CTE（WITH 句） による処理の分割が有効です。CTE を使うと以下のメリットがあります。

中間テーブルに名前を付けて参照可能
クエリのステップが上から下へ自然に読める
同じ集計を複数箇所で再利用できる

今回扱うノックの全体像

No.	テーマ	SQL の機能	製造業での用途
051	サブクエリの基本を理解する	スカラサブクエリ	平均単価・平均不良率との比較
052	WHERE句でサブクエリを使う	WHERE + サブクエリ	平均を超えた異常日の検出
053	FROM句でサブクエリを使う	インラインビュー	集計済みテーブルのさらなるフィルタ
054	SELECT句でサブクエリを使う	スカラサブクエリ	各行に全体平均を付加して乖離確認
055	INを使ったサブクエリを作る	IN / NOT IN	メンテナンス対象ラインの絞り込み
056	EXISTSを使ったサブクエリを作る	EXISTS / NOT EXISTS	メンテナンス実施日の生産実績抽出
057	相関サブクエリを理解する	相関サブクエリ	ライン別平均超え日の動的検出
058	WITH句でCTEを作る	WITH（CTE）	月次不良率テーブルの整理
059	CTEを使って複雑な集計を整理する	複数 CTE + JOIN	月次 KPI ダッシュボード
060	一時的な分析テーブルを作る	CTE 多段積み	総合分析ダッシュボード

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
from datetime import date, timedelta
import numpy as np
import polars as pl
import matplotlib
import matplotlib.pyplot as plt

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


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


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

ライブラリ読み込み完了

架空データの作成

conn = sqlite3.connect(":memory:")

# ── line_master ────────────────────────────────────────────
conn.execute("""
CREATE TABLE line_master (
    line_code   TEXT PRIMARY KEY,
    line_name   TEXT,
    section     TEXT,
    target_dr   REAL,
    unit_cost   INTEGER,
    capacity    INTEGER
)
""")
LINE_CFG = [
    ("LINE-A1", "機械加工ライン1", "機械加工", 0.020, 1200, 500),
    ("LINE-A2", "機械加工ライン2", "機械加工", 0.020, 8500, 250),
    ("LINE-B1", "組立ライン1", "組立", 0.015, 950, 400),
    ("LINE-B2", "組立ライン2", "組立", 0.015, 4200, 200),
    ("LINE-C1", "溶接ライン1", "溶接", 0.025, 6800, 130),
]
conn.executemany("INSERT INTO line_master VALUES (?,?,?,?,?,?)", LINE_CFG)

# ── production_daily ──────────────────────────────────────
conn.execute("""
CREATE TABLE production_daily (
    prod_id        INTEGER PRIMARY KEY,
    prod_date      TEXT,
    line_code      TEXT,
    production_qty INTEGER,
    defect_qty     INTEGER
)
""")

PROD_PARAMS = {
    "LINE-A1": dict(base_prod=460, base_dr=0.020),
    "LINE-A2": dict(base_prod=220, base_dr=0.023),
    "LINE-B1": dict(base_prod=380, base_dr=0.017),
    "LINE-B2": dict(base_prod=175, base_dr=0.025),
    "LINE-C1": dict(base_prod=115, base_dr=0.032),
}

start, end = date(2025, 1, 6), date(2025, 3, 31)
bdays = []
d = start
while d <= end:
    if d.weekday() < 5:
        bdays.append(d)
    d += timedelta(days=1)

records = []
pid = 1
for day in bdays:
    for lc, p in PROD_PARAMS.items():
        prod = max(int(p["base_prod"] + np.random.normal(0, p["base_prod"] * 0.05)), 1)
        dr = max(p["base_dr"] + np.random.normal(0, p["base_dr"] * 0.30), 0.001)
        defect = max(int(round(prod * dr)), 0)
        records.append((pid, str(day), lc, prod, defect))
        pid += 1
conn.executemany("INSERT INTO production_daily VALUES (?,?,?,?,?)", records)

# ── maintenance_log ────────────────────────────────────────
conn.execute("""
CREATE TABLE maintenance_log (
    maint_id      INTEGER PRIMARY KEY,
    line_code     TEXT,
    maint_date    TEXT,
    maint_type    TEXT,
    downtime_hours REAL
)
""")
MAINT_TYPES = ["定期点検", "緊急修理", "部品交換", "精度調整"]
MAINT_COUNTS = {"LINE-A1": 5, "LINE-A2": 5, "LINE-B1": 4, "LINE-B2": 5, "LINE-C1": 8}
np.random.seed(42)
maint_records = []
mid = 1
for lc, cnt in MAINT_COUNTS.items():
    chosen = np.random.choice(len(bdays), cnt, replace=False)
    for idx in sorted(chosen):
        mt = MAINT_TYPES[np.random.randint(len(MAINT_TYPES))]
        hours = round(float(np.random.uniform(1.0, 8.0)), 1)
        maint_records.append((mid, lc, str(bdays[idx]), mt, hours))
        mid += 1
conn.executemany("INSERT INTO maintenance_log VALUES (?,?,?,?,?)", maint_records)
conn.commit()

print(f"稼働日数        : {len(bdays)} 日（{bdays[0]} 〜 {bdays[-1]}）")
print(f"production_daily: {len(records)} 件")
print(f"maintenance_log : {len(maint_records)} 件")

稼働日数        : 61 日（2025-01-06 〜 2025-03-31）
production_daily: 305 件
maintenance_log : 27 件

for tbl in ["line_master", "production_daily", "maintenance_log"]:
    cur = conn.execute(f"SELECT COUNT(*) FROM {tbl}")
    n = cur.fetchone()[0]
    print(f"{tbl:25s}: {n:4d} 件")

print()
q(conn, "SELECT * FROM line_master")

line_master              :    5 件
production_daily         :  305 件
maintenance_log          :   27 件

── SQL ─────────────────────────────────────────
  SELECT * FROM line_master
───────────────────────────────────────────────
shape: (5, 6)
┌───────────┬─────────────────┬──────────┬───────────┬───────────┬──────────┐
│ line_code ┆ line_name       ┆ section  ┆ target_dr ┆ unit_cost ┆ capacity │
│ ---       ┆ ---             ┆ ---      ┆ ---       ┆ ---       ┆ ---      │
│ str       ┆ str             ┆ str      ┆ f64       ┆ i64       ┆ i64      │
╞═══════════╪═════════════════╪══════════╪═══════════╪═══════════╪══════════╡
│ LINE-A1   ┆ 機械加工ライン1 ┆ 機械加工 ┆ 0.02      ┆ 1200      ┆ 500      │
│ LINE-A2   ┆ 機械加工ライン2 ┆ 機械加工 ┆ 0.02      ┆ 8500      ┆ 250      │
│ LINE-B1   ┆ 組立ライン1     ┆ 組立     ┆ 0.015     ┆ 950       ┆ 400      │
│ LINE-B2   ┆ 組立ライン2     ┆ 組立     ┆ 0.015     ┆ 4200      ┆ 200      │
│ LINE-C1   ┆ 溶接ライン1     ┆ 溶接     ┆ 0.025     ┆ 6800      ┆ 130      │
└───────────┴─────────────────┴──────────┴───────────┴───────────┴──────────┘
↳ 5 行取得

shape: (5, 6)

line_code	line_name	section	target_dr	unit_cost	capacity
str	str	str	f64	i64	i64
”LINE-A1"	"機械加工ライン1"	"機械加工”	0.02	1200	500
”LINE-A2"	"機械加工ライン2"	"機械加工”	0.02	8500	250
”LINE-B1"	"組立ライン1"	"組立”	0.015	950	400
”LINE-B2"	"組立ライン2"	"組立”	0.015	4200	200
”LINE-C1"	"溶接ライン1"	"溶接”	0.025	6800	130

No.051：サブクエリの基本を理解する

実務での意味

サブクエリ（副問い合わせ） とは、SQL の中に別の SELECT 文を埋め込む構造です。
製造現場では「全ラインの平均単価より高い部品を扱うラインはどこか」のように、
「比較のための集計値」を動的に求めながら絞り込む場面で必要になります。

分析・モデル化の考え方

スカラサブクエリは、1行1列を返す SELECT 文を比較演算子の右辺として埋め込みます。

\text{WHERE unit\_cost} > \underbrace{\left(\text{SELECT AVG(unit\_cost) FROM line\_master}\right)}_{\text{実行時に計算される動的な値}}

SQL の実行順序は：

内側のサブクエリが先に評価され、平均単価（スカラ値）が確定する
外側の WHERE 句がその値を使って行を絞り込む

Python で確認する

print("=== No.051 サブクエリの基本を理解する ===\n")

# ① 平均単価を超えるラインを抽出
print("① 平均単価より高い unit_cost を持つライン")
df51a = q(
    conn,
    """
SELECT line_code, line_name, section, unit_cost
FROM line_master
WHERE unit_cost > (SELECT AVG(unit_cost) FROM line_master)
ORDER BY unit_cost DESC
""",
)

# ② 比較の基準値（全ライン平均単価）を確認
print("\n② 全ライン平均単価（サブクエリが返す値）")
q(
    conn,
    """
SELECT ROUND(AVG(unit_cost), 0) AS avg_unit_cost
FROM line_master
""",
)

=== No.051 サブクエリの基本を理解する ===

① 平均単価より高い unit_cost を持つライン
── SQL ─────────────────────────────────────────
  SELECT line_code, line_name, section, unit_cost
  FROM line_master
  WHERE unit_cost > (SELECT AVG(unit_cost) FROM line_master)
  ORDER BY unit_cost DESC
───────────────────────────────────────────────
shape: (2, 4)
┌───────────┬─────────────────┬──────────┬───────────┐
│ line_code ┆ line_name       ┆ section  ┆ unit_cost │
│ ---       ┆ ---             ┆ ---      ┆ ---       │
│ str       ┆ str             ┆ str      ┆ i64       │
╞═══════════╪═════════════════╪══════════╪═══════════╡
│ LINE-A2   ┆ 機械加工ライン2 ┆ 機械加工 ┆ 8500      │
│ LINE-C1   ┆ 溶接ライン1     ┆ 溶接     ┆ 6800      │
└───────────┴─────────────────┴──────────┴───────────┘
↳ 2 行取得

② 全ライン平均単価（サブクエリが返す値）
── SQL ─────────────────────────────────────────
  SELECT ROUND(AVG(unit_cost), 0) AS avg_unit_cost
  FROM line_master
───────────────────────────────────────────────
shape: (1, 1)
┌───────────────┐
│ avg_unit_cost │
│ ---           │
│ f64           │
╞═══════════════╡
│ 4330.0        │
└───────────────┘
↳ 1 行取得

shape: (1, 1)

avg_unit_cost
f64
4330.0

結果の読み取り

全ライン平均単価 ≒ 4,330 円 に対し、LINE-A2（クランクシャフト: 8,500円）と LINE-C1（オルタネータ: 6,800円）が該当
この 2 ラインは 1 個あたりの不良損失も大きいため、品質管理の優先度が高い
サブクエリは定数を事前に計算するのではなく、SQL 実行時に動的に評価されるため、データが更新されても常に正確な比較が可能

No.052：WHERE句でサブクエリを使う

実務での意味

「本日の不良数は全ラインの平均より多いか？」という問いは、WHERE 句にサブクエリを使うことで
1 つの SQL で答えられます。定期レポートの自動生成や、アラートシステムの閾値判定に活用できます。

分析・モデル化の考え方

\text{WHERE defect\_qty} > \left(\text{SELECT AVG(defect\_qty) FROM production\_daily}\right)

全体平均 との比較：全ラインを統合した平均不良数を閾値にする
ライン別平均 との比較（→ No.057 相関サブクエリ）：ライン固有の特性を考慮した閾値

Python で確認する

print("=== No.052 WHERE句でサブクエリを使う ===\n")

# ① 全体平均不良数を超えた日のレコードを抽出（上位 10 件）
print("① 全体平均不良数を超えた日（上位10件）")
df52a = q(
    conn,
    """
SELECT prod_date, line_code, production_qty, defect_qty
FROM production_daily
WHERE defect_qty > (
  SELECT AVG(defect_qty) FROM production_daily
)
ORDER BY defect_qty DESC
LIMIT 10
""",
)

# ② LINE-C1 のみ、そのライン固有の平均を超えた日
print("\n② LINE-C1 の平均不良数を超えた日")
df52b = q(
    conn,
    """
SELECT prod_date, line_code, defect_qty
FROM production_daily
WHERE line_code = 'LINE-C1'
  AND defect_qty > (
    SELECT AVG(defect_qty)
    FROM production_daily
    WHERE line_code = 'LINE-C1'
  )
ORDER BY defect_qty DESC
""",
)

=== No.052 WHERE句でサブクエリを使う ===

① 全体平均不良数を超えた日（上位10件）
── SQL ─────────────────────────────────────────
  SELECT prod_date, line_code, production_qty, defect_qty
  FROM production_daily
  WHERE defect_qty > (
    SELECT AVG(defect_qty) FROM production_daily
  )
  ORDER BY defect_qty DESC
  LIMIT 10
───────────────────────────────────────────────
shape: (10, 4)
┌────────────┬───────────┬────────────────┬────────────┐
│ prod_date  ┆ line_code ┆ production_qty ┆ defect_qty │
│ ---        ┆ ---       ┆ ---            ┆ ---        │
│ str        ┆ str       ┆ i64            ┆ i64        │
╞════════════╪═══════════╪════════════════╪════════════╡
│ 2025-03-05 ┆ LINE-A1   ┆ 507            ┆ 15         │
│ 2025-03-17 ┆ LINE-A1   ┆ 481            ┆ 15         │
│ 2025-03-25 ┆ LINE-A1   ┆ 466            ┆ 15         │
│ 2025-01-09 ┆ LINE-A1   ┆ 446            ┆ 14         │
│ 2025-01-15 ┆ LINE-A1   ┆ 468            ┆ 14         │
│ 2025-02-25 ┆ LINE-A1   ┆ 471            ┆ 14         │
│ 2025-03-26 ┆ LINE-A1   ┆ 460            ┆ 14         │
│ 2025-01-24 ┆ LINE-A1   ┆ 465            ┆ 13         │
│ 2025-02-04 ┆ LINE-A1   ┆ 473            ┆ 13         │
│ 2025-02-24 ┆ LINE-A1   ┆ 467            ┆ 13         │
└────────────┴───────────┴────────────────┴────────────┘
↳ 10 行取得

② LINE-C1 の平均不良数を超えた日
── SQL ─────────────────────────────────────────
  SELECT prod_date, line_code, defect_qty
  FROM production_daily
  WHERE line_code = 'LINE-C1'
    AND defect_qty > (
      SELECT AVG(defect_qty)
      FROM production_daily
      WHERE line_code = 'LINE-C1'
    )
  ORDER BY defect_qty DESC
───────────────────────────────────────────────
shape: (29, 3)
┌────────────┬───────────┬────────────┐
│ prod_date  ┆ line_code ┆ defect_qty │
│ ---        ┆ ---       ┆ ---        │
│ str        ┆ str       ┆ i64        │
╞════════════╪═══════════╪════════════╡
│ 2025-02-03 ┆ LINE-C1   ┆ 8          │
│ 2025-01-29 ┆ LINE-C1   ┆ 7          │
│ 2025-03-12 ┆ LINE-C1   ┆ 6          │
│ 2025-01-13 ┆ LINE-C1   ┆ 5          │
│ 2025-01-21 ┆ LINE-C1   ┆ 5          │
│ …          ┆ …         ┆ …          │
│ 2025-02-18 ┆ LINE-C1   ┆ 4          │
│ 2025-02-19 ┆ LINE-C1   ┆ 4          │
│ 2025-03-03 ┆ LINE-C1   ┆ 4          │
│ 2025-03-04 ┆ LINE-C1   ┆ 4          │
│ 2025-03-28 ┆ LINE-C1   ┆ 4          │
└────────────┴───────────┴────────────┘
↳ 29 行取得

結果の読み取り

① 全体平均超え：生産数が多い LINE-A1 が上位を占める傾向がある（不良数の絶対値が大きいため）
② ライン固有平均超え：LINE-C1 の中でも高不良日を特定できる
「全体平均」と「ライン別平均」の 2 つの閾値を使い分けることが実務では重要
→ 全体平均はライン間の比較に、ライン別平均は各ラインの異常検知に適している

No.053：FROM句でサブクエリを使う

実務での意味

「まず集計してから、その集計結果をさらに絞り込む」という 2 段階処理は、
FROM 句にサブクエリ（インラインビュー）を置くことで実現できます。
「不良率を計算したうえで、2% を超えるラインだけ抽出する」などの用途に適しています。

分析・モデル化の考え方

SELECT *
FROM (
  SELECT line_code,
         SUM(defect_qty)*100.0 / SUM(production_qty) AS defect_rate
  FROM production_daily
  GROUP BY line_code       -- ← ここで集計
) AS summary
WHERE defect_rate > 2.0   -- ← 集計後に絞り込み

WHERE 句では SUM() などの集計関数を直接使えないため、FROM サブクエリは
「集計 → フィルタ」という 2 段階処理 を実現する重要なテクニックです。

Python で確認する

print("=== No.053 FROM句でサブクエリを使う ===\n")

# ① FROM サブクエリ：ライン別集計 → 不良率順に表示
print("① FROM サブクエリ：ライン別不良率（集計 → 並び替え）")
df53 = q(
    conn,
    """
SELECT *
FROM (
  SELECT
    line_code,
    SUM(production_qty) AS total_prod,
    SUM(defect_qty)     AS total_defect,
    ROUND(SUM(defect_qty) * 100.0 / SUM(production_qty), 2) AS defect_rate
  FROM production_daily
  GROUP BY line_code
) AS summary
ORDER BY defect_rate DESC
""",
)

# ② 可視化：不良率棒グラフ（目標超えを赤色）
COLORS_53 = ["#e74c3c" if r > 2.0 else "#3498db" for r in df53["defect_rate"].to_list()]
fig, ax = plt.subplots(figsize=(8, 4))
ax.bar(df53["line_code"].to_list(), df53["defect_rate"].to_list(), color=COLORS_53, edgecolor="white", linewidth=0.5)
ax.axhline(2.0, color="orange", linestyle="--", linewidth=1.5, label="目標不良率 2.0%")
ax.set_title("ライン別 不良率（No.053：FROM サブクエリ）", fontsize=13)
ax.set_xlabel("ライン")
ax.set_ylabel("不良率 (%)")
ax.legend(fontsize=10)
ax.grid(axis="y", alpha=0.4)
plt.tight_layout()
plt.show()

=== No.053 FROM句でサブクエリを使う ===

① FROM サブクエリ：ライン別不良率（集計 → 並び替え）
── SQL ─────────────────────────────────────────
  SELECT *
  FROM (
    SELECT
      line_code,
      SUM(production_qty) AS total_prod,
      SUM(defect_qty)     AS total_defect,
      ROUND(SUM(defect_qty) * 100.0 / SUM(production_qty), 2) AS defect_rate
    FROM production_daily
    GROUP BY line_code
  ) AS summary
  ORDER BY defect_rate DESC
───────────────────────────────────────────────
shape: (5, 4)
┌───────────┬────────────┬──────────────┬─────────────┐
│ line_code ┆ total_prod ┆ total_defect ┆ defect_rate │
│ ---       ┆ ---        ┆ ---          ┆ ---         │
│ str       ┆ i64        ┆ i64          ┆ f64         │
╞═══════════╪════════════╪══════════════╪═════════════╡
│ LINE-C1   ┆ 7008       ┆ 219          ┆ 3.13        │
│ LINE-B2   ┆ 10624      ┆ 257          ┆ 2.42        │
│ LINE-A2   ┆ 13341      ┆ 321          ┆ 2.41        │
│ LINE-A1   ┆ 28007      ┆ 576          ┆ 2.06        │
│ LINE-B1   ┆ 23146      ┆ 381          ┆ 1.65        │
└───────────┴────────────┴──────────────┴─────────────┘
↳ 5 行取得

svg

結果の読み取り

LINE-C1（溶接）は目標不良率 2.5% 設定だが、実際の不良率がそれを超えているかを確認できる
LINE-B1（組立ライン1）は目標 1.5% に対して実績が高い場合、工程改善の優先候補
FROM サブクエリは HAVING 句 の代替として使えるが、中間テーブルに名前（エイリアス）を付けられるため、複雑なクエリで可読性が向上する

No.054：SELECT句でサブクエリを使う

実務での意味

SELECT 句にサブクエリを置くと、各行に「全体平均」や「ベンチマーク値」を付加した比較表を
1 つの SQL で作れます。「このラインは全体平均より高いか低いか」が一覧でわかります。

分析・モデル化の考え方

\text{各行の不良率} - \underbrace{\text{全体平均不良率}}_{\text{SELECT句サブクエリで付加}} = \text{乖離量}

SELECT 句サブクエリは 1 行 1 列（スカラ値） を返す必要があります。
全行に同じ定数（全体平均）を付加したいときに有効な手法です。

Python で確認する

print("=== No.054 SELECT句でサブクエリを使う ===\n")

# ① 各ラインの不良率と全体平均を並べて表示
print("① ライン別不良率 vs 全体平均")
df54a = q(
    conn,
    """
SELECT
  line_code,
  ROUND(SUM(defect_qty) * 100.0 / SUM(production_qty), 2) AS line_dr,
  ROUND(
    (SELECT SUM(defect_qty) * 100.0 / SUM(production_qty)
     FROM production_daily),
  2) AS total_avg_dr
FROM production_daily
GROUP BY line_code
ORDER BY line_dr DESC
""",
)

# ② 全体平均との差分（乖離量）も算出
print("\n② 全体平均との差分（正 = 平均超え）")
df54b = q(
    conn,
    """
SELECT
  line_code,
  ROUND(SUM(defect_qty) * 100.0 / SUM(production_qty), 2) AS line_dr,
  ROUND(
    (SELECT SUM(defect_qty) * 100.0 / SUM(production_qty)
     FROM production_daily),
  2) AS total_avg_dr,
  ROUND(
    SUM(defect_qty) * 100.0 / SUM(production_qty)
    - (SELECT SUM(defect_qty) * 100.0 / SUM(production_qty)
       FROM production_daily),
  2) AS diff_from_avg
FROM production_daily
GROUP BY line_code
ORDER BY diff_from_avg DESC
""",
)

=== No.054 SELECT句でサブクエリを使う ===

① ライン別不良率 vs 全体平均
── SQL ─────────────────────────────────────────
  SELECT
    line_code,
    ROUND(SUM(defect_qty) * 100.0 / SUM(production_qty), 2) AS line_dr,
    ROUND(
      (SELECT SUM(defect_qty) * 100.0 / SUM(production_qty)
       FROM production_daily),
    2) AS total_avg_dr
  FROM production_daily
  GROUP BY line_code
  ORDER BY line_dr DESC
───────────────────────────────────────────────
shape: (5, 3)
┌───────────┬─────────┬──────────────┐
│ line_code ┆ line_dr ┆ total_avg_dr │
│ ---       ┆ ---     ┆ ---          │
│ str       ┆ f64     ┆ f64          │
╞═══════════╪═════════╪══════════════╡
│ LINE-C1   ┆ 3.13    ┆ 2.14         │
│ LINE-B2   ┆ 2.42    ┆ 2.14         │
│ LINE-A2   ┆ 2.41    ┆ 2.14         │
│ LINE-A1   ┆ 2.06    ┆ 2.14         │
│ LINE-B1   ┆ 1.65    ┆ 2.14         │
└───────────┴─────────┴──────────────┘
↳ 5 行取得

② 全体平均との差分（正 = 平均超え）
── SQL ─────────────────────────────────────────
  SELECT
    line_code,
    ROUND(SUM(defect_qty) * 100.0 / SUM(production_qty), 2) AS line_dr,
    ROUND(
      (SELECT SUM(defect_qty) * 100.0 / SUM(production_qty)
       FROM production_daily),
    2) AS total_avg_dr,
    ROUND(
      SUM(defect_qty) * 100.0 / SUM(production_qty)
      - (SELECT SUM(defect_qty) * 100.0 / SUM(production_qty)
         FROM production_daily),
    2) AS diff_from_avg
  FROM production_daily
  GROUP BY line_code
  ORDER BY diff_from_avg DESC
───────────────────────────────────────────────
shape: (5, 4)
┌───────────┬─────────┬──────────────┬───────────────┐
│ line_code ┆ line_dr ┆ total_avg_dr ┆ diff_from_avg │
│ ---       ┆ ---     ┆ ---          ┆ ---           │
│ str       ┆ f64     ┆ f64          ┆ f64           │
╞═══════════╪═════════╪══════════════╪═══════════════╡
│ LINE-C1   ┆ 3.13    ┆ 2.14         ┆ 0.99          │
│ LINE-B2   ┆ 2.42    ┆ 2.14         ┆ 0.28          │
│ LINE-A2   ┆ 2.41    ┆ 2.14         ┆ 0.27          │
│ LINE-A1   ┆ 2.06    ┆ 2.14         ┆ -0.08         │
│ LINE-B1   ┆ 1.65    ┆ 2.14         ┆ -0.49         │
└───────────┴─────────┴──────────────┴───────────────┘
↳ 5 行取得

結果の読み取り

diff_from_avg が正のラインは全体平均より不良率が高く、品質改善の優先対象
SELECT 句サブクエリは全行に同じスカラ値を付加するため、ベンチマーク比較表 として活用できる
ROUND() を使って小数点以下 2 桁に揃えることで、報告資料としてもそのまま使用可能

No.055：INを使ったサブクエリを作る

実務での意味

IN (サブクエリ) を使うと、「メンテナンス記録があるラインの生産データだけ取得する」のように、
別テーブルの条件に一致するレコードを動的に絞り込む ことができます。

分析・モデル化の考え方

WHERE line_code IN (
  SELECT DISTINCT line_code FROM maintenance_log
)

これは JOIN で同じ結果を得ることもできますが、IN + サブクエリは：

条件の意図が直感的に読める
サブクエリが返す値リストを動的に生成できる

という特徴があります。反対に NOT IN を使うと「メンテナンス未実施ライン」の抽出が可能です。

注意: NOT IN は比較するリストに NULL が含まれると全件が除外されるため、
NULL が含まれうる列には NOT EXISTS を使うのが安全です（→ No.056）。

Python で確認する

print("=== No.055 INを使ったサブクエリを作る ===\n")

# ① メンテナンス記録があるラインの生産サマリー
print("① メンテナンス実施ラインの生産サマリー（IN）")
df55a = q(
    conn,
    """
SELECT
  line_code,
  COUNT(*)            AS work_days,
  SUM(production_qty) AS total_prod,
  SUM(defect_qty)     AS total_defect
FROM production_daily
WHERE line_code IN (
  SELECT DISTINCT line_code FROM maintenance_log
)
GROUP BY line_code
ORDER BY total_prod DESC
""",
)

# ② メンテナンス未実施ラインの確認（NOT IN）
print("\n② メンテナンス未実施ラインの確認（NOT IN）")
df55b = q(
    conn,
    """
SELECT line_code, line_name
FROM line_master
WHERE line_code NOT IN (
  SELECT DISTINCT line_code FROM maintenance_log
)
""",
)
if len(df55b) == 0:
    print("→ 全ラインにメンテナンス記録あり（0件）")

=== No.055 INを使ったサブクエリを作る ===

① メンテナンス実施ラインの生産サマリー（IN）
── SQL ─────────────────────────────────────────
  SELECT
    line_code,
    COUNT(*)            AS work_days,
    SUM(production_qty) AS total_prod,
    SUM(defect_qty)     AS total_defect
  FROM production_daily
  WHERE line_code IN (
    SELECT DISTINCT line_code FROM maintenance_log
  )
  GROUP BY line_code
  ORDER BY total_prod DESC
───────────────────────────────────────────────
shape: (5, 4)
┌───────────┬───────────┬────────────┬──────────────┐
│ line_code ┆ work_days ┆ total_prod ┆ total_defect │
│ ---       ┆ ---       ┆ ---        ┆ ---          │
│ str       ┆ i64       ┆ i64        ┆ i64          │
╞═══════════╪═══════════╪════════════╪══════════════╡
│ LINE-A1   ┆ 61        ┆ 28007      ┆ 576          │
│ LINE-B1   ┆ 61        ┆ 23146      ┆ 381          │
│ LINE-A2   ┆ 61        ┆ 13341      ┆ 321          │
│ LINE-B2   ┆ 61        ┆ 10624      ┆ 257          │
│ LINE-C1   ┆ 61        ┆ 7008       ┆ 219          │
└───────────┴───────────┴────────────┴──────────────┘
↳ 5 行取得

② メンテナンス未実施ラインの確認（NOT IN）
── SQL ─────────────────────────────────────────
  SELECT line_code, line_name
  FROM line_master
  WHERE line_code NOT IN (
    SELECT DISTINCT line_code FROM maintenance_log
  )
───────────────────────────────────────────────
shape: (0, 2)
┌───────────┬───────────┐
│ line_code ┆ line_name │
│ ---       ┆ ---       │
│ null      ┆ null      │
╞═══════════╪═══════════╡
└───────────┴───────────┘
↳ 0 行取得
→ 全ラインにメンテナンス記録あり（0件）

結果の読み取り

今回は全 5 ラインにメンテナンス記録があるため、① は全ラインの生産サマリーとなる
実際の運用では「メンテナンス未実施のラインを NOT IN で抽出して点検推奨リストを作る」用途が多い
IN (サブクエリ) は 動的なリスト を生成するため、マスタの追加・削除に自動追従する

No.056：EXISTSを使ったサブクエリを作る

実務での意味

EXISTS は「相手テーブルに対応するレコードが存在するかどうか」を確認します。
「メンテナンスを実施した日の生産レコードを抽出する」という問いを、
production_daily と maintenance_log の日付・ラインコードを突合して答えられます。

分析・モデル化の考え方

WHERE EXISTS (
  SELECT 1
  FROM maintenance_log m
  WHERE m.line_code = p.line_code    -- 外側のラインと一致
    AND m.maint_date = p.prod_date   -- 同じ日付
)

EXISTS は 行の存在チェック のみ行い、値の取得は不要なため SELECT 1 で十分です。
IN との違い：

比較	IN	EXISTS
NULL 安全性	△（NOT IN は危険）	○（NULL を持ち込まない）
外側テーブルとの結合条件	単一列	複数列（複合条件）
適した用途	値リストとの照合	関連レコードの存在確認

Python で確認する

print("=== No.056 EXISTSを使ったサブクエリを作る ===\n")

# ① メンテナンス実施日と生産実績の突合
print("① メンテナンス実施日の生産実績（EXISTS）")
df56a = q(
    conn,
    """
SELECT p.prod_date, p.line_code, p.production_qty, p.defect_qty
FROM production_daily p
WHERE EXISTS (
  SELECT 1
  FROM maintenance_log m
  WHERE m.line_code = p.line_code
    AND m.maint_date = p.prod_date
)
ORDER BY p.prod_date, p.line_code
LIMIT 15
""",
)

# ② ライン別：メンテナンス実施日数の集計
print("\n② ライン別 メンテナンス実施日数（EXISTS + GROUP BY）")
df56b = q(
    conn,
    """
SELECT
  p.line_code,
  COUNT(*) AS maint_days,
  ROUND(AVG(p.defect_qty * 100.0 / p.production_qty), 2) AS avg_dr_on_maint_day
FROM production_daily p
WHERE EXISTS (
  SELECT 1
  FROM maintenance_log m
  WHERE m.line_code = p.line_code
    AND m.maint_date = p.prod_date
)
GROUP BY p.line_code
ORDER BY maint_days DESC
""",
)

=== No.056 EXISTSを使ったサブクエリを作る ===

① メンテナンス実施日の生産実績（EXISTS）
── SQL ─────────────────────────────────────────
  SELECT p.prod_date, p.line_code, p.production_qty, p.defect_qty
  FROM production_daily p
  WHERE EXISTS (
    SELECT 1
    FROM maintenance_log m
    WHERE m.line_code = p.line_code
      AND m.maint_date = p.prod_date
  )
  ORDER BY p.prod_date, p.line_code
  LIMIT 15
───────────────────────────────────────────────
shape: (15, 4)
┌────────────┬───────────┬────────────────┬────────────┐
│ prod_date  ┆ line_code ┆ production_qty ┆ defect_qty │
│ ---        ┆ ---       ┆ ---            ┆ ---        │
│ str        ┆ str       ┆ i64            ┆ i64        │
╞════════════╪═══════════╪════════════════╪════════════╡
│ 2025-01-06 ┆ LINE-A1   ┆ 471            ┆ 9          │
│ 2025-01-13 ┆ LINE-A1   ┆ 467            ┆ 8          │
│ 2025-01-17 ┆ LINE-C1   ┆ 115            ┆ 3          │
│ 2025-01-21 ┆ LINE-B2   ┆ 174            ┆ 3          │
│ 2025-01-23 ┆ LINE-A1   ┆ 424            ┆ 9          │
│ …          ┆ …         ┆ …              ┆ …          │
│ 2025-02-18 ┆ LINE-C1   ┆ 116            ┆ 4          │
│ 2025-02-19 ┆ LINE-A2   ┆ 210            ┆ 8          │
│ 2025-02-21 ┆ LINE-A2   ┆ 222            ┆ 4          │
│ 2025-02-21 ┆ LINE-B1   ┆ 371            ┆ 7          │
│ 2025-02-24 ┆ LINE-B2   ┆ 174            ┆ 4          │
└────────────┴───────────┴────────────────┴────────────┘
↳ 15 行取得

② ライン別 メンテナンス実施日数（EXISTS + GROUP BY）
── SQL ─────────────────────────────────────────
  SELECT
    p.line_code,
    COUNT(*) AS maint_days,
    ROUND(AVG(p.defect_qty * 100.0 / p.production_qty), 2) AS avg_dr_on_maint_day
  FROM production_daily p
  WHERE EXISTS (
    SELECT 1
    FROM maintenance_log m
    WHERE m.line_code = p.line_code
      AND m.maint_date = p.prod_date
  )
  GROUP BY p.line_code
  ORDER BY maint_days DESC
───────────────────────────────────────────────
shape: (5, 3)
┌───────────┬────────────┬─────────────────────┐
│ line_code ┆ maint_days ┆ avg_dr_on_maint_day │
│ ---       ┆ ---        ┆ ---                 │
│ str       ┆ i64        ┆ f64                 │
╞═══════════╪════════════╪═════════════════════╡
│ LINE-C1   ┆ 8          ┆ 3.08                │
│ LINE-B2   ┆ 5          ┆ 2.1                 │
│ LINE-A2   ┆ 5          ┆ 2.74                │
│ LINE-A1   ┆ 5          ┆ 2.07                │
│ LINE-B1   ┆ 4          ┆ 1.79                │
└───────────┴────────────┴─────────────────────┘
↳ 5 行取得

結果の読み取り

メンテナンス実施日は通常、ダウンタイムが発生し生産数が減少する傾向があるが、
不良率への影響（改善 or 悪化）は設備状態によって異なる
avg_dr_on_maint_day（メンテ実施日の平均不良率）をメンテ非実施日と比較することで、
「メンテナンスの効果測定」に活用できる
LINE-C1 がメンテナンス日数最多の場合、溶接設備の老朽化や調整頻度の高さを示唆する

No.057：相関サブクエリを理解する

実務での意味

相関サブクエリ は、外側クエリの各行を処理するたびに内側クエリが再実行される構造です。
「各ラインの 自ラインの平均 を超えた日を特定する」という問いに答えられます。

これは「全体平均」との比較（No.052）では実現できない ライン別の異常検知 を可能にします。

分析・モデル化の考え方

WHERE p.defect_qty > (
  SELECT AVG(defect_qty)
  FROM production_daily p2
  WHERE p2.line_code = p.line_code   -- ← 外側のラインに紐づいて評価
)

実行の仕組み：

外側クエリが LINE-A1 の 1 行目を処理するとき、内側は LINE-A1 の平均不良数を計算
LINE-A2 の行では、内側が LINE-A2 の平均を計算

このように 外側の値に連動して内側が変化するのが相関サブクエリの特徴です。

注意：大規模テーブルでは行ごとにサブクエリが実行されるため、パフォーマンスに注意が必要です。
実務では ウィンドウ関数（第7章）への書き換えも検討してください。

Python で確認する

print("=== No.057 相関サブクエリを理解する ===\n")

# ① 各ライン平均不良数を超えた日（相関サブクエリ）
print("① ライン別平均不良数を超えた日（先頭 15 件）")
df57a = q(
    conn,
    """
SELECT
  p.line_code,
  p.prod_date,
  p.defect_qty,
  ROUND(
    (SELECT AVG(defect_qty) FROM production_daily p2
     WHERE p2.line_code = p.line_code),
  1) AS line_avg_defect
FROM production_daily p
WHERE p.defect_qty > (
  SELECT AVG(defect_qty)
  FROM production_daily p2
  WHERE p2.line_code = p.line_code
)
ORDER BY p.line_code, p.defect_qty DESC
LIMIT 15
""",
)

# ② ライン別：平均超え日数の集計
print("\n② ライン別 平均不良数超え日数")
df57b = q(
    conn,
    """
SELECT
  p.line_code,
  COUNT(*) AS above_avg_days
FROM production_daily p
WHERE p.defect_qty > (
  SELECT AVG(defect_qty)
  FROM production_daily p2
  WHERE p2.line_code = p.line_code
)
GROUP BY p.line_code
ORDER BY above_avg_days DESC
""",
)

# 可視化
fig, ax = plt.subplots(figsize=(8, 4))
ax.barh(df57b["line_code"].to_list(), df57b["above_avg_days"].to_list(), color="#e67e22", edgecolor="white")
ax.set_title("ライン別 自ライン平均不良数を超えた日数（No.057：相関サブクエリ）", fontsize=12)
ax.set_xlabel("日数")
ax.set_ylabel("ライン")
ax.grid(axis="x", alpha=0.4)
plt.tight_layout()
plt.show()

=== No.057 相関サブクエリを理解する ===

① ライン別平均不良数を超えた日（先頭 15 件）
── SQL ─────────────────────────────────────────
  SELECT
    p.line_code,
    p.prod_date,
    p.defect_qty,
    ROUND(
      (SELECT AVG(defect_qty) FROM production_daily p2
       WHERE p2.line_code = p.line_code),
    1) AS line_avg_defect
  FROM production_daily p
  WHERE p.defect_qty > (
    SELECT AVG(defect_qty)
    FROM production_daily p2
    WHERE p2.line_code = p.line_code
  )
  ORDER BY p.line_code, p.defect_qty DESC
  LIMIT 15
───────────────────────────────────────────────
shape: (15, 4)
┌───────────┬────────────┬────────────┬─────────────────┐
│ line_code ┆ prod_date  ┆ defect_qty ┆ line_avg_defect │
│ ---       ┆ ---        ┆ ---        ┆ ---             │
│ str       ┆ str        ┆ i64        ┆ f64             │
╞═══════════╪════════════╪════════════╪═════════════════╡
│ LINE-A1   ┆ 2025-03-05 ┆ 15         ┆ 9.4             │
│ LINE-A1   ┆ 2025-03-17 ┆ 15         ┆ 9.4             │
│ LINE-A1   ┆ 2025-03-25 ┆ 15         ┆ 9.4             │
│ LINE-A1   ┆ 2025-01-09 ┆ 14         ┆ 9.4             │
│ LINE-A1   ┆ 2025-01-15 ┆ 14         ┆ 9.4             │
│ …         ┆ …          ┆ …          ┆ …               │
│ LINE-A1   ┆ 2025-03-14 ┆ 13         ┆ 9.4             │
│ LINE-A1   ┆ 2025-01-17 ┆ 12         ┆ 9.4             │
│ LINE-A1   ┆ 2025-02-18 ┆ 12         ┆ 9.4             │
│ LINE-A1   ┆ 2025-03-10 ┆ 12         ┆ 9.4             │
│ LINE-A1   ┆ 2025-01-28 ┆ 11         ┆ 9.4             │
└───────────┴────────────┴────────────┴─────────────────┘
↳ 15 行取得

② ライン別 平均不良数超え日数
── SQL ─────────────────────────────────────────
  SELECT
    p.line_code,
    COUNT(*) AS above_avg_days
  FROM production_daily p
  WHERE p.defect_qty > (
    SELECT AVG(defect_qty)
    FROM production_daily p2
    WHERE p2.line_code = p.line_code
  )
  GROUP BY p.line_code
  ORDER BY above_avg_days DESC
───────────────────────────────────────────────
shape: (5, 2)
┌───────────┬────────────────┐
│ line_code ┆ above_avg_days │
│ ---       ┆ ---            │
│ str       ┆ i64            │
╞═══════════╪════════════════╡
│ LINE-C1   ┆ 29             │
│ LINE-B1   ┆ 27             │
│ LINE-A2   ┆ 27             │
│ LINE-A1   ┆ 27             │
│ LINE-B2   ┆ 23             │
└───────────┴────────────────┘
↳ 5 行取得

svg

結果の読み取り

相関サブクエリにより、各ラインの「自ライン固有の平均」 を閾値として異常日を特定できる
どのラインも稼働日数の約半数が「自ライン平均超え」となるのは統計的に自然（中央値付近で分布）
実務では「連続 3 日以上平均超え」など追加条件を付けることで、より精度の高いアラートを設計できる

No.058：WITH句でCTEを作る

実務での意味

CTE（Common Table Expression） は WITH 句 で定義した名前付き一時テーブルです。
「月次不良率を一度計算してから、その中でさらに比較する」という処理を、
サブクエリを何重にもネストせず、上から順に読める構造 で記述できます。

分析・モデル化の考え方

WITH monthly_dr AS (          -- ← CTE 定義
  SELECT strftime('%Y-%m', prod_date) AS ym,
         line_code,
         SUM(defect_qty)*100.0/SUM(production_qty) AS defect_rate
  FROM production_daily
  GROUP BY ym, line_code
)
SELECT * FROM monthly_dr      -- ← CTE を通常のテーブルとして参照
ORDER BY ym, line_code

CTE は実行時に評価され、クエリ内で複数回参照できる（DBMSによる）という利点もあります。
サブクエリを使った場合と結果は同じですが、可読性と保守性が大きく向上します。

Python で確認する

print("=== No.058 WITH句でCTEを作る ===\n")

# ① CTE で月次不良率テーブルを定義して取得
print("① CTE：月次不良率テーブル（monthly_dr）")
df58a = q(
    conn,
    """
WITH monthly_dr AS (
  SELECT
    strftime('%Y-%m', prod_date) AS ym,
    line_code,
    SUM(production_qty)  AS total_prod,
    SUM(defect_qty)      AS total_defect,
    ROUND(SUM(defect_qty) * 100.0 / SUM(production_qty), 2) AS defect_rate
  FROM production_daily
  GROUP BY ym, line_code
)
SELECT *
FROM monthly_dr
ORDER BY ym, line_code
""",
)

# ② CTE を参照して、月ごとの不良率最大ラインを抽出
print("\n② 月ごとに不良率が最大のライン（CTE 参照 + スカラサブクエリ）")
df58b = q(
    conn,
    """
WITH monthly_dr AS (
  SELECT
    strftime('%Y-%m', prod_date) AS ym,
    line_code,
    ROUND(SUM(defect_qty) * 100.0 / SUM(production_qty), 2) AS defect_rate
  FROM production_daily
  GROUP BY ym, line_code
)
SELECT ym, line_code, defect_rate
FROM monthly_dr
WHERE defect_rate = (
  SELECT MAX(defect_rate) FROM monthly_dr AS sub
  WHERE sub.ym = monthly_dr.ym
)
ORDER BY ym
""",
)

=== No.058 WITH句でCTEを作る ===

① CTE：月次不良率テーブル（monthly_dr）
── SQL ─────────────────────────────────────────
  WITH monthly_dr AS (
    SELECT
      strftime('%Y-%m', prod_date) AS ym,
      line_code,
      SUM(production_qty)  AS total_prod,
      SUM(defect_qty)      AS total_defect,
      ROUND(SUM(defect_qty) * 100.0 / SUM(production_qty), 2) AS defect_rate
    FROM production_daily
    GROUP BY ym, line_code
  )
  SELECT *
  FROM monthly_dr
  ORDER BY ym, line_code
───────────────────────────────────────────────
shape: (15, 5)
┌─────────┬───────────┬────────────┬──────────────┬─────────────┐
│ ym      ┆ line_code ┆ total_prod ┆ total_defect ┆ defect_rate │
│ ---     ┆ ---       ┆ ---        ┆ ---          ┆ ---         │
│ str     ┆ str       ┆ i64        ┆ i64          ┆ f64         │
╞═════════╪═══════════╪════════════╪══════════════╪═════════════╡
│ 2025-01 ┆ LINE-A1   ┆ 9107       ┆ 192          ┆ 2.11        │
│ 2025-01 ┆ LINE-A2   ┆ 4366       ┆ 95           ┆ 2.18        │
│ 2025-01 ┆ LINE-B1   ┆ 7512       ┆ 138          ┆ 1.84        │
│ 2025-01 ┆ LINE-B2   ┆ 3497       ┆ 91           ┆ 2.6         │
│ 2025-01 ┆ LINE-C1   ┆ 2277       ┆ 67           ┆ 2.94        │
│ …       ┆ …         ┆ …          ┆ …            ┆ …           │
│ 2025-03 ┆ LINE-A1   ┆ 9751       ┆ 191          ┆ 1.96        │
│ 2025-03 ┆ LINE-A2   ┆ 4541       ┆ 117          ┆ 2.58        │
│ 2025-03 ┆ LINE-B1   ┆ 7995       ┆ 127          ┆ 1.59        │
│ 2025-03 ┆ LINE-B2   ┆ 3623       ┆ 80           ┆ 2.21        │
│ 2025-03 ┆ LINE-C1   ┆ 2406       ┆ 73           ┆ 3.03        │
└─────────┴───────────┴────────────┴──────────────┴─────────────┘
↳ 15 行取得

② 月ごとに不良率が最大のライン（CTE 参照 + スカラサブクエリ）
── SQL ─────────────────────────────────────────
  WITH monthly_dr AS (
    SELECT
      strftime('%Y-%m', prod_date) AS ym,
      line_code,
      ROUND(SUM(defect_qty) * 100.0 / SUM(production_qty), 2) AS defect_rate
    FROM production_daily
    GROUP BY ym, line_code
  )
  SELECT ym, line_code, defect_rate
  FROM monthly_dr
  WHERE defect_rate = (
    SELECT MAX(defect_rate) FROM monthly_dr AS sub
    WHERE sub.ym = monthly_dr.ym
  )
  ORDER BY ym
───────────────────────────────────────────────
shape: (3, 3)
┌─────────┬───────────┬─────────────┐
│ ym      ┆ line_code ┆ defect_rate │
│ ---     ┆ ---       ┆ ---         │
│ str     ┆ str       ┆ f64         │
╞═════════╪═══════════╪═════════════╡
│ 2025-01 ┆ LINE-C1   ┆ 2.94        │
│ 2025-02 ┆ LINE-C1   ┆ 3.4         │
│ 2025-03 ┆ LINE-C1   ┆ 3.03        │
└─────────┴───────────┴─────────────┘
↳ 3 行取得

結果の読み取り

CTE monthly_dr を定義することで、月次集計ロジックを 一か所に集約 できる
② では CTE を複数回参照（外側 SELECT と内側スカラサブクエリ）しており、同一ロジックの重複記述を排除している
毎月の不良率ワーストライン情報は、翌月の改善優先度設定に直結する重要な KPI

No.059：CTEを使って複雑な集計を整理する

実務での意味

月次 KPI レポートでは「月別・ライン別の生産数、不良率、損失コスト」を一度に集計して
管理職に提出することが求められます。複数 CTE を積み重ねることで、
Excel の複数シートに相当する処理 を 1 つの SQL で完結できます。

分析・モデル化の考え方

[CTE 1: monthly]   日別 → 月別集計
        ↓
[CTE 2: with_loss]  月別集計 × line_master → 損失コスト換算
        ↓
[最終 SELECT]       報告書向け出力

各 CTE は前段の CTE を参照して加工できるため、
データパイプライン を SQL 内に表現することが可能です。

Python で確認する

print("=== No.059 CTEを使って複雑な集計を整理する ===\n")

# ① 複数CTE：月次 KPI ダッシュボード
print("① 複数CTE：月次損失コスト一覧")
df59 = q(
    conn,
    """
WITH
monthly AS (
  SELECT
    strftime('%Y-%m', prod_date) AS ym,
    line_code,
    SUM(production_qty)  AS total_prod,
    SUM(defect_qty)      AS total_defect
  FROM production_daily
  GROUP BY ym, line_code
),
with_loss AS (
  SELECT
    m.ym,
    m.line_code,
    m.total_prod,
    m.total_defect,
    ROUND(m.total_defect * 100.0 / m.total_prod, 2) AS defect_rate,
    m.total_defect * l.unit_cost AS loss_cost
  FROM monthly m
  JOIN line_master l ON m.line_code = l.line_code
)
SELECT *
FROM with_loss
ORDER BY ym, loss_cost DESC
""",
)

# 可視化：月次損失コスト（ライン別積み上げ棒グラフ）
LINES_ORDER = ["LINE-A1", "LINE-A2", "LINE-B1", "LINE-B2", "LINE-C1"]
COLORS_59 = ["#3498db", "#e74c3c", "#2ecc71", "#f39c12", "#9b59b6"]
YMS = sorted(df59["ym"].unique().to_list())

fig, ax = plt.subplots(figsize=(9, 5))
bottoms = [0] * len(YMS)
for i, lc in enumerate(LINES_ORDER):
    sub = df59.filter(pl.col("line_code") == lc).sort("ym")
    ym_set = sub["ym"].to_list()
    costs = [int(sub.filter(pl.col("ym") == ym)["loss_cost"][0]) if ym in ym_set else 0 for ym in YMS]
    ax.bar(YMS, costs, bottom=bottoms, color=COLORS_59[i], label=lc, edgecolor="white", linewidth=0.5)
    bottoms = [b + c for b, c in zip(bottoms, costs)]

ax.set_title("月次 不良損失コスト（No.059：複数CTE）", fontsize=13)
ax.set_xlabel("年月")
ax.set_ylabel("損失コスト（円）")
ax.legend(loc="upper right", fontsize=9)
ax.grid(axis="y", alpha=0.4)
plt.tight_layout()
plt.show()

=== No.059 CTEを使って複雑な集計を整理する ===

① 複数CTE：月次損失コスト一覧
── SQL ─────────────────────────────────────────
  WITH
  monthly AS (
    SELECT
      strftime('%Y-%m', prod_date) AS ym,
      line_code,
      SUM(production_qty)  AS total_prod,
      SUM(defect_qty)      AS total_defect
    FROM production_daily
    GROUP BY ym, line_code
  ),
  with_loss AS (
    SELECT
      m.ym,
      m.line_code,
      m.total_prod,
      m.total_defect,
      ROUND(m.total_defect * 100.0 / m.total_prod, 2) AS defect_rate,
      m.total_defect * l.unit_cost AS loss_cost
    FROM monthly m
    JOIN line_master l ON m.line_code = l.line_code
  )
  SELECT *
  FROM with_loss
  ORDER BY ym, loss_cost DESC
───────────────────────────────────────────────
shape: (15, 6)
┌─────────┬───────────┬────────────┬──────────────┬─────────────┬───────────┐
│ ym      ┆ line_code ┆ total_prod ┆ total_defect ┆ defect_rate ┆ loss_cost │
│ ---     ┆ ---       ┆ ---        ┆ ---          ┆ ---         ┆ ---       │
│ str     ┆ str       ┆ i64        ┆ i64          ┆ f64         ┆ i64       │
╞═════════╪═══════════╪════════════╪══════════════╪═════════════╪═══════════╡
│ 2025-01 ┆ LINE-A2   ┆ 4366       ┆ 95           ┆ 2.18        ┆ 807500    │
│ 2025-01 ┆ LINE-C1   ┆ 2277       ┆ 67           ┆ 2.94        ┆ 455600    │
│ 2025-01 ┆ LINE-B2   ┆ 3497       ┆ 91           ┆ 2.6         ┆ 382200    │
│ 2025-01 ┆ LINE-A1   ┆ 9107       ┆ 192          ┆ 2.11        ┆ 230400    │
│ 2025-01 ┆ LINE-B1   ┆ 7512       ┆ 138          ┆ 1.84        ┆ 131100    │
│ …       ┆ …         ┆ …          ┆ …            ┆ …           ┆ …         │
│ 2025-03 ┆ LINE-A2   ┆ 4541       ┆ 117          ┆ 2.58        ┆ 994500    │
│ 2025-03 ┆ LINE-C1   ┆ 2406       ┆ 73           ┆ 3.03        ┆ 496400    │
│ 2025-03 ┆ LINE-B2   ┆ 3623       ┆ 80           ┆ 2.21        ┆ 336000    │
│ 2025-03 ┆ LINE-A1   ┆ 9751       ┆ 191          ┆ 1.96        ┆ 229200    │
│ 2025-03 ┆ LINE-B1   ┆ 7995       ┆ 127          ┆ 1.59        ┆ 120650    │
└─────────┴───────────┴────────────┴──────────────┴─────────────┴───────────┘
↳ 15 行取得

svg

結果の読み取り

積み上げ棒グラフで 月次損失コストの構成 が一目でわかる
LINE-A2（クランクシャフト）は unit_cost が 8,500 円と高いため、不良数が少なくても損失コストへの影響が大きい
複数 CTE を使うことで、途中ステップ（monthly）を独立して検証・デバッグしやすくなる
→ 品質管理ダッシュボードのクエリ保守工数削減に直結

No.060：一時的な分析テーブルを作る

実務での意味

月次の品質管理レポートでは「全ラインの生産実績・不良率・損失コスト・メンテナンス実績・ステータス判定」を
1 枚の表にまとめて提出することが求められます。
CTE を多段に積み上げることで、こうした 総合ダッシュボードテーブル を SQL 単体で生成できます。

分析・モデル化の考え方

[CTE 1: prod_summary]    ライン別 生産集計（稼働日数・総生産・不良率）
        ↓
[CTE 2: maint_summary]   ライン別 メンテナンス集計（件数・ダウンタイム）
        ↓
[CTE 3: dashboard]       JOIN + ステータス判定（CASE式）
        ↓
[最終 SELECT]            報告書出力

ステータス判定ロジック：

\text{status} = \begin{cases} \text{要改善} & \text{if } \text{defect\_rate} > \text{target\_dr} \times 1.2 \\ \text{要注意} & \text{if } \text{defect\_rate} > \text{target\_dr} \\ \text{正常} & \text{otherwise} \end{cases}

Python で確認する

print("=== No.060 一時的な分析テーブルを作る ===\n")

# ① CTE 多段：総合分析ダッシュボード
print("① CTEによる総合分析ダッシュボード")
df60 = q(
    conn,
    """
WITH
prod_summary AS (
  SELECT
    line_code,
    COUNT(DISTINCT prod_date) AS work_days,
    SUM(production_qty)       AS total_prod,
    SUM(defect_qty)           AS total_defect,
    ROUND(SUM(defect_qty) * 100.0 / SUM(production_qty), 2) AS defect_rate
  FROM production_daily
  GROUP BY line_code
),
maint_summary AS (
  SELECT
    line_code,
    COUNT(*)                      AS maint_count,
    ROUND(SUM(downtime_hours), 1) AS total_downtime
  FROM maintenance_log
  GROUP BY line_code
),
dashboard AS (
  SELECT
    p.line_code,
    l.line_name,
    l.section,
    p.work_days,
    p.total_prod,
    p.defect_rate,
    ROUND(l.target_dr * 100, 1)       AS target_dr_pct,
    COALESCE(m.maint_count,    0)     AS maint_count,
    COALESCE(m.total_downtime, 0)     AS total_downtime,
    p.total_defect * l.unit_cost      AS total_loss,
    CASE
      WHEN p.defect_rate > l.target_dr * 100 * 1.2 THEN '要改善'
      WHEN p.defect_rate > l.target_dr * 100       THEN '要注意'
      ELSE '正常'
    END AS status
  FROM prod_summary p
  JOIN line_master l ON p.line_code = l.line_code
  LEFT JOIN maint_summary m ON p.line_code = m.line_code
)
SELECT * FROM dashboard ORDER BY defect_rate DESC
""",
)

# 可視化：実績 vs 目標不良率（グループ棒グラフ）
LINES_60 = df60["line_code"].to_list()
ACTUAL_DR = df60["defect_rate"].to_list()
TARGET_DR = df60["target_dr_pct"].to_list()
STATUSES = df60["status"].to_list()
STATUS_COL = {"正常": "#2ecc71", "要注意": "#f39c12", "要改善": "#e74c3c"}
BAR_COLORS = [STATUS_COL[s] for s in STATUSES]

x = np.arange(len(LINES_60))
width = 0.35
fig, ax = plt.subplots(figsize=(9, 5))
ax.bar(x - width / 2, ACTUAL_DR, width, color=BAR_COLORS, label="実績不良率", edgecolor="white")
ax.bar(x + width / 2, TARGET_DR, width, color="#95a5a6", label="目標不良率", edgecolor="white")
ax.set_title("ライン別 実績不良率 vs 目標不良率（No.060：CTE 総合ダッシュボード）", fontsize=12)
ax.set_xlabel("ライン")
ax.set_ylabel("不良率 (%)")
ax.set_xticks(x)
ax.set_xticklabels(LINES_60)
ax.legend(fontsize=10)
ax.grid(axis="y", alpha=0.4)
plt.tight_layout()
plt.show()

=== No.060 一時的な分析テーブルを作る ===

① CTEによる総合分析ダッシュボード
── SQL ─────────────────────────────────────────
  WITH
  prod_summary AS (
    SELECT
      line_code,
      COUNT(DISTINCT prod_date) AS work_days,
      SUM(production_qty)       AS total_prod,
      SUM(defect_qty)           AS total_defect,
      ROUND(SUM(defect_qty) * 100.0 / SUM(production_qty), 2) AS defect_rate
    FROM production_daily
    GROUP BY line_code
  ),
  maint_summary AS (
    SELECT
      line_code,
      COUNT(*)                      AS maint_count,
      ROUND(SUM(downtime_hours), 1) AS total_downtime
    FROM maintenance_log
    GROUP BY line_code
  ),
  dashboard AS (
    SELECT
      p.line_code,
      l.line_name,
      l.section,
      p.work_days,
      p.total_prod,
      p.defect_rate,
      ROUND(l.target_dr * 100, 1)       AS target_dr_pct,
      COALESCE(m.maint_count,    0)     AS maint_count,
      COALESCE(m.total_downtime, 0)     AS total_downtime,
      p.total_defect * l.unit_cost      AS total_loss,
      CASE
        WHEN p.defect_rate > l.target_dr * 100 * 1.2 THEN '要改善'
        WHEN p.defect_rate > l.target_dr * 100       THEN '要注意'
        ELSE '正常'
      END AS status
    FROM prod_summary p
    JOIN line_master l ON p.line_code = l.line_code
    LEFT JOIN maint_summary m ON p.line_code = m.line_code
  )
  SELECT * FROM dashboard ORDER BY defect_rate DESC
───────────────────────────────────────────────
shape: (5, 11)
┌───────────┬────────────┬──────────┬───────────┬───┬────────────┬────────────┬───────────┬────────┐
│ line_code ┆ line_name  ┆ section  ┆ work_days ┆ … ┆ maint_coun ┆ total_down ┆ total_los ┆ status │
│ ---       ┆ ---        ┆ ---      ┆ ---       ┆   ┆ t          ┆ time       ┆ s         ┆ ---    │
│ str       ┆ str        ┆ str      ┆ i64       ┆   ┆ ---        ┆ ---        ┆ ---       ┆ str    │
│           ┆            ┆          ┆           ┆   ┆ i64        ┆ f64        ┆ i64       ┆        │
╞═══════════╪════════════╪══════════╪═══════════╪═══╪════════════╪════════════╪═══════════╪════════╡
│ LINE-C1   ┆ 溶接ライン ┆ 溶接     ┆ 61        ┆ … ┆ 8          ┆ 45.6       ┆ 1489200   ┆ 要改善 │
│           ┆ 1          ┆          ┆           ┆   ┆            ┆            ┆           ┆        │
│ LINE-B2   ┆ 組立ライン ┆ 組立     ┆ 61        ┆ … ┆ 5          ┆ 29.1       ┆ 1079400   ┆ 要改善 │
│           ┆ 2          ┆          ┆           ┆   ┆            ┆            ┆           ┆        │
│ LINE-A2   ┆ 機械加工ラ ┆ 機械加工 ┆ 61        ┆ … ┆ 5          ┆ 25.6       ┆ 2728500   ┆ 要改善 │
│           ┆ イン2      ┆          ┆           ┆   ┆            ┆            ┆           ┆        │
│ LINE-A1   ┆ 機械加工ラ ┆ 機械加工 ┆ 61        ┆ … ┆ 5          ┆ 19.8       ┆ 691200    ┆ 要注意 │
│           ┆ イン1      ┆          ┆           ┆   ┆            ┆            ┆           ┆        │
│ LINE-B1   ┆ 組立ライン ┆ 組立     ┆ 61        ┆ … ┆ 4          ┆ 14.6       ┆ 361950    ┆ 要注意 │
│           ┆ 1          ┆          ┆           ┆   ┆            ┆            ┆           ┆        │
└───────────┴────────────┴──────────┴───────────┴───┴────────────┴────────────┴───────────┴────────┘
↳ 5 行取得

svg

結果の読み取り

実績 vs 目標 の比較グラフで、各ラインの品質ステータスが一目でわかる
total_loss（不良損失コスト合計）は「改善投資の上限額」の目安として活用できる
→ 「改善投資コスト < 削減できる損失コスト」ならば投資正当化が可能
LINE-C1 は maint_count が多いにもかかわらず不良率が高い場合、
「設備の根本的な更新」が必要なサインかもしれない
このダッシュボード SQL は定期バッチとして実行し、BIツールのビュー として定義することで
毎月自動更新されるレポートを実現できる

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

サブクエリと CTE の使い分け

状況	推奨アプローチ
比較基準を動的に計算したい	スカラサブクエリ（WHERE 句）
集計後にさらにフィルタしたい	FROM サブクエリ（インラインビュー）
各行にベンチマーク値を付加したい	SELECT 句サブクエリ
ライン・グループ別の動的な閾値を使いたい	相関サブクエリ
複数段階の集計を整理したい	CTE（WITH 句）
同一ロジックを複数箇所で再利用したい	CTE

製造業 KPI 集計への適用パターン

WITH 日別集計 AS (...)
   , 月別集計 AS (日別集計から集計)
   , 損失コスト計算 AS (月別集計 × マスタ)
   , ステータス判定 AS (損失コスト × 閾値)
SELECT * FROM ステータス判定

このパターンで 品質 KPI ダッシュボード SQL を設計すると、
毎月の報告書作成をボタン 1 つで自動化できます。

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

1. テーブル設計の見直し

本ノックで使用した production_daily、line_master、maintenance_log は、
実際の製造実行システム（MES）や品質管理システム（QMS）に対応するテーブルを想定しています
実際の導入では、テーブルの正規化レベルと日付型の統一（TEXT vs DATE 型）が重要です

2. パフォーマンスへの配慮

相関サブクエリは行数が多いと O(n²) になる可能性があります
大規模テーブルでは JOIN への書き換えや ウィンドウ関数（第7章）の活用を検討してください

3. BIツールとの連携

ここで設計した CTE ダッシュボード SQL を、BigQuery / Redshift / Snowflake などの
クラウド DWH 上で 定期ビュー（Materialized View） として定義すると、
Tableau / Power BI / Looker へのデータ連携が簡単になります

4. 段階的な自動化

Step 1: 手動クエリ実行 → 月次レポート作成
Step 2: CTE でクエリを整理 → 保守しやすい SQL に
Step 3: DWH へのビュー登録 → 自動更新
Step 4: BI ツール連携 → セルフサービス分析

まとめ

本章では、SQL の応用機能である サブクエリ と CTE を製造業の品質管理データで実践しました。

No.	習得した機能	実務での価値
051	スカラサブクエリ	動的な比較基準の設定
052	WHERE サブクエリ	平均を超えた異常日の検出
053	FROM サブクエリ	集計後フィルタ（インラインビュー）
054	SELECT サブクエリ	全体平均付加・乖離量計算
055	IN サブクエリ	動的リストによる絞り込み
056	EXISTS	複合条件での存在確認
057	相関サブクエリ	ライン別動的閾値による異常検知
058	CTE（WITH句）	集計ロジックの名前付き整理
059	複数 CTE + JOIN	月次 KPI ダッシュボード構築
060	CTE 多段積み	総合分析テーブルの自動生成

次章（第7章） では ウィンドウ関数（ROW_NUMBER、LAG、SUM OVER）を学びます。
相関サブクエリで実現した「行ごとの動的集計」をより効率的に記述する方法を習得しましょう。

法人向けのご相談

製造業における データ分析基盤の構築、SQL 研修・内製化支援、KPI ダッシュボード設計 に関して、
数理工房では法人様向けのご相談を承っております。

本ノックで扱ったような「製造現場の品質データを SQL で自動集計・可視化する仕組み」の
実装支援から教育プログラムの設計まで、お気軽にお問い合わせください。

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

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

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

SQL 100本ノック 第6章（No.051〜No.060）：サブクエリとCTE

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

現場でよくある状況

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

今回扱うノックの全体像

Python 環境の準備

架空データの作成

No.051：サブクエリの基本を理解する

実務での意味

分析・モデル化の考え方

Python で確認する

結果の読み取り

No.052：WHERE句でサブクエリを使う

実務での意味

分析・モデル化の考え方

Python で確認する

結果の読み取り

No.053：FROM句でサブクエリを使う

実務での意味

分析・モデル化の考え方

Python で確認する

結果の読み取り

No.054：SELECT句でサブクエリを使う

実務での意味

分析・モデル化の考え方

Python で確認する

結果の読み取り

No.055：INを使ったサブクエリを作る

実務での意味

分析・モデル化の考え方

Python で確認する

結果の読み取り

No.056：EXISTSを使ったサブクエリを作る

実務での意味

分析・モデル化の考え方

Python で確認する

結果の読み取り

No.057：相関サブクエリを理解する

実務での意味

分析・モデル化の考え方

Python で確認する

結果の読み取り

No.058：WITH句でCTEを作る

実務での意味

分析・モデル化の考え方

Python で確認する

結果の読み取り

No.059：CTEを使って複雑な集計を整理する

実務での意味

分析・モデル化の考え方

Python で確認する

結果の読み取り

No.060：一時的な分析テーブルを作る

実務での意味

分析・モデル化の考え方

Python で確認する

結果の読み取り

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

サブクエリと CTE の使い分け

製造業 KPI 集計への適用パターン

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

1. テーブル設計の見直し

2. パフォーマンスへの配慮

3. BIツールとの連携

4. 段階的な自動化

まとめ

法人向けのご相談

Related Posts

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

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

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

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

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

SQL 100本ノック第6章（No.051〜No.060）：サブクエリとCTE