🔖 キーワード索引

本ページは DML（データ操作言語）（Data Manipulation Language）を多角的に解説します。 上のチップは、 検索・関連語の手がかりです。

「dml」は統計データ分析の文脈で扱う重要概念のひとつ。 本ページでは「dml」を取り巻く中核キーワードを以下にチップで一覧化する。 各キーワードは関連する概念・手法・道具立てを含み、 文献検索や学習計画の起点になる。

これらのキーワードは「dml の理解 → 適用 → 検証」のプロセスを構成する。 各章で詳しく解説する。

🎨 直感で掴む — 具体例で理解する

SQL の命令は大きく 3 つに分類されます：

本ページで扱う DML は 「データの読み書き」そのもの。 業務アプリで一番使う命令群です。

📐 定義

SELECT/INSERT/UPDATE/DELETE 等の操作言語。 関係代数の視点では、 各命令は集合演算として形式化される:

ここで $R$ はリレーション（テーブル）、 $\sigma_p$ は条件 $p$ による選択、 $\pi$ は射影、 $f$ は更新写像。 英語名 Data Manipulation Language、 カテゴリ：データエンジニアリング。

📐 関係代数の全演算 — 数式を言葉で読み解く

SQL は関係代数の 実用版。 純粋な集合論的扱いから、 NULL や重複の扱いが現実的な妥協を含む。

🔬 数式を言葉で読み解く

🔬 EXPLAIN で実行計画を読む

SQL の高速化は EXPLAIN で内部を見ることから。 PostgreSQL の例:

「Seq Scan」は全行スキャン。 「Index Scan」「Bitmap Heap Scan」が出ればインデックスが効いている。 大規模データでは Seq Scan を Index Scan に変えるだけで 100 倍速くなることも。

👁 ビューとマテリアライズドビュー

💾 ストレージエンジンと内部構造

☁ クラウド DB と DWH

📊 主要データ型

✅ DML ベストプラクティス 20

1. UPDATE/DELETE 前に SELECT で確認
2. BEGIN; ROLLBACK; の儀式を習慣化
3. プレースホルダで SQL インジェクション防止
4. SELECT * を避ける、 必要列を明示
5. JOIN は ON で条件指定
6. WHERE で関数を使わない (インデックス無効化)
7. LIKE のワイルドカードは右側のみ
8. NULL は IS NULL で比較
9. 大量 INSERT はバルクで
10. EXPLAIN で実行計画確認
11. 適切なインデックス追加
12. 過剰なインデックスは INSERT を遅くする
13. 定期的な VACUUM/ANALYZE
14. ストアドプロシージャより アプリ側でロジック
15. マテリアライズドビューで重い集計を高速化
16. パーティションで大規模テーブル分割
17. ORM 使用時は N+1 問題に注意
18. マイグレーション (Flyway, Alembic) で変更管理
19. バックアップを定期的に
20. 本番では権限を最小化 (DCL)

💭 考察問題

1. 歴史的考察: なぜ SQL は 50 年経っても代替されないのか? Codd の関係モデルの何が革新的だったか?
2. 技術的考察: NoSQL ブームが過ぎ、 NewSQL (SQL 互換の分散 DB) が台頭する理由
3. 設計考察: 完全正規化 (3NF) と非正規化、 どちらが正しいか? シチュエーション別に
4. パフォーマンス考察: SSDSE-B-2026 (564 行) を SQLite/pandas/PostgreSQL で処理する場合の速度比較
5. 運用考察: 「マイグレーション失敗で本番停止」を防ぐためのプロセスは?
6. セキュリティ考察: SQL インジェクション以外の DB セキュリティリスクは何があるか?
7. 未来予測: 生成 AI と DML の関係 — 自然言語から SQL を生成する Text-to-SQL の未来
8. 教育考察: 高校生にプログラミング教育する際、 SQL は何番目に教えるべきか?

🤖 LLM 時代の DML — Text-to-SQL

「日本語で質問するだけで SQL が自動生成される」時代が到来しています。 これは DML の未来を変える可能性があります。

「東京都の 2023 年人口を教えて」と聞けば SQL が生成され、 DB から答えが返る。 ただし生成された SQL の正しさ検証は人間の責任。

🇯🇵 日本での DML 活用

• 政府基幹系: 住基ネット、 マイナンバー基盤 (Oracle / PostgreSQL)
• 金融: 銀行勘定系 (大型コンピュータ + DB2/Oracle)
• EC: 楽天 (MySQL + 自社拡張)、 Amazon Japan (Oracle/DynamoDB)
• SNS: LINE (MySQL/Redis)
• 製造: トヨタの生産管理 (SAP HANA, Oracle)
• 政府データ: e-Stat の裏側 (PostgreSQL)
• 研究: 独立行政法人統計センターの SSDSE データ (CSV → PostgreSQL/SQLite)
• 教育: 学校基本調査の集計 (SAS, R, SQL)

日本の社会インフラの大半が SQL + DML で動いている。 「目に見えないが社会を支える技術」の代表例。

🎓 学習パス

1. Week 1: SQLite インストール、 SSDSE-B-2026 を投入
2. Week 2: SELECT の基本 (WHERE, ORDER BY, LIMIT)
3. Week 3: GROUP BY と集計関数
4. Week 4: JOIN を理解
5. Week 5: UPDATE / DELETE / INSERT (トランザクションも)
6. Week 6: サブクエリと CTE
7. Week 7: Window 関数
8. Week 8: インデックスと EXPLAIN
9. Week 9: PostgreSQL に移行、 本番機能を学ぶ
10. Week 10: ORM (SQLAlchemy) を学ぶ
11. Week 11: パフォーマンスチューニング
12. Week 12: 実プロジェクトで運用

🔗 学習リソース

• SQLZoo: ブラウザで SQL を学べる無料サイト
• HackerRank SQL: 問題を解いて学ぶ
• LeetCode Database: 中〜上級者向け
• StrataScratch: 実務的な SQL 問題
• Coursera "Databases" by Stanford: 古典的講義
• Khan Academy SQL: 入門に最適
• Mode SQL Tutorial: BI ツール統合
• PostgreSQL 公式チュートリアル: 公式ドキュメント
• 達人 SQL (ミック): 日本語の名著
• SQLBolt: 短時間で基本を学ぶ

🐼 pandas 操作と SQL 完全対応表

🚫 アンチパターン集

• SELECT *: 不要な列まで取って遅い、 スキーマ変更で壊れる
• WHERE 1=1: 動的クエリ生成時の悪習
• WHERE 句なしの UPDATE/DELETE: 全行破壊
• サブクエリ濫用: JOIN や CTE の方が読みやすい
• カラムに関数: WHERE LOWER(name)=... はインデックス無効
• LIKE の両端 %: インデックス無効
• NOT IN: NULL を含むとバグ
• 暗黙の JOIN: WHERE t1.id = t2.id はやめ、 JOIN ON に
• 大量データの 1 行ずつ INSERT: バルクで
• 本番でテスト DELETE: ステージング環境を使う
• auto_commit off で COMMIT 忘れ: データが反映されない
• ストアドプロシージャ濫用: ロジックがアプリと DB に分散
• 過剰インデックス: INSERT/UPDATE が遅くなる
• NULL を 0 で代用: 集計が狂う
• 日付を VARCHAR で保存: 比較・並び替えがおかしい

📂 ケーススタディ集

ケース ① 銀行送金

A → B に 1 万円送金:

ACID の Atomicity が必須。 片方だけ成功して片方失敗は許されない。

ケース ② 在庫管理

商品の在庫が複数あるとき、 同時に複数の注文が来たら:

ケース ③ ユーザー集計

「最近 30 日でアクティブな県別ユーザー数」:

ケース ④ SSDSE × 補助データの分析

SSDSE-B-2026 と外部の経済指標を JOIN して相関分析:

📦 マイグレーション — DB スキーマ変更管理

本番 DB のスキーマ変更を安全に行うのが マイグレーション。 ツール一覧:

マイグレーションは「up (適用)」と「down (取り消し)」のペア。 失敗時に即ロールバックできる。

💾 バックアップとリストア

「バックアップを取っただけでリストア練習をしていない = バックアップなし」というのが業界の格言。 必ず復元テストを。

🔬 集合代数とトランザクション意味論 — 深掘り

DML (SELECT / INSERT / UPDATE / DELETE) は数学的には「関係代数」+「トランザクション意味論」で形式化される。 関係 R を集合と見なすと、 各演算は集合演算に対応する:

このコードでやること: SSDSE-B-2026 を SQLite に投入し、 SELECT で集約 → UPDATE で派生列を一括計算 → トランザクション内で BEGIN/COMMIT を使う一連の DML フローを示す。

📥 入力データ:

 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

import pandas as pd
import sqlite3

# 英字の項目コードを使うので、2 行目の日本語名を読み飛ばす
df = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', skiprows=[1])
df = df[df['SSDSE-B-2026'] == df['SSDSE-B-2026'].max()].copy()   # 最新年 (2023) の 47 県

conn = sqlite3.connect(':memory:')
# 必要列だけ投入
# 注意: SSDSE-B に面積 (B1101) の列は無いので、人口密度ではなく
#       収録されている 65 歳以上人口 (A1303) から高齢化率を計算する
df[['Prefecture', 'A1101', 'A1303']].to_sql('pref', conn, index=False)

cur = conn.cursor()
cur.execute('BEGIN')
# ALTER で派生列を追加し UPDATE で高齢化率を計算
cur.execute('ALTER TABLE pref ADD COLUMN aging_rate REAL')
cur.execute('UPDATE pref SET aging_rate = A1303 * 100.0 / A1101')  # %
cur.execute('COMMIT')

# 高齢化率 32% 以上の上位 5 件
rows = cur.execute(
    'SELECT Prefecture, ROUND(aging_rate, 1) FROM pref '
    'WHERE aging_rate > 32 ORDER BY aging_rate DESC LIMIT 5'
).fetchall()
for r in rows:
    print(r)

📤 実行例:

💬 結果の読み方: BEGIN/COMMIT で囲んだ UPDATE は ACID の「Atomicity」を担保し、 失敗時は ROLLBACK で全て巻き戻る。 1 文の UPDATE で 47 行すべてに派生列 aging_rate (%) を一括計算でき、 続く SELECT (WHERE aging_rate > 32) で秋田 39.1%・高知 36.3%・徳島 35.4%・山口 35.4%・青森 35.2% と上位 5 県が即座に得られる。 徳島と山口は小数 1 桁に丸めると同じ 35.4 だが、 並べ替えは丸める前の値で行われている。 pandas でも df['aging_rate'] = df['A1303'] * 100 / df['A1101'] の 1 行で書けるが、 SQL の UPDATE はデータをデータベースに置いたまま宣言的に書き換えられ、 トランザクションで守られる点が DML の威力。

🖼 視覚で確認する：DML 結果を集計・可視化する

DML（INSERT/UPDATE/DELETE/SELECT）の結果は、 多くの場合「集計→分布→ランキング」という3段階で可視化されます。 SSDSE-B-2026 を例にした典型的な可視化を3点示します。

DML は数値を返すだけで完結せず、 続く可視化と組み合わせることで意思決定に直結する情報になります。

🧮 実値で計算してみる

具体的な DML 操作の例（都道府県データ）：

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

-- 1. SELECT（読み取り）
SELECT 都道府県名, 高齢化率, 死亡率
  FROM 統計表
 WHERE 高齢化率 > 30
 ORDER BY 高齢化率 DESC
 LIMIT 10;

-- 2. INSERT（追加）
INSERT INTO 統計表 (都道府県名, 高齢化率, 死亡率)
VALUES ('東京都', 23.4, 9.8);

-- 3. UPDATE（更新）— WHERE 必須
UPDATE 統計表
   SET 死亡率 = 10.5
 WHERE 都道府県名 = '東京都';

-- 4. DELETE（削除）— WHERE 必須
DELETE FROM 統計表
 WHERE 年 < 2010;

赤線：UPDATE と DELETE で WHERE を忘れると全行が一発で書き換えられる。 必ず先に SELECT で確認すること。

DML 操作後の行数変化を数値で確認する

初期テーブルに 5 都道府県が登録されているとする。高齢化率 (%) のベクトルは（SSDSE-B-2026 の 2023 年、65 歳以上人口 A1303 ÷ 総人口 A1101 × 100）:

高齢化率 データ: [33.0, 22.8, 39.1, 35.0, 34.9]（北海道・東京・秋田・岩手・島根 の順）

初期行数 $n_0 = 5$。 以下の DML を順に実行した場合の行数変化を手計算する。

同じ計算を Python で再現する:

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

import numpy as np

# 初期テーブル: 5 都道府県の高齢化率 (SSDSE-B-2026, 2023 年, A1303/A1101)
aging_rates = [33.0, 22.8, 39.1, 35.0, 34.9]
n0 = len(aging_rates)  # 5

# Step 1: INSERT 2 件
delta_insert = [2]
n1 = n0 + sum(delta_insert)  # 7

# Step 2: DELETE WHERE 高齢化率 < 25 → 東京都の 22.8 が 1 件マッチ
delta_delete = [1]
n2 = n1 - sum(delta_delete)  # 6

# Step 3: UPDATE は行数変化なし
n_final = n2  # 6

# 数式で直接計算
n_formula = n0 + sum(delta_insert) - sum(delta_delete)
print(f"手計算: {n_final}, 数式: {n_formula}, 一致: {np.allclose(n_final, n_formula)}")

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

💬 手計算 6 と Python 出力 6 で一致。 np.allclose が True を返したことで、 $n_{\text{final}} = 5 + 2 - 1 = 6$ が数式・手計算・実装の三者で同じ値であることが確認できる。

🧮 SSDSE-B-2026 を SQLite に投入 — 数式を言葉で読み解く

DML の真価は 実データで体感するのが一番。 SSDSE-B-2026 (47 都道府県 × 12 年 = 564 行) を SQLite に投入し、 様々な SELECT/UPDATE を試してみましょう。

関係代数の 3 つの基本演算 — 数式を言葉で読み解く

例えば「東京都の 2023 年人口」を取るクエリ:

🐍 Python 実装

このコードでやること: SSDSE-B-2026.csv を SQLite に読み込み、 pd.read_sql で SELECT 文を実行して結果を DataFrame として取得する最小構成。 DML の基本フローをそのまま Python で確認できる。

📥 入力データ (SSDSE-B-2026 抜粋):

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

# ── この抜粋で使うデータを用意します（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
import sqlite3

# SQLite で DML を実行
conn = sqlite3.connect('mydata.db')
df.to_sql('stat_table', conn, if_exists='replace', index=False)

# SELECT を pandas で
result = pd.read_sql('SELECT * FROM stat_table WHERE 高齢化率 > 30', conn)
print(f'{len(result)} 件')
print(result[['都道府県', '総人口', '高齢化率']].head())

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

💬 結果の読み方: WHERE 高齢化率 > 30 に当てはまるのは 47 都道府県中 35 件で、 30% 以下は東京 (22.8%)・沖縄 (23.8%) など 12 都府県しかない。 ORDER BY を付けていないので先頭 5 行は北海道・青森・岩手・秋田・山形と投入順 (地域コード順) のままで、 秋田 39.1% が 4 行目に来る。 高い順に見たいなら ORDER BY 高齢化率 DESC を書く必要がある。

🐍 Python 実装 — pandas + SQLite で DML を体感

① SSDSE-B-2026 を SQLite に投入 (INSERT)

🎯 このコードでやること: SSDSE-B-2026.csv (564 行) を SQLite データベースに投入し、 SQL でクエリ可能な状態にする。 pandas の to_sql は内部で INSERT を実行。

📥 入力データ: SSDSE-B-2026.csv (47 都道府県 × 12 年 = 564 行 × 112 列)

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

import pandas as pd
import sqlite3

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

# 年列の名前を分かりやすく
df = df.rename(columns={'SSDSE-B-2026': 'year'})

# SQLite に投入 (INSERT を内部で実行)
conn = sqlite3.connect('ssdse.db')
df.to_sql('ssdse_b', conn, if_exists='replace', index=False)

# 確認
count = pd.read_sql('SELECT COUNT(*) AS n FROM ssdse_b', conn).iloc[0,0]
print(f'投入レコード数: {count}')

# テーブル構造確認
schema = pd.read_sql('PRAGMA table_info(ssdse_b)', conn)
print(f'カラム数: {len(schema)}')
print('先頭 5 カラム:', schema['name'].head().tolist())

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

💬 結果の読み方: 47 都道府県 × 12 年 = 564 行が正しく投入。 pandas の to_sql は INSERT 文を 1 行ずつ発行するのではなく、 内部でバルクインサート (executemany) を使い高速。

② SELECT で人口上位 10 都道府県を取得

🎯 このコードでやること: 2023 年の人口上位 10 都道府県を SELECT で取得。 WHERE + ORDER BY + LIMIT の基本パターン。

📥 入力データ: ssdse.db の ssdse_b テーブル (564 行)

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

import pandas as pd
import sqlite3

conn = sqlite3.connect('ssdse.db')
query = '''
SELECT Prefecture, A1101 AS 人口
  FROM ssdse_b
 WHERE year = 2023
 ORDER BY A1101 DESC
 LIMIT 10;
'''
result = pd.read_sql(query, conn)
print(result.to_string(index=False))

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

💬 結果の読み方: 上位 10 都道府県は東京・神奈川・大阪を含む大都市圏 + 北海道・福岡などの地方中核。 SELECT は DML 中で最も使われる命令で、 全 DML 命令の 95% 以上を占める (実務感覚)。

③ GROUP BY で年代別集計

🎯 このコードでやること: 47 都道府県の合計人口を年代ごとに集計し、 日本の人口推移を確認する。 GROUP BY + SUM の基本パターン。

📥 入力データ: ssdse.db の ssdse_b テーブル

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

import pandas as pd
import sqlite3

conn = sqlite3.connect('ssdse.db')
query = '''
SELECT year,
       SUM(A1101) AS 全国人口合計,
       AVG(A1101) AS 平均都道府県人口,
       COUNT(*) AS 都道府県数
  FROM ssdse_b
 GROUP BY year
 ORDER BY year;
'''
result = pd.read_sql(query, conn)
print(result.to_string(index=False))

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

💬 結果の読み方: 全 47 都道府県を合計すると 2012 年 1 億 2,758 万 9 千人 → 2023 年 1 億 2,435 万 3 千人で、 11 年で約 324 万人 (2.5%) 減った。 AVG は SUM を 47 で割っただけなので、 平均都道府県人口 264 万人は東京 1,409 万人に引っ張られた値で、 中央値 155 万人よりずっと大きい。 GROUP BY は実務で最も使われる DML 構造の 1 つ。 「集計の単位 + 集計関数 (SUM/AVG/COUNT/MAX/MIN)」がセットで覚えるのがコツ。

④ UPDATE と トランザクション

🎯 このコードでやること: 東京都の 2023 年人口を仮に更新する例。 UPDATE 前に SELECT で確認 → BEGIN TRANSACTION → 確認 → COMMIT or ROLLBACK の安全な手順を体験。

📥 入力データ: ssdse.db

 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

import sqlite3

conn = sqlite3.connect('ssdse.db')
cur = conn.cursor()

# 1. まず確認 (絶対重要)
cur.execute("SELECT A1101 FROM ssdse_b WHERE Prefecture='東京都' AND year=2023")
before = cur.fetchone()[0]
print(f'更新前: 東京都 2023 年人口 = {before}')

# 2. トランザクション開始
cur.execute('BEGIN TRANSACTION')

# 3. UPDATE
new_value = 14_100_000  # 仮の更新値
cur.execute(
    "UPDATE ssdse_b SET A1101 = ? WHERE Prefecture='東京都' AND year=2023",
    (new_value,)
)
print(f'影響行数: {cur.rowcount}')  # 必ず 1 行のはず

# 4. 確認
cur.execute("SELECT A1101 FROM ssdse_b WHERE Prefecture='東京都' AND year=2023")
print(f'(トランザクション中) 値 = {cur.fetchone()[0]}')

# 5. 元に戻す (実データを壊さないため ROLLBACK)
conn.rollback()
print('ROLLBACK 実行')

cur.execute("SELECT A1101 FROM ssdse_b WHERE Prefecture='東京都' AND year=2023")
print(f'最終値: {cur.fetchone()[0]} (元に戻った)')

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

💬 結果の読み方: 影響行数が想定 (1) と一致することを確認してから COMMIT、 違えば ROLLBACK。 この「BEGIN → 確認 → COMMIT/ROLLBACK」の儀式が本番運用での事故防止に必須。 UPDATE WHERE 条件ミスで全行更新する事故は実務で頻発。

⑤ JOIN で複数テーブル連結

🎯 このコードでやること: SSDSE-B-2026 と都道府県マスタを JOIN して、 地方区分 (東北、 関東、 ...) 別に人口を集計する。 INNER JOIN の典型例。

📥 入力データ: ssdse.db + 自作の都道府県地方マスタ

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

import pandas as pd
import sqlite3

conn = sqlite3.connect('ssdse.db')

# 都道府県と地方 (8 区分) の対応表を 47 都道府県ぶん作成
regions = {
    '北海道': ['北海道'],
    '東北': ['青森県', '岩手県', '宮城県', '秋田県', '山形県', '福島県'],
    '関東': ['茨城県', '栃木県', '群馬県', '埼玉県', '千葉県', '東京都', '神奈川県'],
    '中部': ['新潟県', '富山県', '石川県', '福井県', '山梨県', '長野県',
             '岐阜県', '静岡県', '愛知県'],
    '近畿': ['三重県', '滋賀県', '京都府', '大阪府', '兵庫県', '奈良県', '和歌山県'],
    '中国': ['鳥取県', '島根県', '岡山県', '広島県', '山口県'],
    '四国': ['徳島県', '香川県', '愛媛県', '高知県'],
    '九州・沖縄': ['福岡県', '佐賀県', '長崎県', '熊本県', '大分県', '宮崎県',
                 '鹿児島県', '沖縄県'],
}
region_map = pd.DataFrame([{'Prefecture': p, 'Region': r}
                           for r, prefs in regions.items() for p in prefs])
print(f'対応表: {len(region_map)} 都道府県')
region_map.to_sql('prefecture_region', conn, if_exists='replace', index=False)

# JOIN
query = '''
SELECT r.Region AS 地方,
       COUNT(*) AS 県数,
       SUM(s.A1101) AS 人口合計
  FROM ssdse_b s
  INNER JOIN prefecture_region r ON s.Prefecture = r.Prefecture
 WHERE s.year = 2023
 GROUP BY r.Region
 ORDER BY 人口合計 DESC;
'''
result = pd.read_sql(query, conn)
print(result.to_string(index=False))
print(f'合計: {result["人口合計"].sum()}')

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

💬 結果の読み方: 8 地方の人口合計を足すと 1 億 2,435 万 3 千人で、 2023 年の 47 都道府県の合計と一致するので、 対応表に漏れも重複もない。 関東 7 都県だけで 4,352 万 7 千人と全国の 35% を占め、 最少の四国 4 県 (357 万 8 千人) の 12 倍になる。 INNER JOIN は両テーブルにある行だけを残すので、 対応表から県が 1 つ抜けているとその県の人口は黙って合計から消える。 合計が全国値と合うかを必ず確かめる。

🔒 トランザクションと ACID

DML を本番運用するうえで欠かせないのが トランザクションと ACID 特性。 数式を言葉で読み解くと、 これは「データベース操作の安全保証ルール」です。

SQL の BEGIN; ...; COMMIT; でトランザクション。 失敗したら ROLLBACK;。 PostgreSQL や Oracle は ACID を厳密に守るが、 NoSQL では緩い (BASE 原則) ことが多い。

🔐 分離レベル (Isolation Level)

並行トランザクションでどの程度厳密に独立を保つか。 厳しいほど安全だが遅い。

SSDSE のような分析用途では READ COMMITTED で十分。 在庫管理や金融はより厳格な REPEATABLE READ や SERIALIZABLE が必要。

🔓 ロックの種類

ロック競合は実務で頻発するパフォーマンス問題。 「短いトランザクション」「同じ順序で複数行ロック」が回避のコツ。

🚨 SQL インジェクション — 必ず防ぐ

DML を実行するアプリで最も多い脆弱性が SQL インジェクション。 ユーザー入力を文字列連結で SQL に埋め込むと、 攻撃者に任意の SQL を実行されます。

Python の sqlite3, psycopg2, mysql.connector はすべてプレースホルダ対応。 ? や %s で書き、 値は別引数で渡す。 ORM (SQLAlchemy 等) を使えば自動的に安全。

🐍 追加 Python 実装

⑥ DELETE のサンプル (BEGIN/ROLLBACK で安全に)

🎯 このコードでやること: 2012 年のレコード (47 件) を DELETE する例を、 必ず ROLLBACK して実データを保護しながら体験する。 影響行数の確認が安全運用の鍵。

📥 入力データ: ssdse.db (564 行)

 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

import sqlite3

conn = sqlite3.connect('ssdse.db')
cur = conn.cursor()

# 削除前の件数確認
cur.execute('SELECT COUNT(*) FROM ssdse_b')
before = cur.fetchone()[0]
print(f'削除前: {before} 行')

# トランザクション開始
cur.execute('BEGIN')

# DELETE
cur.execute('DELETE FROM ssdse_b WHERE year = 2012')
print(f'影響行数: {cur.rowcount}')

# 削除後の件数
cur.execute('SELECT COUNT(*) FROM ssdse_b')
print(f'(トランザクション中) {cur.fetchone()[0]} 行')

# 実データを壊さないため ROLLBACK
conn.rollback()
print('ROLLBACK 実行')

# 元に戻ったか確認
cur.execute('SELECT COUNT(*) FROM ssdse_b')
print(f'最終: {cur.fetchone()[0]} 行 (元通り)')

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

💬 結果の読み方: 影響行数が想定 (47 = 都道府県数 × 1 年) と一致することを確認。 もし 517 とか想定外なら ROLLBACK で守る。 これが本番運用の鉄則。

⑦ Window 関数で前年比を計算

🎯 このコードでやること: 各都道府県の人口について、 LAG 関数で前年の値を取得し、 増減率を計算する。 SQL の Window 関数はピボットなしで時系列比較ができる強力機能。

📥 入力データ: ssdse.db

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

import pandas as pd
import sqlite3

conn = sqlite3.connect('ssdse.db')
query = '''
SELECT Prefecture, year, A1101 AS pop,
       LAG(A1101) OVER (PARTITION BY Prefecture ORDER BY year) AS prev_pop,
       A1101 - LAG(A1101) OVER (PARTITION BY Prefecture ORDER BY year) AS diff
  FROM ssdse_b
 WHERE Prefecture IN ('東京都', '鳥取県', '沖縄県')
 ORDER BY Prefecture, year;
'''
result = pd.read_sql(query, conn)
print(result.to_string(index=False))

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

💬 結果の読み方: 2012→2023 年で東京は 1,323 万 4 千人 → 1,408 万 6 千人と約 85 万人増、 鳥取は 58 万 3 千人 → 53 万 7 千人と約 4 万 6 千人減、 沖縄は約 5 万 7 千人増だが 2022・2023 年の diff は 0 で増加が止まっている。 東京も 2021 年だけ −37,594 と減っている。 各県の初年度 2012 は前の行が無いので prev_pop・diff が NaN になり、 PARTITION BY があるので沖縄の 2012 年が東京の 2023 年と比べられることはない。

🐍 追加 Python — 47 都道府県の人口ランキング

⑧ Window 関数で順位付け

🎯 このコードでやること: 2023 年の 47 都道府県人口に ROW_NUMBER と RANK でランキングを付け、 同順位の有無を確認する。 数式を言葉で読み解くと「順序統計量を SQL で計算」する例。

📥 入力データ: ssdse.db (47 件 × 2023 年)

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

import pandas as pd
import sqlite3

conn = sqlite3.connect('ssdse.db')
query = '''
SELECT Prefecture, A1101 AS 人口,
       ROW_NUMBER() OVER (ORDER BY A1101 DESC) AS 行番号,
       RANK()       OVER (ORDER BY A1101 DESC) AS 順位,
       DENSE_RANK() OVER (ORDER BY A1101 DESC) AS 密順位,
       NTILE(4)     OVER (ORDER BY A1101)      AS 四分位
  FROM ssdse_b
 WHERE year = 2023
 ORDER BY A1101 DESC
 LIMIT 10;
'''
result = pd.read_sql(query, conn)
print(result.to_string(index=False))

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

💬 結果の読み方: 47 都道府県の人口は全て異なるので、 ROW_NUMBER / RANK / DENSE_RANK が一致。 四分位 (NTILE(4)) は 47 行を 12・12・12・11 行に分け、 ORDER BY A1101 の昇順で数えるので最後の Q4 は 11 行で、 人口上位 11 都道府県 (11 位は茨城県) が Q4 になる。 Window 関数は SQL:2003 で標準化された比較的新しい機能だが、 現代分析には必須。

⑨ UPSERT パターン (INSERT or UPDATE)

🎯 このコードでやること: 「都道府県データが既存ならUPDATE、 なければINSERT」という UPSERT 操作を 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

import sqlite3

conn = sqlite3.connect('ssdse.db')
cur = conn.cursor()

# 仮想的な「県人口集計」テーブルを作成 (主キー: Prefecture)
cur.execute('''
CREATE TABLE IF NOT EXISTS pref_summary (
  Prefecture TEXT PRIMARY KEY,
  total_pop INTEGER,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
''')

# UPSERT (SQLite 3.24+ 構文)
data = [
    ('東京都', 14086000),
    ('鳥取県', 537000),
    ('沖縄県', 1468000),
]
cur.executemany('''
INSERT INTO pref_summary (Prefecture, total_pop) VALUES (?, ?)
ON CONFLICT(Prefecture) DO UPDATE SET
  total_pop = excluded.total_pop,
  updated_at = CURRENT_TIMESTAMP
''', data)
conn.commit()

# 確認
cur.execute('SELECT * FROM pref_summary')
for row in cur.fetchall():
    print(row)

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

💬 結果の読み方: UPSERT は「データの取り込みを冪等にする」便利パターン。 同じデータを何度実行しても結果が同じになるので、 ETL バッチで多用される。 PostgreSQL は ON CONFLICT、 MySQL は ON DUPLICATE KEY UPDATE。

⚠️ よくある落とし穴

⚠️ WHERE 句の落とし穴

⚡ DML パフォーマンス・チューニング

• インデックス: WHERE / JOIN / ORDER BY 列に作成。 検索が 100〜10000 倍速くなる
• EXPLAIN: クエリ実行計画を確認 (PostgreSQL: EXPLAIN ANALYZE)
• カバリング・インデックス: SELECT する列もインデックスに含めて、 テーブル参照不要に
• JOIN 順序: 小さいテーブルから JOIN すると速い
• サブクエリより JOIN: 一般に JOIN の方が高速
• UNION ALL: 重複排除不要なら UNION より速い
• パーティション: 大きなテーブルを年度等で物理分割
• マテリアライズドビュー: 集計結果を保存して再計算回避
• バルク挿入: 1 行ずつ INSERT より INSERT INTO ... VALUES (...), (...), ...
• VACUUM: PostgreSQL/SQLite で不要領域を回収

📇 インデックスの種類

🏢 主要 RDBMS 比較

🪟 Window 関数 — 現代 DML の核心

「グループごとに集計しつつ、 行も個別に保持したい」というニーズに応えるのが Window 関数。 SQL:2003 以降標準。

例: 「都道府県別の前年比人口増減」:

⚠️ DML 落とし穴コレクション

• WHERE 忘れ: UPDATE/DELETE で全行に影響 → BEGIN; ... ROLLBACK; の儀式必須
• NULL 比較: = NULL ではなく IS NULL
• 暗黙の型変換: 文字列と数値の混在
• 日付フォーマット: ISO 8601 (YYYY-MM-DD) で統一
• 大文字小文字: SQL キーワードは大文字、 識別子は環境依存
• 予約語の識別子: "order" や "user" はクォート必須
• LIKE のワイルドカード: % は 0 文字以上、 _ は 1 文字
• カラム順序: INSERT INTO t VALUES (...) で列順依存
• 自動コミット: ライブラリにより設定が違う
• 文字エンコーディング: DB と接続両方の設定が必要
• タイムゾーン: UTC で統一、 表示時のみ JST 変換
• カスケード削除: 外部キーで連鎖削除に注意

🔄 ORM — Python から DML を扱う高レベル方法

ORM は SQL を書かなくて済むが、 N+1 問題 (1 件取得ごとに 1 クエリ発行) などの落とし穴あり。 SSDSE 程度の分析なら pandas + raw SQL の方が直感的。

🐼 pandas vs SQL — どちらを使うか

使い分け: メモリに収まる (< 数 GB) なら pandas、 大規模なら SQL。 SSDSE-B-2026 (564 行) は完全に pandas 範囲。

⚠️ 条件・限界・誤解回避（DML: データ操作言語）

DML (Data Manipulation Language) は SELECT/INSERT/UPDATE/DELETE/MERGE など、 リレーショナル DB のデータ自体を操作する SQL のサブセットです。 強力で生産性が高い反面、 トランザクション管理・性能設計・データ整合性の理解が不十分だと「数百万行を誤って更新」「本番環境のロックで業務停止」といった致命的事故を起こします。 SSDSE-B-2026 を題材に、 適用条件・限界・典型的な誤解を整理します。

適用条件

1. スキーマと制約の理解: DML は DDL で定義された型・制約 (NOT NULL, UNIQUE, FOREIGN KEY, CHECK) を遵守する形でしか操作できません。 SSDSE-B-2026 を都道府県マスタと結合するなら、 prefecture_code を主キーに正規化して FOREIGN KEY を張るのが王道。 制約に違反する INSERT/UPDATE は失敗し、 トランザクション全体がロールバックされます。
2. トランザクション境界の明示: 複数行を更新するときは BEGIN; ... COMMIT; で境界を明示。 暗黙コミット (autocommit) のままだと、 途中で失敗しても部分反映されて整合性が壊れます。 ACID 特性 (Atomicity, Consistency, Isolation, Durability) を意識した運用が前提です。
3. WHERE 句の必須性: UPDATE/DELETE は WHERE を省略すると全行が対象になります。 SSDSE のように 47 行しかないテーブルでも、 本番では数億行になることを想定し、 必ず WHERE + LIMIT + 事前 SELECT 確認の三点セットを徹底。
4. 適切なインデックス: WHERE 列・JOIN 列にインデックスがないとフルテーブルスキャンになり、 ロック範囲も広がります。 EXPLAIN ANALYZE で実行計画を確認し、 想定通りにインデックスが使われているかを検証。
5. 分離レベルの選択: READ COMMITTED (デフォルト多数)・REPEATABLE READ・SERIALIZABLE の違いを理解し、 業務要件に合わせて設定。 SSDSE のような分析専用 DB なら READ COMMITTED で十分ですが、 在庫・金融なら SERIALIZABLE 相当が必要なことも。
6. 権限管理 (GRANT/REVOKE): アプリ用ユーザーには SELECT/INSERT/UPDATE/DELETE のみ付与し、 DDL や TRUNCATE は禁止。 分析者には読み取り専用ユーザーを用意して本番改変を防ぐ。

限界

1. 大量更新のロックと性能: 1 トランザクションで数千万行を UPDATE するとロックエスカレーション・WAL 肥大・レプリカ遅延を引き起こします。 バッチ更新は LIMIT 10000 単位で分割し、 適度にコミットするのが定石。
2. RDBMS 方言の違い: 標準 SQL を中心にしても、 PostgreSQL の RETURNING、 MySQL の INSERT ... ON DUPLICATE KEY UPDATE、 SQL Server の MERGE、 BigQuery の MERGE INTO など差異があります。 移植性を考えるなら共通部分にとどめる工夫が必要。
3. NULL 比較の罠: WHERE x = NULL は常に NULL (=偽) になり、 期待した行が返りません。 WHERE x IS NULL を使う必要があります。 集計関数も NULL の扱いが異なる (COUNT(*) vs COUNT(col))。
4. 監査・履歴の欠如: DML は「現状」を更新するだけで、 過去の状態を保存しません。 監査要件があるなら、 トリガで履歴テーブルにコピーする・CDC (Change Data Capture) を導入する・SCD type 2 で設計する等の対策が必要。
5. 並行更新の競合: 同時に複数セッションが同じ行を更新するとデッドロックが発生。 アプリ側でリトライ機構を実装し、 再試行可能なトランザクション設計にする。

誤解回避

1. 「TRUNCATE は DELETE の高速版」は誤り: TRUNCATE は DDL に分類され、 ロールバックできない実装が多く、 トリガが発火しません。 監査要件のある環境では使えない場合があります。
2. 「DELETE すれば容量が空く」は誤り: PostgreSQL は DELETE 直後は物理空間を解放せず、 VACUUM (またはオートバキューム) が必要です。 InnoDB も同様で OPTIMIZE TABLE が要ります。
3. 「サブクエリより JOIN が常に速い」は誤り: 最近のオプティマイザはサブクエリを JOIN に書き換えます。 むしろ EXISTS や IN の方が意図が明確で読みやすいケースが多い。
4. 「ORDER BY を入れれば順序が保証される」は誤り: INSERT INTO ... SELECT ... ORDER BY は宛先テーブルの並び順を保証しません。 ORDER BY は SELECT 結果の順序付けにのみ有効。
5. 「インデックスを増やせば速くなる」は誤り: SELECT は速くなりますが、 INSERT/UPDATE/DELETE は遅くなります。 書き込み頻度の高いテーブルではインデックスを最小限に。
6. 「LIMIT で安全」は誤り: UPDATE ... LIMIT 100 は対象がランダム抽出される可能性があり、 確定性がありません。 ORDER BY と組み合わせ、 主キーを基準に処理する。

典型ワークフロー

1. 要件整理: SSDSE-B-2026 を題材に「東京を除く 46 道府県で 65 歳以上人口を集計」のような要件を SQL に落とす前に、 自然言語で書き出す。
2. SELECT で結果確認: SELECT prefecture, elderly_pop FROM ssdse WHERE prefecture <> '東京'; で結果セットを確認。 想定行数・想定値域を目視。
3. トランザクション開始: BEGIN; でトランザクション開始。
4. 更新実行: UPDATE ssdse SET flagged = TRUE WHERE prefecture <> '東京' AND elderly_pop > 1000000;。 影響行数を EXPLAIN や RETURNING で確認。
5. 検証: SELECT COUNT(*) FROM ssdse WHERE flagged = TRUE; で期待数と一致するかチェック。
6. コミットまたはロールバック: 期待通りなら COMMIT;、 想定外なら ROLLBACK;。
7. 監査ログ確認: pg_stat_statements / slow query log / 履歴テーブルで実行履歴を残す。
8. 性能分析: EXPLAIN (ANALYZE, BUFFERS) で実行計画とコストを確認し、 必要ならインデックス追加。

ケーススタディ

• 事例1: SSDSE-B-2026 を分析用 DM に取り込む — CSV を staging テーブルに INSERT し、 マスタと JOIN して正規化された fact テーブルに MERGE。 トランザクションで包み、 失敗時は ROLLBACK。 ロード後に VACUUM ANALYZE で統計情報を更新。
• 事例2: 大量バッチ UPDATE のチャンク化 — 1 億行を一度に UPDATE せず、 主キーで WHERE id BETWEEN n AND n+10000 のように区切って繰り返す。 各チャンクの後に COMMIT し、 レプリカ遅延を監視。
• 事例3: UPSERT (INSERT ... ON CONFLICT) — PostgreSQL の INSERT ... ON CONFLICT (id) DO UPDATE SET ... で「あれば更新、なければ挿入」を 1 文で実現。 SSDSE のような毎年の更新スナップショットに最適。
• 事例4: ソフトデリート設計 — 物理 DELETE せず UPDATE deleted_at = NOW() で論理削除。 監査・復元・参照整合性に強く、 多くの業務システムで採用される。
• 事例5: CDC (Change Data Capture) — Debezium で WAL を読み、 DML イベントを Kafka に流す。 DWH やデータレイクへリアルタイム同期を実現する典型構成。

DML は「データを動かす最後のキー操作」であると同時に「事故が一番起きる場所」です。 SSDSE-B-2026 のような小規模データで「SELECT で確認 → BEGIN → 更新 → 検証 → COMMIT」の規律を体に叩き込んでから本番に臨みましょう。

運用 Tips: 安全に DML を回すための実務知見

1. 本番更新前のドライラン: 更新前に SELECT ... FOR UPDATE + ROLLBACK で「影響範囲だけ事前確認」する手法。 PostgreSQL なら EXPLAIN UPDATE ... や BEGIN; UPDATE ...; SELECT * FROM updated_table; ROLLBACK; を組み合わせて、 副作用なしで挙動を観察できます。
2. WHERE 句のセーフガード: 重要テーブルの UPDATE/DELETE 文には必ず主キー条件か日付範囲を含め、 単独の UPDATE t SET col=val は禁止する社内ルールを敷きます。 ORM (Django, SQLAlchemy) でも .all().delete() のような書き方は警告する lint を導入。
3. ロック時間の監視: pg_locks, SHOW PROCESSLIST, v$session_longops 等でロック保持時間を監視し、 一定時間を超えたら自動 kill する仕組みを整備。 SSDSE のような分析専用 DB でも、 長時間ロックはレプリケーション遅延の原因。
4. 履歴テーブルの設計: 監査要件があるなら、 トリガで INSERT INTO history (op, old_row, new_row, updated_by, updated_at) を発火させ、 BEFORE/AFTER 値を残します。 GDPR の権利行使対応にも有効。
5. テスト DB と本番 DB の差分管理: スキーマ移行ツール (Flyway, Alembic, Liquibase) で DDL/DML マイグレーションを版管理。 SSDSE-B-2026 の取り込みスクリプトも、 idempotent (何度実行しても同じ結果) になるよう ON CONFLICT で書く。
6. ベンチマークと負荷試験: pgbench, sysbench, JMeter で典型クエリの TPS を計測し、 SLO (例: 99 パーセンタイル 200ms 以内) を設定。 本番デプロイ前にステージング環境で同等負荷をかける。

初学者がよく踏むトラップ集

DML を学び始めた人がよく踏む地雷を 8 つに整理しておきます。 1) WHERE を忘れた全行更新、 2) NULL = NULL の落とし穴、 3) サブクエリの相関エラー、 4) 結合キー間違いによるデカルト積、 5) 文字列リテラルとカラム名の混同 (シングル/ダブルクォート)、 6) DATE と TIMESTAMP の暗黙キャスト、 7) ORDER BY なしの LIMIT による不確定な結果、 8) UPDATE 直後の COMMIT 忘れ。 SSDSE-B-2026 のような小規模データで実験して全部踏んだ後、 本番に挑むのが理想です。

DML と現代データスタックの接続

伝統的 RDBMS の DML は今でも分析・運用の土台ですが、 現代では DWH (Snowflake, BigQuery, Redshift)、 データレイクハウス (Databricks, Iceberg)、 ストリーミング (Kafka, Flink) と組み合わせて使うことが増えています。 SSDSE-B-2026 を題材に置いてみると、 「DML で staging → MERGE で dim/fact 更新 → dbt model で集計 → BI で可視化」というパイプラインが現代的な形です。 dbt は SQL ベースの DML を Jinja テンプレートで再利用しやすくし、 incremental model で大量更新を効率化します。 一方ストリーミング DML では、 Kafka Connect + Debezium で OLTP の WAL を CDC として配信し、 DWH 側で MERGE INTO を実行する構成が標準化しつつあります。 こうした流れの中でも、 トランザクション境界・WHERE 句の確認・履歴管理という DML の基本規律は変わりません。

📋 DML を安全に運用するための前提条件と限界 (誤解回避)

DML (Data Manipulation Language: INSERT/UPDATE/DELETE/MERGE) は強力だが、 1 つの WHERE 句のミスで全行が壊れる。 SSDSE-B-2026 で実験する場合も、 本番運用と同じ規律で扱う必要がある。

前提 1: トランザクション境界が明確

DML は BEGIN; ... COMMIT; (または ROLLBACK) で囲み、 全体を 1 つの原子的な単位として扱う。 COMMIT を忘れたまま接続が切れると、 セッション末で自動 ROLLBACK して変更が消える。 SSDSE-B のような小データでも、 まず BEGIN + 確認 SELECT + COMMIT の習慣をつける。

前提 2: WHERE 句は SELECT で必ず先に確認

UPDATE prefecture SET population=0 WHERE region='北海道' の前に、 必ず SELECT * FROM prefecture WHERE region='北海道' で対象行を目視する。 「north hokkaido」のようなタイポで対象が 0 件、 または逆に WHERE を抜いて全 47 県が更新される事故を防ぐ。

前提 3: 履歴 (audit log) の保持

UPDATE/DELETE は元の値を破壊する。 監査可能性のため、 ① 別テーブルへ前値を INSERT、 ② temporal table (時間軸付きテーブル)、 ③ CDC (Change Data Capture) のいずれかを併用するのが理想。 SSDSE-B の年次データなら、 year 列を追加して上書きせず追記する設計が最も簡単。

限界 1: 大量更新はロックと WAL を爆発させる

UPDATE 1000万行 SET ... は WAL (Write-Ahead Log) を膨張させ、 ロック待ちで他クエリが詰まる。 chunked UPDATE (LIMIT 10000 × 1000 回) または INSERT INTO new_table SELECT ... ; RENAME のスワップ戦略に切り替える。

限界 2: ORDER BY なしの LIMIT は不定

DELETE FROM tbl WHERE ... LIMIT 100 でどの 100 行が削除されるかは、 ORDER BY なしでは DB 実装依存。 PostgreSQL/MySQL でも結果が再現しない。 必ず ORDER BY id LIMIT 100 のように決定的に書く。

📝 理解度チェック (5 問 / SSDSE-B-2026 を想定)

1. Q1. UPDATE prefecture SET region='関東' WHERE prefecture_code IN ('13','11','12','14') を実行する前に必ず行うべき確認手順を 3 ステップで記述せよ。
2. Q2. 47 都道府県のうち、 同じ name で 2 行ある (重複登録) ことが判明した。 重複を削除しつつ ID の小さい方を残す DML 文を 1 文で書け。
3. Q3. 1000 万行のテーブルで UPDATE をすると数時間かかる。 1) chunked UPDATE と 2) スワップ戦略の長所と短所を比較せよ。
4. Q4. MERGE INTO 文が「DWH ではよく使われるが、 OLTP では使われない」と言われる理由を 2 行で述べよ。
5. Q5. SSDSE-B のような少量データで DML を学ぶときに、 本番運用に活きる規律 (チェックリスト) を 5 項目挙げよ。

解答方針

• A1. ① BEGIN でトランザクション開始、 ② SELECT prefecture_code, region FROM prefecture WHERE prefecture_code IN (...) で対象行を目視、 ③ 想定行数と一致したら UPDATE 実行 → SELECT で再確認 → COMMIT。
• A2. DELETE FROM prefecture WHERE id NOT IN (SELECT MIN(id) FROM prefecture GROUP BY name)。 ただし MySQL では同テーブル参照に制限あり、 一時テーブル経由が必要。
• A3. chunked UPDATE は安全だがオンライン中も書き込み発生。 スワップは並列構築で速いが、 トリガ・外部キー・参照クエリの整合性管理が必要。
• A4. MERGE は冪等な「upsert」処理に最適だが、 同時並行性が高い OLTP では行ロックが取られすぎて性能劣化する。 OLTP は INSERT ON CONFLICT などの単純文が好まれる。
• A5. ① BEGIN/COMMIT を必ず使う、 ② SELECT で WHERE を事前確認、 ③ 件数を想定値と比較、 ④ 履歴を残す (timestamp 列 or audit table)、 ⑤ ステージング → 本番の段階的適用。

📊 数式に値を入れて手で計算する — DML 行数変化と高齢化率の計算

DML は数学的には集合演算。 INSERT は集合の和 $R \leftarrow R \cup \{t\}$、 DELETE は差 $R \leftarrow R \setminus \sigma_p R$。 一連の DML 操作後のテーブル行数 $n$ は:

Step 1: 各 DML オペレーションの行数変化

Step 2: 式に値を代入

Step 3: さらに高齢化率 (aging_rate = 65 歳以上人口 / 総人口) を UPDATE で計算

SSDSE-B には面積の列が無いので、 収録されている 65 歳以上人口 A1303 を使う。 東京都 (2023 年) の高齢化率: $\text{aging\_rate} = \dfrac{A1303 \times 100}{A1101} = \dfrac{3{,}205{,}000 \times 100}{14{,}086{,}000} \approx 22.75 \ \%$

🐍 Python で Step 2・Step 3 を再現

このコードでやること: 手計算 Step 2 (行数変化の式) と Step 3 (東京都の高齢化率) を Python で検証し、 UPDATE・DELETE の対象件数と東京都の値を SSDSE-B-2026 の CSV でも確かめる。

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

import pandas as pd

# Step 2: 行数変化の式 n_final = n0 + Σ(INSERT) - Σ(DELETE)
n0 = 47   # SSDSE-B-2026 の 2023 年分
ins1, ins2 = 5, 3     # INSERT x2
del1 = 2             # DELETE (pop < 660000 → 鳥取県・島根県)
n_final = n0 + ins1 + ins2 - del1
print(f"最終行数: {n0} + {ins1} + {ins2} - {del1} = {n_final} 行")

# Step 3: 東京都の高齢化率 aging_rate = A1303 * 100 / A1101
tokyo_65 = 3205000     # A1303 (65 歳以上人口・人, 2023 年)
tokyo_pop = 14086000   # A1101 (総人口・人, 2023 年)
aging_rate = tokyo_65 * 100 / tokyo_pop
print(f"東京都 高齢化率: {aging_rate:.2f} %")

# 実データ (SSDSE-B-2026) で DELETE の対象件数と東京都の値を確かめる
df = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', skiprows=[1])
d = df[df['SSDSE-B-2026'] == 2023]
print("UPDATE 対象 (pop > 5000000):", (d['A1101'] > 5000000).sum(), "行")
print("DELETE 対象 (pop < 660000):", d.loc[d['A1101'] < 660000, 'Prefecture'].tolist())
t = d[d['Prefecture'] == '東京都'].iloc[0]
print(f"CSV から: {t['A1303']} * 100 / {t['A1101']} = {t['A1303'] * 100 / t['A1101']:.2f} %")

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

💬 結果の読み方: 手計算 (Step 2) の 53 行と Python 出力が完全一致。 東京都の高齢化率 22.75% も Step 3 の手計算 ($3205000 \times 100 / 14086000 \approx 22.75$) と、 CSV から読んだ値の両方で一致。 CSV で数えると UPDATE の対象 (500 万人超) は 9 行、 DELETE の対象 (66 万人未満) は鳥取・島根の 2 行で、 Step 1 の表の件数と合う。 UPDATE の 9 行は値が変わるだけで行数は 52 のまま、 という関係代数の原則が式の上でも効いている。

🌐 関連手法・派生

• DDL：CREATE TABLE, ALTER TABLE。 構造の定義
• DCL：GRANT, REVOKE。 権限管理
• TCL：BEGIN, COMMIT, ROLLBACK。 トランザクション
• Window 関数：ROW_NUMBER, RANK, LAG。 高度な分析
• CTE (WITH)：複雑なクエリを段階的に組み立てる
• ORM：SQLAlchemy, Django ORM。 Python コードから DML を生成

🌐 NoSQL との比較

NoSQL も結局、 何らかのクエリ言語が必要。 「SQL ライク」が増えているのは、 SQL の表現力の高さの証拠。

🌐 分散 DB と DML

📊 DML パフォーマンス監視

Datadog, New Relic, pganalyze, percona 等のツールで自動監視が定番。

🛡 DB セキュリティ

• SQL インジェクション防止: プレースホルダ必須
• 権限最小化: アプリ用ユーザーは必要な権限のみ
• 暗号化: 通信は TLS、 保存は暗号化
• 個人情報マスキング: 開発環境では伏字化
• 監査ログ: 誰がいつ何をしたか記録
• パスワード保護: ハッシュ化 (bcrypt, argon2)
• バックアップ暗号化: 盗難リスク対策
• ネットワーク分離: DB は VPC 内のみアクセス可
• 定期パッチ: CVE 脆弱性対応
• ペネトレーションテスト: 定期的にセキュリティ診断

💥 DML 事故事例 (実例ベース)

• GitLab 2017: エンジニアが本番 DB を誤削除、 6 時間のサービス停止
• みずほ銀行 2002: 統合システム稼働初日に DML 処理が滞り ATM 停止
• JR 東日本 2007: Suica のシステム障害、 DB ロック競合が原因
• 東証 2020: アロー HEAD のメモリエラー、 DB アクセス停止
• Knight Capital 2012: 45 分で 4.4 億ドル損失、 古い DML が誤発動
• British Airways 2017: DB システム不具合で全便欠航
• 各種 EC サイト: 1 円販売事故、 価格テーブルの UPDATE ミス

「DML は強力だからこそ危険」。 本番では BEGIN; ROLLBACK; の儀式、 ステージング環境での事前テスト、 二人以上のレビュー、 監査ログの厳格化が必須。

🔮 DML の未来 (2030 年代)

• Text-to-SQL の精度向上: 自然言語からの SQL 生成が実用レベルに
• サーバーレス DB の普及: PlanetScale, Neon 等
• Lakehouse の標準化: データレイクとデータウェアハウスの融合 (Databricks, Iceberg)
• ベクトル検索の SQL 統合: pgvector, Postgres 17+
• マルチモーダル DB: 構造化 + 非構造化 + ベクトル一元管理
• 量子 DB の研究: 量子コンピュータ向け DML 設計
• エッジ DB: スマホ・IoT 向け SQLite 進化系
• WebAssembly DB: ブラウザ内で動く高速 DB
• プライバシー保護 DB: ゼロ知識証明、 同型暗号
• AI 統合運用: 自動チューニング、 自動インデックス推薦

🧐 SQL 哲学

SQL は 「宣言的言語」の代表。 「何を欲しいか」を書けば、 「どう取るか」は DBMS が最適化する。 これは Python のような手続き的言語と対照的。

「人間は SQL を書き、 機械は最適化する」が宣言的言語の理想。 50 年の研究で DBMS はクエリ最適化に長けています。

🌟 結びに — 50 年生き続ける技術

DML (SQL) は 1974 年から 50 年以上現役の極めて稀有な技術です。 NoSQL ブーム、 ビッグデータ、 機械学習、 そして LLM 時代 ─ 何度も「SQL は終わる」と言われながら、 結局すべての時代を生き抜いてきました。

理由はシンプル: 「データの形」が変わっても「関係 (リレーション)」という抽象は変わらないから。 表という構造、 集合論的演算、 そして「SELECT/INSERT/UPDATE/DELETE」という 4 命令は、 データ操作の本質を捉えています。

SSDSE-B-2026 のような統計データから、 GPT を支える巨大 DB まで、 DML はあらゆる場所で動いています。 「データを扱う仕事」をするなら、 SQL は 一生使える投資です。

本ページが、 あなたの DML 学習の確かな出発点となれば幸いです。 SQLite で SSDSE を投入する小さな一歩から、 数十年使える知識を育ててください。

🎁 DML/SQL トリビア

• Codd は IBM 内で冷遇: 1970 年論文発表、 商用化は 1979 年 Oracle (IBM 競合)
• 「SQL」の発音: 「シーケル」と「エス・キュー・エル」両派あり
• SQL の語源: もとは SEQUEL (Structured English QUEry Language)
• Codd の 12 条: 1985 年に「真のリレーショナル DB の 12 条件」を発表
• Oracle の創業者: Larry Ellison、 自分は退役軍人ではないと公言
• PostgreSQL の祖先: 1970 年代の Ingres → POSTGRES → PostgreSQL
• MySQL の M: 創業者 Michael "Monty" Widenius の娘 My にちなむ
• MariaDB の Maria: Monty の別の娘 Maria にちなむ
• SQLite の使用数: 世界中の Android/iPhone 内に組み込まれ、 推定 10 兆インスタンス以上稼働
• Boeing 737: 機体に SQLite が組み込まれている
• Codd の墓碑銘: 「彼はデータの父」

🔗 関連用語

dml を学ぶ際に併読すると理解が深まる関連用語を「前提・並列・発展」の 3 区分で整理する。

• 前提概念: SQL の SELECT 文 / リレーショナルモデル / 主キー・外部キー / トランザクション (ACID) / テーブル正規化 — これら無しに DML の意味は理解できない
• 並列概念: DDL (CREATE/ALTER/DROP) / DCL (GRANT/REVOKE) / TCL (COMMIT/ROLLBACK) — SQL 構文の他カテゴリ。 NoSQL の put/get/delete API も同じ役割を別アーキテクチャで果たす
• 発展概念: UPSERT (MERGE 文) / バルク INSERT (COPY, LOAD DATA) / CTE (WITH 句) / window 関数 / ストアドプロシージャ、 SSDSE-B-2026 を PostgreSQL に投入し県別経済指標を年次更新する ETL パイプライン

各概念は本用語集の他ページにリンクしている。 関連用語を横断的に学ぶことで、 DML (INSERT / UPDATE / DELETE / SELECT) の位置付けが明確になる。

📚 関連グループ教材

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

• 📚 データエンジニアリング — RDB は基盤技術

📜 DML / SQL の 50 年史

DML は SQL の中核として 1974 年から現役で使われている、 IT 史上もっとも長寿な技術の 1 つです。

SQL は 「50 年経っても代替されない」稀有な言語。 NoSQL ブームの後にも結局 SQL ライク (NewSQL) が主流に。 DML の安定性は IT の財産。

📚 CTE (WITH 句) — 複雑なクエリの可読性

複雑なクエリを 段階的に組み立てるのが CTE (Common Table Expression)。 サブクエリを名前付きで定義できます。

ネストしたサブクエリより圧倒的に読みやすい。 再帰 CTE で組織階層のような木構造も扱えます。

📚 参考文献

• Codd, E. F. (1970) "A Relational Model of Data for Large Shared Data Banks" — 原典
• ミック『達人に学ぶ SQL 徹底指南書』(2018, 翔泳社)
• ミック『SQL 第 2 版』(2017, ピアソン)
• 木村明治『内部構造から学ぶ PostgreSQL』(2018, 技術評論社)
• 奥野幹也『達人に学ぶ DB 設計徹底指南書』(2012, 翔泳社)
• SQL 標準: ISO/IEC 9075 シリーズ
• PostgreSQL 公式ドキュメント: postgresql.org/docs
• SQLite 公式: sqlite.org

📝 やってみよう

1. (基礎) SSDSE-B-2026 を SQLite に投入し、 SELECT で 47 都道府県を確認
2. (基礎) 人口 500 万以上の都道府県を WHERE で抽出
3. (基礎) 年ごとに全国人口合計を GROUP BY + SUM で集計
4. (応用) 各都道府県の人口を 2012 → 2023 で前年比計算 (LAG 関数)
5. (応用) 都道府県マスタを JOIN して地方別集計
6. (発展) Window 関数で都道府県人口ランキング (ROW_NUMBER)
7. (発展) CTE で多段クエリを書き、 「人口減少率 TOP 5」を抽出
8. (安全) UPDATE/DELETE を BEGIN/ROLLBACK の儀式で安全に実行

🏢 産業界での DML 活用例

📖 用語辞典

DML (Data Manipulation Language)

SELECT/INSERT/UPDATE/DELETE 等、 データ操作の SQL コマンド群

DDL (Data Definition Language)

CREATE TABLE/ALTER/DROP 等、 構造定義の SQL

DCL (Data Control Language)

GRANT/REVOKE 等、 権限制御の SQL

TCL (Transaction Control Language)

BEGIN/COMMIT/ROLLBACK 等、 トランザクション制御

トランザクション

複数操作を 1 単位で扱う仕組み。 ACID を保証

ACID

Atomicity / Consistency / Isolation / Durability の頭字語

インデックス

検索を高速化するデータ構造 (主に B-Tree)

主キー (Primary Key)

各行を一意に識別する列 (または列の組)

外部キー (Foreign Key)

別テーブルの主キーを参照する列

正規化

データ重複を排除する設計手法 (1NF, 2NF, 3NF...)

非正規化

読み取り性能のために意図的に重複を持つ

ビュー (View)

SELECT 結果を仮想テーブルとして扱う

マテリアライズドビュー

ビューの結果を物理的に保存

ストアドプロシージャ

DB 側に保存された SQL ルーチン

トリガー

特定イベント (INSERT 等) で自動実行される SQL

📋 DML チートシート

🔍 SELECT の詳細パターン

SELECT は DML の中で最も多用され、 表現力が豊富です。 SSDSE-B-2026 で実例を見ましょう。

パターン 1: 単純な条件抽出

パターン 2: 集計と HAVING

パターン 3: サブクエリで「平均以上」

パターン 4: 自己結合 (Self Join)

パターン 5: CASE 文で条件分岐

✏️ UPDATE の詳細パターン

🗑 DELETE の詳細パターンと TRUNCATE

「データだけ全消去」は TRUNCATE が速いが、 復元できないので注意。 開発環境のリセット等で使う。

➕ INSERT の詳細パターン

📚 正規化 — DML を健全に保つ設計

実務では 3NF + 必要に応じて非正規化が妥協点。 完全正規化すると JOIN だらけで遅くなる。

🔗 JOIN の全種類

🔢 集計関数の全種類

🪟 Window 関数 — 詳細パターン

PARTITION BY と ORDER BY

FRAME 句

NTILE で分位点

🔁 再帰 CTE — 木構造とグラフ処理

CTE には 再帰版があり、 組織階層やフォルダツリーのような木構造を扱えます:

グラフ探索 (友達の友達、 最短経路) も再帰 CTE で書ける。 PostgreSQL や SQLite で対応。

📚 関連トピック一覧

「dml」と関連する基礎統計・データ分析の主要トピックを横断的に参照できる。 各リンクから対応する用語ページへジャンプして、 体系的な学習を進められる。

• 相関
• 回帰分析
• 平均
• 分散
• 標準偏差
• 中央値
• 正規分布
• 仮説検定
• p 値
• 信頼区間
• NumPy
• pandas
• ヒストグラム
• 箱ひげ図
• 散布図
• データクレンジング
• データ収集

📝 演習問題 — 説明 → 演習 → 解答 → Python 検証

以下の 5 つの演習セットで、 DML の理論と実装を体で覚えよう。 各セットは「問題 → 解答方針 → Python で動かして確認」の順で構成されている。 SSDSE-B-2026 の実数値を使う。

演習 1: SELECT + WHERE + ORDER BY — 条件抽出の基本

🎯 このコードでやること: SSDSE-B-2026 を SQLite に投入し、 2023 年の人口 300 万人以上 (A1101 >= 3000000) の都道府県を人口降順で取得する。

📥 入力データ: SSDSE-B-2026.csv (47 都道府県 × 12 年、 year=2023 の行が対象)

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

import pandas as pd
import sqlite3

# SSDSE-B-2026 を SQLite に投入
df = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', skiprows=[1])
df = df.rename(columns={'SSDSE-B-2026': 'year'})
conn = sqlite3.connect(':memory:')
df.to_sql('ssdse', conn, if_exists='replace', index=False)

# 演習 1: 2023 年の人口 300 万人 (A1101 >= 3000000) 以上を人口降順で取得
q = """
SELECT Prefecture, A1101 AS 人口
  FROM ssdse
 WHERE year = 2023 AND A1101 >= 3000000
 ORDER BY A1101 DESC
"""
result = pd.read_sql(q, conn)
print(f"ヒット件数: {len(result)} 件")
print(result.to_string(index=False))

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

💬 結果の読み方: A1101 >= 3000000 の条件で 10 件 (11 位の茨城県は 2,825,000 人なので不含)。 WHERE 条件が正確に機能していることを「件数確認」で体感できた。 実務でも UPDATE/DELETE の前に SELECT でこの「件数確認」を必ず行う。

演習 2: GROUP BY + SUM + HAVING — 集計と絞り込み

🎯 このコードでやること: GROUP BY で年別集計し、 HAVING で全国人口 126,000 千人以上の年を絞り込む。 WHERE と HAVING の違いを実際に確認する。

📥 入力データ: ssdse テーブル (564 行、 year=2012〜2023)

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

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'})
conn = sqlite3.connect(':memory:')
df.to_sql('ssdse', conn, if_exists='replace', index=False)

# 演習 2: GROUP BY year + HAVING で全国人口 1 億 2600 万人以上の年を絞り込む
q = """
SELECT year, SUM(A1101) AS 全国総人口
  FROM ssdse
 GROUP BY year
HAVING SUM(A1101) >= 126000000
 ORDER BY year
"""
result = pd.read_sql(q, conn)
print(f"1 億 2600 万人以上: {len(result)} 年")
print(result.to_string(index=False))

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

💬 結果の読み方: 2012〜2020 年の 9 年間が 1 億 2,600 万人以上で、 2021 年 (1 億 2,550 万人) から下回る。 年間の減少幅は 2012→2020 年が年 5〜41 万人だったのに対し、 2020→2021 年以降は 55〜65 万人に広がっており、 ここから人口減少が加速したと読める。 HAVING は集計後のフィルタなので WHERE の位置に書いてはいけない (WHERE SUM(...) はエラー)。

演習 3: UPDATE + トランザクション — BEGIN/COMMIT/ROLLBACK の儀式

🎯 このコードでやること: ALTER TABLE で列追加 → CASE 式で UPDATE → カテゴリ別件数を GROUP BY で確認するフル手順。

📥 入力データ: ssdse テーブル (year=2023 の 47 行が対象)

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

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'})
conn = sqlite3.connect(':memory:')
df.to_sql('ssdse', conn, if_exists='replace', index=False)

cur = conn.cursor()

# 1. 列追加
cur.execute('ALTER TABLE ssdse ADD COLUMN pop_category TEXT')

# 2. BEGIN でトランザクション開始
cur.execute('BEGIN')

# 3. CASE 式で一括 UPDATE (2023 年のみ、A1101 の単位は「人」)
cur.execute("""
UPDATE ssdse
   SET pop_category = CASE
         WHEN A1101 >= 5000000 THEN '大規模'
         WHEN A1101 >= 1000000 THEN '中規模'
         ELSE '小規模'
       END
 WHERE year = 2023
""")
print(f'影響行数: {cur.rowcount} 行')

# 4. カテゴリ別件数を確認
result = pd.read_sql(
    'SELECT pop_category, COUNT(*) AS 件数 FROM ssdse WHERE year=2023 GROUP BY pop_category ORDER BY 件数 DESC', conn)
print(result.to_string(index=False))

# 5. 想定件数と一致 → COMMIT
conn.commit()
print('COMMIT 完了')

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

💬 結果の読み方: 大規模 9 件 (500 万人以上: 東京〜北海道の 9 都道府県、 10 位の静岡 355 万人は中規模)、 中規模 28 件 (100〜500 万)、 小規模 10 件 (100 万未満: 秋田 91 万 4 千人以下)。 A1101 の単位は「人」なので、 しきい値を千人単位のつもりで 5000・1000 と書くと 47 件すべてが大規模になってしまう。 影響行数 47 に加えて、 カテゴリの内訳が予想と合うかまで見てから COMMIT する。 CASE 式は UPDATE でも SELECT でも使える強力な条件分岐手法。

演習 4: Window 関数 (LAG) — 前年比増減の計算

🎯 このコードでやること: Window 関数 LAG で前年値を取得し、 2023 年の人口増減 TOP3 増・TOP3 減を計算する。 手計算値 (東京都: +48,000 人) と Python 出力を照合する。

📥 入力データ: ssdse テーブル (全 564 行。 LAG は前年値が必要なので全年分が必要)

 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

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'})
conn = sqlite3.connect(':memory:')
df.to_sql('ssdse', conn, if_exists='replace', index=False)

# 手計算確認: 東京都 2023 年 - 2022 年
tokyo_check = pd.read_sql(
    "SELECT year, A1101 FROM ssdse WHERE Prefecture='東京都' AND year IN (2022,2023) ORDER BY year", conn)
diff_manual = tokyo_check['A1101'].iloc[1] - tokyo_check['A1101'].iloc[0]
print(f"東京都 手計算: {diff_manual} 人 (2022 → 2023)")

# CTE + LAG で全都道府県の 2023 年前年比を計算
q = """
WITH lag_data AS (
  SELECT Prefecture, year, A1101,
         LAG(A1101) OVER (PARTITION BY Prefecture ORDER BY year) AS prev_pop,
         A1101 - LAG(A1101) OVER (PARTITION BY Prefecture ORDER BY year) AS diff
    FROM ssdse
)
SELECT Prefecture, A1101 AS 人口, prev_pop AS 前年, diff AS 増減
  FROM lag_data
 WHERE year = 2023 AND diff IS NOT NULL
 ORDER BY diff DESC
"""
result = pd.read_sql(q, conn)

print("--- 増加 TOP3 ---")
print(result.head(3).to_string(index=False))
print("--- 減少 TOP3 ---")
print(result.tail(3).to_string(index=False))

# 東京都の LAG 結果と手計算の照合
tokyo_lag = result[result['Prefecture']=='東京都']['増減'].iloc[0]
print(f"\n東京都 LAG 結果: {tokyo_lag} 人 → 手計算 {diff_manual} と一致: {tokyo_lag == diff_manual}")

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

💬 結果の読み方: 東京都の LAG 計算値 +48,000 人が手計算 (14,086,000−14,038,000) と完全一致 → Window 関数の動作を数値で確認できた。 2023 年に前年から人口が増えたのは東京都 (+48,000) だけで、 沖縄県が横ばい (±0)、 3 位の滋賀県はすでに減少 (−2,000)。 減少トップは北海道 (−48,000)・兵庫県 (−32,000)・静岡県 (−27,000)。 東京 1 県の増加と北海道 1 県の減少がちょうど同じ幅である点が象徴的。

演習 5: CTE + RANK — 複合クエリで「人口減少率ワースト 5」

🎯 このコードでやること: 3 段階 CTE で 2012→2023 の人口変化率を計算し、 RANK でワースト 5 を表示。 手計算 (秋田県 −14.02%) と照合する。

📥 入力データ: ssdse テーブル (year=2012 と year=2023 の合計 94 行を使用)

 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
44
45
46
47
48

import pandas as pd
import sqlite3

# このブロックは 2012 年と 2023 年を比べるので、年度を絞らずに全期間を読み込む
df = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', skiprows=[1])
df = df.rename(columns={'SSDSE-B-2026': 'year'})
conn = sqlite3.connect(':memory:')
df.to_sql('ssdse', conn, if_exists='replace', index=False)

# 手計算確認: 秋田県の人口変化率
akita = pd.read_sql(
    "SELECT year, A1101 FROM ssdse WHERE Prefecture='秋田県' AND year IN (2012,2023)",
    conn).set_index('year')
rate_manual = ((akita.loc[2023, 'A1101'] - akita.loc[2012, 'A1101'])
               / akita.loc[2012, 'A1101'] * 100)
print(f"秋田県 手計算: ({akita.loc[2023,'A1101']} - {akita.loc[2012,'A1101']})"
      f" / {akita.loc[2012,'A1101']} * 100 = {rate_manual:.2f} %")

# CTE で 3 段階クエリ: 2012→2023 変化率 + RANK
q = """
WITH pop_2012 AS (
  SELECT Prefecture, A1101 AS p2012 FROM ssdse WHERE year = 2012
),
pop_2023 AS (
  SELECT Prefecture, A1101 AS p2023 FROM ssdse WHERE year = 2023
),
change_rate AS (
  SELECT a.Prefecture,
         a.p2012, b.p2023,
         ROUND((b.p2023 - a.p2012) * 100.0 / a.p2012, 2) AS 変化率_pct
    FROM pop_2012 a JOIN pop_2023 b ON a.Prefecture = b.Prefecture
)
SELECT Prefecture,
       p2012 AS "2012年_人", p2023 AS "2023年_人",
       変化率_pct,
       RANK() OVER (ORDER BY 変化率_pct ASC) AS ワーストランク
  FROM change_rate
 ORDER BY ワーストランク
 LIMIT 5
"""
result = pd.read_sql(q, conn)
print(result.to_string(index=False))

# 秋田県の CTE 結果と手計算の照合
akita_cte = pd.read_sql(q.replace('LIMIT 5', ''), conn)
akita_pct = akita_cte[akita_cte['Prefecture'] == '秋田県']['変化率_pct'].iloc[0]
print(f"\n秋田県 CTE: {akita_pct} % → 手計算 {rate_manual:.2f} % と一致: "
      f"{abs(akita_pct - rate_manual) < 0.01}")

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

💬 結果の読み方: 秋田県の CTE 計算値 −14.02% が手計算と完全一致 → CTE + RANK の動作が正しいことを数値で検証。 人口減少率ワースト 1 は秋田県 (−14.02%)、 以下 青森県 (−12.30%)・高知県 (−11.32%)・山形県 (−11.01%)・岩手県 (−10.95%) と、 東北と四国の県が上位を占める。 CTE を使うことで「2 年分の JOIN → 変化率計算 → RANK」という複雑な 3 段階処理を読みやすく記述できた。

🗺 DML 概念マップ

図: DML の全体像。 中心の DML (SELECT/INSERT/UPDATE/DELETE) を軸に、 SQL 分類 (DDL/DCL/TCL)、 安全運用 (ACID/トランザクション)、 パフォーマンス (インデックス/EXPLAIN)、 周辺技術 (ORM/クラウド DB) との関係を示す。

✅ DML 実プロジェクト最終チェックリスト

• ☐ UPDATE/DELETE 前に SELECT で対象行を確認した
• ☐ トランザクション内で BEGIN; ROLLBACK; テストした
• ☐ プレースホルダ (? や %s) を使った (文字列連結禁止)
• ☐ SELECT * ではなく必要列を明示した
• ☐ JOIN 句を ON で書いた (WHERE で書かない)
• ☐ WHERE 句で関数を使っていない (インデックス無効化回避)
• ☐ EXPLAIN ANALYZE で実行計画を確認した
• ☐ 適切なインデックスを設定した
• ☐ NULL は IS NULL で比較した
• ☐ 数千行以上の INSERT はバルク化した
• ☐ クライアント側のタイムアウトを設定した
• ☐ コネクションプールを適切に設定した
• ☐ バックアップとリストアの手順を確認した
• ☐ 監査ログを有効化した
• ☐ ステージング環境で十分にテストした
• ☐ デプロイ手順とロールバック手順を文書化した
• ☐ 二人以上でレビューした

⚖ DML と他のデータ処理技術の比較

「データ操作」の本質は DML が捉えており、 新しい技術もすべて SQL ライクな構文に収束しています。

📊 SSDSE-B-2026 を SQL で分析した代表的な統計

これらの数値はすべて SSDSE-B-2026 を SQL で集計したもの。 都道府県間の格差や人口減少の進行が一目で分かります。 統計データ解析コンペで活用可能。

⚡ DML クイックリファレンス

📋 まとめ

• DML は SQL の中核 (SELECT / INSERT / UPDATE / DELETE)
• 50 年間現役で代替されない強力な技術
• WHERE 句忘れの事故を防ぐため BEGIN; ROLLBACK; の儀式
• ACID とトランザクション、 分離レベルを理解
• SQL インジェクションには必ずプレースホルダ
• パフォーマンスはインデックスと EXPLAIN で改善
• Window 関数と CTE で複雑分析も可能
• pandas vs SQL は規模で使い分け、 SSDSE 程度なら pandas
• SSDSE-B-2026 のような公的データを SQLite に投入すれば SQL 学習に最適

🔗 隣接手法への橋渡し — DML と周辺技術の接続

DML（データ操作言語）は単独で完結せず、 上流・並列・下流の技術と連携することで真価を発揮する。

典型的な 分析パイプライン: データ収集 (スクレイピング / CSV) → DML (INSERT で DB 投入) → SELECT + GROUP BY で集計 → BI / pandas で可視化 → ML 特徴量として利用。 DML はこのパイプラインの「動脈」に位置する。

🌳 手法選択フロー — どの DML 命令・アプローチを使うか

データ操作の目的と状況に応じて、 適切な DML 命令・ツール・アーキテクチャを選択するフロー。

要約フロー: データ操作目的 → 命令選択 (SELECT/INSERT/UPDATE/DELETE/MERGE) → スケール判断 (ローカル/クラウド) → トランザクション設計 → BEGIN; 確認; COMMIT の儀式。

🎮 データ操作ラボ：INSERT / UPDATE / DELETE で表を書き換える

このコーナーは DML の主役＝データの「変化」（行の追加・条件付き更新・条件付き削除）を手を動かして体感する場です。 SQL プレイグラウンド（SELECT 中心の問い合わせ）や DDL（テーブル構造の定義）とは役割が異なり、 ここでは中身がどう書き換わるかだけに集中します。 実行はページ内蔵の簡易エンジン（JS）が正確に計算し、 変更された行を色付きでハイライトします。

※ 下の表は教材用の架空データ（在庫表）です。 SSDSE 等の実測統計ではありません。

📦 対象テーブル（教材例・架空）— 在庫表

(a) 3 つの操作を実行してみる

行を 追加（INSERT）・条件付きで更新（UPDATE）・条件付きで削除（DELETE）。 実行するたびに、 影響を受けた行が 緑＝新規 / 黄＝更新 / 赤＝削除予定 でハイライトされます。

(b) WHERE 忘れの恐怖 — 全行操作の事故

上の UPDATE / DELETE パネルで 「WHERE を付けない（全行）」にチェックを入れて実行してみてください。 たった 1 回の操作ですべての行が書き換わる／消える様子が見えます。 これは実務で最も多い DML 事故で、 WHERE id = 3 と書くつもりが WHERE ごと消してしまうと、 全顧客・全在庫を一撃で破壊します。 だからこそ次の (c) トランザクションが命綱になります。

(c) トランザクション — COMMIT で確定 / ROLLBACK で取消

ここでの INSERT/UPDATE/DELETE は、 すべて「未確定（作業中）」の状態です。 COMMIT を押すまで確定されず、 ROLLBACK を押せば直前の確定状態までまるごと取り消せます。 事故った UPDATE/DELETE も、 COMMIT 前なら ROLLBACK で無かったことにできます。

🧭 直感・落とし穴・発展

• 直感：DML の変更系は「表の中身を、 追加／書換／削除する」操作。 INSERT は行を足し、 UPDATE は既存行の値を書き換え、 DELETE は行を消す。 DDL（CREATE で「入れ物」を作る）と違い、 DML は入れ物の中身を動かす。 SELECT は「見るだけ」で表は変わらないのに対し、 INSERT/UPDATE/DELETE は状態を変えるのが決定的な違い。
• 落とし穴①：WHERE 忘れ：(b) の通り、 UPDATE 表 SET x=0 や DELETE FROM 表 のように WHERE を書き忘れると全行に及ぶ。 実行前に必ず同じ条件で SELECT COUNT(*) して「何行に効くか」を確認する習慣が事故を防ぐ。
• 落とし穴②：トランザクション未使用：オートコミットのまま実行すると、 間違った DELETE が即座に確定して戻せない。 BEGIN; で囲み、 結果を確認してから COMMIT;、 おかしければ ROLLBACK;。 (c) で体感した通り。
• 落とし穴③：ロック：複数の利用者が同じ行を同時に UPDATE すると、 一方が終わるまで他方は待たされる（行ロック）。 長いトランザクションを開きっぱなしにするとロックが広がり、 システム全体が詰まる。 トランザクションは短く保つ。
• 発展：ACID と分離レベル：トランザクションは Atomicity（全部成功か全部失敗）・Consistency（整合性）・Isolation（他と干渉しない）・Durability（確定後は消えない）を保証する。 分離レベル（READ COMMITTED / REPEATABLE READ / SERIALIZABLE）を上げるほど安全だが並行性は落ちる。
• 発展：UPSERT / MERGE：「あれば UPDATE、 なければ INSERT」は INSERT ... ON CONFLICT DO UPDATE（PostgreSQL）や MERGE で 1 文にできる。 ETL バッチの冪等性（何度流しても同じ結果）に必須。 SQL の重要イディオム。