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

SQL PostgreSQL
スポンサーリンク

Day29 後半のゴール

「“動くスキーマ”から、“ビジネスに答えを返すDB”にする」

前半で、顧客・商品・注文ヘッダ・注文明細・JSONB付きのスキーマを作り、実際に1件の注文まで流しました。 後半では、その上に「分析クエリ」と「JSONデータ処理」をしっかり乗せていきます。 ゴールは、「このスキーマから、売上・ランキング・顧客分析・JSON条件付き集計までSQLで答えられる」状態になることです。

売上集計の基本クエリ

「日別売上・顧客別売上を“GROUP BY”で取る」

まずは一番ベーシックな「集計クエリ」からいきます。 orders と order_items をどう使い分けるかがポイントです。

日別売上を出したい場合、基本的には orders だけで足ります。 total_cents に「注文全体の金額」が入っているからです。

SELECT
  date_trunc('day', created_at) AS day,
  SUM(total_cents)              AS total_cents_sum,
  COUNT(*)                      AS order_count
FROM orders
GROUP BY date_trunc('day', created_at)
ORDER BY day;
SQL

ここでの重要ポイントは、date_trunc で「日単位に丸めてから GROUP BY している」ことです。 created_at をそのまま GROUP BY すると、秒単位でバラバラになってしまうので、「どの粒度で集計したいか」を意識して丸めます。

顧客別売上を出したい場合は、customer_id で GROUP BY します。

SELECT
  c.id          AS customer_id,
  c.name        AS customer_name,
  SUM(o.total_cents) AS total_cents_sum,
  COUNT(*)      AS order_count
FROM orders o
JOIN customers c ON c.id = o.customer_id
GROUP BY c.id, c.name
ORDER BY total_cents_sum DESC;
SQL

これで、「誰がいくら使っているか」「何回注文しているか」が一目で分かります。 ここまでが「分析クエリの土台」です。

ランキングとウィンドウ関数

「“順位を付ける”をSQLでやる」

次に、「売上上位の顧客ランキング」を作ってみます。 さっきの顧客別集計に「順位」を付けるには、ウィンドウ関数を使います。

SELECT
  customer_id,
  customer_name,
  total_cents_sum,
  order_count,
  RANK() OVER (ORDER BY total_cents_sum DESC) AS sales_rank
FROM (
  SELECT
    c.id          AS customer_id,
    c.name        AS customer_name,
    SUM(o.total_cents) AS total_cents_sum,
    COUNT(*)      AS order_count
  FROM orders o
  JOIN customers c ON c.id = o.customer_id
  GROUP BY c.id, c.name
) t
ORDER BY sales_rank
LIMIT 10;
SQL

ここでのキモは、RANK() OVER (ORDER BY total_cents_sum DESC) です。 GROUP BY で集計した結果に対して、「売上の大きい順に順位を振る」ことができます。 LIMIT 10 を付ければ、「売上トップ10顧客」が簡単に取れます。

ウィンドウ関数は「集計結果にさらに計算を重ねる」ための強力な道具で、 ランキング・移動平均・累積合計など、分析系クエリでよく使われます。

JSONBを使った条件付き集計

「“premium顧客だけの売上”を集計する」

前半で、customers.extra_info に tags などを入れました。 これを使って、「premiumタグが付いている顧客だけの売上」を集計してみます。

SELECT
  SUM(o.total_cents) AS premium_total_cents,
  COUNT(*)           AS premium_order_count
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.extra_info -> 'tags' ? 'premium';
SQL

ここでのポイントは、extra_info -> 'tags' ? 'premium' という条件です。 -> 'tags' で tags 配列を取り出し、? 'premium' で「その配列に ‘premium’ が含まれているか」をチェックしています。

同じように、「favorite_category が ‘books’ の顧客だけの売上」を集計することもできます。

SELECT
  SUM(o.total_cents) AS books_fans_total_cents,
  COUNT(*)           AS books_fans_order_count
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.extra_info ->> 'favorite_category' = 'books';
SQL

ここでは ->> を使って、「文字列として取り出して比較」しています。 JSONBを使うと、「スキーマにない柔軟な属性でセグメントを切って集計する」ことができるようになります。

JSONBにインデックスを張って“実用速度”にする

「“柔軟だけど遅い”を“柔軟で速い”に変える」

JSONBは便利ですが、WHERE句で多用すると、データ量が増えたときに遅くなります。 そこで効いてくるのが、JSONB専用のインデックスです。

例えば、「tags に ‘premium’ を含む顧客」をよく絞り込みに使うなら、こういうインデックスを張ります。

CREATE INDEX idx_customers_extra_info_tags
  ON customers
  USING GIN (extra_info -> 'tags');
SQL

あるいは、「extra_info 全体に対して汎用的に検索したい」なら、こうもできます。

CREATE INDEX idx_customers_extra_info_gin
  ON customers
  USING GIN (extra_info);
SQL

GIN インデックスは、「配列やJSONの中身を検索する」のに向いたインデックスです。 これを張っておくと、extra_info -> 'tags' ? 'premium' のような条件が、フルスキャンではなくインデックス経由で高速に処理されるようになります。

重要なのは、「JSONBを本気で分析に使うなら、インデックス設計までセットで考える」ということです。 柔軟さと性能は、インデックスで両立させます。

注文明細を使った商品別売上分析

「“どの商品がどれだけ売れているか”を取る」

ここまで orders を中心に見てきましたが、「商品別売上」を出すには order_items が主役になります。 「数量×単価」を合計して、商品ごとの売上を出してみます。

SELECT
  p.id            AS product_id,
  p.name          AS product_name,
  SUM(oi.quantity) AS total_quantity,
  SUM(oi.quantity * oi.unit_price_cents) AS total_cents
FROM order_items oi
JOIN products p ON p.id = oi.product_id
GROUP BY p.id, p.name
ORDER BY total_cents DESC;
SQL

これで、「どの商品が何個売れて、いくら売上を作っているか」が分かります。 さらに、「カテゴリごとに売上を見たい」となったら、products.attributes の JSONB を使います。

SELECT
  p.attributes ->> 'category' AS category,
  SUM(oi.quantity * oi.unit_price_cents) AS total_cents
FROM order_items oi
JOIN products p ON p.id = oi.product_id
GROUP BY p.attributes ->> 'category'
ORDER BY total_cents DESC;
SQL

attributes->>’category’ でカテゴリ名を取り出し、それで GROUP BY しています。 こうして、「商品テーブルのJSON属性を使ったカテゴリ別売上」も簡単に書けます。

顧客属性 × 商品カテゴリ × 期間のクロス集計

「“ビジネスっぽい問い”を1本のSQLで表現する」

少しだけ難易度を上げて、「premium顧客が、直近30日間に、カテゴリごとにいくら使ったか」を出してみます。 顧客のJSON属性、商品のJSON属性、期間フィルタ、集計を全部組み合わせます。

SELECT
  p.attributes ->> 'category' AS category,
  SUM(oi.quantity * oi.unit_price_cents) AS total_cents,
  COUNT(DISTINCT o.customer_id)          AS customer_count
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
  c.extra_info -> 'tags' ? 'premium'
  AND o.created_at >= now() - interval '30 days'
GROUP BY p.attributes ->> 'category'
ORDER BY total_cents DESC;
SQL

このクエリは、こういう問いに答えています。

premiumタグの顧客だけを対象にする。 直近30日間の注文だけを見る。 商品カテゴリごとに、「売上合計」と「購入した顧客数」を出す。

これができると、「マーケ担当が聞きそうな質問」にSQLで直接答えられるようになります。 Day1〜Day28で学んだ「正規化」「JOIN」「集計」「JSONB」「時間条件」が、ここで全部つながっています。

セキュリティ・運用目線での総合課題の位置づけ

「“分析できるDB”を“安全に運用できるDB”にする」

総合課題だからこそ、セキュリティと運用の視点も少しだけ重ねておきます。

顧客情報（メールアドレスなど）は個人情報なので、 アプリ用ロールには「必要なテーブルへの必要な権限だけ」を与える。 分析用ロール（BIツールなど）は、UPDATE/DELETEを持たない読み取り専用にする。

JSONBには、「ログインIP」「デバイス情報」「クーポンコード」など、センシティブな情報も入りがちです。 どのロールがどのカラムを読めるべきか、権限設計を意識しておく必要があります。

また、分析クエリは重くなりやすいので、 本番トラフィックと同じDBで直接重い分析を回すのではなく、 レプリカや別環境で実行する、インデックスを適切に張る、バッチで事前集計する、などの工夫も現実には必要です。

「何ができるか」だけでなく、「どう安全に・安定して使い続けるか」まで考えられると、一気にプロ寄りになります。

Day29 後半のまとめ

Day29 後半では、前半で作った顧客・商品・注文ヘッダ・注文明細・JSONB付きスキーマの上に、「日別売上・顧客別売上を GROUP BY で集計し、ウィンドウ関数 RANK() で売上ランキングを付ける」「customers.extra_info や products.attributes の JSONB を使って premium 顧客だけ・特定カテゴリだけの売上を集計する」「JSONB に GIN インデックスを張って柔軟な条件でも実用的な速度を出す」「顧客属性 × 商品カテゴリ × 期間のようなビジネス寄りの問いを1本のSQLで表現する」といった分析クエリとJSONデータ処理を一通り通し、さらに「この分析が本番運用で安全に回るように、権限設計や実行環境（レプリカ・インデックス）もセットで考える」という視点まで重ねることで、「PostgreSQLで小さなECのデータ基盤を設計・実装・分析できる」レベルに一段引き上げることができた、というのがDay29 後半の着地点になります。

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