🔖 キーワード索引

「リレーショナルDB (RDB)」は構造化データを表 (テーブル) と関係 (リレーション) で管理する仕組みで、 統計・業務データ基盤の中核を担う。 本ページでは RDB を理解するうえで要となるキーワードを以下にチップで一覧化する。 各キーワードは関連する概念・手法・道具立てを含み、 文献検索や学習計画の起点になる。

これらのキーワードは「リレーショナルDB の理解 → 設計 (正規化) → 操作 (SQL) → 運用」のプロセスを構成する。 各章で詳しく解説する。

🎨 直感で掴む

リレーショナル DB は 「表 (テーブル) と表の関係 (リレーション) でデータを管理する」仕組みです。 例えば SSDSE-B-2026 を RDB に投入すると、 「都道府県マスタ (47 行) 」と「年度別指標 (47×12=564 行) 」「項目マスタ (109 行) 」の 3 つの表に分解し、 都道府県コード (R01000 等) で JOIN して必要な切り口で再構成できます。

直感的にはExcel ファイル群を「ID で繋ぐルール」付きで束ねたもの。 ただし Excel と違って (1) 同じ ID で 2 行を作れない (主キー制約) 、 (2) 存在しない都道府県を参照できない (外部キー制約) 、 (3) 数百万行を WHERE + インデックスで瞬時に絞れる、 という点が決定的に違います。

🎨 基本概念

🎨 補講：ER 図と論理設計 — SSDSE のスキーマを描いてみる

RDB の設計は「エンティティ（実体）」「リレーション（関係）」「属性」で表現します。 ER 図（Entity-Relationship Diagram）はその設計図です。 SSDSE-B-2026 を ER で表現してみましょう。

📊 SSDSE の ER 図（テキスト版）

📐 カーディナリティ（多重度）

• 1:1：例「ユーザー – プロフィール」（多くは 1 テーブルにまとめても OK）。
• 1:N：例「pref_master – population_fact」（1 県に対し複数年の人口）。
• N:N：例「学生 – 講義」（中間テーブル「履修」が必要）。

🐍 Python 実装 17：N:N の中間テーブル例

🎯 このコードでやること：SSDSE には N:N がないため、 仮想的に「都道府県 × タグ（地方区分）」の N:N を作って体感する。

📥 入力データ：pref_master + 地方区分。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

import sqlite3, pandas as pd

conn = sqlite3.connect('rdb_demo.db')
# tag マスタ
conn.execute("CREATE TABLE IF NOT EXISTS region_tag (tag_code TEXT PRIMARY KEY, tag_name TEXT)")
conn.executemany("INSERT OR REPLACE INTO region_tag VALUES (?,?)",
    [('HK','北海道'),('TH','東北'),('KT','関東'),('CB','中部'),('KS','近畿'),('CG','中国'),('SK','四国'),('KY','九州')])
# pref × tag の中間表（N:N）
conn.execute("""CREATE TABLE IF NOT EXISTS pref_region (
    code TEXT, tag_code TEXT, PRIMARY KEY (code, tag_code),
    FOREIGN KEY (code) REFERENCES pref_master(code),
    FOREIGN KEY (tag_code) REFERENCES region_tag(tag_code))""")
conn.executemany("INSERT OR REPLACE INTO pref_region VALUES (?,?)",
    [('R13000','KT'), ('R14000','KT'), ('R27000','KS'), ('R47000','KY'), ('R01000','HK')])
conn.commit()

q = "SELECT m.pref_name, r.tag_name FROM pref_region pr JOIN pref_master m ON pr.code=m.code JOIN region_tag r ON pr.tag_code=r.tag_code"
print(pd.read_sql(q, conn))

📤 実行すると次の出力が得られる：

💬 結果の読み方：N:N は「中間テーブル」で表現するのが定石。 「東京は関東 + 首都圏」と複数タグを持たせたいときも、 中間表に行を増やすだけで拡張可能。

⚠️ ER 設計の落とし穴

• N:N を 1:N で無理やり表現：列を「東京,神奈川,千葉」と詰める → 1NF 違反。 必ず中間表に。
• 主キーが業務キー直結：商品コードが業務変更で書き換わると参照壊滅。 サロゲートキー（自動採番 ID）が無難。
• マスタの「ゴミ箱」化：「その他」「不明」を増やしすぎると分析が破綻。 業務ルールを明確に。

🎨 補講：実務でよく出るスキーマパターン 5 選

SSDSE は集計データですが、 業務では多様なスキーマが登場します。 SSDSE の延長で考えられるパターンを 5 つ紹介。

① Star Schema（スタースキーマ）

1 つの fact 表 + 複数の dimension 表が放射状に。 OLAP/DWH の定番。 本ページの (pref_master, metric_master, population_fact) はミニ Star Schema。

② Snowflake Schema

Star を更に正規化、 dimension が子 dimension を持つ。 例：pref_master → region_master（東北、 関東 …）→ pref_master。 ストレージ削減、 ただし JOIN コスト増。

③ SCD Type 2（履歴保持）

マスタが時間で変化する場合、 valid_from / valid_to 列で履歴を残す。 例：「会社名変更前後を別行で保持」「都道府県合併の履歴」。 集計時にあるべき時点の名称を引ける。

④ Event Sourcing

状態ではなく「イベント」を保存し、 集計で現在状態を導出。 例：「人口が +500 増加」「-300 減少」を全件保存。 監査性と再現性が高いが、 クエリは重め。

⑤ EAV（Entity-Attribute-Value）

属性が動的に増減する場合、 (entity_id, attribute, value) の縦持ち。 SSDSE-B-2026 の long 形式 (year, code, metric, value) はまさに EAV。 柔軟だが整合性管理が難しい。

⚠️ パターン選択時の指針

• OLAP / BI / DWH → Star or Snowflake
• 履歴必須（コンプライアンス） → SCD Type 2 or Event Sourcing
• 属性が動的 → EAV（ただし型整合に注意）
• OLTP / トランザクション → 3NF + サロゲートキー
• SSDSE のような公的統計 → Star Schema（本ページ実装）が最適

これでスキーマパターンの引き出しが 5 つ揃いました。 実際の業務では複数を組み合わせます（例：Star + SCD Type 2）。 「パターンを暗記」ではなく「業務要件から逆算」が正攻法です。

📐 定義

テーブル形式の構造化データ管理システム

英語名 Relational Database。 同義・関連語：RDB, RDBMS。

🎯 RDB を選ぶ場面

• SSDSE-B-2026 の wide 形式を long 形式に正規化：564 行 × 112 列を pref_master + population_fact に分割し、 SQLite/PostgreSQL に投入。 3NF 設計でストレージを約 96% 削減できる。
• 受発注・予約・在庫：ACID トランザクションが必須の業務システムで RDB が定番。 PostgreSQL/MySQL/Oracle がワークロードの中核。
• BI ツールのバックエンド：Tableau・Power BI・Looker は SQL で集計するので RDB or DWH が前提。 SSDSE-B-2026 をダッシュボード化するなら SQLite + Power BI が手軽。
• マスタ・ディメンション管理：都道府県マスタ・市町村マスタ・JIS コードのような参照データは RDB の正規化と FK 制約で保守が楽。
• 監査ログ・トランザクションログ：行レベルの追記専用テーブル + インデックスで履歴管理。 NoSQL では一貫性が緩いため不向き。

📋 RDB を選ぶ前提・適用範囲

RDB (関係データベース) を採用するか他のストア (NoSQL, データウェアハウス) を選ぶかは、 次の前提で判断する：

• スキーマが事前に決まる：SSDSE-B-2026 のように列名・型・主キーが固定されたデータは RDB が最適。 スキーマが頻繁に変わるログデータには Document DB (MongoDB) や JSON 型の方が柔軟。
• 関係 (リレーション) が中核：都道府県 ↔ 市町村 ↔ 統計値のような多対多関係を JOIN で表現したいなら RDB の独擅場。 グラフ構造が中心なら Neo4j など Graph DB を検討。
• ACID トランザクションが必要：金融・予約・在庫など整合性が至上命題なら RDB の COMMIT/ROLLBACK が安心。 SSDSE-B-2026 のような分析専用なら緩い一貫性の DWH でも OK。
• 規模 (行数 × 列数)：数百万行までは PostgreSQL/MySQL で快適、 数十億行超なら BigQuery / Redshift / Snowflake などカラムナ型 DWH へ。 SSDSE-B-2026 (564 行 × 112 列) なら SQLite でも余裕。
• JOIN 性能と非正規化のトレードオフ：3NF まで正規化するとストレージは節約できるが、 分析時に毎回 JOIN が走り遅くなる。 分析ワークロードでは集計済みスタースキーマやマテリアライズドビューが定石。

📐 正規化

情報の重複と矛盾を排除する設計プロセス：

実務では 3NF / BCNF が現実的目標。 OLTP は高い正規化、分析用は逆正規化（スター スキーマ）も組合せる。

🔬 ACID 特性

• A: 原子性 ── 全成功か全失敗。 「引き出しだけ成功して入金失敗」を防ぐ
• C: 一貫性 ── 制約を守ったまま状態遷移
• I: 独立性 ── 同時実行されても干渉しない。 隔離レベル 4 段階
• D: 永続性 ── COMMIT 後は障害でも保持。 WAL で保証

$$ \text{隔離レベル}: \text{READ UNCOMMITTED} < \text{RC} < \text{RR} < \text{SERIALIZABLE} $$

🔬 深掘り：RDB の正規化・ACID・JOIN を SSDSE-B-2026 で完全理解

リレーショナルデータベース (RDB) は テーブル + 主キー + 外部キー + SQL の 4 要素で構成されます。 ここでは公的データ SSDSE-B-2026（47 都道府県 × 12 年（2012〜2023））を SQLite に投入し、 正規化・ACID・JOIN・index・トランザクションをすべて実コードで体感します。

🎨 直感：RDB は「行（レコード）× 列（属性）」のスプレッドシート群

📐 数式：正規化の数学的定義

関係 $R$ の属性集合 $X, Y$ について、 関数従属を $X \to Y$（$X$ が決まれば $Y$ が一意に決まる）と書きます。 各正規形は以下で定義：

$$\text{1NF}: \text{全列が原子値（リスト不可）}$$

$$\text{2NF}: \text{1NF} \land (\forall X \to A,\; A \notin \text{key} \Rightarrow X \text{ は候補キー全体})$$

$$\text{3NF}: \text{2NF} \land (\forall X \to A,\; A \notin \text{key} \Rightarrow X \supseteq \text{candidate key} \lor A \in \text{candidate key})$$

$$\text{BCNF}: \forall X \to Y,\; X \text{ はスーパーキー}$$

🔬 数式を言葉で読み解く

• 1NF（第 1 正規形）：1 セルに複数値を入れない。 例：「東京,神奈川,千葉」と詰めない、 3 行に分ける。
• 2NF（第 2 正規形）：部分従属を排除。 複合キー (year, code) の一部だけで決まる列（例：pref 名）を別表に。
• 3NF（第 3 正規形）：推移従属を排除。 「キー → A → B」の連鎖を切り、 B を独立テーブルへ。
• BCNF：すべての関数従属の左辺がスーパーキー。 3NF の強化版。
• SSDSE では「(year, code) → pref_name」は部分従属 → pref_name を都道府県マスタに切り出す = 2NF 化。

🧮 実値で計算：SSDSE-B-2026 を正規化前後で比較

SSDSE は wide 形式（564 行 × 112 列）。 これを正規化して 2 つのテーブルに分けると：

冗長性が 96% 減。 ただし JOIN コストとのトレードオフ — 分析時に毎回 JOIN するなら、 マテリアライズドビューや非正規化（denormalize）も選択肢に入ります。

🐍 Python 実装 3：SSDSE-B-2026 を SQLite に正規化投入

🎯 このコードでやること：SSDSE-B-2026 を pref_master と population_fact の 2 テーブルに分解し、 SQLite に投入する（3NF）。

📥 入力データ：data/raw/SSDSE-B-2026.csv（564 行 × 112 列）。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

import pandas as pd
import sqlite3

df = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', skiprows=[1])
df = df.rename(columns={'SSDSE-B-2026': 'year', 'Code': 'code', 'Prefecture': 'pref_name'})

# 1. pref_master: code → pref_name の対応
pref_master = df[['code', 'pref_name']].drop_duplicates().reset_index(drop=True)

# 2. population_fact: 主要指標のみに絞る
pop_fact = df[['year', 'code', 'A1101']].rename(columns={'A1101': 'total_population'})

conn = sqlite3.connect('rdb_demo.db')
cur = conn.cursor()
cur.execute("DROP TABLE IF EXISTS pref_master")
cur.execute("DROP TABLE IF EXISTS population_fact")
cur.execute("CREATE TABLE pref_master (code TEXT PRIMARY KEY, pref_name TEXT NOT NULL)")
cur.execute("""CREATE TABLE population_fact (year INTEGER, code TEXT, total_population INTEGER,
                PRIMARY KEY (year, code), FOREIGN KEY (code) REFERENCES pref_master(code))""")
pref_master.to_sql('pref_master', conn, if_exists='append', index=False)
pop_fact.to_sql('population_fact', conn, if_exists='append', index=False)
print(f"pref_master: {conn.execute('SELECT COUNT(*) FROM pref_master').fetchone()[0]} 行")
print(f"population_fact: {conn.execute('SELECT COUNT(*) FROM population_fact').fetchone()[0]} 行")

📤 実行すると次の出力が得られる：

💬 結果の読み方：47 都道府県マスタと 564 行の事実表が 2 テーブルに分かれた。 pref_name は 47 行に集約され、 冗長度が大幅減。 主キー + 外部キーで整合性も保証。 この設計が「3NF + 関係モデル」の基本形。

🐍 Python 実装 4：SQL JOIN で 2 テーブルを結合

🎯 このコードでやること：pref_master と population_fact を INNER JOIN し、 2023 年の人口 TOP5 を取得。

📥 入力データ：先ほど作成した rdb_demo.db。

1
2
3
4
5
6
7
8
9
10

import sqlite3
import pandas as pd

conn = sqlite3.connect('rdb_demo.db')
q = """SELECT m.pref_name, f.total_population
       FROM population_fact f
       JOIN pref_master m ON f.code = m.code
       WHERE f.year = 2023
       ORDER BY f.total_population DESC LIMIT 5"""
print(pd.read_sql(q, conn))

📤 実行すると次の出力が得られる：

💬 結果の読み方：2 テーブルを ON 句で結合し、 1 つの結果セットに統合。 これが SQL JOIN の威力。 正規化したからこそ、 マスタが 1 箇所で管理でき、 名前変更時も 1 行更新で全件反映される。

🐍 Python 実装 5：INDEX による検索高速化

🎯 このコードでやること：population_fact.year に INDEX を貼り、 WHERE year=2023 の検索時間を比較。

📥 入力データ：rdb_demo.db の population_fact (564 行)。

1
2
3
4
5
6
7
8
9
10
11
12
13

import sqlite3, time

conn = sqlite3.connect('rdb_demo.db')
q = "SELECT * FROM population_fact WHERE year = 2023"

# INDEX なし
t0 = time.time(); conn.execute(q).fetchall(); t_no = time.time() - t0

conn.execute("CREATE INDEX IF NOT EXISTS idx_year ON population_fact(year)")
conn.commit()

t0 = time.time(); conn.execute(q).fetchall(); t_idx = time.time() - t0
print(f"INDEX なし: {t_no*1000:.3f} ms")
print(f"INDEX あり: {t_idx*1000:.3f} ms")

📤 実行すると次の出力が得られる：

💬 結果の読み方：564 行では差がほとんど出ない（実測で 0.03〜0.9 ms の範囲を行き来し、 逆転することもある）。 これは「INDEX が効かない」のではなく、 フルスキャンでも一瞬で終わる規模だから差が見えないのです。 1 億行ならフルスキャンが数秒、 INDEX 経由なら数ミリ秒 → 1000 倍以上の差になります。 ただし INDEX は INSERT/UPDATE を遅くする副作用あり。 READ 頻度の高い列だけに貼るのが鉄則。

📐 ACID 特性

• Atomicity（原子性）：トランザクション内の全操作は「すべて成功」か「すべて失敗」か。 中途半端な状態を作らない。
• Consistency（一貫性）：制約（主キー・外部キー・CHECK）を常に満たす状態。
• Isolation（独立性）：並行トランザクション同士が干渉しない。 分離レベル: READ UNCOMMITTED / READ COMMITTED / REPEATABLE READ / SERIALIZABLE。
• Durability（永続性）：COMMIT 後は障害でも消えない。 WAL（Write-Ahead Log）で実現。

🐍 Python 実装 6：トランザクション（ACID の Atomicity）

🎯 このコードでやること：population_fact に 2 件 INSERT する。 2 件目で例外発生 → ROLLBACK で両方なかったことになる（原子性）。

📥 入力データ：rdb_demo.db。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

import sqlite3

conn = sqlite3.connect('rdb_demo.db')
before = conn.execute("SELECT COUNT(*) FROM population_fact").fetchone()[0]

try:
    cur = conn.cursor()
    cur.execute("BEGIN")
    cur.execute("INSERT INTO population_fact VALUES (2024, 'R01000', 5000000)")
    # わざと主キー衝突させて例外
    cur.execute("INSERT INTO population_fact VALUES (2023, 'R13000', 99)")
    conn.commit()
except sqlite3.IntegrityError as e:
    conn.rollback(); print(f"ROLLBACK: {e}")

after = conn.execute("SELECT COUNT(*) FROM population_fact").fetchone()[0]
print(f"件数 before={before}, after={after} → 増減なし = 原子性 OK")

📤 実行すると次の出力が得られる：

💬 結果の読み方：2 件目の INSERT で主キー衝突エラー → 1 件目も ROLLBACK で取り消し。 「片方だけ反映」の中途半端状態を防いだ = ACID の A（原子性）の実例。 銀行送金などはこの仕組みが必須。

⚠️ RDB の落とし穴（深掘り版）

• 過剰正規化：JOIN だらけで読み取りが遅い。 OLAP では非正規化（star schema）が定石。
• INDEX の貼りすぎ：INSERT/UPDATE が遅くなる。 EXPLAIN で実際に使われている INDEX のみ残す。
• N+1 問題：ORM が 1 件ずつクエリを発行 → 1000 件で 1000 + 1 回の往復。 JOIN や IN 句で 1 回にまとめる。
• トランザクションの長保持：ロック競合で全体が止まる。 トランザクションは「短く・小さく」が鉄則。
• 外部キーを張らない：参照整合性が崩れ、 孤児レコードが発生。 開発初期から FK 制約を入れる。
• NULL の扱い：「未入力」「該当なし」「ゼロ」を NULL 1 つで表現すると曖昧。 業務ルールを明確化。
• 命名規約の不在：snake_case / camelCase 混在で SQL が読みづらい。 統一する。

🌐 関連手法・派生

• NoSQL：MongoDB（ドキュメント）、 Cassandra（列指向）、 Redis（KV）。 「スキーマレス」「水平スケール」が必要なときに。
• NewSQL：CockroachDB, Spanner — RDB の SQL + 分散 NoSQL の水平スケール。
• OLAP 専用 DWH：BigQuery, Snowflake, Redshift — 列指向・読み取り最適化。
• Time-Series DB：InfluxDB, TimescaleDB — 時系列に特化した RDB 拡張。
• Graph DB：Neo4j — ノード・エッジ・関係を扱う。
• SQLite：単一ファイル、 サーバ不要。 個人開発・組み込み・テストに最適。
• PostgreSQL：OSS で機能豊富（JSON, GIS, ベクトル）。 業務用途のデファクト。

🔬 補講：トランザクション分離レベルと並行制御

複数ユーザが同時に書き込む環境では、 ACID の I（Isolation）の設計が重要です。 分離レベルが弱いと「あるはずのないデータが見える」現象（ダーティリード、 ファントムリード）が起きます。

📊 分離レベル一覧

🐍 Python 実装 10：SQLite のロック挙動を実演

🎯 このコードでやること：1 つの SQLite ファイルに 2 接続で同時書き込みし、 ロック挙動を観察する。

📥 入力データ：rdb_demo.db。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

import sqlite3

c1 = sqlite3.connect('rdb_demo.db', timeout=1)  # ロック待ち 1 秒
c2 = sqlite3.connect('rdb_demo.db', timeout=1)

c1.execute("BEGIN IMMEDIATE")
c1.execute("UPDATE population_fact SET total_population = 1 WHERE year = 1999 AND code = 'R01000'")
# c1 はまだ COMMIT していない

try:
    c2.execute("UPDATE population_fact SET total_population = 2 WHERE year = 1999 AND code = 'R01000'")
except sqlite3.OperationalError as e:
    print(f"c2 はロック待ちでタイムアウト: {e}")

c1.rollback(); c1.close(); c2.close()

📤 実行すると次の出力が得られる：

💬 結果の読み方：SQLite は「ファイルレベルロック」で、 書き込み中の他接続をブロック。 PostgreSQL/MySQL は行レベルロック + MVCC で並行性が高い。 SQLite は単純で速いが、 高並列書込には不向き。 用途で選ぶ。

⚠️ 並行制御の落とし穴

• 長時間トランザクション：ロック保持で他処理が止まる。 トランザクションは秒単位以下に。
• デッドロック：A→B、 B→A の順でロック取得 → 永久待ち。 ロック順序を一貫化、 DB のデッドロック検出器を信頼。
• SERIALIZABLE の濫用：完全な直列化で性能低下。 業務的に許容できる最弱レベルを選ぶ。
• MVCC を知らずに分離レベルを選ぶ：MVCC（Multi-Version Concurrency Control）は読み取りロックフリー。 PostgreSQL は標準で MVCC。

🔬 補講：パフォーマンスチューニングの基本

SSDSE-B-2026 規模（数千行）では何でも瞬時ですが、 1 億行になると「設計」と「INDEX」と「クエリ書き方」が性能を決めます。 基本テクをまとめます。

🐍 Python 実装 18：複合 INDEX の効果

🎯 このコードでやること：(year, code) の複合 INDEX を貼り、 「特定年・特定県」検索の高速化を比較。

📥 入力データ：rdb_demo.db。

1
2
3
4
5
6
7
8
9
10
11
12

import sqlite3, time

conn = sqlite3.connect('rdb_demo.db')
conn.execute("DROP INDEX IF EXISTS idx_year_code")

q = "SELECT * FROM population_fact WHERE year = 2023 AND code = 'R13000'"
t0=time.time(); conn.execute(q).fetchall(); t_no=time.time()-t0

conn.execute("CREATE INDEX idx_year_code ON population_fact(year, code)")
t0=time.time(); conn.execute(q).fetchall(); t_idx=time.time()-t0
print(f"複合 INDEX なし: {t_no*1000:.3f} ms")
print(f"複合 INDEX あり: {t_idx*1000:.3f} ms")

📤 実行すると次の出力が得られる：

💬 結果の読み方：こちらも 564 行では差が計測ノイズに埋もれます。 注意：複合 INDEX は (year, code) の順なので「year のみ検索」「(year, code) 検索」には効くが、 「code のみ検索」には効かない。 順序設計が肝。

⚠️ チューニング時の落とし穴

• 本番未満のデータで計測：1000 行で速くても 1 億行で破綻。 必ず本番相当データで EXPLAIN ANALYZE。
• キャッシュヒットで計測：2 回目は速い。 cold cache での計測も必須。
• INDEX 貼りすぎ：書き込み遅延が増える。 INDEX 利用率を pg_stat_user_indexes で監視。
• クエリだけ最適化：本質はスキーマ設計（正規化・パーティション）。 まず設計、 次に INDEX、 最後にクエリ。

🔬 補講：RDB 初学者がよく詰まる 10 の Q&A

Q1. 主キーと外部キーの違いは？

A. 主キー（PK）はその表の中で行を一意に識別する列。 外部キー（FK）は他表の PK を参照する列。 SSDSE 例：pref_master.code が PK、 population_fact.code が FK で pref_master.code を指す。

Q2. SQLite と PostgreSQL のどっちを学ぶ？

A. SQLite で SQL の基本を学び、 PostgreSQL で本番感を学ぶ。 SQLite は単一ファイル、 セットアップ不要、 教材最適。 業務では PostgreSQL が圧倒的に多い。 両方触ると差分（型・関数）も理解できる。

Q3. INNER と OUTER の使い分けは？

A. INNER：両表に必ず存在する行のみ欲しい時。 LEFT OUTER：左表（基準）の全行を残し、 右表に無ければ NULL で埋めたい時。 欠損監視や「マスタ基準の集計」で OUTER を多用。

Q4. NULL の比較で WHERE col = NULL が効かない

A. NULL は「不明」なので等号比較ができない。 WHERE col IS NULL / IS NOT NULL を使う。 集計関数（SUM, AVG）は NULL を無視する点も覚えておく。

Q5. ORDER BY が遅い

A. ソート対象列に INDEX があれば速い（INDEX は元々ソート済み）。 また LIMIT を併用すると、 TOP-K アルゴリズムが効いて高速化。 ORDER BY col + LIMIT 10 が定石。

Q6. WHERE と HAVING はどっちで書く？

A. GROUP BY 前のフィルタは WHERE（行レベル）、 GROUP BY 後の集計値フィルタは HAVING。 「COUNT(*) > 5」は HAVING、 「year = 2023」は WHERE。

Q7. UNIQUE と PRIMARY KEY の違いは？

A. PRIMARY KEY は NOT NULL + UNIQUE、 1 表に 1 つ。 UNIQUE は NULL 許容、 複数列に張れる。 業務上の主識別子は PK、 「メールアドレスは重複不可」のような補助は UNIQUE。

Q8. トリガーやストアドプロシージャは使うべき？

A. 避けがち。 ロジックが DB に散らばり、 アプリ側からデバッグ困難になる。 整合性制約は宣言的（PK, FK, CHECK）で表現し、 ビジネスロジックはアプリ層に置くのが現代主流。

Q9. パーティションはいつ必要？

A. 目安「単一表が 1000 万行を超える」「クエリが特定範囲（year, region）に集中」「古いデータ削除を高速化したい」。 SSDSE 564 行ではオーバースペック。 BigQuery 等 DWH では小規模でもパーティションを切ることが多い。

Q10. もう SQL は時代遅れ？

A. むしろ全盛期。 BigQuery, Snowflake, Athena すべて SQL ベース。 dbt も SQL。 ML/AI 全盛時代でも「データを取り出す」最後の 1km は SQL です。 30 年前から続き、 これからも続く。

本ページに登場した Python 実装は 計 18 本、 SQL クエリは 20 種以上。 すべて SSDSE-B-2026 を SQLite に投入して動きます。 「正規化 → 投入 → JOIN → ウィンドウ → INDEX → トランザクション → EXPLAIN」までを 1 ファイルで体験できる構成です。 RDB は古典ですが、 21 世紀の DWH もすべてその延長線。 まずここを盤石にしてください。

🔬 締めくくり：RDB を「使える道具」にする最後の確認

本ページでは 正規化（1NF→3NF）→ ACID → JOIN → ウィンドウ関数 → トランザクション → INDEX → EXPLAIN → ER 設計 → スキーマパターン までを一気通貫で扱いました。 すべて SSDSE-B-2026 を 1 ファイルから動かせます。

📊 学習成果チェック（全部 Yes なら RDB 卒業）

• ✅ 主キー / 外部キー / NOT NULL / UNIQUE を意識して CREATE TABLE が書ける
• ✅ 1NF / 2NF / 3NF の違いを SSDSE の例で説明できる
• ✅ INNER / LEFT / RIGHT / FULL の使い分けが判断できる
• ✅ GROUP BY / HAVING / WINDOW の使い分けができる
• ✅ CTE で複雑クエリを段階化できる
• ✅ INDEX の効果を EXPLAIN で確認できる
• ✅ トランザクション境界を意識して BEGIN / COMMIT / ROLLBACK が書ける
• ✅ Star Schema と 3NF を業務要件で使い分けられる
• ✅ RDB と NoSQL を用途で選べる

すべて Yes と言えるなら、 業務 RDB の入門は卒業です。 次は BigQuery / Snowflake などのクラウド DWH や dbt に進んで、 「分析基盤の SQL」へ視野を広げてください。

📚 次に学ぶべきトピック

• パーティション設計：RANGE / LIST / HASH の使い分け
• ベクトル検索：pgvector で類似度検索（LLM 時代の新機能）
• 論理レプリケーション：PostgreSQL の publication / subscription
• 列指向 DB：DuckDB / ClickHouse — OLAP 専用の高速エンジン
• 分散 SQL：CockroachDB / Spanner / TiDB
• ORM (SQLAlchemy / Django ORM)：Python から RDB を使う標準的方法
• マイグレーションツール：Alembic / Flyway — スキーマのバージョン管理

RDB は約 50 年の歴史を持つ「枯れた」技術ですが、 現代のデータ基盤の中核です。 SSDSE のような公的データを練習材料に、 まず SQLite で手を動かし、 PostgreSQL で本番感を学び、 BigQuery / Snowflake でクラウド規模を体感する——この階段を昇れば、 データエンジニア / アナリストとして強固な土台ができます。

最後に一言：「RDB は 制約こそ価値」。 主キー・外部キー・NOT NULL・CHECK で「ありえない状態」を弾く設計が、 データ品質と運用負荷を 10 倍下げます。 制約を恐れず、 むしろ宣言的に書いて DB に守ってもらう——これがプロの仕事です。

本ページの内容（19 個の Python 実装 + 20 種以上の SQL + 5 種のスキーマパターン + 10 個の Q&A）は、 すべて SSDSE-B-2026 を題材に作成しました。 自分の手で動かし、 設計を紙に書き、 EXPLAIN を読む習慣を身につければ、 数年で「RDB を任せられる人」になれます。 最後まで読んでいただきありがとうございました。

なお、 本ページの実装は SQLite を中心としていますが、 PostgreSQL / MySQL / BigQuery / Snowflake すべてに 95% そのまま通用します。 SQL は数十年の歴史で枯れた共通言語であり、 これからの 30 年も使われ続けるでしょう。 投資対効果が極めて高い学習対象です。

🔒 ACID トランザクションを SSDSE データで体験する

RDB の真価は ACID 特性 (Atomicity / Consistency / Isolation / Durability) にある。 NoSQL では犠牲にされがちなこの保証は、 金融・人口統計・行政データのような「整合性が崩れると国家レベルで困る」 領域で本質的。 ここでは SSDSE-B-2026 を題材に、 「都道府県の人口総数を更新しつつ、 同時に変更履歴テーブルに記録する」 トランザクションを実装する。

🐍 Python 実装: BEGIN / COMMIT / ROLLBACK の挙動

🎯 このコードでやること: 都道府県人口の修正と、 変更履歴の記録を 原子的 に行う。 履歴記録に失敗したら人口修正もロールバックされることを確認する。

📥 入力データ (SSDSE-B-2026 抜粋を SQLite に投入):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

import sqlite3
import pandas as pd

con = sqlite3.connect(':memory:')
con.isolation_level = None  # 明示的な BEGIN/COMMIT を使う
cur = con.cursor()
cur.execute('CREATE TABLE prefecture (code TEXT PRIMARY KEY, name TEXT, population INT CHECK(population >= 0))')
cur.execute('CREATE TABLE audit_log (id INTEGER PRIMARY KEY AUTOINCREMENT, code TEXT NOT NULL, old_pop INT, new_pop INT, ts TEXT DEFAULT CURRENT_TIMESTAMP)')

# SSDSE は英語コード列。 都道府県ごとに全年平均をとって投入する
df = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', skiprows=[1])
agg = df.groupby(['Code', 'Prefecture'])['A1101'].mean().round().astype(int).reset_index()
cur.executemany('INSERT OR REPLACE INTO prefecture VALUES (?,?,?)',
                agg[['Code', 'Prefecture', 'A1101']].values.tolist())

# トランザクション: 東京都の人口を更新 + audit_log に記録
try:
    cur.execute('BEGIN')
    cur.execute('SELECT population FROM prefecture WHERE code = ?', ('R13000',))
    old_pop = cur.fetchone()[0]
    new_pop = 14100000
    cur.execute('UPDATE prefecture SET population = ? WHERE code = ?', (new_pop, 'R13000'))
    cur.execute('INSERT INTO audit_log (code, old_pop, new_pop) VALUES (?,?,?)', ('R13000', old_pop, new_pop))
    con.commit()
    print('COMMIT 成功 旧={} 新={}'.format(old_pop, new_pop))
except Exception as e:
    con.rollback()
    print('ROLLBACK: ', e)

# 制約違反を起こしてみる (人口を負にする)
try:
    cur.execute('BEGIN')
    cur.execute('UPDATE prefecture SET population = -1 WHERE code = ?', ('R27000',))
    cur.execute('INSERT INTO audit_log (code, old_pop, new_pop) VALUES (?,?,?)', ('R27000', 8829346, -1))
    con.commit()
except sqlite3.IntegrityError as e:
    con.rollback()
    print('CHECK 制約違反でロールバック: ', str(e))

cur.execute('SELECT code, population FROM prefecture WHERE code IN (?,?)', ('R13000', 'R27000'))
print('更新後の値:', cur.fetchall())
cur.execute('SELECT COUNT(*) FROM audit_log')
print('audit_log 行数:', cur.fetchone()[0])

📤 実行すると次の出力が得られる:

💬 結果の読み方: 1 つ目のトランザクションは COMMIT され、 prefecture と audit_log の両方が更新された。 2 つ目は CHECK 制約 (人口 >= 0) に違反したため UPDATE と INSERT の両方が取り消された。 これが Atomicity (原子性): 「全部やるか、 何もやらないか」。 NoSQL ではアプリ側で補償処理を書く必要があるが、 RDB は宣言的に保証してくれる。

分離レベルと現実のトレードオフ

⚠️ SERIALIZABLE は遅い。 大量同時更新が起きる本番では READ COMMITTED + 楽観ロック (version カラム) が現実解。 SSDSE のような read-mostly な公的統計データ なら、 分離レベルは気にせず読み込みパフォーマンスに最適化すべき。

🧮 SQL の基本構文

🧮 SQL の基本構文

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

CREATE TABLE customers (
  id        SERIAL PRIMARY KEY,
  name      TEXT NOT NULL,
  pref      TEXT,
  created_at TIMESTAMP DEFAULT now()
);

INSERT INTO customers (name, pref) VALUES ('山田', '東京都');

SELECT id, name FROM customers WHERE pref = '東京都';

UPDATE customers SET pref = '神奈川県' WHERE id = 1;

DELETE FROM customers WHERE id = 1;

-- 結合
SELECT c.name, SUM(o.amount) AS total
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.name ORDER BY total DESC LIMIT 10;

-- トランザクション
BEGIN;
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
COMMIT;

🧮 さらに深掘り：JOIN の種類、 ウィンドウ関数、 物理設計

📊 JOIN の 4 種類

🐍 Python 実装 7：LEFT JOIN で欠損補完を可視化

🎯 このコードでやること：population_fact から沖縄を一時的に削除し、 pref_master を基準に LEFT JOIN すると沖縄が NULL で残ることを確認する。

📥 入力データ：rdb_demo.db。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

import sqlite3
import pandas as pd

conn = sqlite3.connect('rdb_demo.db')
# 沖縄の 2023 行を一時的に削除
conn.execute("DELETE FROM population_fact WHERE year=2023 AND code='R47000'")
conn.commit()

q = """SELECT m.pref_name, f.total_population
       FROM pref_master m LEFT JOIN population_fact f
         ON m.code = f.code AND f.year = 2023
       WHERE m.pref_name IN ('東京都','沖縄県','大阪府')"""
res = pd.read_sql(q, conn)
# LEFT JOIN なので右側に行が無い県は NULL になる。そのままだと NaN と表示されて
# 「計算が壊れた」ように見えるので、NULL と分かる形にして表示する
res['total_population'] = res['total_population'].map(
    lambda v: 'NULL（右側に行なし）' if pd.isna(v) else f'{v:,.0f}')
print(res.to_string(index=False))
conn.close()

📤 実行すると次の出力が得られる：

💬 結果の読み方：沖縄は LEFT JOIN で NULL で残った。 INNER JOIN なら消えていたが、 LEFT で「マスタ基準・データ欠損も見える」表ができる。 欠損監視に必須のパターン。

🐍 Python 実装 8：ウィンドウ関数で都道府県ランキング

🎯 このコードでやること：population_fact を年ごとに人口降順でランク付け（RANK() ウィンドウ関数）。

📥 入力データ：rdb_demo.db。

1
2
3
4
5
6
7
8
9
10
11

import sqlite3
import pandas as pd

conn = sqlite3.connect('rdb_demo.db')
q = """SELECT year, m.pref_name, f.total_population,
              RANK() OVER (PARTITION BY year ORDER BY total_population DESC) AS rk
       FROM population_fact f JOIN pref_master m ON f.code = m.code
       WHERE year IN (2012, 2023) AND rk <= 3
       ORDER BY year, rk"""
# SQLite では WHERE で window 結果フィルタ不可なのでサブクエリ化
print(pd.read_sql(f"SELECT * FROM ({q.replace('AND rk <= 3','')}) WHERE rk <= 3", conn))

📤 実行すると次の出力が得られる：

💬 結果の読み方：2012 年も 2023 年も TOP3 は東京・神奈川・大阪。 ウィンドウ関数は GROUP BY と違い、 行を集約せずにランクを付与できる。 「県別最大/最小/順位」を 1 クエリで取れる強力機能。

🐍 Python 実装 9：EXPLAIN でクエリプランを確認

🎯 このコードでやること：先ほどの JOIN クエリに EXPLAIN を付け、 INDEX が実際に使われているか確認する。

📥 入力データ：rdb_demo.db。

1
2
3
4
5
6
7

import sqlite3

conn = sqlite3.connect('rdb_demo.db')
plan = conn.execute("""EXPLAIN QUERY PLAN
    SELECT * FROM population_fact f JOIN pref_master m ON f.code = m.code
    WHERE f.year = 2023""").fetchall()
for row in plan: print(row)

📤 実行すると次の出力が得られる：

💬 結果の読み方：idx_year が使われている（SEARCH USING INDEX）。 マスタ側は PRIMARY KEY 検索。 「SCAN」が出たらフルスキャン、 INDEX 追加 or クエリ修正を検討するサイン。 EXPLAIN は性能改善の必須ツール。

📊 物理設計の基本

⚠️ クエリ設計の落とし穴

• SELECT *：不要列を引きまくり、 ネットワーク・メモリを浪費。 必要列だけ列挙。
• 関数 WHERE：WHERE UPPER(name) = 'TOKYO' は INDEX 無効化。 事前正規化 or 関数 INDEX を。
• ORDER BY 巨大ソート：メモリ溢れで遅い。 LIMIT 併用 + INDEX 利用。
• サブクエリ多用：CTE （WITH 句）で読みやすく + DB によっては最適化が効きやすい。
• 暗黙の型変換：文字列 / 数値 が混ざると INDEX 効かない。 型を揃える。

🧮 補講：SQL の基本構文を SSDSE で総ざらい

RDB を使うとは SQL を書くこと。 SSDSE-B-2026 を題材に、 SELECT / WHERE / GROUP BY / HAVING / ORDER BY / LIMIT / JOIN / サブクエリ / CTE / WINDOW を一気に体験します。

🐍 Python 実装 13：SELECT + WHERE + ORDER BY + LIMIT

🎯 このコードでやること：2023 年の人口 100 万人以上の都道府県を、 人口降順 TOP10 で表示。

📥 入力データ：rdb_demo.db。

1
2
3
4
5
6
7
8
9
10
11

import sqlite3
import pandas as pd

conn = sqlite3.connect('rdb_demo.db')
q = """SELECT m.pref_name, f.total_population
       FROM population_fact f JOIN pref_master m ON f.code = m.code
       WHERE f.year = 2023 AND f.total_population >= 1000000
       ORDER BY f.total_population DESC
       LIMIT 10"""
print(pd.read_sql(q, conn))
conn.close()

📤 実行すると次の出力が得られる：

💬 結果の読み方：100 万人以上に絞った TOP10。 SQL は「やりたいこと」を宣言的に書ける = 手続き型より生産性高い。

🐍 Python 実装 14：GROUP BY + HAVING で集計

🎯 このコードでやること：年ごとの「人口 500 万人以上の都道府県数」を集計（HAVING で集計後フィルタ）。

📥 入力データ：rdb_demo.db。

1
2
3
4
5
6
7
8
9
10
11
12

import sqlite3
import pandas as pd

conn = sqlite3.connect('rdb_demo.db')
q = """SELECT year, COUNT(*) AS big_prefs
       FROM population_fact
       WHERE total_population >= 5000000
       GROUP BY year
       HAVING COUNT(*) >= 8
       ORDER BY year DESC LIMIT 5"""
print(pd.read_sql(q, conn))
conn.close()

📤 実行すると次の出力が得られる：

💬 結果の読み方：直近 5 年、 500 万人超は常に 9 県（東京、 神奈川、 大阪、 愛知、 埼玉、 千葉、 兵庫、 北海道、 福岡）。 HAVING は GROUP BY 後の条件フィルタ、 WHERE は GROUP BY 前のフィルタ、 を明確に区別する。

🐍 Python 実装 15：CTE（WITH 句）で複雑クエリを段階化

🎯 このコードでやること：CTE で「各県の最新年人口」と「各県の平均人口」を別ステップで出し、 結合して「最新が平均より多い県」を抽出。

📥 入力データ：rdb_demo.db。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

import sqlite3
import pandas as pd

conn = sqlite3.connect('rdb_demo.db')
q = """WITH latest AS (
  SELECT code, total_population AS latest_pop
  FROM population_fact WHERE year = 2023
), avg_p AS (
  SELECT code, AVG(total_population) AS avg_pop FROM population_fact GROUP BY code
)
SELECT m.pref_name, l.latest_pop, a.avg_pop
FROM latest l JOIN avg_p a USING (code) JOIN pref_master m ON l.code = m.code
WHERE l.latest_pop > a.avg_pop
ORDER BY (l.latest_pop - a.avg_pop) DESC LIMIT 5"""
print(pd.read_sql(q, conn))

📤 実行すると次の出力が得られる：

💬 結果の読み方：東京・神奈川・埼玉・沖縄・千葉は「歴史平均より直近が多い」= 増加トレンド。 CTE で意図を段階的に書けば、 サブクエリ多重化より遥かに読みやすい。 dbt model も CTE が基本。

🐍 Python 実装 16：WINDOW 関数で前年比成長率

🎯 このコードでやること：LAG() ウィンドウ関数で前年の人口を参照し、 都道府県別の年次成長率を計算。

📥 入力データ：rdb_demo.db。

1
2
3
4
5
6
7
8
9
10
11
12
13
14

import sqlite3
import pandas as pd

conn = sqlite3.connect('rdb_demo.db')
q = """SELECT year, m.pref_name, total_population,
              LAG(total_population) OVER (PARTITION BY f.code ORDER BY year) AS prev_pop,
              ROUND(100.0 * (total_population -
                  LAG(total_population) OVER (PARTITION BY f.code ORDER BY year))
                / LAG(total_population) OVER (PARTITION BY f.code ORDER BY year), 3) AS growth_pct
       FROM population_fact f JOIN pref_master m ON f.code = m.code
       WHERE m.pref_name IN ('東京都', '沖縄県')
       AND year >= 2018
       ORDER BY pref_name, year"""
print(pd.read_sql(q, conn))

📤 実行すると次の出力が得られる：

💬 結果の読み方：LAG ウィンドウで「前年比成長率」を 1 クエリで生成。 東京は 2021 年が初の減少（コロナ）、 沖縄は 2021 年以降ほぼ横ばい。 GROUP BY と違い、 元の行粒度を保ったまま「行間計算」ができるのがウィンドウ関数の強み。

🧮 ボーナス：SSDSE 全 47 県 × 全 109 指標を 1 クエリで分析

🐍 Python 実装 19：long fact + 都道府県 × 指標横持ち集計

🎯 このコードでやること：long 形式 fact を全件投入し、 SQL で「総人口 / 65 歳以上人口 = 高齢化率」を計算、 2023 年で県別ランキングを取得。

📥 入力データ：先ほどの fact_long と pref_master。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

import sqlite3, pandas as pd

df = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', skiprows=[1])
df = df.rename(columns={'SSDSE-B-2026': 'year', 'Code': 'code', 'Prefecture': 'pref_name'})
long_df = df.drop(columns=['pref_name']).melt(id_vars=['year','code'], var_name='metric', value_name='value')

conn = sqlite3.connect('rdb_demo.db')
long_df.to_sql('fact_long', conn, if_exists='replace', index=False)

# 高齢化率 = A1303 (65歳以上) / A1101 (総人口) を SQL で
q = """SELECT m.pref_name,
              ROUND(100.0 * MAX(CASE WHEN f.metric='A1303' THEN f.value END) /
                          MAX(CASE WHEN f.metric='A1101' THEN f.value END), 2) AS aging_pct
       FROM fact_long f JOIN pref_master m ON f.code = m.code
       WHERE f.year = 2023
       GROUP BY m.pref_name
       ORDER BY aging_pct DESC LIMIT 5"""
print(pd.read_sql(q, conn))

📤 実行すると次の出力が得られる：

💬 結果の読み方：CASE 式で long 形式の指標を横展開し、 1 クエリで高齢化率を計算。 秋田 39.06% が全国最高。 long 形式 + CASE は「動的なピボット」として強力。 BI レポート定形パターン。

これで本ページの実装は 計 19 本。 SSDSE-B-2026 の 1 ファイルから「設計 → 投入 → 分析 → 最適化 → 運用」までを一気通貫で体験できる構成です。 ぜひ自分の手で実行し、 SQL の表現力を体感してください。

合成データで RDB の取引前後の整合性を確認する。

Step 1: 取引前残高

合計 = 15,000

Step 2: 1,000 を A→B 送金 (アトミック)

Step 3: ロールバックなら

🐍 Python で再現

1
2
3
4
5
6

A, B = 10000, 5000
amount = 1000
A_new, B_new = A - amount, B + amount
print(f"取引前 合計: {A + B}")
print(f"取引後 合計: {A_new + B_new}")
print(f"整合性: {(A_new + B_new) == (A + B)}")

📤 実行結果

💬 手計算 (Step 2) と Python 出力が完全一致。

🐍 Python での扱い

SSDSE-B-2026 のような公的統計データを Python で扱う際の基本パターン：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

import pandas as pd
import numpy as np

# データ読み込み
df = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', skiprows=1)
print(df.shape)
print(df.dtypes)
print(df.describe())

# 「リレーショナルDB」の文脈で扱う場合の例：
# 分野: データエンジニアリング
# 関連手法は同カテゴリの他用語を参照してください。

具体的なコードは データエンジニアリング を参照してください。

📝 レポートでの報告

分析結果を報告するときに含めるべき情報：

• 使ったデータ：出典・期間・サンプル数
• 適用条件の確認：前提が満たされているか
• 計算結果：数値だけでなく不確実性（CI・SE）も
• 解釈：何を意味するか、 何を意味しないか
• 限界：適用範囲外への拡張は避ける

✅ チェックリスト

• □ 「リレーショナルDB」を使う場面か再確認したか
• □ データの尺度・分布・サンプル数を確認したか
• □ 前提条件を満たしているか
• □ 計算した値だけでなく不確実性も把握したか
• □ 解釈と限界を区別したか
• □ 関連グループ教材で全体像を確認したか

🐍 Python 実装

(1) SQLite で SSDSE データ：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

# ── この抜粋で使うデータを用意します（SSDSE-B の 47 都道府県・最新年度）──
import pandas as pd

df = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', header=1)
df = df[df['地域コード'].astype(str).str.match(r'^R\d{5}$', na=False)].copy()
df['年度'] = pd.to_numeric(df['年度'], errors='coerce')
df = df[df['年度'] == df['年度'].max()]
for _c in df.columns[3:]:
    df[_c] = pd.to_numeric(df[_c], errors='coerce')
df['高齢化率'] = df['65歳以上人口'] / df['総人口'] * 100

# 見本でよく使われる仮の列名を、実データから作っておく
df['income'] = df['消費支出（二人以上の世帯）']
df['population'] = df['総人口']
_region = {'北海道': '北海道', '青森県': '東北', '岩手県': '東北', '宮城県': '東北',
           '秋田県': '東北', '山形県': '東北', '福島県': '東北', '茨城県': '関東',
           '栃木県': '関東', '群馬県': '関東', '埼玉県': '関東', '千葉県': '関東',
           '東京都': '関東', '神奈川県': '関東'}
df['region'] = df['都道府県'].map(_region).fillna('その他')
df['地域'] = df['region']

import pandas as pd, sqlite3

con = sqlite3.connect('ssdse.db')
df.to_sql('ssdse_b', con, if_exists='replace', index=False)

result = pd.read_sql_query("""
SELECT 都道府県, AVG(高齢化率) AS avg_aging
FROM ssdse_b
GROUP BY 都道府県
ORDER BY avg_aging DESC LIMIT 10
""", con)
print(result)

(2) SQLAlchemy で PostgreSQL：

1
2
3
4
5

from sqlalchemy import create_engine, text
engine = create_engine('postgresql://user:pass@localhost:5432/mydb')
with engine.connect() as conn:
    df = pd.read_sql_query(text("SELECT * FROM ssdse_b WHERE 年度=2024"), conn)
print(df.head())

(3) 安全な書き方（パラメータ）：

1
2
3
4
5
6
7
8

cur = con.cursor()
pref = '東京都'  # ユーザ入力想定

# 良い例（プレースホルダ）
cur.execute("SELECT * FROM ssdse_b WHERE 都道府県 = ?", (pref,))

# 悪い例（SQLインジェクション可）
# cur.execute(f"SELECT * FROM ssdse_b WHERE 都道府県 = '{pref}'")

🏢 主要 RDB の比較

🐍 補講：SSDSE で見る 1NF → 2NF → 3NF の段階的正規化

正規形は「概念」だけ覚えても使えません。 SSDSE-B-2026 を題材に、 wide 形式 → 1NF → 2NF → 3NF と段階的に変形し、 各段で何が変わるのかをコードで体感します。

📊 ステップ 1: 元の wide 形式（非正規）

SSDSE-B-2026 は実は「1NF を満たすが冗長」な形。 各セルは原子値だが、 (year, code) → pref_name の部分従属あり。

🐍 Python 実装 11：1NF → 2NF への分解

🎯 このコードでやること：SSDSE の wide テーブルから、 (year, code, metric, value) の long fact + (code, pref_name) のマスタ に分解する（2NF 化）。

📥 入力データ：data/raw/SSDSE-B-2026.csv（cp932）。

1
2
3
4
5
6
7
8
9
10
11
12
13

import pandas as pd

df = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', skiprows=[1])
df = df.rename(columns={'SSDSE-B-2026': 'year', 'Code': 'code', 'Prefecture': 'pref_name'})

# 2NF 化: マスタとファクトに分離
pref_master = df[['code', 'pref_name']].drop_duplicates().reset_index(drop=True)
fact_long = df.drop(columns=['pref_name']).melt(
    id_vars=['year', 'code'], var_name='metric', value_name='value')

print(f"pref_master 行数: {len(pref_master)}（47 都道府県）")
print(f"fact_long 行数 : {len(fact_long):,} 行")
print(f"重複 pref_name : 元 {len(df):,} 行 → マスタ {len(pref_master)} 行 ({100*len(pref_master)/len(df):.2f}%)")

📤 実行すると次の出力が得られる：

💬 結果の読み方：pref_name が 564 回繰り返されていた冗長性を 47 行に集約 = ストレージ削減 + 名称変更時の一括反映が可能に。 fact_long は (year, code, metric, value) のシンプルな構造で、 BI/分析に流しやすい。

🐍 Python 実装 12：2NF → 3NF — metric マスタを追加

🎯 このコードでやること：metric 列も「コード（A1101 等）→ 日本語名（総人口）」の対応がある。 metric_master を追加して 3NF 化。

📥 入力データ：SSDSE-B-2026.csv の 1 行目（カラム英語コード）と 2 行目（カラム日本語名）。

1
2
3
4
5
6
7
8
9
10
11
12
13

import pandas as pd

# 1 行目: 英語コード、 2 行目: 日本語名
header = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', nrows=1).columns.tolist()
jp_label = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', nrows=2).iloc[0].tolist()

# 最初の 3 列は year/code/pref なのでスキップ
metric_master = pd.DataFrame({'metric_code': header[3:], 'metric_jp': jp_label[3:]})
print(f"metric_master: {len(metric_master)} 行")
print(metric_master.head(8))
print()
print(f"3NF 後の合計セル: {47*2 + len(metric_master)*2 + 61476*4:,} = pref + metric + fact")
print(f"元 wide 形式   : {564 * 112:,} セル")

📤 実行すると次の出力が得られる：

💬 結果の読み方：metric_master 109 行が分離され、 metric_jp が一元管理可能に。 セル数は long 形式化で増えるが、 列指向圧縮（Parquet）と組み合わせれば実ストレージは縮む。 BI/ML での「指標切替」が SQL の WHERE metric_code = 'A1101' で済む利便性も得られる。

📐 セル数だけ見ると long は不利だが…

long 形式は「同じ値が縦に並ぶ → 圧縮率高い」「列追加に強い（新 metric_code 追加で済む）」という利点があります。 wide vs long は単純な比較ではなく、 ストレージ・クエリ・スキーマ進化のトレードオフです。

⚠️ 過剰正規化の警告

• 4NF / 5NF まで突き詰めると：JOIN が増えすぎて分析が遅い。 OLAP では 3NF 止まり + star schema が定石。
• マスタを切り出しすぎ：「全列をマスタ化」してリスト形式 DB に。 業務ドメインで意味のある粒度で止める。
• JOIN 性能を測らずに設計：3NF にした結果、 BI が秒単位で遅くなることも。 必ず実データで EXPLAIN + ベンチ。

⚠️ よくある落とし穴

⚠️ 落とし穴

🌐 補講：RDB vs NoSQL — SSDSE 規模で選ぶならどっち？

🌐 補講：RDB vs NoSQL — SSDSE 規模で選ぶならどっち？

「RDB か NoSQL か」は 2010 年代の大論争でしたが、 答えは 「用途で使い分け」に収束しました。 SSDSE-B-2026 を題材に整理します。

SSDSE のような 「中小規模・構造化・JOIN 多用」なら、 RDB が圧倒的に楽です。 「とりあえず NoSQL」は 2010 年代の遺物。 まず PostgreSQL/SQLite で始め、 水平スケールが本当に必要になったら NoSQL を検討、 が現代の正攻法。

🌐 補講：PostgreSQL vs MySQL vs SQLite — 選び方の指針

OSS RDB の三大選択肢を比較。 何を選ぶかでアーキテクチャが変わるので、 用途に応じて理解しましょう。

迷ったら PostgreSQL。 多機能・標準準拠・コミュニティ活発。 本ページでは SQLite を使いましたが、 PostgreSQL に置き換えても 95% のクエリはそのまま動きます（型・関数名の方言差のみ）。

📊 各 DB のキャラクター

• PostgreSQL：「全方位対応の Linux」。 拡張モジュール（PostGIS, pgvector）も豊富。
• MySQL / MariaDB：「Web 御用達」。 WordPress, Rails アプリの定番。
• SQLite：「ファイル 1 つで完結する DB」。 iPhone, Android, ブラウザ内部でも使われる。

⚠️ 移行時の落とし穴

• SQLite → PostgreSQL：型が厳密に変わる（SQLite は弱い型）。 文字列で入れていた数値が PG で reject される。
• MySQL → PostgreSQL：自動採番（AUTO_INCREMENT vs SERIAL）、 LIMIT/OFFSET vs FETCH、 大文字小文字感度（PG は識別子で大文字小文字区別）。
• SQL 方言：日付関数、 文字列関数、 ウィンドウ関数の細部が違う。 ANSI SQL の範囲で書くと移植性高い。

🔗 関連用語（前提・並列・発展）

役割で 3 区分: 前提（先に押さえる基礎概念） / 並列（同レベルで併走する手法） / 発展（応用・派生・上位概念）。

📘 前提となる用語

🤝 並列・関連する用語

🚀 発展・派生する用語

⚙️ インデックス設計

❓ FAQ

📜 歴史

• 1970年：Edgar Codd の関係モデル論文
• 1974年：IBM System R、SQL の起源
• 1979年：Oracle V2 リリース、商用
• 1995年：MySQL リリース
• 1996年：PostgreSQL 6.0 リリース
• 2000年：SQLite リリース
• 2010年代：NoSQL の台頭、 RDB との使い分け
• 2020年代：NewSQL、 分散 RDB、 PostgreSQL の AI ベクトル拡張

📚 関連グループ教材

この用語の全体像を学ぶには、 まず横断的な教材で文脈を掴むのが効率的です：

• 📚 データエンジニアリング — このカテゴリの全体像を学ぶ
• 📚 データリテラシー — このカテゴリの全体像を学ぶ
• 📚 RDB 入門 — より基礎
• 📚 データガバナンス — 管理体制

🔗 同カテゴリの他用語

📚 チートシート：SQL の必修構文一覧

この 19 構文を覚えれば、 SSDSE クラスのデータベース操作は 95% 賄えます。 本番 RDB（PostgreSQL/MySQL）にも同じ SQL が（方言差はあれ）通用します。

📚 最終まとめ：RDB を「使える」ための 12 のチェックリスト

1. テーブル設計時に PK と FK を明示したか
2. 1NF / 2NF / 3NF を意識して分解したか
3. NOT NULL / UNIQUE / CHECK 制約を貼ったか
4. INDEX を WHERE / JOIN / ORDER BY 列に貼ったか
5. INSERT/UPDATE は適切にトランザクションで包んだか
6. SELECT * を避け必要列のみ取得したか
7. JOIN と WHERE を整理し、 必要な行のみ JOIN しているか
8. GROUP BY 後の絞り込みは HAVING にしたか
9. サブクエリ多用を CTE で整理したか
10. ウィンドウ関数で行間計算を 1 クエリ化したか
11. EXPLAIN で実行計画を確認したか
12. バックアップ / リカバリ手順を整備したか

これら 12 項目を意識すれば、 SSDSE クラスはもちろん、 数億行の業務 DB でも応用が利きます。 RDB の習熟は「コードよりも設計」が 80%。 まずスキーマを紙に書いてから、 SQL を書く習慣をつけてください。

📚 おすすめ書籍 / リソース

• 『達人に学ぶ SQL 徹底指南書』（ミック）— SQL の思考法を鍛える名著
• 『SQL アンチパターン』（Bill Karwin）— よくある失敗の事例集
• 『失敗から学ぶ RDB の正しい歩き方』（曽根壮大）— 設計の罠を網羅
• PostgreSQL 公式ドキュメント— 機能リファレンスの決定版
• SQLBolt / Mode Analytics SQL Tutorial— ブラウザで SQL を学べる

🗺 あなたが今見ているもの — RDB の全体地図 (冒頭文脈の再掲)

この補講は、 ここまでの「正規化・ACID・JOIN・SQL」の各論を 1 枚絵で俯瞰し直す ためのまとめです。 あなたが今見ているものは「RDB という統計データ・業務データを格納する標準箱の中身」であり、 ジャストインタイム型データサイエンス教育では、 SSDSE-B-2026 (47 都道府県 × 109 指標) を例題に「読みやすい表＝統計の前提となる正規化された表」をまず手に入れることを最優先で扱います。 冒頭文脈をもう一度押さえます: RDB は「分析の信頼性 (正しさ・再現性)」を保証する装置であり、 ノートブックで pandas を叩く前段にある「データの基盤」です。

📊 図 1: RDB 設計フロー (概念→論理→物理)

下図は SSDSE-B-2026.csv を RDB に格納する際の 3 段階設計を模式化したもの (実際の散布図で代用)。 概念設計 (エンティティ抽出) → 論理設計 (正規化と関係定義) → 物理設計 (インデックス・パーティション) の順に進める。

→ 散布図の各点を「テーブル候補」と読み替えると、 まとまりごとに正規化単位 (prefecture / population_year / industry_year など) が見えてくる。 RDB 設計はまさにこの「点を線でくくる」作業に似ている。

📊 図 2: 都道府県人口のヒストグラム — テーブル化前の確認

RDB に格納する前に必ず「分布を見て CHECK 制約と型を決める」。 下のヒストグラムは SSDSE-B-2026 (2023 年・47 都道府県) の総人口分布。 右に大きく外れる東京を見て、 population BIGINT CHECK (population >= 0) 程度の制約に落ち着くことが分かる。

→ 最大値は 14,047,594 (東京) で、 INT (2,147,483,647 上限) で十分だが、 将来的な世界都市データ統合を見越して BIGINT を採用しておくと安全。 「分布を見て型を決める」は RDB 設計の鉄則。

📊 図 3: 多地域比較ボックスプロット — GROUP BY の前提分布

SQL の GROUP BY region で集計する前に、 ボックスプロットで 地域内ばらつき を確認する。 内的ばらつきが大きいほど平均値の意味は薄れ、 中央値や四分位を出すべきと判断できる。

→ ボックスが縦に長い地域 (関東) は中の都県差が大きい。 RDB では PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY population) で中央値を取れる (PostgreSQL/Oracle)。 MySQL では ROW_NUMBER() で擬似的に実装する。

🔬 補講: RDB と pandas — どちらでやるべきか

SSDSE のような 約 560 行・約 110 列クラスのデータでは、 多くの操作は pandas でも RDB でも書ける。 ではどちらを選ぶべきか。 結論を先に書くと「データの正本 (Source of Truth) は RDB、 分析の作業場は pandas」がほぼ全ての現場で採用されているパターンである。 理由は以下の通り。

→ 実務では RDB → SELECT で部分抽出 → pandas で分析 → 結果テーブルとして RDB に書き戻し のパイプラインが標準。 SSDSE のような公開データを扱う研究室なら、 SQLite を 1 つ立てて全員で参照するだけでも、 「ファイル名が違って分析がズレた」 系の事故を激減させられる。

🐍 Python 実装: SSDSE-B-2026 を SQLite に投入して都道府県別集計

このコードでやること: SSDSE-B-2026.csv を読み込み、 SQLite に prefecture + population_year の 2 テーブルとして正規化投入、 SQL で「2023 年人口上位 5 県」を集計する。 RDB を「データ分析の正本」として扱う標準パターン。

📥 入力データ (SSDSE-B-2026 抜粋 — 投入前の df.head()):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

import sqlite3
import pandas as pd

# 1. CSV を読み込み (1 行目=英字コード, 2 行目=日本語名なので skiprows=[1])
d = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', skiprows=[1])

# 2. 2023 年のみ抽出 (年度列の名前は 'SSDSE-B-2026')
d23 = d[d['SSDSE-B-2026'] == 2023]

# 3. SQLite 接続（同じページの他の例がファイル DB を掴んでいることがあるのでメモリ上に作る）
conn = sqlite3.connect(':memory:')

# 4. 正規化: prefecture (県マスタ) と population_year (年別人口) に分割投入
pref = d23[['Code', 'Prefecture']].drop_duplicates()
pref.columns = ['pref_code', 'name']
pref.to_sql('prefecture', conn, if_exists='replace', index=False)

pop = d23[['Code', 'A1101']].copy()
pop.columns = ['pref_code', 'population']
pop['year'] = 2023
pop.to_sql('population_year', conn, if_exists='replace', index=False)

# 5. SQL で集計: 2023 年人口上位 5 県
sql = """
SELECT p.name, py.population
FROM   prefecture p
JOIN   population_year py ON p.pref_code = py.pref_code
WHERE  py.year = 2023
ORDER  BY py.population DESC
LIMIT  5
"""
print(pd.read_sql(sql, conn))

📤 実行すると次の出力が得られる:

💬 結果の読み方: 「県マスタ」と「年別人口」に分けたおかげで、 将来 2024 年のデータが入っても population_year に 47 行追加するだけで済む。 もし 1 つのテーブルに pop_2023, pop_2024, pop_2025 と横に並べていたら、 毎年 ALTER TABLE ADD COLUMN が必要になり、 BI ツールのクエリも全部書き直しになる。 これが「縦持ち (long format) で正規化する」 ことの実利。

📚 RDB 設計でよくある 5 つの失敗パターンと修正

→ SSDSE データを RDB 化する研究室で最も多いのは パターン 1 (1 セルに複数値) と パターン 4 (1 テーブル肥大化)。 ジャストインタイム教育では、 「最初は CSV 1 枚で OK、 ただし 200 行を超えたら必ず正規化」 を経験則として教えると衝突が少ない。

🌐 関連用語: RDB を取り巻く周辺技術

📝 理解度チェック (10 問)

この補講で扱った内容について、 自分で答えてみよう。 即答できない問題があれば該当セクションに戻って読み直す。

1. 第 1 正規形 (1NF) 違反の典型例を SSDSE データで 1 つ挙げよ。 → ヒント: 1 セル複数値
2. 第 3 正規形 (3NF) で削除される依存関係は何か。 → ヒント: 推移的関数従属
3. 外部キー (FK) を宣言する 3 つのメリットは何か。 → ヒント: 整合性 / カスケード / 文書性
4. ACID の D (Durability) が破られる典型シナリオは何か。 → ヒント: COMMIT 直後の停電
5. READ COMMITTED と REPEATABLE READ の違いを SSDSE 文脈で説明せよ。 → ヒント: 同一トランザクション内の再読み込み
6. INNER JOIN と LEFT JOIN の結果行数差はどのような場合に発生するか。 → ヒント: 子側のマッチ無し
7. SSDSE-B-2026 を SQLite に投入するとき、 テーブルを 1 つにせず分けるべき理由を 2 つ挙げよ。 → ヒント: 重複削減 / 拡張性
8. インデックスを張りすぎると遅くなる操作は何か。 → ヒント: INSERT / UPDATE
9. NULL の意味を 3 種類区別せよ。 → ヒント: 未入力 / 欠損 / 該当無し
10. SSDSE で 500 行のデータを扱うとき、 RDB と pandas のどちらを「正本」とすべきか、 理由とともに答えよ。 → ヒント: 同時編集 / 制約 / 履歴

→ 解答例は明示しないが、 すべて本文・補講・上の表のどこかに根拠がある。 自分で答えを書き出してから本文に戻ると、 RDB の設計判断が「定型作業」 ではなく「分析の信頼性を担保する明示的設計」 として腑に落ちる。

🧪 補講: SSDSE-B-2026 を 3NF で正規化する具体手順

SSDSE-B-2026 を素直に CSV のまま投入すると 1 行 = 1 県、 1 列 = 1 指標の横持ち (109 指標列) になる。 これは BI の一枚絵には便利だが、 RDB の世界では 「指標が増える度に ALTER TABLE が必要」という致命的な保守コストを生む。 ここでは縦持ち (long format) の 3NF テーブル設計を、 SSDSE 4 指標を例に段階的に示す。

→ 3NF に到達すると、 「指標を 1 つ追加」 は indicator_master に 1 行追加 + indicator_value に 47 行追加だけで済む。 0NF (CSV) では新しい列を追加してから全 BI クエリを書き直す必要があった。 SSDSE が毎年更新される現実を考えれば、 縦持ち 3NF が圧倒的に低コスト。

🐍 0NF → 3NF を Python で機械的に変換

このコードでやること: SSDSE-B-2026 (横持ち 109 指標列) を pandas で melt し、 3 つの正規化テーブルに分けて SQLite に投入する。 横持ち → 縦持ち変換は pd.melt 一発。

📥 入力データ (0NF, 47 行 × 109 指標列の先頭抜粋):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

import pandas as pd, sqlite3

df = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', skiprows=[1])

# 2023 年のみ抽出 → 1 県 1 行の 0NF 横持ちにする
d23 = df[df['SSDSE-B-2026'] == 2023].drop(columns=['SSDSE-B-2026'])

# 1NF: 横持ち → 縦持ち (Code/Prefecture 以外の指標列を melt)
long = d23.melt(id_vars=['Code', 'Prefecture'],
                var_name='indicator_code',
                value_name='value')
long['year'] = 2023
long = long.rename(columns={'Code': 'pref_code', 'Prefecture': 'name'})

# 2NF: prefecture (name の部分従属を分離)
prefecture = long[['pref_code', 'name']].drop_duplicates()
indicator_value = long[['pref_code', 'indicator_code', 'year', 'value']]

# 3NF: indicator_master (label/unit の推移従属を分離 — ここでは仮値で生成)
indicator_master = pd.DataFrame({
    'indicator_code': long['indicator_code'].unique(),
    'label': '(SSDSE 指標)',
    'unit': '(単位)',
})

conn = sqlite3.connect('ssdse_3nf.db')
prefecture.to_sql('prefecture', conn, if_exists='replace', index=False)
indicator_value.to_sql('indicator_value', conn, if_exists='replace', index=False)
indicator_master.to_sql('indicator_master', conn, if_exists='replace', index=False)

print('prefecture rows:', len(prefecture))
print('indicator_value rows:', len(indicator_value))
print('indicator_master rows:', len(indicator_master))

📤 実行すると次の出力が得られる:

💬 結果の読み方: 47 県 × 109 指標 = 5,123 行が縦持ちで生まれる。 一見「行数が爆発した」 ようだが、 これが 正規化の正常な姿。 indicator_value にインデックス (indicator_code, year) を張れば検索は O(log n) で済むので、 BI 用途でもパフォーマンス劣化は起きない。 むしろ「指標追加が ALTER TABLE 不要」「複数年データの統合が UNION 不要」 という保守利得が圧倒的。

🔚 まとめ: RDB を「使える道具」にするための最後の 3 行

• (1) 正規化はパフォーマンスのためではなく「変更に強い構造」を作るためにやる。 SSDSE が毎年更新されることを思い出せば、 横持ちより縦持ちの方が将来コストが低いと分かる。
• (2) ACID は「分析の信頼性」を裏で支える静かな機能。 普段は意識しなくてよいが、 並行更新や障害時にこそ価値が出る。 NoSQL を選ぶ前に「自分のデータは ACID が要らないか」 を必ず問う。
• (3) RDB は「データの正本 (Source of Truth)」 として置き、 pandas/Polars を「作業場」として使う。 この役割分担を徹底すれば、 ファイル名違いや手作業ミスによる分析事故は劇的に減る。

🔗 隣接手法への橋渡し

「リレーショナルDB」は単独で完結する手法ではなく、 隣接領域と連携することで真価を発揮する。

• 上流: RDB・SQL の基礎の上に位置
• 並列: NoSQL・データウェアハウスと比較し、 厳格な整合性が必要なら RDB
• 下流: BI ツール・データパイプラインで利活用

SSDSE-B-2026 を SQLite に正規化投入し、 マスタ + 観測の JOIN + 集計 + ビュー定義まで通すと、 CSV 直読みでは見えなかった「整合性 + 速度 + 共有」が得られる。

🌳 手法選択フロー

「RDB (リレーショナルデータベース) 詳細」を実際の課題に当てはめるとき、 状況別に何を選ぶかを 3 段階で判定する。

1. スキーマは固定か可変か? 固定 → RDB (PostgreSQL/MySQL)、 可変 → NoSQL (MongoDB)
2. トランザクションは必要か? Yes (金融・在庫) → RDB の ACID、 No (ログ集計) → DWH/ストリーム
3. スケールは縦/横どちら? 縦 (大きな単一サーバ) → RDB、 横 (分散) → NewSQL / NoSQL

SSDSE-B-2026 を 47 行 × 112 列で扱う限りは SQLite で十分。 県別・年別の集計テーブルを SQL で書けば、 pandas より高速かつ再現性が高い。