PostgreSQL | SQLite＋MySQL経験者向け、30日で習得するPostgreSQL：プロレベル運用 － Day29 総合課題

SQL PostgreSQL
スポンサーリンク

Day29 前半のゴール

「バラバラに学んだ機能を、“1つのミニ業務システム”としてつなげる」

Day29 は総合課題です。 テーマは「顧客管理」「注文処理」「分析クエリ」「JSONデータ処理」を全部まとめて実装すること。 前半では、まず「どんなテーブル設計にするか」「基本的なINSERT／SELECTができる状態にすること」をゴールにします。

ここまで学んだ正規化・インデックス・JSON・分析系関数を、「小さなECサイト（通販）」をイメージしながら一つにまとめていきます。

全体シナリオを決める

「小さなECサイトをPostgreSQLで支える、という設定にする」

いきなりテーブルを作る前に、「どんな世界をDBで表現するか」を言葉にしておきます。 ここでは「小さなECサイト」を想定します。

顧客が会員登録をする。 顧客が商品をカートに入れて、注文を確定する。 注文には複数の商品が含まれる。 管理画面では、「売上ランキング」「顧客ごとの購入回数」「期間別売上」などを分析したい。 一部の柔軟な情報（顧客の属性タグ、注文時のメタ情報など）はJSONで持ちたい。

このシナリオを支えるために、最低限必要なテーブルはこういうイメージになります。

顧客を管理するテーブル（customers） 商品を管理するテーブル（products） 注文のヘッダ（orders） 注文の明細（order_items）

そして、顧客や注文に「柔軟な追加情報」を持たせるための JSONB カラムをいくつか仕込んでおきます。

顧客管理テーブルの設計

「正規化しつつ、“柔らかい情報”はJSONに逃がす」

まずは顧客テーブルです。 基本的な情報は普通のカラムで持ち、変化しやすい・項目が増えがちな情報は JSONB に逃がす、というハイブリッド構成にします。

CREATE TABLE customers (
  id            bigserial PRIMARY KEY,
  name          text NOT NULL,
  email         text NOT NULL UNIQUE,
  created_at    timestamptz NOT NULL DEFAULT now(),
  updated_at    timestamptz NOT NULL DEFAULT now(),
  extra_info    jsonb NOT NULL DEFAULT '{}'::jsonb
);
SQL

ここでのポイントを深掘りします。

id は bigserial の主キー。 name, email はよく使うので普通のカラム。 email に UNIQUE 制約を付けて、「同じメールアドレスで複数登録できない」ようにしている。 created_at, updated_at はトリガーで自動更新してもよいが、ここではシンプルに DEFAULT now() から始める。 extra_info は jsonb 型で、「顧客の属性タグ」「マーケティング用フラグ」「任意のメモ」などを柔軟に入れられる。

例えば、extra_info にはこんなJSONを入れられます。

{
  "tags": ["premium", "newsletter"],
  "age_range": "30-39",
  "favorite_category": "books"
}

こうしておくと、「基本的な構造は保ちつつ、ビジネス側の“あとから増える要望”に耐えられる顧客テーブル」になります。

商品テーブルの設計

「価格や在庫は普通のカラム、属性はJSONで拡張可能に」

次に商品テーブルです。 ここも同じく、「よく使うものはカラム」「変わりやすいものはJSONB」という構成にします。

CREATE TABLE products (
  id            bigserial PRIMARY KEY,
  name          text NOT NULL,
  sku           text NOT NULL UNIQUE,
  price_cents   integer NOT NULL,
  stock_qty     integer NOT NULL DEFAULT 0,
  created_at    timestamptz NOT NULL DEFAULT now(),
  updated_at    timestamptz NOT NULL DEFAULT now(),
  attributes    jsonb NOT NULL DEFAULT '{}'::jsonb
);
SQL

重要なポイントはこうです。

sku は「商品コード」のイメージで、UNIQUE 制約を付けている。 price_cents は「金額を整数（円・セント）で持つ」ためのカラム。浮動小数ではなく整数にしておくと安全。 stock_qty は在庫数。ここから在庫管理のロジックを組み立てられる。 attributes は JSONB で、「色」「サイズ」「ブランド」「タグ」などを柔軟に持てる。

例えば、attributes にはこんなJSONを入れられます。

{
  "color": "red",
  "size": "M",
  "categories": ["fashion", "t-shirt"]
}

こうしておくと、「分析クエリで attributes->>’color’ = ‘red’ のような条件を使う」こともできます。

注文ヘッダ（orders）の設計

「顧客と紐づけ、状態や合計金額を持たせる」

注文は、「ヘッダ」と「明細」に分けるのが定番です。 まずはヘッダ側（orders）を作ります。

CREATE TABLE orders (
  id              bigserial PRIMARY KEY,
  customer_id     bigint NOT NULL REFERENCES customers(id),
  status          text NOT NULL,
  total_cents     integer NOT NULL,
  created_at      timestamptz NOT NULL DEFAULT now(),
  updated_at      timestamptz NOT NULL DEFAULT now(),
  meta            jsonb NOT NULL DEFAULT '{}'::jsonb
);
SQL

ここでの重要ポイントを整理します。

customer_id は customers.id への外部キー。 「この注文はどの顧客のものか」を表す。 status は ‘pending’, ‘paid’, ‘shipped’, ‘cancelled’ などの状態を文字列で持つ（本格的にはENUMや別テーブルでもよい）。 total_cents は「注文全体の合計金額」。明細から集計して入れる。 meta は JSONB で、「配送メモ」「クーポン情報」「決済プロバイダのレスポンス」などを柔軟に保存できる。

例えば、meta にはこんなJSONが入ります。

{
  "payment_method": "credit_card",
  "coupon_code": "WELCOME10",
  "note": "玄関前に置き配希望"
}

これにより、「注文の基本情報＋柔軟なメタ情報」を1つのテーブルで扱えます。

注文明細（order_items）の設計

「1つの注文に複数商品をぶら下げる“子テーブル”」

注文明細は、「どの注文に、どの商品が、いくつ、いくらで入っているか」を表すテーブルです。

CREATE TABLE order_items (
  id              bigserial PRIMARY KEY,
  order_id        bigint NOT NULL REFERENCES orders(id),
  product_id      bigint NOT NULL REFERENCES products(id),
  quantity        integer NOT NULL,
  unit_price_cents integer NOT NULL,
  created_at      timestamptz NOT NULL DEFAULT now()
);
SQL

ここでのポイントはこうです。

order_id は orders.id への外部キーで、「どの注文の明細か」を表す。 product_id は products.id への外部キーで、「どの商品か」を表す。 quantity は数量。 unit_price_cents は「そのときの単価」。 ここが重要で、「後から商品価格が変わっても、注文時の価格は変わらないようにする」ために、明細にコピーしておく。

この構造により、

1つの注文（orders.id）に対して、複数の order_items がぶら下がる。 顧客1人に対して、複数の注文がぶら下がる。

という「1対多」「多対多（顧客と商品）」の関係を、正規化された形で表現できます。

サンプルデータを入れて“動くイメージ”を掴む

「顧客1人・商品2つ・注文1件を手で入れてみる」

設計だけだとイメージがぼやけるので、実際にINSERTしてみます。

顧客を1人登録します。

INSERT INTO customers (name, email, extra_info)
VALUES (
  '山田太郎',
  'taro@example.com',
  '{"tags": ["premium"], "favorite_category": "books"}'
)
RETURNING id;
SQL

ここで返ってきた id をメモしておきます（仮に1とします）。

商品を2つ登録します。

INSERT INTO products (name, sku, price_cents, stock_qty, attributes)
VALUES
  ('PostgreSQL入門本', 'BOOK-001', 2500 * 100, 100, '{"category": "books"}'),
  ('ロゴ入りTシャツ', 'TSHIRT-001', 1500 * 100, 50, '{"category": "fashion", "size": "M"}')
RETURNING id;
SQL

ここで返ってきた id を、仮に 1（本）、2（Tシャツ）とします。

次に、注文ヘッダを1件作ります。

INSERT INTO orders (customer_id, status, total_cents, meta)
VALUES (
  1,
  'paid',
  2500 * 100 + 1500 * 100,
  '{"payment_method": "credit_card"}'
)
RETURNING id;
SQL

これで orders.id が例えば 1 だとします。

最後に、注文明細を2行入れます。

INSERT INTO order_items (order_id, product_id, quantity, unit_price_cents)
VALUES
  (1, 1, 1, 2500 * 100),
  (1, 2, 2, 1500 * 100);
SQL

これで、「山田太郎さんが、本を1冊とTシャツを2枚買った」という注文が、 customers／products／orders／order_items の4テーブルにまたがって表現されました。

基本的な結合クエリで“全体像”を確認する

「顧客＋注文＋明細＋商品を1本のSELECTで見る」

ここまで来たら、「ちゃんとつながっているか」をSELECTで確認してみます。

SELECT
  c.name          AS customer_name,
  o.id            AS order_id,
  o.status        AS order_status,
  o.total_cents   AS order_total_cents,
  p.name          AS product_name,
  oi.quantity     AS quantity,
  oi.unit_price_cents AS unit_price_cents
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.id = 1;
SQL

この結果は、例えばこんなイメージになります。

顧客名：山田太郎 注文ID：1 ステータス：paid 合計金額：550000（＝5500円を100倍した値） 商品名：PostgreSQL入門本、数量1、単価250000 商品名：ロゴ入りTシャツ、数量2、単価150000

この1本のSELECTで、「顧客→注文→明細→商品」という関係が全部つながっていることが確認できます。

ここまでできれば、「顧客管理」「注文処理」の基本部分は実装できたと言えます。

JSONBを使った簡単なフィルタの例

「“タグがpremiumの顧客の注文だけ”を取る」

最後に、JSONBを使った簡単な例を1つだけ前半に入れておきます。 顧客の extra_info.tags に “premium” が含まれている人の注文だけを取りたいとします。

SELECT
  c.name,
  o.id,
  o.total_cents
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.extra_info -> 'tags' ? 'premium';
SQL

ここで使っているポイントはこうです。

c.extra_info -> 'tags' で、JSONBから tags 配列を取り出している。 ? 'premium' は、「そのJSON配列（またはオブジェクト）の中に ‘premium’ という要素（キー）があるか」を判定する演算子。

これにより、「premiumタグが付いている顧客の注文だけ」を簡単に絞り込めます。

Day29 後半では、ここからさらに「分析クエリ（集計・ランキング）」「JSONBのインデックス」「もう少し複雑な条件」を扱っていきます。

Day29 前半のまとめ

Day29 前半では、「小さなECサイト」を題材にして、顧客（customers）、商品（products）、注文ヘッダ（orders）、注文明細（order_items）という4つのテーブルを正規化しつつ、顧客の属性や商品の属性、注文のメタ情報など“変化しやすい情報”を JSONB カラム（extra_info, attributes, meta）に逃がすハイブリッド設計を行い、実際に顧客1人・商品2つ・注文1件をINSERTしてから、JOINを使って「顧客→注文→明細→商品」を1本のSELECTで確認し、さらに extra_info -> 'tags' ? 'premium' のようなJSONBフィルタで「premiumタグの顧客の注文だけ」を絞り込むところまでを通して、「顧客管理＋注文処理＋JSONデータ処理」が1つのスキーマとしてつながる感覚を掴むことができた、という状態がゴールになります。

次へ
12
スポンサーリンク
タイトルとURLをコピーしました