Day29 後半のゴール
「自分で設計したEC風DBを、“実際のクエリ”で動かして確かめる」
前半で、ECサイト風のテーブル設計を言葉とDDLレベルで組み立てました。
後半ではいよいよ、
実際にテーブルを作る
サンプルデータを入れる
典型的なクエリ（履歴・集計・ランキング）を叩いてみる
ところまで一気にやります。
ここまで来ると、「教科書の例」ではなく「自分の設計した世界」をSQLで動かす感覚になります。
テーブル定義を一気に形にする
「前半で決めた“世界観”を、そのままDDLに落とす」
まずは、前半で決めたテーブルを SQLite の DDL としてまとめます。
CREATE TABLE users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE,
password_hash TEXT NOT NULL,
registered_at TEXT NOT NULL,
status INTEGER NOT NULL DEFAULT 1
);
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
description TEXT,
price INTEGER NOT NULL,
status INTEGER NOT NULL DEFAULT 1
);
CREATE TABLE cart_items (
id INTEGER PRIMARY KEY,
user_id INTEGER NOT NULL,
product_id INTEGER NOT NULL,
quantity INTEGER NOT NULL
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
user_id INTEGER NOT NULL,
ordered_at TEXT NOT NULL,
status INTEGER NOT NULL,
total_amount INTEGER NOT NULL,
shipping_name TEXT NOT NULL,
shipping_zip TEXT NOT NULL,
shipping_addr TEXT NOT NULL,
payment_method TEXT,
payment_status INTEGER NOT NULL DEFAULT 1
);
CREATE TABLE order_items (
id INTEGER PRIMARY KEY,
order_id INTEGER NOT NULL,
product_id INTEGER NOT NULL,
product_name TEXT NOT NULL,
unit_price INTEGER NOT NULL,
quantity INTEGER NOT NULL
);
ここで大事なのは、「テーブル同士の関係が頭に描けているか」です。
users と orders は user_id でつながり、orders と order_items は order_id でつながり、order_items と products は product_id で“元の商品情報”にたどり着ける、という構造です。
サンプルデータを入れて“世界に血を通わせる”
「最低限のデータでも、関係性が見えるように入れる」
次に、少しだけサンプルデータを入れます。
量は少なくていいので、「関係が分かる」ことを優先します。
INSERT INTO users (id, name, email, password_hash, registered_at, status) VALUES
(1, '山田太郎', 'taro@example.com', 'hash1', '2025-05-01 10:00:00', 1),
(2, '佐藤花子', 'hanako@example.com', 'hash2', '2025-05-02 11:00:00', 1);
INSERT INTO products (id, name, description, price, status) VALUES
(1, 'USBマウス', '有線マウス', 1500, 1),
(2, 'メカニカルキーボード', '青軸キーボード', 8000, 1),
(3, '27インチモニタ', 'フルHDディスプレイ', 20000, 1);
INSERT INTO orders (id, user_id, ordered_at, status, total_amount,
shipping_name, shipping_zip, shipping_addr,
payment_method, payment_status)
VALUES
(1, 1, '2025-05-10 09:00:00', 2, 23000,
'山田太郎', '100-0001', '東京都千代田区1-1-1',
'credit_card', 2),
(2, 1, '2025-05-15 15:30:00', 1, 8000,
'山田太郎', '100-0001', '東京都千代田区1-1-1',
'bank_transfer', 1),
(3, 2, '2025-05-20 20:00:00', 2, 21500,
'佐藤花子', '150-0001', '東京都渋谷区2-2-2',
'credit_card', 2);
INSERT INTO order_items (id, order_id, product_id, product_name, unit_price, quantity) VALUES
(1, 1, 1, 'USBマウス', 1500, 1),
(2, 1, 2, 'メカニカルキーボード', 8000, 1),
(3, 1, 3, '27インチモニタ', 20000, 1),
(4, 2, 2, 'メカニカルキーボード', 8000, 1),
(5, 3, 1, 'USBマウス', 1500, 1),
(6, 3, 3, '27インチモニタ', 20000, 1);
この時点で、
「山田さんが2回注文していて、その中身はこう」
「佐藤さんは1回注文していて、その中身はこう」
というストーリーが、テーブルの中に埋め込まれています。
ユーザー別の注文履歴を出すクエリ
「JOIN の流れを“自分の設計”に当てはめて確認する」
まずは、山田さん（user_id = 1）の注文履歴を出してみます。
SELECT
o.id AS order_id,
o.ordered_at,
o.status,
o.total_amount,
o.payment_method,
o.payment_status
FROM orders o
JOIN users u
ON o.user_id = u.id
WHERE
u.email = 'taro@example.com'
ORDER BY
o.ordered_at;
ここでやっていることはシンプルです。
users と orders を JOIN して、「ユーザー×注文」の世界を作る
WHERE で「メールアドレスが taro@example.com のユーザー」に絞る
そのユーザーの注文だけを、時系列で並べる
このクエリが素直に書ける時点で、
「users と orders の関係を正しく設計できている」と言えます。
さらに、注文ごとの明細まで含めて出すならこうです。
SELECT
o.id AS order_id,
o.ordered_at,
oi.product_name,
oi.unit_price,
oi.quantity
FROM users u
JOIN orders o
ON u.id = o.user_id
JOIN order_items oi
ON o.id = oi.order_id
WHERE
u.email = 'taro@example.com'
ORDER BY
o.ordered_at,
oi.id;
ここでは、
users → orders → order_items
という JOIN の流れをたどりながら、
「ユーザー×注文×明細」の世界を作っています。
商品別の売上集計を出すクエリ
「Day27 でやった“商品別売上”を、自分のEC設計に適用する」
次に、「商品ごとの売上合計」を出してみます。
SELECT
oi.product_id,
oi.product_name,
SUM(oi.unit_price * oi.quantity) AS total_amount,
SUM(oi.quantity) AS total_quantity
FROM order_items oi
JOIN orders o
ON oi.order_id = o.id
WHERE
o.status <> 3 -- 3=キャンセルを除外する想定
GROUP BY
oi.product_id,
oi.product_name
ORDER BY
total_amount DESC;
ここでのポイントは二つです。
売上としてカウントしたくない注文（キャンセルなど）を WHERE で除外している
商品名・単価は「注文時点の値」を order_items から取っている
このクエリが自然に書けるのは、
前半で「商品テーブルの price と、注文明細の unit_price の役割を分けた」からです。
もし、注文明細に unit_price を持たせていなかったら、
「過去の注文時の価格」が分からなくなり、
正しい売上集計ができません。
ここで、設計の判断がクエリの書きやすさ・正しさに直結していることが体感できます。
日別売上推移を出すクエリ
「時間軸の集計で、“どのテーブルを起点にするか”を意識する」
次は、「日別の売上推移」を出します。
起点は orders です。
SELECT
date(o.ordered_at) AS order_date,
SUM(oi.unit_price * oi.quantity) AS total_amount
FROM orders o
JOIN order_items oi
ON o.id = oi.order_id
WHERE
o.status <> 3
GROUP BY
date(o.ordered_at)
ORDER BY
order_date;
流れとしては、
orders と order_items を JOIN して「注文×明細」の世界を作る
キャンセルを除外するdate(ordered_at) ごとに GROUP BY して、売上合計を出す
Day27・Day28 でやった「時間軸の集計」と「インデックスの効き方」を思い出すと、
本番では ordered_at にインデックスを張り、
場合によっては WHERE ordered_at >= ... AND ordered_at < ... のように
範囲条件で書く方がよい、という話にもつながります。
VIEW を使って“よく使う集計”に名前をつける
「総合課題でも、再利用しやすい形にしておく」
Day27 でやったように、
よく使う集計は VIEW にしておくと便利です。
例えば、「商品別売上」をビューにします。
CREATE VIEW product_sales AS
SELECT
oi.product_id,
oi.product_name,
SUM(oi.unit_price * oi.quantity) AS total_amount,
SUM(oi.quantity) AS total_quantity
FROM order_items oi
JOIN orders o
ON oi.order_id = o.id
WHERE
o.status <> 3
GROUP BY
oi.product_id,
oi.product_name;
こうしておけば、
SELECT *
FROM product_sales
ORDER BY total_amount DESC
LIMIT 10;
のように、
「トップ10商品」を簡単に取れます。
総合課題の段階で、
「生のクエリ」だけでなく「ビューとしての切り出し」まで意識できると、
設計〜実装〜再利用の流れが一気にプロっぽくなります。
インデックスを実際のクエリに結びつけて考える
「“このクエリを速くしたいなら、どこに張る？”と自問する」
ここまで書いたクエリを眺めながら、
インデックスの候補を改めて考えてみます。
ユーザー別注文履歴
WHERE で u.email を使っている → users.email に UNIQUE（インデックス）
JOIN で o.user_id を使っている → orders.user_id にインデックス
商品別売上
JOIN で oi.order_id と o.id を使っている → order_items.order_id にインデックス
集計の軸は oi.product_id → order_items.product_id にインデックス
日別売上
WHERE・GROUP BY で o.ordered_at を使っている → orders.ordered_at にインデックス
こうやって、
「このクエリを速くしたいなら、どのカラムが“検索の軸”になっているか」
「そのカラムにインデックスがあるか」
をセットで考える癖をつけると、
設計とパフォーマンスの距離がぐっと縮まります。
セキュリティ・権限の観点で“どのクエリが重いか／危ないか”を見る
「総合課題だからこそ、“動けばOK”で終わらせない」
最後に、セキュリティの視点も少しだけ重ねます。
ユーザー別注文履歴
個人情報＋購入履歴がセットで見える
→ アクセスできる人・画面はかなり限定すべき
商品別売上・日別売上
個人情報は出ないが、ビジネス上センシティブな数字
→ 社内でも権限を分ける対象
重い集計クエリ（期間が長い・条件がゆるい）
→ インデックスが効いていないと、DoS 的な攻撃ポイントになりうる
総合課題の段階で、
「このクエリは誰が叩けるべきか」
「このテーブルにアクセスできる権限はどこまで許すか」
まで意識できると、
単なる“SQL が書ける人”から一歩抜け出せます。
Day29 後半のまとめ
前半で設計した users・products・cart_items・orders・order_items を実際に DDL として定義し、サンプルデータを入れることで、「自分の設計した世界」を SQLite 上に立ち上げた。
ユーザー別注文履歴、商品別売上、日別売上といったクエリを、自分のスキーマに対して JOIN＋GROUP BY で素直に書けることが、「テーブル設計が現実と噛み合っている」ことの確認になる。
商品別売上のような集計では、「商品テーブルの price ではなく、注文明細の unit_price を使う」ことで、「そのときどう売ったか」という履歴を正しく集計できる。これは Day25・Day26 の設計判断が効いているポイント。
よく使う集計（商品別売上など）は VIEW に切り出して名前をつけておくと、レポートやダッシュボードで再利用しやすくなり、「定義がバラバラな数字」が生まれにくくなる。
書いたクエリを眺めながら、「このクエリを速くしたいならどのカラムにインデックスを張るか」「このクエリは誰が叩けるべきか」をセットで考えることで、設計・パフォーマンス・セキュリティを一体として捉える視点が身につく。
ここまで来たあなたは、
「サンプルを真似してSQLを書く人」ではなく、
「自分で世界を設計し、その世界をSQLで動かせる人」になりつつあります。
Day30 では、この29日間で身につけた感覚を振り返りつつ、「この先どう伸ばしていくか」まで一緒に整理していきましょう。