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

SQL 100本ノック 第10章（No.091〜No.100）：実務運用・性能・設計

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

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

SQL 100本ノック最終章である第10章では、分析SQLを実務で安定的に運用するための設計・品質管理・性能改善を扱います。

「SQLが書けるようになった」だけでは不十分です。実務では：

• 担当者が変わっても読める 可読性の高いSQL が必要
• データに混入する 重複・欠損・異常値 を自動的に検知できる仕組みが必要
• 100万行を超えるデータでも 高速に動作するSQL が必要
• 経営会議で即座に使える BI ダッシュボード向けの集計テーブル が必要

本章では、製造ラインの稼働記録（production_log）を使い、これらを一気通貫で学びます。

テーブル	概要	件数
machines	製造機械マスタ	5台
production_log	日次稼働記録（意図的に品質問題あり）	約820件
defects	不良品記録	約60件

現場でよくある状況

中堅製造業のデータ分析担当者が直面する典型的な問題を想像してください。

シナリオ A：引き継ぎ問題 前任者が書いた 300 行の SQL があるが、コメントがない・変数名が意味不明・サブクエリが入れ子になっていて誰も読めない。

シナリオ B：データ品質問題 「先月の不良率がいつもと違う」と言われて調べると、同じ日付・同じ機械のレコードが 2 件入っていた（二重登録）。さらに、一部レコードの defect_qty が NULL になっていた。

シナリオ C：パフォーマンス問題 月次レポートの SQL が毎回 10 分以上かかる。朝会議に間に合わない。

シナリオ D：BI ツール連携問題 Power BI や Tableau に直接 SQL を投げると重すぎる。事前に集計テーブルを作る必要がある。

本章ではこれらの問題すべてに対する SQL ベースの解決策を学びます。

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

実務 SQL の品質・性能問題は 「動いているが壊れている」 状態が長続きしやすいのが特徴です。

$$\text{分析の信頼性} = \underbrace{f(\text{データ品質})}_{\text{No.093〜095}} \times \underbrace{g(\text{SQL品質})}_{\text{No.091〜092}} \times \underbrace{h(\text{実行速度})}_{\text{No.096〜098}}$$

いずれかが 0 に近いと、結果の信頼性も 0 に近づきます。

問題の種類	気づきにくい理由	影響
SQL可読性の低下	動作していれば分からない	誤修正・保守コスト増大
重複データ	集計すると合計が変わる	誤った意思決定
欠損データ	計算式で無視されることも	不良率の過小評価
クエリ性能劣化	徐々に進行する	報告書作成の遅延
設計の問題	リファクタが難しい	BI ツール連携不能

対策のカギは 「問題が起きてから直す」ではなく「問題を自動検知する SQL を書く」 ことです。

今回扱うノックの全体像

No.	テーマ	習得する技術	製造業での価値
091	SQLの可読性を高める書き方	インデント・エイリアス・改行	引き継ぎ可能な分析コードの作成
092	分析SQLにコメントを書く	-- コメント・ヘッダ構造	チームでのSQL共同管理
093	集計結果を検算する	部分和 = 全体和, NULL確認	分析レポートの信頼性担保
094	重複データを検出する	GROUP BY + HAVING, ROW_NUMBER	二重登録の自動検知・除去
095	欠損データを検出する	COUNT(*) vs COUNT(col)	データ品質ダッシュボードの構築
096	インデックスの基本を理解する	CREATE INDEX, EXPLAIN QUERY PLAN	クエリ高速化の基礎設計
097	実行計画の見方を理解する	SCAN vs SEARCH, JOIN戦略	ボトルネック特定の標準手順
098	重いSQLを改善する	SELECT *廃止, JOIN変換, 早期絞り込み	月次レポートの高速化
099	BIダッシュボード用集計テーブルを設計する	CREATE TABLE AS SELECT, 事前集計	Power BI / Tableau 連携
100	SQLを使ったデータ分析プロジェクトの流れを整理する	全章の統合・プロジェクト設計	データドリブン経営の実現

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 time
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

ライブラリ読み込み完了

架空データの作成

本章では製造ラインの稼働記録を使います。
データには 意図的に品質問題（重複・欠損） を混入させており、No.094〜095 でそれを検出します。

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

# ── machines ──────────────────────────────────────────────
conn.execute("""
CREATE TABLE machines (
    machine_id   TEXT PRIMARY KEY,
    machine_name TEXT,
    category     TEXT,
    rated_qty    INTEGER
)
""")
MACHINE_DATA = [
    ("M001", "旋盤A", "機械加工", 300),
    ("M002", "旋盤B", "機械加工", 280),
    ("M003", "溶接機C", "溶接", 200),
    ("M004", "プレス機D", "プレス", 400),
    ("M005", "組立ラインE", "組立", 180),
]
conn.executemany("INSERT INTO machines VALUES (?,?,?,?)", MACHINE_DATA)
RATED = {r[0]: r[3] for r in MACHINE_DATA}

# ── production_log ────────────────────────────────────────
conn.execute("""
CREATE TABLE production_log (
    log_id         INTEGER PRIMARY KEY,
    machine_id     TEXT,
    shift_id       TEXT,
    log_date       TEXT,
    production_qty INTEGER,
    defect_qty     INTEGER,
    operator_id    TEXT
)
""")

SHIFTS = ["S01", "S02", "S03"]
MACH_IDS = ["M001", "M002", "M003", "M004", "M005"]

# 2024年の平日を生成
START, END = date(2024, 1, 1), date(2024, 12, 31)
wdays = [START + timedelta(d) for d in range((END - START).days + 1) if (START + timedelta(d)).weekday() < 5]

rows, lid = [], 1
for d in wdays:
    # 1日あたり2〜4機械が稼働
    n_m = np.random.randint(2, 5)
    for m in np.random.choice(MACH_IDS, n_m, replace=False):
        shift = np.random.choice(SHIFTS)
        op = f"OP{np.random.randint(1, 11):02d}"
        prod = max(50, int(np.random.normal(RATED[m], RATED[m] * 0.1)))
        defq = max(0, int(np.random.normal(prod * 0.018, prod * 0.004)))
        rows.append((lid, m, shift, str(d), prod, defq, op))
        lid += 1

# ── 意図的な品質問題の混入 ────────────────────────────────
# ①重複レコード（同 machine_id + log_date + shift_id, 新 log_id で再挿入）
dup_indices = np.random.choice(len(rows), 20, replace=False)
for idx in dup_indices:
    _, m, sh, dt, pq, dq, op = rows[idx]
    rows.append((lid, m, sh, dt, pq, dq, op))
    lid += 1

# ②欠損: defect_qty を NULL に（15件）
rows_m = [list(r) for r in rows]
null_dq = np.random.choice(len(rows_m), 15, replace=False)
for i in null_dq:
    rows_m[i][5] = None
# ③欠損: operator_id を NULL に（8件）
null_op = np.random.choice(len(rows_m), 8, replace=False)
for i in null_op:
    rows_m[i][6] = None
rows = [tuple(r) for r in rows_m]

conn.executemany("INSERT INTO production_log VALUES (?,?,?,?,?,?,?)", rows)

# ── defects ───────────────────────────────────────────────
conn.execute("""
CREATE TABLE defects (
    defect_id   INTEGER PRIMARY KEY,
    machine_id  TEXT,
    log_date    TEXT,
    defect_type TEXT,
    qty         INTEGER,
    root_cause  TEXT
)
""")
DEFECT_TYPES = ["寸法不良", "表面傷", "溶接欠陥", "圧力不足", "組み付けミス"]
ROOT_CAUSES = ["材料不良", "機械摩耗", "作業ミス", "温度異常", "設備故障"]
defect_rows = []
for i in range(60):
    m = np.random.choice(MACH_IDS)
    d = wdays[np.random.randint(0, len(wdays))]
    dt = np.random.choice(DEFECT_TYPES)
    q_ = np.random.randint(1, 20)
    rc = np.random.choice(ROOT_CAUSES)
    defect_rows.append((i + 1, m, str(d), dt, q_, rc))
conn.executemany("INSERT INTO defects VALUES (?,?,?,?,?,?)", defect_rows)
conn.commit()

print(f"machines     : {len(MACHINE_DATA)} 件")
print(f"production_log: {len(rows)} 件")
print(f"  うち重複混入: {len(dup_indices)} 件")
print(f"  うち欠損混入（defect_qty）: {len(null_dq)} 件")
print(f"  うち欠損混入（operator_id）: {len(null_op)} 件")
print(f"defects      : {len(defect_rows)} 件")

machines     : 5 件
production_log: 800 件
  うち重複混入: 20 件
  うち欠損混入（defect_qty）: 15 件
  うち欠損混入（operator_id）: 8 件
defects      : 60 件

for tbl in ["machines", "production_log", "defects"]:
    n = conn.execute(f"SELECT COUNT(*) FROM {tbl}").fetchone()[0]
    print(f"{tbl:16s}: {n:5d} 件")
print()
q(conn, "SELECT * FROM machines")

machines        :     5 件
production_log  :   800 件
defects         :    60 件

── SQL ─────────────────────────────────────────
  SELECT * FROM machines
───────────────────────────────────────────────
shape: (5, 4)
┌────────────┬──────────────┬──────────┬───────────┐
│ machine_id ┆ machine_name ┆ category ┆ rated_qty │
│ ---        ┆ ---          ┆ ---      ┆ ---       │
│ str        ┆ str          ┆ str      ┆ i64       │
╞════════════╪══════════════╪══════════╪═══════════╡
│ M001       ┆ 旋盤A        ┆ 機械加工 ┆ 300       │
│ M002       ┆ 旋盤B        ┆ 機械加工 ┆ 280       │
│ M003       ┆ 溶接機C      ┆ 溶接     ┆ 200       │
│ M004       ┆ プレス機D    ┆ プレス   ┆ 400       │
│ M005       ┆ 組立ラインE  ┆ 組立     ┆ 180       │
└────────────┴──────────────┴──────────┴───────────┘
↳ 5 行取得

shape: (5, 4)

machine_id	machine_name	category	rated_qty
str	str	str	i64
”M001"	"旋盤A"	"機械加工”	300
”M002"	"旋盤B"	"機械加工”	280
”M003"	"溶接機C"	"溶接”	200
”M004"	"プレス機D"	"プレス”	400
”M005"	"組立ラインE"	"組立”	180

No.091：SQLの可読性を高める書き方を理解する

実務での意味

SQL は 「書いた人しか読めない資産」 になりやすい言語です。
製造業のデータ分析では、担当者交代・監査・コードレビューなど、自分以外の人が読む機会が必ず訪れます。

可読性の高い SQL は：

• バグの発見が早い
• 修正・拡張が容易
• チームでのレビューが可能

分析・モデル化の考え方

SQL の可読性を高める主なルールを整理します：

ルール	悪い例	良い例
キーワードは大文字	select	SELECT
列は 1 列 1 行	col1, col2, col3	各列を改行
エイリアスは AS を明示	SUM(qty) total	SUM(qty) AS total
JOIN の ON は明示的に	FROM a, b WHERE a.id = b.id	JOIN b ON a.id = b.id
計算式は ROUND() でラップ	生のキャスト連鎖	ROUND(CAST(...) / ..., 2)
NULL 対策を明示	SUM(col)	SUM(COALESCE(col, 0))

Python で確認する

print("=== No.091 SQLの可読性を高める書き方 ===\n")

# ── 悪い例（1行に詰め込んだSQL）───────────────────────────
bad_sql = (
    "SELECT m.machine_id,m.machine_name,SUM(p.production_qty) AS total,"
    "SUM(COALESCE(p.defect_qty,0)) AS defects,"
    "ROUND(CAST(SUM(COALESCE(p.defect_qty,0))AS REAL)"
    "/NULLIF(SUM(p.production_qty),0)*100,2) AS defect_rate "
    "FROM production_log p "
    "JOIN machines m ON p.machine_id=m.machine_id "
    "WHERE p.log_date BETWEEN '2024-01-01' AND '2024-06-30' "
    "GROUP BY m.machine_id ORDER BY defect_rate DESC"
)

print("【悪い例: 一行に詰め込んだSQL】")
print(f"文字数: {len(bad_sql)} 文字")
print(f"行数: 1 行（機械には読めるが人間には辛い）\n")

# ── 良い例（適切にフォーマットされたSQL）──────────────────
good_sql = """
SELECT
    m.machine_id,
    m.machine_name,
    SUM(p.production_qty)                              AS total_qty,
    SUM(COALESCE(p.defect_qty, 0))                     AS total_defects,
    ROUND(
        CAST(SUM(COALESCE(p.defect_qty, 0)) AS REAL)
        / NULLIF(SUM(p.production_qty), 0) * 100, 2
    )                                                  AS defect_rate
FROM production_log AS p
JOIN machines       AS m ON p.machine_id = m.machine_id
WHERE p.log_date BETWEEN '2024-01-01' AND '2024-06-30'
GROUP BY m.machine_id
ORDER BY defect_rate DESC
"""

print("【良い例: 適切にフォーマットされたSQL】")
print(f"行数: {len(good_sql.strip().splitlines())} 行")
for line in good_sql.strip().split("\n"):
    print(f"  {line}")

# ── 実行して同一結果を確認 ─────────────────────────────────
print("\n【実行結果（良い例）】")
df91 = q(conn, good_sql)

# 悪い例も実行して行数が同じことを確認
bad_count = len(conn.execute(bad_sql).fetchall())
good_count = len(df91)
print(f"\n悪い例の取得行数: {bad_count}")
print(f"良い例の取得行数: {good_count}")
print(f"結果の一致: {bad_count == good_count} ✅")

=== No.091 SQLの可読性を高める書き方 ===

【悪い例: 一行に詰め込んだSQL】
文字数: 379 文字
行数: 1 行（機械には読めるが人間には辛い）

【良い例: 適切にフォーマットされたSQL】
行数: 14 行
  SELECT
      m.machine_id,
      m.machine_name,
      SUM(p.production_qty)                              AS total_qty,
      SUM(COALESCE(p.defect_qty, 0))                     AS total_defects,
      ROUND(
          CAST(SUM(COALESCE(p.defect_qty, 0)) AS REAL)
          / NULLIF(SUM(p.production_qty), 0) * 100, 2
      )                                                  AS defect_rate
  FROM production_log AS p
  JOIN machines       AS m ON p.machine_id = m.machine_id
  WHERE p.log_date BETWEEN '2024-01-01' AND '2024-06-30'
  GROUP BY m.machine_id
  ORDER BY defect_rate DESC

【実行結果（良い例）】
── SQL ─────────────────────────────────────────
  SELECT
      m.machine_id,
      m.machine_name,
      SUM(p.production_qty)                              AS total_qty,
      SUM(COALESCE(p.defect_qty, 0))                     AS total_defects,
      ROUND(
          CAST(SUM(COALESCE(p.defect_qty, 0)) AS REAL)
          / NULLIF(SUM(p.production_qty), 0) * 100, 2
      )                                                  AS defect_rate
  FROM production_log AS p
  JOIN machines       AS m ON p.machine_id = m.machine_id
  WHERE p.log_date BETWEEN '2024-01-01' AND '2024-06-30'
  GROUP BY m.machine_id
  ORDER BY defect_rate DESC
───────────────────────────────────────────────
shape: (5, 5)
┌────────────┬──────────────┬───────────┬───────────────┬─────────────┐
│ machine_id ┆ machine_name ┆ total_qty ┆ total_defects ┆ defect_rate │
│ ---        ┆ ---          ┆ ---       ┆ ---           ┆ ---         │
│ str        ┆ str          ┆ i64       ┆ i64           ┆ f64         │
╞════════════╪══════════════╪═══════════╪═══════════════╪═════════════╡
│ M002       ┆ 旋盤B        ┆ 21968     ┆ 368           ┆ 1.68        │
│ M004       ┆ プレス機D    ┆ 33113     ┆ 548           ┆ 1.65        │
│ M003       ┆ 溶接機C      ┆ 14848     ┆ 234           ┆ 1.58        │
│ M001       ┆ 旋盤A        ┆ 23399     ┆ 360           ┆ 1.54        │
│ M005       ┆ 組立ラインE  ┆ 14254     ┆ 209           ┆ 1.47        │
└────────────┴──────────────┴───────────┴───────────────┴─────────────┘
↳ 5 行取得

悪い例の取得行数: 5
良い例の取得行数: 5
結果の一致: True ✅

結果の読み取り

• 悪い例と良い例は完全に同じ結果を返す → フォーマットは動作に影響しない
• しかし、良い例では COALESCE(defect_qty, 0) や NULLIF(production_qty, 0) が 明示的に書かれており、NULL 対策の意図が伝わる
• チームへの引き継ぎ・コードレビューでは、良い例の形式が強く推奨される
• CI/CD パイプラインに sqlfluff などの SQL リンターを組み込むと、フォーマットを自動でチェックできる

No.092：分析SQLにコメントを書く

実務での意味

製造業の分析 SQL は、 1 年後に自分または別の担当者が修正する ことを前提に書く必要があります。

コメントには 3 種類があります：

種類	構文	使いどころ
ヘッダコメント	-- ====...====	ファイル・目的・対象期間の説明
ブロックコメント	-- 段落説明	CTE・サブクエリの目的説明
インラインコメント	col, -- 理由	特定の列・条件の補足説明

分析・モデル化の考え方

コメントに書くべき情報の優先度：

$$\text{コメントの優先度} = \text{「なぜ」} > \text{「何を」} > \text{「どうやって」}$$

「何を」 はコードを読めば分かります。「なぜ」（ビジネス上の判断・例外処理の理由）こそが価値あるコメントです。

Python で確認する

print("=== No.092 分析SQLにコメントを書く ===\n")

commented_sql = """
-- ================================================================
-- 分析SQL: 機械別 月次不良率レポート
-- 目的   : 各製造ラインの品質 KPI を月単位でモニタリングする
-- 対象   : production_log（2024年通年）
-- ================================================================

WITH monthly_stats AS (
    -- 月別・機械別の集計
    -- 注意: defect_qty が NULL のレコードは "未記録（0件扱い）" として処理する
    --       （記録漏れと不良ゼロを混同しないよう、後工程で別途フラグを立てること）
    SELECT
        p.machine_id,
        strftime('%Y-%m', p.log_date)  AS ym,           -- 年月キー
        COUNT(p.log_id)                AS record_count,  -- 集計対象レコード数
        SUM(p.production_qty)          AS total_qty,     -- 月次生産数
        SUM(COALESCE(p.defect_qty, 0)) AS total_defects  -- 月次不良数（NULL→0）
    FROM production_log AS p
    WHERE p.log_date >= '2024-01-01'                    -- 2024年以降のみ
    GROUP BY p.machine_id, ym
)
SELECT
    ms.machine_id,
    m.machine_name,
    m.category,
    ms.ym,
    ms.record_count,
    ms.total_qty,
    ms.total_defects,
    -- 不良率: ゼロ除算防止のため NULLIF を使用
    ROUND(
        ms.total_defects * 100.0
        / NULLIF(ms.total_qty, 0), 2
    ) AS defect_rate
FROM monthly_stats AS ms
JOIN machines AS m ON ms.machine_id = m.machine_id
ORDER BY ms.machine_id, ms.ym
"""

print("コメント付き SQL の実行結果（最初の12行）:")
df92 = q(conn, commented_sql)
print(f"\n総行数: {len(df92)} 行（5機械 × 12ヶ月）")

=== No.092 分析SQLにコメントを書く ===

コメント付き SQL の実行結果（最初の12行）:
── SQL ─────────────────────────────────────────
  -- ================================================================
  -- 分析SQL: 機械別 月次不良率レポート
  -- 目的   : 各製造ラインの品質 KPI を月単位でモニタリングする
  -- 対象   : production_log（2024年通年）
  -- ================================================================
  
  WITH monthly_stats AS (
      -- 月別・機械別の集計
      -- 注意: defect_qty が NULL のレコードは "未記録（0件扱い）" として処理する
      --       （記録漏れと不良ゼロを混同しないよう、後工程で別途フラグを立てること）
      SELECT
          p.machine_id,
          strftime('%Y-%m', p.log_date)  AS ym,           -- 年月キー
          COUNT(p.log_id)                AS record_count,  -- 集計対象レコード数
          SUM(p.production_qty)          AS total_qty,     -- 月次生産数
          SUM(COALESCE(p.defect_qty, 0)) AS total_defects  -- 月次不良数（NULL→0）
      FROM production_log AS p
      WHERE p.log_date >= '2024-01-01'                    -- 2024年以降のみ
      GROUP BY p.machine_id, ym
  )
  SELECT
      ms.machine_id,
      m.machine_name,
      m.category,
      ms.ym,
      ms.record_count,
      ms.total_qty,
      ms.total_defects,
      -- 不良率: ゼロ除算防止のため NULLIF を使用
      ROUND(
          ms.total_defects * 100.0
          / NULLIF(ms.total_qty, 0), 2
      ) AS defect_rate
  FROM monthly_stats AS ms
  JOIN machines AS m ON ms.machine_id = m.machine_id
  ORDER BY ms.machine_id, ms.ym
───────────────────────────────────────────────
shape: (60, 8)
┌────────────┬─────────────┬──────────┬─────────┬────────────┬───────────┬────────────┬────────────┐
│ machine_id ┆ machine_nam ┆ category ┆ ym      ┆ record_cou ┆ total_qty ┆ total_defe ┆ defect_rat │
│ ---        ┆ e           ┆ ---      ┆ ---     ┆ nt         ┆ ---       ┆ cts        ┆ e          │
│ str        ┆ ---         ┆ str      ┆ str     ┆ ---        ┆ i64       ┆ ---        ┆ ---        │
│            ┆ str         ┆          ┆         ┆ i64        ┆           ┆ i64        ┆ f64        │
╞════════════╪═════════════╪══════════╪═════════╪════════════╪═══════════╪════════════╪════════════╡
│ M001       ┆ 旋盤A       ┆ 機械加工 ┆ 2024-01 ┆ 12         ┆ 3422      ┆ 54         ┆ 1.58       │
│ M001       ┆ 旋盤A       ┆ 機械加工 ┆ 2024-02 ┆ 11         ┆ 3407      ┆ 51         ┆ 1.5        │
│ M001       ┆ 旋盤A       ┆ 機械加工 ┆ 2024-03 ┆ 11         ┆ 3148      ┆ 57         ┆ 1.81       │
│ M001       ┆ 旋盤A       ┆ 機械加工 ┆ 2024-04 ┆ 17         ┆ 5082      ┆ 73         ┆ 1.44       │
│ M001       ┆ 旋盤A       ┆ 機械加工 ┆ 2024-05 ┆ 18         ┆ 5670      ┆ 83         ┆ 1.46       │
│ …          ┆ …           ┆ …        ┆ …       ┆ …          ┆ …         ┆ …          ┆ …          │
│ M005       ┆ 組立ラインE ┆ 組立     ┆ 2024-08 ┆ 14         ┆ 2496      ┆ 37         ┆ 1.48       │
│ M005       ┆ 組立ラインE ┆ 組立     ┆ 2024-09 ┆ 7          ┆ 1260      ┆ 18         ┆ 1.43       │
│ M005       ┆ 組立ラインE ┆ 組立     ┆ 2024-10 ┆ 9          ┆ 1549      ┆ 29         ┆ 1.87       │
│ M005       ┆ 組立ラインE ┆ 組立     ┆ 2024-11 ┆ 18         ┆ 3407      ┆ 52         ┆ 1.53       │
│ M005       ┆ 組立ラインE ┆ 組立     ┆ 2024-12 ┆ 13         ┆ 2224      ┆ 34         ┆ 1.53       │
└────────────┴─────────────┴──────────┴─────────┴────────────┴───────────┴────────────┴────────────┘
↳ 60 行取得

総行数: 60 行（5機械 × 12ヶ月）

結果の読み取り

• ヘッダコメントに 「目的・対象・注意点」 を書くことで、SQL の意図が一目で分かる
• CTE の中に -- 注意: コメントを入れることで、ビジネス上の判断（NULL の扱い） を将来の修正者に伝えられる
• インラインコメントで -- 年月キー と書くことで、strftime の出力形式が一目で分かる
• 「コメントは未来の自分へのメッセージ」 という意識で書くと、品質が上がる

No.093：集計結果を検算する

実務での意味

分析レポートの数値は 「正しそうに見えても間違っている」 ことがあります。
特に製造業では、不良率の集計ミスは品質管理の判断ミスに直結します。

分析・モデル化の考え方

検算の 3 原則：

原則	確認する内容	SQL のパターン
部分和 = 全体和	機械別合計の和 = 総合計	2 クエリの結果比較
カウント整合性	COUNT(*) vs COUNT(col) の差 = NULL 件数	差分算出
値域チェック	不良率が 0〜100% の範囲内か	MIN / MAX + 異常値カウント

実務では月次レポートを提出する前に、この 3 つを自動的にチェックするバリデーション SQL を実行する習慣をつけてください。

Python で確認する

print("=== No.093 集計結果を検算する ===\n")

# 検算①: 機械別合計の和 = 全体合計
print("① 機械別合計の和 vs 全体合計（一致確認）")
grand = conn.execute("SELECT SUM(production_qty) AS gt FROM production_log").fetchone()[0]
by_m = conn.execute("""
SELECT SUM(machine_total) FROM
  (SELECT machine_id, SUM(production_qty) AS machine_total FROM production_log GROUP BY machine_id)
""").fetchone()[0]
print(f"  全体合計         : {grand:>10,}")
print(f"  機械別合計の和   : {by_m:>10,}")
print(f"  一致             : {'✅ 一致' if grand == by_m else '❌ 不一致（差: ' + str(grand - by_m) + '）'}")

# 検算②: COUNT(*) vs COUNT(defect_qty)
print("\n② COUNT(*) vs COUNT(col) の差 = NULL件数")
df93b = q(
    conn,
    """
SELECT
    COUNT(*)                                      AS total_rows,
    COUNT(defect_qty)                             AS non_null_defect,
    COUNT(*) - COUNT(defect_qty)                  AS null_defect_count,
    COUNT(operator_id)                            AS non_null_operator,
    COUNT(*) - COUNT(operator_id)                 AS null_operator_count
FROM production_log
""",
)

# 検算③: 値域チェック（不良率の異常値確認）
print("\n③ 値域チェック（不良率 0〜100%以外がないか）")
df93c = q(
    conn,
    """
SELECT
    ROUND(MIN(defect_qty * 100.0 / production_qty), 2) AS min_rate,
    ROUND(MAX(defect_qty * 100.0 / production_qty), 2) AS max_rate,
    ROUND(AVG(defect_qty * 100.0 / production_qty), 2) AS avg_rate,
    COUNT(CASE WHEN defect_qty > production_qty THEN 1 END) AS anomaly_count
FROM production_log
WHERE defect_qty IS NOT NULL AND production_qty > 0
""",
)

# グラフ: 機械別 良品数 + 不良数（積み上げ棒グラフで集計の妥当性を可視化）
df93_chart = q(
    conn,
    """
SELECT
    machine_id,
    SUM(production_qty)                   AS total_qty,
    SUM(COALESCE(defect_qty, 0))          AS total_defects,
    SUM(production_qty)
      - SUM(COALESCE(defect_qty, 0))      AS good_qty
FROM production_log
GROUP BY machine_id
ORDER BY machine_id
""",
)

mids = df93_chart["machine_id"].to_list()
good_q = df93_chart["good_qty"].to_list()
def_q = df93_chart["total_defects"].to_list()

fig, ax = plt.subplots(figsize=(9, 5))
ax.bar(mids, good_q, color="#2ecc71", label="良品数", edgecolor="white")
ax.bar(mids, def_q, color="#e74c3c", label="不良数", bottom=good_q, edgecolor="white")
ax.set_title("機械別 生産数内訳（良品 + 不良）（No.093：検算用可視化）", fontsize=12)
ax.set_xlabel("機械ID")
ax.set_ylabel("生産数量")
ax.legend(fontsize=10)
ax.grid(axis="y", alpha=0.4)

# 合計値を棒の上に表示
for mid, gq, dq in zip(mids, good_q, def_q):
    ax.text(mid, gq + dq + 50, f"{gq+dq:,}", ha="center", va="bottom", fontsize=9)

plt.tight_layout()
plt.show()

=== No.093 集計結果を検算する ===

① 機械別合計の和 vs 全体合計（一致確認）
  全体合計         :    219,758
  機械別合計の和   :    219,758
  一致             : ✅ 一致

② COUNT(*) vs COUNT(col) の差 = NULL件数
── SQL ─────────────────────────────────────────
  SELECT
      COUNT(*)                                      AS total_rows,
      COUNT(defect_qty)                             AS non_null_defect,
      COUNT(*) - COUNT(defect_qty)                  AS null_defect_count,
      COUNT(operator_id)                            AS non_null_operator,
      COUNT(*) - COUNT(operator_id)                 AS null_operator_count
  FROM production_log
───────────────────────────────────────────────
shape: (1, 5)
┌────────────┬─────────────────┬───────────────────┬───────────────────┬─────────────────────┐
│ total_rows ┆ non_null_defect ┆ null_defect_count ┆ non_null_operator ┆ null_operator_count │
│ ---        ┆ ---             ┆ ---               ┆ ---               ┆ ---                 │
│ i64        ┆ i64             ┆ i64               ┆ i64               ┆ i64                 │
╞════════════╪═════════════════╪═══════════════════╪═══════════════════╪═════════════════════╡
│ 800        ┆ 785             ┆ 15                ┆ 792               ┆ 8                   │
└────────────┴─────────────────┴───────────────────┴───────────────────┴─────────────────────┘
↳ 1 行取得

③ 値域チェック（不良率 0〜100%以外がないか）
── SQL ─────────────────────────────────────────
  SELECT
      ROUND(MIN(defect_qty * 100.0 / production_qty), 2) AS min_rate,
      ROUND(MAX(defect_qty * 100.0 / production_qty), 2) AS max_rate,
      ROUND(AVG(defect_qty * 100.0 / production_qty), 2) AS avg_rate,
      COUNT(CASE WHEN defect_qty > production_qty THEN 1 END) AS anomaly_count
  FROM production_log
  WHERE defect_qty IS NOT NULL AND production_qty > 0
───────────────────────────────────────────────
shape: (1, 4)
┌──────────┬──────────┬──────────┬───────────────┐
│ min_rate ┆ max_rate ┆ avg_rate ┆ anomaly_count │
│ ---      ┆ ---      ┆ ---      ┆ ---           │
│ f64      ┆ f64      ┆ f64      ┆ i64           │
╞══════════╪══════════╪══════════╪═══════════════╡
│ 0.35     ┆ 2.96     ┆ 1.62     ┆ 0             │
└──────────┴──────────┴──────────┴───────────────┘
↳ 1 行取得
── SQL ─────────────────────────────────────────
  SELECT
      machine_id,
      SUM(production_qty)                   AS total_qty,
      SUM(COALESCE(defect_qty, 0))          AS total_defects,
      SUM(production_qty)
        - SUM(COALESCE(defect_qty, 0))      AS good_qty
  FROM production_log
  GROUP BY machine_id
  ORDER BY machine_id
───────────────────────────────────────────────
shape: (5, 4)
┌────────────┬───────────┬───────────────┬──────────┐
│ machine_id ┆ total_qty ┆ total_defects ┆ good_qty │
│ ---        ┆ ---       ┆ ---           ┆ ---      │
│ str        ┆ i64       ┆ i64           ┆ i64      │
╞════════════╪═══════════╪═══════════════╪══════════╡
│ M001       ┆ 48408     ┆ 768           ┆ 47640    │
│ M002       ┆ 44203     ┆ 729           ┆ 43474    │
│ M003       ┆ 31976     ┆ 498           ┆ 31478    │
│ M004       ┆ 67829     ┆ 1120          ┆ 66709    │
│ M005       ┆ 27342     ┆ 413           ┆ 26929    │
└────────────┴───────────┴───────────────┴──────────┘
↳ 5 行取得

結果の読み取り

• ①で 機械別合計の和 = 全体合計 が一致 → 集計クエリに欠落・重複がないことを確認
• ② COUNT(*) - COUNT(defect_qty) が NULL 件数と一致している → データ品質レポートの基礎
• ③ 不良率の最大値が 100%未満 → 「不良数 > 生産数」という異常値がないことを確認
• グラフの各棒の上に表示された合計値が、全体合計と一致しているかを目視で確認できる

No.094：重複データを検出する

実務での意味

製造業の稼働記録では、以下の原因で 重複データ（二重登録） が発生します：

• 手入力システムでの「送信ボタン 2 回押し」
• バッチ処理の再実行による二重投入
• CSV インポートの重複実行

重複があると：

$$\text{集計結果} = \text{正しい値} + \underbrace{\text{重複分の上乗せ}}_{\text{誤差}}$$

不良率の過大評価・生産数の過大計上につながり、誤った意思決定を引き起こします。

分析・モデル化の考え方

重複検出の 2 ステップ：

1. GROUP BY + HAVING COUNT(*) > 1 で重複キーを特定
2. ROW_NUMBER() OVER (PARTITION BY ... ORDER BY log_id) で重複を番号付けし、rn > 1 を除外

Python で確認する

print("=== No.094 重複データを検出する ===\n")

# 重複検出①: 同一キー（machine_id + log_date + shift_id）が複数存在するレコード
print("① キー重複の検出")
df94a = q(
    conn,
    """
SELECT
    machine_id,
    log_date,
    shift_id,
    COUNT(*) AS dup_count
FROM production_log
GROUP BY machine_id, log_date, shift_id
HAVING COUNT(*) > 1
ORDER BY dup_count DESC, machine_id, log_date
LIMIT 10
""",
)
print(f"  重複キーの組み合わせ数: {len(df94a)}")

# 重複検出②: ROW_NUMBER によるランク付けと件数集計
print("\n② ROW_NUMBER() による重複件数集計")
df94b = q(
    conn,
    """
WITH ranked AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY machine_id, log_date, shift_id
            ORDER BY log_id
        ) AS rn
    FROM production_log
)
SELECT
    COUNT(*)                                  AS total_records,
    SUM(CASE WHEN rn = 1 THEN 1 ELSE 0 END)  AS unique_records,
    SUM(CASE WHEN rn > 1 THEN 1 ELSE 0 END)  AS duplicate_records,
    ROUND(
        SUM(CASE WHEN rn > 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*),
        1
    )                                         AS dup_rate_pct
FROM ranked
""",
)

# 機械別 重複件数
print("\n③ 機械別 重複件数")
df94c = q(
    conn,
    """
WITH ranked AS (
    SELECT
        machine_id,
        ROW_NUMBER() OVER (
            PARTITION BY machine_id, log_date, shift_id
            ORDER BY log_id
        ) AS rn
    FROM production_log
)
SELECT machine_id, COUNT(*) AS dup_records
FROM ranked
WHERE rn > 1
GROUP BY machine_id
ORDER BY dup_records DESC
""",
)

# グラフ: 全体件数 vs ユニーク件数（棒グラフ）
total = df94b["total_records"][0]
unique = df94b["unique_records"][0]
dups = df94b["duplicate_records"][0]

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

# 左: 全体 vs ユニーク
axes[0].bar(
    ["全レコード", "ユニーク\n（重複除去後）"],
    [total, unique],
    color=["#3498db", "#2ecc71"],
    edgecolor="white",
    width=0.5,
)
axes[0].set_title("全レコード vs 重複除去後", fontsize=12)
axes[0].set_ylabel("レコード件数")
axes[0].grid(axis="y", alpha=0.4)
for i, v in enumerate([total, unique]):
    axes[0].text(i, v + 5, f"{v:,}", ha="center", va="bottom", fontsize=11)

# 右: 機械別 重複件数
mids94 = df94c["machine_id"].to_list()
dups94 = df94c["dup_records"].to_list()
axes[1].bar(mids94, dups94, color="#e74c3c", edgecolor="white")
axes[1].set_title("機械別 重複レコード数（No.094）", fontsize=12)
axes[1].set_xlabel("機械ID")
axes[1].set_ylabel("重複件数")
axes[1].grid(axis="y", alpha=0.4)
for mid, d in zip(mids94, dups94):
    axes[1].text(mid, d + 0.2, str(d), ha="center", va="bottom", fontsize=11)

plt.tight_layout()
plt.show()

=== No.094 重複データを検出する ===

① キー重複の検出
── SQL ─────────────────────────────────────────
  SELECT
      machine_id,
      log_date,
      shift_id,
      COUNT(*) AS dup_count
  FROM production_log
  GROUP BY machine_id, log_date, shift_id
  HAVING COUNT(*) > 1
  ORDER BY dup_count DESC, machine_id, log_date
  LIMIT 10
───────────────────────────────────────────────
shape: (10, 4)
┌────────────┬────────────┬──────────┬───────────┐
│ machine_id ┆ log_date   ┆ shift_id ┆ dup_count │
│ ---        ┆ ---        ┆ ---      ┆ ---       │
│ str        ┆ str        ┆ str      ┆ i64       │
╞════════════╪════════════╪══════════╪═══════════╡
│ M001       ┆ 2024-05-01 ┆ S01      ┆ 2         │
│ M001       ┆ 2024-05-07 ┆ S02      ┆ 2         │
│ M001       ┆ 2024-10-16 ┆ S03      ┆ 2         │
│ M001       ┆ 2024-11-08 ┆ S01      ┆ 2         │
│ M001       ┆ 2024-11-13 ┆ S01      ┆ 2         │
│ M002       ┆ 2024-01-02 ┆ S03      ┆ 2         │
│ M002       ┆ 2024-07-23 ┆ S01      ┆ 2         │
│ M002       ┆ 2024-10-07 ┆ S03      ┆ 2         │
│ M002       ┆ 2024-11-04 ┆ S01      ┆ 2         │
│ M002       ┆ 2024-11-19 ┆ S03      ┆ 2         │
└────────────┴────────────┴──────────┴───────────┘
↳ 10 行取得
  重複キーの組み合わせ数: 10

② ROW_NUMBER() による重複件数集計
── SQL ─────────────────────────────────────────
  WITH ranked AS (
      SELECT
          *,
          ROW_NUMBER() OVER (
              PARTITION BY machine_id, log_date, shift_id
              ORDER BY log_id
          ) AS rn
      FROM production_log
  )
  SELECT
      COUNT(*)                                  AS total_records,
      SUM(CASE WHEN rn = 1 THEN 1 ELSE 0 END)  AS unique_records,
      SUM(CASE WHEN rn > 1 THEN 1 ELSE 0 END)  AS duplicate_records,
      ROUND(
          SUM(CASE WHEN rn > 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*),
          1
      )                                         AS dup_rate_pct
  FROM ranked
───────────────────────────────────────────────
shape: (1, 4)
┌───────────────┬────────────────┬───────────────────┬──────────────┐
│ total_records ┆ unique_records ┆ duplicate_records ┆ dup_rate_pct │
│ ---           ┆ ---            ┆ ---               ┆ ---          │
│ i64           ┆ i64            ┆ i64               ┆ f64          │
╞═══════════════╪════════════════╪═══════════════════╪══════════════╡
│ 800           ┆ 780            ┆ 20                ┆ 2.5          │
└───────────────┴────────────────┴───────────────────┴──────────────┘
↳ 1 行取得

③ 機械別 重複件数
── SQL ─────────────────────────────────────────
  WITH ranked AS (
      SELECT
          machine_id,
          ROW_NUMBER() OVER (
              PARTITION BY machine_id, log_date, shift_id
              ORDER BY log_id
          ) AS rn
      FROM production_log
  )
  SELECT machine_id, COUNT(*) AS dup_records
  FROM ranked
  WHERE rn > 1
  GROUP BY machine_id
  ORDER BY dup_records DESC
───────────────────────────────────────────────
shape: (5, 2)
┌────────────┬─────────────┐
│ machine_id ┆ dup_records │
│ ---        ┆ ---         │
│ str        ┆ i64         │
╞════════════╪═════════════╡
│ M002       ┆ 5           │
│ M001       ┆ 5           │
│ M003       ┆ 4           │
│ M005       ┆ 3           │
│ M004       ┆ 3           │
└────────────┴─────────────┘
↳ 5 行取得

結果の読み取り

• 重複レコードが検出された → 生成時に意図的に混入した 20 件が正しく検出できている
• 重複除去後のユニーク件数が「正しい」件数
• 実務では、本番集計の前に必ず重複チェック SQL を実行し、重複率をモニタリングする
• ROW_NUMBER() OVER (PARTITION BY ... ORDER BY log_id) の rn = 1 レコードのみで集計 VIEW を作ると、常に重複なしで分析できる環境 を整備できる

No.095：欠損データを検出する

実務での意味

製造業の稼働記録には、以下の理由で NULL（欠損値） が混入します：

• センサー障害による未記録
• 手入力フォームの記入漏れ
• システム移行時のデータ変換エラー

defect_qty が NULL のレコードで SUM(defect_qty) を実行すると、NULL は無視されるため：

$$\text{計算上の不良率} < \text{真の不良率} \quad \text{（過小評価）}$$

これは 品質問題の見落とし につながります。

分析・モデル化の考え方

COUNT(*) と COUNT(column名) の差が NULL 件数を表します：

$$\text{NULL件数} = \text{COUNT}(*) - \text{COUNT(col)}$$

計算式	意味
COUNT(*)	全行数（NULL を含む）
COUNT(col)	NULL を除いた行数
COUNT(*) - COUNT(col)	NULL の行数
NULL件数 / COUNT(*) × 100	NULL 率（%）

Python で確認する

print("=== No.095 欠損データを検出する ===\n")

# 列ごとの NULL 件数・NULL 率
print("① 列ごとの欠損値チェック")
df95 = q(
    conn,
    """
SELECT
    COUNT(*) AS total_rows,
    COUNT(*) - COUNT(machine_id)     AS null_machine_id,
    COUNT(*) - COUNT(shift_id)       AS null_shift_id,
    COUNT(*) - COUNT(log_date)       AS null_log_date,
    COUNT(*) - COUNT(production_qty) AS null_production_qty,
    COUNT(*) - COUNT(defect_qty)     AS null_defect_qty,
    COUNT(*) - COUNT(operator_id)    AS null_operator_id
FROM production_log
""",
)

# NULL率の計算
total = df95["total_rows"][0]
null_cols = {
    "machine_id": df95["null_machine_id"][0],
    "shift_id": df95["null_shift_id"][0],
    "log_date": df95["null_log_date"][0],
    "production_qty": df95["null_production_qty"][0],
    "defect_qty": df95["null_defect_qty"][0],
    "operator_id": df95["null_operator_id"][0],
}
print(f"\n② NULL率サマリー（全 {total:,} 件）")
for col, cnt in null_cols.items():
    rate = cnt / total * 100
    bar = "█" * int(rate * 2) if rate > 0 else ""
    print(f"  {col:16s}: {cnt:4d} 件 ({rate:5.1f}%) {bar}")

# NULL含む/除くで集計結果が変わることを示す
print("\n③ NULLの扱いによる集計の違い（defect_qty）")
q(
    conn,
    """
SELECT
    SUM(defect_qty)                AS sum_with_null_ignored,
    SUM(COALESCE(defect_qty, 0))   AS sum_with_null_as_zero,
    AVG(defect_qty)                AS avg_with_null_ignored,
    AVG(COALESCE(defect_qty, 0.0)) AS avg_with_null_as_zero
FROM production_log
""",
)

# グラフ: 列ごとの NULL 率（水平棒グラフ）
col_names = list(null_cols.keys())
null_rates = [v / total * 100 for v in null_cols.values()]
bar_colors = ["#e74c3c" if r > 0 else "#2ecc71" for r in null_rates]

fig, ax = plt.subplots(figsize=(9, 5))
bars = ax.barh(col_names[::-1], null_rates[::-1], color=bar_colors[::-1], edgecolor="white")
ax.set_title("列ごとの欠損率（%）（No.095：欠損データ検出）", fontsize=12)
ax.set_xlabel("NULL 率 (%)")
ax.set_ylabel("列名")
ax.axvline(0.1, color="gray", linestyle="--", linewidth=1, alpha=0.5)
ax.grid(axis="x", alpha=0.4)
for bar, rate in zip(bars, null_rates[::-1]):
    if rate > 0:
        ax.text(rate + 0.05, bar.get_y() + bar.get_height() / 2, f"{rate:.1f}%", va="center", fontsize=10)
    else:
        ax.text(0.05, bar.get_y() + bar.get_height() / 2, "0.0% ✅", va="center", fontsize=10, color="green")
plt.tight_layout()
plt.show()

=== No.095 欠損データを検出する ===

① 列ごとの欠損値チェック
── SQL ─────────────────────────────────────────
  SELECT
      COUNT(*) AS total_rows,
      COUNT(*) - COUNT(machine_id)     AS null_machine_id,
      COUNT(*) - COUNT(shift_id)       AS null_shift_id,
      COUNT(*) - COUNT(log_date)       AS null_log_date,
      COUNT(*) - COUNT(production_qty) AS null_production_qty,
      COUNT(*) - COUNT(defect_qty)     AS null_defect_qty,
      COUNT(*) - COUNT(operator_id)    AS null_operator_id
  FROM production_log
───────────────────────────────────────────────
shape: (1, 7)
┌────────────┬──────────────┬──────────────┬─────────────┬─────────────┬─────────────┬─────────────┐
│ total_rows ┆ null_machine ┆ null_shift_i ┆ null_log_da ┆ null_produc ┆ null_defect ┆ null_operat │
│ ---        ┆ _id          ┆ d            ┆ te          ┆ tion_qty    ┆ _qty        ┆ or_id       │
│ i64        ┆ ---          ┆ ---          ┆ ---         ┆ ---         ┆ ---         ┆ ---         │
│            ┆ i64          ┆ i64          ┆ i64         ┆ i64         ┆ i64         ┆ i64         │
╞════════════╪══════════════╪══════════════╪═════════════╪═════════════╪═════════════╪═════════════╡
│ 800        ┆ 0            ┆ 0            ┆ 0           ┆ 0           ┆ 15          ┆ 8           │
└────────────┴──────────────┴──────────────┴─────────────┴─────────────┴─────────────┴─────────────┘
↳ 1 行取得

② NULL率サマリー（全 800 件）
  machine_id      :    0 件 (  0.0%) 
  shift_id        :    0 件 (  0.0%) 
  log_date        :    0 件 (  0.0%) 
  production_qty  :    0 件 (  0.0%) 
  defect_qty      :   15 件 (  1.9%) ███
  operator_id     :    8 件 (  1.0%) ██

③ NULLの扱いによる集計の違い（defect_qty）
── SQL ─────────────────────────────────────────
  SELECT
      SUM(defect_qty)                AS sum_with_null_ignored,
      SUM(COALESCE(defect_qty, 0))   AS sum_with_null_as_zero,
      AVG(defect_qty)                AS avg_with_null_ignored,
      AVG(COALESCE(defect_qty, 0.0)) AS avg_with_null_as_zero
  FROM production_log
───────────────────────────────────────────────
shape: (1, 4)
┌───────────────────────┬───────────────────────┬───────────────────────┬───────────────────────┐
│ sum_with_null_ignored ┆ sum_with_null_as_zero ┆ avg_with_null_ignored ┆ avg_with_null_as_zero │
│ ---                   ┆ ---                   ┆ ---                   ┆ ---                   │
│ i64                   ┆ i64                   ┆ f64                   ┆ f64                   │
╞═══════════════════════╪═══════════════════════╪═══════════════════════╪═══════════════════════╡
│ 3528                  ┆ 3528                  ┆ 4.494268              ┆ 4.41                  │
└───────────────────────┴───────────────────────┴───────────────────────┴───────────────────────┘
↳ 1 行取得


/var/folders/3y/fmw40k0x78xblvb3gkcyvy1h0000gn/T/ipykernel_26783/4080718219.py:67: UserWarning: Glyph 9989 (\N{WHITE HEAVY CHECK MARK}) missing from font(s) Hiragino Maru Gothic Pro.
  plt.tight_layout()

結果の読み取り

• defect_qty と operator_id に NULL が存在する（意図的に混入）
• SUM(defect_qty) は NULL を無視するため、SUM(COALESCE(defect_qty, 0)) より少ない値になる → 不良率の過小評価につながる
• machine_id, shift_id, log_date, production_qty は NULL ゼロ → これらは必須項目として適切に管理されている
• 実務では NULL 率が閾値（例：1%）を超えたら自動アラートを出す仕組みを設けることを推奨する

No.096：インデックスの基本を理解する

実務での意味

インデックスは 「書籍の索引」 に相当します。
索引なしで本文を読む（フルスキャン）より、索引で目的のページを探す（インデックス検索）方が高速です。

製造業の稼働データは毎月数万件ずつ蓄積されます。インデックスがないと：

$$\text{検索時間} \approx O(n) \quad (\text{全行スキャン})$$

インデックスがあると：

$$\text{検索時間} \approx O(\log n) \quad (\text{B木による絞り込み})$$

分析・モデル化の考え方

インデックスを貼るべき列の目安：

ケース	理由
WHERE で頻繁に絞り込む列	フルスキャンを回避
JOIN ON で使う結合キー	結合コストを削減
ORDER BY に使う列	ソートコストを削減

インデックスを貼るべきでない列：

• カーディナリティが低い列（例：shift_id は 3 種類しかない）
• 更新頻度が非常に高い列（書き込みコストが上がる）

Python で確認する

print("=== No.096 インデックスの基本を理解する ===\n")

# 現在のインデックスを確認
print("① 現在のインデックス一覧")
idxs = conn.execute("SELECT name, tbl_name, sql FROM sqlite_master WHERE type='index'").fetchall()
if idxs:
    for idx in idxs:
        print(f"  {idx[0]:20s} → {idx[1]}")
else:
    print("  （インデックスなし）")

# インデックスなしでの実行計画
print("\n② インデックスなし: EXPLAIN QUERY PLAN")
plan_no = conn.execute("""
    EXPLAIN QUERY PLAN
    SELECT * FROM production_log
    WHERE machine_id = 'M003' AND log_date >= '2024-07-01'
""").fetchall()
for row in plan_no:
    print(f"  {row}")

# machine_id インデックスを作成
print("\n③ インデックス作成: CREATE INDEX")
conn.execute("DROP INDEX IF EXISTS idx_machine_id")
conn.execute("DROP INDEX IF EXISTS idx_log_date")
conn.execute("CREATE INDEX idx_machine_id ON production_log(machine_id)")
conn.execute("CREATE INDEX idx_log_date   ON production_log(log_date)")
print("  CREATE INDEX idx_machine_id ON production_log(machine_id) ... 完了")
print("  CREATE INDEX idx_log_date   ON production_log(log_date)   ... 完了")

# インデックスありでの実行計画
print("\n④ インデックスあり: EXPLAIN QUERY PLAN")
plan_with = conn.execute("""
    EXPLAIN QUERY PLAN
    SELECT * FROM production_log
    WHERE machine_id = 'M003' AND log_date >= '2024-07-01'
""").fetchall()
for row in plan_with:
    print(f"  {row}")

# 複合インデックスの追加
conn.execute("CREATE INDEX idx_machine_date ON production_log(machine_id, log_date)")
print("\n⑤ 複合インデックス追加後: EXPLAIN QUERY PLAN")
plan_comp = conn.execute("""
    EXPLAIN QUERY PLAN
    SELECT * FROM production_log
    WHERE machine_id = 'M003' AND log_date >= '2024-07-01'
""").fetchall()
for row in plan_comp:
    print(f"  {row}")

# 現在のインデックス確認
print("\n⑥ 作成後のインデックス一覧")
idxs2 = conn.execute("SELECT name, tbl_name FROM sqlite_master WHERE type='index'").fetchall()
for idx in idxs2:
    print(f"  {idx[0]:30s} → {idx[1]}")

=== No.096 インデックスの基本を理解する ===

① 現在のインデックス一覧
  sqlite_autoindex_machines_1 → machines

② インデックスなし: EXPLAIN QUERY PLAN
  (2, 0, 216, 'SCAN production_log')

③ インデックス作成: CREATE INDEX
  CREATE INDEX idx_machine_id ON production_log(machine_id) ... 完了
  CREATE INDEX idx_log_date   ON production_log(log_date)   ... 完了

④ インデックスあり: EXPLAIN QUERY PLAN
  (3, 0, 62, 'SEARCH production_log USING INDEX idx_machine_id (machine_id=?)')

⑤ 複合インデックス追加後: EXPLAIN QUERY PLAN
  (3, 0, 51, 'SEARCH production_log USING INDEX idx_machine_date (machine_id=? AND log_date>?)')

⑥ 作成後のインデックス一覧
  sqlite_autoindex_machines_1    → machines
  idx_machine_id                 → production_log
  idx_log_date                   → production_log
  idx_machine_date               → production_log

結果の読み取り

• インデックスなし → SCAN production_log（全行スキャン）
• インデックスあり → SEARCH production_log USING INDEX（インデックス検索）
• 複合インデックス (machine_id, log_date) を使うと、WHERE machine_id = ? AND log_date >= ? のように 両方を同時に絞り込める
• 実務では大規模テーブルの WHERE 句と JOIN ON には 必ずインデックスを設定する
• SQLite の EXPLAIN QUERY PLAN、MySQL の EXPLAIN、PostgreSQL の EXPLAIN ANALYZE で実行計画を確認できる

No.097：実行計画の見方を理解する

実務での意味

「SQL が遅い」と言われたとき、実行計画を読めるかどうかがボトルネック特定の鍵です。
実行計画は「SQL エンジンが内部でどのようにクエリを実行するか」を示す設計図です。

分析・モデル化の考え方

SQLite の EXPLAIN QUERY PLAN の読み方：

キーワード	意味	速度
SCAN テーブル名	全行スキャン（最も遅い）	$O(n)$
SEARCH テーブル名 USING INDEX	インデックス検索	$O(\log n)$
SEARCH テーブル名 USING COVERING INDEX	カバリングインデックス（最速）	$O(\log n)$、追加アクセスなし

JOIN の実行計画を読む：

外側のループが「driving table」です。小さいテーブル（machines: 5件）が outer になれば効率的です。

Python で確認する

print("=== No.097 実行計画の見方を理解する ===\n")

# パターン①: 単純なフルスキャン
print("① フルスキャン（インデックスなし列で絞り込み）")
p1 = conn.execute("""
    EXPLAIN QUERY PLAN
    SELECT * FROM production_log WHERE shift_id = 'S02'
""").fetchall()
for r in p1:
    print(f"  {r}")
# shift_id はカーディナリティが低く、インデックスを貼っていないのでSCAN

# パターン②: インデックス検索
print("\n② インデックス検索（machine_id + log_date）")
p2 = conn.execute("""
    EXPLAIN QUERY PLAN
    SELECT log_id, machine_id, production_qty
    FROM production_log
    WHERE machine_id = 'M001'
      AND log_date BETWEEN '2024-03-01' AND '2024-03-31'
""").fetchall()
for r in p2:
    print(f"  {r}")

# パターン③: JOIN の実行計画
print("\n③ JOIN の実行計画（小テーブル × 大テーブル）")
p3 = conn.execute("""
    EXPLAIN QUERY PLAN
    SELECT p.log_date, m.machine_name, p.production_qty
    FROM production_log AS p
    JOIN machines       AS m ON p.machine_id = m.machine_id
    WHERE p.log_date >= '2024-10-01'
""").fetchall()
for r in p3:
    print(f"  {r}")

# パターン④: GROUP BY の実行計画
print("\n④ GROUP BY の実行計画")
p4 = conn.execute("""
    EXPLAIN QUERY PLAN
    SELECT machine_id, strftime('%Y-%m', log_date) AS ym,
           SUM(production_qty)
    FROM production_log
    GROUP BY machine_id, ym
    ORDER BY machine_id, ym
""").fetchall()
for r in p4:
    print(f"  {r}")

print("\n【解説】")
print("  SCAN   = フルスキャン（遅い可能性あり）")
print("  SEARCH = インデックス検索（高速）")
print("  USING COVERING INDEX = 追加ルックアップ不要（最速）")

=== No.097 実行計画の見方を理解する ===

① フルスキャン（インデックスなし列で絞り込み）
  (2, 0, 216, 'SCAN production_log')

② インデックス検索（machine_id + log_date）
  (3, 0, 49, 'SEARCH production_log USING INDEX idx_machine_date (machine_id=? AND log_date>? AND log_date<?)')

③ JOIN の実行計画（小テーブル × 大テーブル）
  (5, 0, 204, 'SEARCH p USING INDEX idx_log_date (log_date>?)')
  (9, 0, 47, 'SEARCH m USING INDEX sqlite_autoindex_machines_1 (machine_id=?)')

④ GROUP BY の実行計画
  (8, 0, 224, 'SCAN production_log USING INDEX idx_machine_id')
  (11, 0, 0, 'USE TEMP B-TREE FOR GROUP BY')

【解説】
  SCAN   = フルスキャン（遅い可能性あり）
  SEARCH = インデックス検索（高速）
  USING COVERING INDEX = 追加ルックアップ不要（最速）

結果の読み取り

• shift_id の絞り込みは SCAN（インデックスなし列）→ 全行を確認している
• machine_id と log_date の絞り込みは SEARCH USING INDEX → 複合インデックスが効いている
• JOIN では machines（5件）が先に SCAN される → 小テーブル先読みの効率的なパターン
• GROUP BY + ORDER BY でも USING COVERING INDEX が活用されることがある
• 実行計画に SCAN が出たら、WHERE に使っている列にインデックスが必要かを検討する

No.098：重いSQLを改善する

実務での意味

製造業の月次レポートで「SQL が遅すぎる」問題は非常によく起きます。
主な原因と対策をパターン化することで、性能改善を体系的に行えます。

分析・モデル化の考え方

代表的な 3 つの改善パターン：

パターン	問題のある書き方	改善後	効果
① SELECT * の廃止	SELECT *	必要な列だけ選択	転送データ量を削減
② IN (サブクエリ) → JOIN	WHERE id IN (SELECT...)	JOIN に書き直す	実行計画が最適化されやすい
③ 早期フィルタリング	結合後に WHERE	WHERE を先に適用	結合対象行数を削減

Python で確認する

print("=== No.098 重いSQLを改善する ===\n")

# ─── パターン①: SELECT * の廃止 ─────────────────────────
print("【パターン①】SELECT * の廃止\n")

bad_p1 = """SELECT * FROM production_log WHERE machine_id = 'M001' """
good_p1 = """
SELECT log_id, machine_id, log_date, production_qty, defect_qty
FROM production_log
WHERE machine_id = 'M001'
"""

bad_cols = len(conn.execute(bad_p1).description)
good_cols = len(conn.execute(good_p1).description)
bad_rows = len(conn.execute(bad_p1).fetchall())
good_rows = len(conn.execute(good_p1).fetchall())

print(f"  SELECT *          : 列数={bad_cols}、行数={bad_rows}")
print(f"  SELECT 必要な列   : 列数={good_cols}、行数={good_rows}")
print(f"  取得列数を {bad_cols - good_cols} 列削減（不要な列 shift_id, operator_id を除外）")

# ─── パターン②: IN(サブクエリ) → JOIN ──────────────────────
print("\n【パターン②】IN(サブクエリ) → JOIN への変換\n")

bad_p2 = """
SELECT log_id, machine_id, log_date, production_qty
FROM production_log
WHERE machine_id IN (
    SELECT machine_id FROM machines WHERE category = '機械加工'
)
"""
good_p2 = """
SELECT p.log_id, p.machine_id, p.log_date, p.production_qty
FROM production_log AS p
JOIN machines       AS m ON p.machine_id = m.machine_id
WHERE m.category = '機械加工'
"""

print("  悪い例（IN サブクエリ）の実行計画:")
for r in conn.execute(f"EXPLAIN QUERY PLAN {bad_p2}").fetchall():
    print(f"    {r}")

print("\n  良い例（JOIN）の実行計画:")
for r in conn.execute(f"EXPLAIN QUERY PLAN {good_p2}").fetchall():
    print(f"    {r}")

r_bad = len(conn.execute(bad_p2).fetchall())
r_good = len(conn.execute(good_p2).fetchall())
print(f"\n  結果の一致確認: bad={r_bad}件 / good={r_good}件 → {'✅' if r_bad == r_good else '❌'}")

# ─── パターン③: 早期フィルタリング ───────────────────────────
print("\n【パターン③】早期フィルタリング（WHERE を JOIN の前に）\n")

bad_p3 = """
SELECT p.machine_id, m.machine_name, SUM(p.production_qty) AS total
FROM production_log AS p
JOIN machines AS m ON p.machine_id = m.machine_id
GROUP BY p.machine_id
"""
good_p3 = """
SELECT p.machine_id, m.machine_name, SUM(p.production_qty) AS total
FROM production_log AS p
JOIN machines AS m ON p.machine_id = m.machine_id
WHERE p.log_date >= '2024-10-01'
GROUP BY p.machine_id
"""
# ※悪い例は WHERE がなく全期間を集計しているが、
#   本来 "Q4 のみ集計したい" 要件なら WHERE で先に絞るべき

q4_rows = conn.execute("SELECT COUNT(*) FROM production_log WHERE log_date >= '2024-10-01'").fetchone()[0]
all_rows = conn.execute("SELECT COUNT(*) FROM production_log").fetchone()[0]
print(f"  全期間の行数: {all_rows:,}")
print(f"  Q4 のみ絞り込み後: {q4_rows:,} 行（{q4_rows/all_rows*100:.1f}%に削減）")
print(f"  WHERE で先に絞ることで、JOIN・GROUP BY の対象行数を {all_rows - q4_rows:,} 行削減")

print("\n  Q4集計結果（早期フィルタリング後）:")
df98 = q(conn, good_p3)

=== No.098 重いSQLを改善する ===

【パターン①】SELECT * の廃止

  SELECT *          : 列数=7、行数=160
  SELECT 必要な列   : 列数=5、行数=160
  取得列数を 2 列削減（不要な列 shift_id, operator_id を除外）

【パターン②】IN(サブクエリ) → JOIN への変換

  悪い例（IN サブクエリ）の実行計画:
    (3, 0, 108, 'SEARCH production_log USING INDEX idx_machine_date (machine_id=?)')
    (7, 0, 0, 'LIST SUBQUERY 1')
    (10, 7, 216, 'SCAN machines')
    (18, 7, 0, 'CREATE BLOOM FILTER')

  良い例（JOIN）の実行計画:
    (4, 0, 216, 'SCAN m')
    (8, 0, 62, 'SEARCH p USING INDEX idx_machine_date (machine_id=?)')

  結果の一致確認: bad=318件 / good=318件 → ✅

【パターン③】早期フィルタリング（WHERE を JOIN の前に）

  全期間の行数: 800
  Q4 のみ絞り込み後: 218 行（27.3%に削減）
  WHERE で先に絞ることで、JOIN・GROUP BY の対象行数を 582 行削減

  Q4集計結果（早期フィルタリング後）:
── SQL ─────────────────────────────────────────
  SELECT p.machine_id, m.machine_name, SUM(p.production_qty) AS total
  FROM production_log AS p
  JOIN machines AS m ON p.machine_id = m.machine_id
  WHERE p.log_date >= '2024-10-01'
  GROUP BY p.machine_id
───────────────────────────────────────────────
shape: (5, 3)
┌────────────┬──────────────┬───────┐
│ machine_id ┆ machine_name ┆ total │
│ ---        ┆ ---          ┆ ---   │
│ str        ┆ str          ┆ i64   │
╞════════════╪══════════════╪═══════╡
│ M001       ┆ 旋盤A        ┆ 13793 │
│ M002       ┆ 旋盤B        ┆ 11163 │
│ M003       ┆ 溶接機C      ┆ 9115  │
│ M004       ┆ プレス機D    ┆ 19210 │
│ M005       ┆ 組立ラインE  ┆ 7180  │
└────────────┴──────────────┴───────┘
↳ 5 行取得

結果の読み取り

• SELECT * の廃止: 不要な列（shift_id・operator_id）を除外することで、転送データ量を削減
• IN(サブクエリ) → JOIN: 実行計画が SEARCH USING COVERING INDEX を活用しやすくなる
• 早期フィルタリング: Q4 のみ絞り込むことで JOIN・GROUP BY の対象行数を大幅削減
• 実務では 「遅い SQL を特定 → EXPLAIN で確認 → 上記 3 パターンで改善」 という手順が標準的
• 大規模 DWH（BigQuery / Snowflake）では列指定と早期フィルタリングの効果が特に大きい（課金コスト削減にも直結）

No.099：BIダッシュボード用の集計テーブルを設計する

実務での意味

Power BI・Tableau・Looker などの BI ツールは、大量の生データに直接クエリを投げると重くなります。
事前に集計テーブル（サマリーテーブル）を作成・更新する設計が、製造業の BI 活用の実務標準です。

分析・モデル化の考え方

設計の基本は 「よく使う集計を事前に計算して保存する」 ことです：

$$\text{BI クエリ時間} \approx O\left(\frac{1}{\text{事前集計率}}\right)$$

事前集計されているほど、BI クエリは高速になります。

集計テーブルの種類	集計粒度	用途
daily_kpi	日次 × 機械カテゴリ	日次ダッシュボード
monthly_kpi	月次 × 機械	月次報告書
machine_summary	機械別 年間	KPI ランキング

Python で確認する

print("=== No.099 BIダッシュボード用の集計テーブルを設計する ===\n")

# ── daily_kpi テーブル作成 ─────────────────────────────────
conn.execute("DROP TABLE IF EXISTS daily_kpi")
conn.execute("""
CREATE TABLE daily_kpi AS
SELECT
    p.log_date,
    m.category                                     AS machine_category,
    COUNT(p.log_id)                                AS record_count,
    SUM(p.production_qty)                          AS total_qty,
    SUM(COALESCE(p.defect_qty, 0))                 AS total_defects,
    ROUND(
        SUM(COALESCE(p.defect_qty, 0)) * 100.0
        / NULLIF(SUM(p.production_qty), 0), 2
    )                                              AS defect_rate
FROM production_log AS p
JOIN machines AS m ON p.machine_id = m.machine_id
GROUP BY p.log_date, m.category
""")

n_daily = conn.execute("SELECT COUNT(*) FROM daily_kpi").fetchone()[0]
n_prod = conn.execute("SELECT COUNT(*) FROM production_log").fetchone()[0]
print(f"production_log の行数: {n_prod:,} 件")
print(f"daily_kpi の行数    : {n_daily:,} 件（{n_prod/n_daily:.1f}x 圧縮）")

# ── monthly_kpi ビュー作成 ─────────────────────────────────
conn.execute("DROP VIEW IF EXISTS monthly_kpi")
conn.execute("""
CREATE VIEW monthly_kpi AS
SELECT
    strftime('%Y-%m', log_date) AS ym,
    machine_category,
    SUM(record_count)           AS record_count,
    SUM(total_qty)              AS total_qty,
    SUM(total_defects)          AS total_defects,
    ROUND(
        SUM(total_defects) * 100.0
        / NULLIF(SUM(total_qty), 0), 2
    )                           AS defect_rate
FROM daily_kpi
GROUP BY ym, machine_category
""")
print("monthly_kpi VIEW 作成完了")

# ── BI クエリ: 月次不良率推移 ──────────────────────────────
print("\n月次不良率推移（monthly_kpi から高速取得）:")
df99 = q(
    conn,
    """
SELECT ym, machine_category, defect_rate
FROM monthly_kpi
ORDER BY ym, machine_category
""",
)

# グラフ: カテゴリ別 月次不良率推移（折れ線グラフ）
categories = df99["machine_category"].unique().to_list()
yms = sorted(df99["ym"].unique().to_list())
CAT_COLORS = {"機械加工": "#3498db", "溶接": "#e74c3c", "プレス": "#2ecc71", "組立": "#f39c12"}
MARKERS = {"機械加工": "o", "溶接": "s", "プレス": "^", "組立": "D"}

fig, ax = plt.subplots(figsize=(12, 5))
for cat in categories:
    sub = df99.filter(pl.col("machine_category") == cat).sort("ym")
    cat_yms = sub["ym"].to_list()
    cat_rates = sub["defect_rate"].to_list()
    ax.plot(
        cat_yms,
        cat_rates,
        color=CAT_COLORS.get(cat, "#999"),
        marker=MARKERS.get(cat, "o"),
        linewidth=2,
        label=cat,
        markersize=6,
    )

ax.axhline(2.0, color="gray", linestyle="--", linewidth=1, label="管理基準（2.0%）")
ax.set_title("カテゴリ別 月次不良率推移（No.099：daily_kpi → monthly_kpi）", fontsize=12)
ax.set_xlabel("年月")
ax.set_ylabel("不良率（%）")
ax.legend(fontsize=9, loc="upper right")
ax.grid(alpha=0.3)
plt.xticks(rotation=45)
plt.tight_layout()
plt.show()

=== No.099 BIダッシュボード用の集計テーブルを設計する ===

production_log の行数: 800 件
daily_kpi の行数    : 700 件（1.1x 圧縮）
monthly_kpi VIEW 作成完了

月次不良率推移（monthly_kpi から高速取得）:
── SQL ─────────────────────────────────────────
  SELECT ym, machine_category, defect_rate
  FROM monthly_kpi
  ORDER BY ym, machine_category
───────────────────────────────────────────────
shape: (48, 3)
┌─────────┬──────────────────┬─────────────┐
│ ym      ┆ machine_category ┆ defect_rate │
│ ---     ┆ ---              ┆ ---         │
│ str     ┆ str              ┆ f64         │
╞═════════╪══════════════════╪═════════════╡
│ 2024-01 ┆ プレス           ┆ 1.5         │
│ 2024-01 ┆ 機械加工         ┆ 1.65        │
│ 2024-01 ┆ 溶接             ┆ 1.28        │
│ 2024-01 ┆ 組立             ┆ 1.45        │
│ 2024-02 ┆ プレス           ┆ 1.75        │
│ …       ┆ …                ┆ …           │
│ 2024-11 ┆ 組立             ┆ 1.53        │
│ 2024-12 ┆ プレス           ┆ 1.75        │
│ 2024-12 ┆ 機械加工         ┆ 1.7         │
│ 2024-12 ┆ 溶接             ┆ 1.49        │
│ 2024-12 ┆ 組立             ┆ 1.53        │
└─────────┴──────────────────┴─────────────┘
↳ 48 行取得

結果の読み取り

• production_log（約820件）→ daily_kpi（小さいテーブル）へ圧縮 → BI クエリが高速化される
• CREATE TABLE AS SELECT は事前集計の最もシンプルな方法。定期バッチ（毎日0時）で再作成するのが一般的
• CREATE VIEW は事前集計はしないが、BI ツールからシンプルなテーブル名でアクセスできる
• 折れ線グラフで管理基準（2.0%）との比較が一目で分かる → 翌朝の会議で即座に使えるダッシュボード設計
• 実務では daily_kpi を日次バッチで更新し、monthly_kpi ビューを Power BI / Tableau に接続する

No.100：SQLを使ったデータ分析プロジェクトの流れを整理する

実務での意味

SQL 100本ノックのゴールは「SQL が書ける」ではなく、「SQL を使ってビジネス上の意思決定を支援できる」 ことです。

第10章で学んだことを踏まえ、製造業のデータ分析プロジェクト全体像 を整理します。

分析・モデル化の考え方

データ分析プロジェクトは以下の 5 フェーズで進みます：

$$\underbrace{\text{課題定義}}_{\text{①}} \rightarrow \underbrace{\text{データ収集・品質確認}}_{\text{②③}} \rightarrow \underbrace{\text{分析・可視化}}_{\text{④}} \rightarrow \underbrace{\text{運用・改善}}_{\text{⑤}}$$

各フェーズで使う SQL パターンは、これまでの 9 章で網羅しました。

Python で確認する

print("=== No.100 SQLを使ったデータ分析プロジェクトの流れを整理する ===\n")

# ── プロジェクトフロー: Polars DataFrame として整理 ──────────
flow_data = {
    "フェーズ": [
        "①課題定義",
        "②データ収集",
        "③データ品質確認",
        "③データ品質確認",
        "③データ品質確認",
        "④分析・集計",
        "④分析・集計",
        "④分析・集計",
        "④分析・集計",
        "⑤可視化・運用",
        "⑤可視化・運用",
    ],
    "作業内容": [
        "分析目的・KPIを定義する",
        "テーブル構造・データ量を把握する（SELECT COUNT, DESCRIBE）",
        "重複データを検出・除去する（No.094）",
        "欠損データを検出・対処する（No.095）",
        "集計結果の検算・バリデーション（No.093）",
        "KPI集計（GROUP BY, SUM, AVG）（No.021〜030）",
        "時系列分析（日別・月別・前月比）（No.031〜040, 067〜070）",
        "顧客/機械セグメント分析（RFM, JOIN）（No.041〜050, 077）",
        "サブクエリ・CTEで複雑な集計を整理する（No.051〜060）",
        "BIダッシュボード用集計テーブルを設計・作成（No.099）",
        "インデックス・実行計画でパフォーマンスを最適化（No.096〜098）",
    ],
    "SQLパターン": [
        "—",
        "SELECT COUNT(*), PRAGMA table_info",
        "GROUP BY + HAVING, ROW_NUMBER",
        "COUNT(*) - COUNT(col), COALESCE",
        "部分和 = 全体和, MIN/MAX チェック",
        "GROUP BY + 集計関数",
        "strftime + LAG + 移動平均",
        "JOIN + CASE WHEN",
        "WITH ... AS (CTE)",
        "CREATE TABLE AS SELECT, CREATE VIEW",
        "CREATE INDEX, EXPLAIN QUERY PLAN",
    ],
    "対応章": [
        "—",
        "第1〜2章",
        "第10章",
        "第10章",
        "第10章",
        "第3章",
        "第4・7章",
        "第5・8章",
        "第6章",
        "第10章",
        "第10章",
    ],
}

df100_flow = pl.DataFrame(flow_data)
print("製造業 データ分析プロジェクト 標準フロー")
print("=" * 80)
with pl.Config(tbl_rows=20, tbl_width_chars=120):
    print(df100_flow)

# ── 最終統合クエリ: 全章の要素を組み合わせた品質管理ダッシュボードSQL ──
print("\n\n最終統合クエリ: 機械別 品質 KPI サマリー（全章の知識を統合）")
df100_final = q(
    conn,
    """
-- ================================================================
-- 最終統合クエリ: 機械別 年間品質 KPI ダッシュボード（No.100）
-- 使用技術: CTE / JOIN / GROUP BY / COALESCE / CASE / ウィンドウ関数
-- ================================================================
WITH
-- ① 重複除去した稼働レコード（No.094）
deduped AS (
    SELECT *
    FROM (
        SELECT
            *,
            ROW_NUMBER() OVER (
                PARTITION BY machine_id, log_date, shift_id
                ORDER BY log_id
            ) AS rn
        FROM production_log
    )
    WHERE rn = 1
),

-- ② 機械別 年間集計（No.093 の検算対象）
machine_stats AS (
    SELECT
        d.machine_id,
        m.machine_name,
        m.category,
        COUNT(d.log_id)                             AS record_count,
        SUM(d.production_qty)                       AS total_qty,
        SUM(COALESCE(d.defect_qty, 0))              AS total_defects,
        COUNT(*) - COUNT(d.defect_qty)              AS null_defect_cnt,
        ROUND(
            SUM(COALESCE(d.defect_qty, 0)) * 100.0
            / NULLIF(SUM(d.production_qty), 0), 2
        )                                           AS defect_rate
    FROM deduped AS d
    JOIN machines AS m ON d.machine_id = m.machine_id
    GROUP BY d.machine_id
)
SELECT
    machine_id,
    machine_name,
    category,
    record_count,
    total_qty,
    total_defects,
    null_defect_cnt,
    defect_rate,
    RANK() OVER (ORDER BY defect_rate DESC) AS defect_rank,
    CASE
        WHEN defect_rate >= 2.5 THEN '要改善'
        WHEN defect_rate >= 1.5 THEN '要注意'
        ELSE '良好'
    END AS kpi_status
FROM machine_stats
ORDER BY defect_rate DESC
""",
)

# 完了メッセージ
print()
print("=" * 60)
print("  SQL 100本ノック 全100問 完走！")
print("  第1章〜第10章 お疲れさまでした。")
print("=" * 60)
print()
total_sum = conn.execute("SELECT SUM(production_qty) FROM production_log").fetchone()[0]
print(f"  分析した製造データ: {total_sum:,} 個の生産記録")
print(f"  構築したテーブル  : machines / production_log / defects / daily_kpi")
print(f"  構築したビュー    : monthly_kpi")
print(f"  作成したインデックス: idx_machine_id / idx_log_date / idx_machine_date")

=== No.100 SQLを使ったデータ分析プロジェクトの流れを整理する ===

製造業 データ分析プロジェクト 標準フロー
================================================================================
shape: (11, 4)
┌─────────────────┬─────────────────────────────────┬─────────────────────────────────┬──────────┐
│ フェーズ        ┆ 作業内容                        ┆ SQLパターン                     ┆ 対応章   │
│ ---             ┆ ---                             ┆ ---                             ┆ ---      │
│ str             ┆ str                             ┆ str                             ┆ str      │
╞═════════════════╪═════════════════════════════════╪═════════════════════════════════╪══════════╡
│ ①課題定義       ┆ 分析目的・KPIを定義する         ┆ —                               ┆ —        │
│ ②データ収集     ┆ テーブル構造・データ量を把握す  ┆ SELECT COUNT(*), PRAGMA table_… ┆ 第1〜2章 │
│                 ┆ る（SELECT COUNT,…              ┆                                 ┆          │
│ ③データ品質確認 ┆ 重複データを検出・除去する（No. ┆ GROUP BY + HAVING, ROW_NUMBER   ┆ 第10章   │
│                 ┆ 094）                           ┆                                 ┆          │
│ ③データ品質確認 ┆ 欠損データを検出・対処する（No. ┆ COUNT(*) - COUNT(col), COALESC… ┆ 第10章   │
│                 ┆ 095）                           ┆                                 ┆          │
│ ③データ品質確認 ┆ 集計結果の検算・バリデーション  ┆ 部分和 = 全体和, MIN/MAX        ┆ 第10章   │
│                 ┆ （No.093）                      ┆ チェック                        ┆          │
│ ④分析・集計     ┆ KPI集計（GROUP BY, SUM,         ┆ GROUP BY + 集計関数             ┆ 第3章    │
│                 ┆ AVG）（No.0…                    ┆                                 ┆          │
│ ④分析・集計     ┆ 時系列分析（日別・月別・前月比  ┆ strftime + LAG + 移動平均       ┆ 第4・7章 │
│                 ┆ ）（No.031〜040, 0…             ┆                                 ┆          │
│ ④分析・集計     ┆ 顧客/機械セグメント分析（RFM,   ┆ JOIN + CASE WHEN                ┆ 第5・8章 │
│                 ┆ JOIN）（No.041…                 ┆                                 ┆          │
│ ④分析・集計     ┆ サブクエリ・CTEで複雑な集計を整 ┆ WITH ... AS (CTE)               ┆ 第6章    │
│                 ┆ 理する（No.051〜06…             ┆                                 ┆          │
│ ⑤可視化・運用   ┆ BIダッシュボード用集計テーブル  ┆ CREATE TABLE AS SELECT, CREATE… ┆ 第10章   │
│                 ┆ を設計・作成（No.099）          ┆                                 ┆          │
│ ⑤可視化・運用   ┆ インデックス・実行計画でパフォ  ┆ CREATE INDEX, EXPLAIN QUERY PL… ┆ 第10章   │
│                 ┆ ーマンスを最適化（No.096…       ┆                                 ┆          │
└─────────────────┴─────────────────────────────────┴─────────────────────────────────┴──────────┘


最終統合クエリ: 機械別 品質 KPI サマリー（全章の知識を統合）
── SQL ─────────────────────────────────────────
  -- ================================================================
  -- 最終統合クエリ: 機械別 年間品質 KPI ダッシュボード（No.100）
  -- 使用技術: CTE / JOIN / GROUP BY / COALESCE / CASE / ウィンドウ関数
  -- ================================================================
  WITH
  -- ① 重複除去した稼働レコード（No.094）
  deduped AS (
      SELECT *
      FROM (
          SELECT
              *,
              ROW_NUMBER() OVER (
                  PARTITION BY machine_id, log_date, shift_id
                  ORDER BY log_id
              ) AS rn
          FROM production_log
      )
      WHERE rn = 1
  ),
  
  -- ② 機械別 年間集計（No.093 の検算対象）
  machine_stats AS (
      SELECT
          d.machine_id,
          m.machine_name,
          m.category,
          COUNT(d.log_id)                             AS record_count,
          SUM(d.production_qty)                       AS total_qty,
          SUM(COALESCE(d.defect_qty, 0))              AS total_defects,
          COUNT(*) - COUNT(d.defect_qty)              AS null_defect_cnt,
          ROUND(
              SUM(COALESCE(d.defect_qty, 0)) * 100.0
              / NULLIF(SUM(d.production_qty), 0), 2
          )                                           AS defect_rate
      FROM deduped AS d
      JOIN machines AS m ON d.machine_id = m.machine_id
      GROUP BY d.machine_id
  )
  SELECT
      machine_id,
      machine_name,
      category,
      record_count,
      total_qty,
      total_defects,
      null_defect_cnt,
      defect_rate,
      RANK() OVER (ORDER BY defect_rate DESC) AS defect_rank,
      CASE
          WHEN defect_rate >= 2.5 THEN '要改善'
          WHEN defect_rate >= 1.5 THEN '要注意'
          ELSE '良好'
      END AS kpi_status
  FROM machine_stats
  ORDER BY defect_rate DESC
───────────────────────────────────────────────
shape: (5, 10)
┌───────────┬───────────┬──────────┬───────────┬───┬───────────┬───────────┬───────────┬───────────┐
│ machine_i ┆ machine_n ┆ category ┆ record_co ┆ … ┆ null_defe ┆ defect_ra ┆ defect_ra ┆ kpi_statu │
│ d         ┆ ame       ┆ ---      ┆ unt       ┆   ┆ ct_cnt    ┆ te        ┆ nk        ┆ s         │
│ ---       ┆ ---       ┆ str      ┆ ---       ┆   ┆ ---       ┆ ---       ┆ ---       ┆ ---       │
│ str       ┆ str       ┆          ┆ i64       ┆   ┆ i64       ┆ f64       ┆ i64       ┆ str       │
╞═══════════╪═══════════╪══════════╪═══════════╪═══╪═══════════╪═══════════╪═══════════╪═══════════╡
│ M004      ┆ プレス機D ┆ プレス   ┆ 167       ┆ … ┆ 1         ┆ 1.65      ┆ 1         ┆ 要注意    │
│ M002      ┆ 旋盤B     ┆ 機械加工 ┆ 153       ┆ … ┆ 2         ┆ 1.64      ┆ 2         ┆ 要注意    │
│ M001      ┆ 旋盤A     ┆ 機械加工 ┆ 155       ┆ … ┆ 5         ┆ 1.59      ┆ 3         ┆ 要注意    │
│ M003      ┆ 溶接機C   ┆ 溶接     ┆ 157       ┆ … ┆ 5         ┆ 1.56      ┆ 4         ┆ 要注意    │
│ M005      ┆ 組立ライ  ┆ 組立     ┆ 148       ┆ … ┆ 2         ┆ 1.5       ┆ 5         ┆ 要注意    │
│           ┆ ンE       ┆          ┆           ┆   ┆           ┆           ┆           ┆           │
└───────────┴───────────┴──────────┴───────────┴───┴───────────┴───────────┴───────────┴───────────┘
↳ 5 行取得

============================================================
  SQL 100本ノック 全100問 完走！
  第1章〜第10章 お疲れさまでした。
============================================================

  分析した製造データ: 219,758 個の生産記録
  構築したテーブル  : machines / production_log / defects / daily_kpi
  構築したビュー    : monthly_kpi
  作成したインデックス: idx_machine_id / idx_log_date / idx_machine_date

結果の読み取り

• KPI ステータス が「⚠️ 要改善」の機械が分かる → 即座に対応すべきライン
• null_defect_cnt > 0 の機械は データ記録漏れがある → 現場へのフィードバックが必要
• defect_rank と kpi_status で 機械の優先度が一目で判断できる
• 最終統合クエリは CTE（No.058〜060）、ROW_NUMBER（No.062）、RANK（No.063）、COALESCE（No.020）、CASE（No.040）、ウィンドウ関数（第7章）など、全章の要素を統合している

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

第10章で習得した「SQL エンジニアリング」の視点

能力	習得技術	製造業での価値
可読性	フォーマット・コメント（No.091〜092）	引き継ぎ可能な資産化
信頼性	検算・重複検出・欠損検出（No.093〜095）	誤った意思決定を防ぐ
性能	インデックス・実行計画・SQL改善（No.096〜098）	朝会議に間に合うレポート
設計	集計テーブル・プロジェクト設計（No.099〜100）	BI ツールとの連携基盤

SQL 100本ノック 全章の振り返り

章	テーマ	製造業での位置づけ
第1〜2章	SELECT・WHERE	データを「読む」能力
第3章	集計	KPI を「計算する」能力
第4章	日付・文字列	データを「加工する」能力
第5章	JOIN	複数テーブルを「つなぐ」能力
第6章	サブクエリ・CTE	「複雑な分析」を整理する能力
第7章	ウィンドウ関数	「時系列・ランキング」を扱う能力
第8章	実務データ分析	「受注・在庫・顧客」を分析する能力
第9章	応用分析	「コホート・ファネル・ML特徴量」を作る能力
第10章	実務運用	「本番で安定稼働させる」能力

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

1. SQL 品質管理の仕組みを作る

・sqlfluff（SQL リンター）を CI/CD に組み込む
・SQL レビューチェックリストを作成する
・コメントテンプレートを標準化する

2. データ品質監視の自動化

・重複チェック SQL を毎朝定期実行
・欠損率が閾値（例：1%）を超えたらアラート
・集計レポートの検算 SQL を自動実行

3. BI 連携の設計

ステップ	内容
事前集計テーブルの設計	daily_kpi・monthly_kpi の粒度・列を決める
バッチ更新の自動化	cron / Airflow / dbt で毎日更新
BI ツールへの接続	ODBC / JDBC / クラウド DB コネクタ
ダッシュボードの設計	閾値・アラート・ドリルダウンの設定

4. 大規模データへのスケールアップ

本ノックでは SQLite（数百〜数千件）を使いましたが、実務では：

• PostgreSQL / MySQL: 中規模（数百万行）
• BigQuery / Snowflake / Redshift: 大規模 DWH（数億行）

SQL の書き方は基本的に同じですが、インデックスの代わりに パーティショニング や クラスタリング を使います。

まとめ

本章では SQL 100本ノックの最終章として、製造業の稼働データを使い 実務 SQL を安定稼働させる技術を網羅しました。

No.	習得した技術	実務での価値
091	SQL フォーマット・可読性	引き継ぎコストの削減
092	SQL コメント	チームでの協業基盤
093	集計結果の検算	レポート信頼性の担保
094	重複データ検出・除去	データ品質の自動チェック
095	欠損データ検出・対処	NULL による集計誤差の防止
096	インデックスの設計・作成	クエリ高速化の基礎
097	実行計画の読み方	ボトルネックの特定
098	重い SQL の改善	月次レポートの時間短縮
099	BI 向け集計テーブル設計	Power BI / Tableau 連携
100	プロジェクト全体フローの整理	データドリブン経営の実現

SQL 100本ノック 完走おめでとうございます！

第1章の SELECT 文の基本 から始まり、第10章の 実務運用・性能・設計 まで、
100 本のノックを通じて 製造業のデータ分析に必要な SQL の全技術 を習得しました。

次のステップとして：

• データエンジニアリング：dbt・Airflow で SQL パイプラインを自動化する
• 機械学習との連携：SQL で作成した特徴量テーブルを Python ML モデルに入力する
• クラウド DWH：BigQuery・Snowflake で大規模データ分析に挑戦する

法人向けのご相談

製造業における SQL 分析基盤の内製化支援、データ品質管理プロセスの設計、
BI ダッシュボード構築（Power BI / Tableau / Metabase）、SQL 研修プログラムの設計 に関して、
数理工房では法人様向けのご相談を承っております。

本ノックで扱ったような「稼働データの品質チェック → KPI 集計 → BI 連携」の
エンドツーエンド実装支援から教育プログラム設計まで、お気軽にお問い合わせください。

---

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