全部のテーブルをつなげたら、いよいよクエリを書いてみよう。まあ、2つくらいじゃ初心者向けだよね。君はもうプロだから、データベースに50個(!)のクエリを書いてもらうよ。しかも、これが一番必要なやつだけ。
データベースへのクエリ
1. ショーケース用商品のリスト取得
このクエリは、メインページやカタログに表示するための、すべてのアクティブな商品とその基本価格・画像を返すよ。これでショーケースを素早く作れて、商品の情報も常に最新にできる。
2. キーワードで商品の検索
ユーザーが商品名や説明文に一致するキーワードで商品を探せるようにする。カタログ検索のための超重要なユーザ機能だよ。
3. 商品IDで商品カード取得
特定の商品について、ブランドやカテゴリも含めた詳細情報を返す。商品ページの詳細表示に必須。
4. 商品バリエーションのリスト
その商品のすべてのバリエーション（SKU）：サイズ、色、在庫、価格を出す。商品ページで好きなバリエーションを選ぶために使う。
5. 商品画像ギャラリー
商品カードをちゃんと表示するには、全部の写真が大事。メイン画像も含めて全部返すクエリだよ。
6. 商品の平均評価とレビュー数
商品の評価やレビュー数を表示するために使う。これは信頼や評判に超大事。
7. 商品への詳細レビューリスト
商品カードのレビュー欄用：評価、テキスト、著者、レビュー日。新しい購入者の判断材料になるよ。
8. 商品のQ&A
各商品ごとの質問と回答を取得するクエリ。商品カードのFAQブロックに必須。
9. 階層付き商品カテゴリ
カタログ構造を可視化したり、フィルターやメニュー用のナビゲーションツリーを作るのに使う。
10. カテゴリとサブカテゴリの商品
選択したカテゴリやその「子」カテゴリ（ネストレベル）の商品を全部出すのに便利。
11. ブランドリスト
ブランドでのフィルタやブランドリスト、ランディングページ作成用。
12. 人気タグとその商品数
一番使われてるタグを分析して、トレンド商品やタグクラウドを作るのに使う。
13. 商品の価格変更履歴
分析や価格推移（旧価格/新価格、セール）の表示用。
14. 商品ステータス変更履歴
商品のライフサイクルやショーケースから消えた理由、返品理由を追跡できる。
15. 証明書やライセンスでの検索
プロのバイヤーやB2Bセグメントには超重要（品質や合法性チェック）。
16. 商品のサプライヤーデータ
管理や品質管理、サプライヤーとの連絡に大事。
17. 倉庫ごとの商品在庫
各倉庫の最新在庫を管理・把握。物流システムや「在庫切れ」防止に必須。
18. 在庫が閾値以下の商品
自動補充や、在庫切れによる売上損失防止のための自動化。
19. 倉庫内の商品移動（監査）
指定期間内のすべての入出庫・調整を追跡。棚卸や損失防止に大事。
20. 倉庫間移動のロジスティクス
物流センター間の商品移動の履歴やステータスを見れる。
21. 配送：方法と料金
注文時の配送料計算やユーザーへの案内用。
22. ユーザーの注文履歴
マイページの超重要部分 — すべての注文、ステータス、合計金額。
23. 注文の詳細（アイテム付き）
注文の全構成（内容、価格、数量）を取得して、フロントやサポートで表示できる。
24. 期間・ステータス別注文レポート
売上分析やレポート用。期間や必要なステータス（例：「完了」）で注文を返す。
25. 「放置」カート
マーケター向け分析：注文されなかったカート。リターゲティングの候補。
26. 売上トップ
「売れ筋」やマーケティング特集用の分析：どの商品が一番売れてるか。
27. 日別売上（グラフ用）
日次売上レポート — ビジネス動向分析やグラフ作成の基礎。
28. 返品リスト
すべての注文の返品とその理由・ステータスを表示。返品理由の分析に役立つ。
29. 注文キャンセルリスト
損失やキャンセル理由の管理：理由、誰がいつキャンセルしたかも表示。
30. 発送待ち注文
倉庫や配送部門向け — 梱包・発送が必要な注文と配送詳細。
31. 平均注文額
「Average Order Value」 — マーケや品揃え評価のキー指標。
32. プロモコード利用注文
キャンペーン効果分析：どのプロモコードがどれだけ使われたか。
33. カテゴリ・ブランド別割引利用
どのキャンペーンが効いてるか、カテゴリやブランドごとの割引人気をモニタリング。
34. 適用済みプロモコードとそのユーザー
プロモコード利用の管理、異常や悪用の発見。
35. 注文ごとの支払い履歴
サポートや会計用：すべての支払いトランザクション、ステータス、支払い方法を表示。
36. 返金付き注文
返金分析、会計レポート生成、不正防止に。
37. ユーザーウォレット残高と取引履歴
ユーザーのボーナスやキャッシュバック残高、履歴の管理・表示。
38. ユーザーのサポート申請
自分の問い合わせとその対応状況をユーザーが見れる。
39. サポート申請のSLA分析
各優先度ごとの平均応答・解決時間を分析。SLA管理に大事。
40. サポート申請のメッセージ
選択した申請の全やりとりを見れる。ユーザーにもサポートにも重要。
41. カテゴリ別アクティブFAQ
クライアントナレッジベース用のよくある質問を出す。サポート負荷軽減に役立つ。
42. アクティブなマーケティングキャンペーンとバナー
サイト上の最新プロモーション表示用。
43. トップページのおすすめ商品
「お気に入り」ブロック用：トップページで目立たせたい商品。
44. A/Bテスト履歴
UXやマーケ最適化のための実験分析。
45. 特定ユーザーの商品閲覧履歴
「あなたが見た商品」やパーソナライズ推薦用。
46. 人気ユーザー検索クエリ
ユーザー需要分析。検索やサジェスト最適化に役立つ。
47. トラフィックソース分析
どの広告チャネルがトラフィックやコンバージョンを生んでるか評価できる。
48. コホート別ユーザーリテンション
ロイヤリティやリピート購入評価のキー指標。
49. トップページ用ニュース/記事
ブログのニュースや記事表示、ユーザーエンゲージメント向上に。
50. アクティブなサイトページと関連コンテンツブロック
サイトコンテンツの整合性、CMS動作、ページ上のデータ表示チェック用。
インデックスを追加しよう
クエリはもちろん大事だけど、速く動かなきゃ意味ないよね。だから、プロジェクトのメインテーブルに40個のインデックスを追加して、クエリのパフォーマンスと運用のしやすさをアップしよう。
1. product.product(status)のインデックス
ほぼすべての商品クエリはstatusでフィルタされる（例：ショーケースや検索用のアクティブ商品）。インデックスで特定ステータスの商品取得が速くなる。
2. product.variant(product_id, is_active)のインデックス
SKUやショーケース用のバリエーションクエリは、商品との紐付けやアクティブ状態でフィルタする。複合インデックスで特定商品のアクティブバリエーションを最適に取得。
3. product.image(product_id, is_main DESC)のインデックス
商品のメイン画像（または全画像）取得は、商品ごとのフィルタと「メイン」フラグでソートする。インデックスでギャラリー用データの高速取得が可能。
4. product.product(name text_pattern_ops)のインデックス
商品名のキーワード検索（ILIKE '%...%'）を速くする専用インデックス。大規模データでも部分一致検索が快適。
5. product.product(description gin_trgm_ops)のインデックス
4と同じく、説明文のILIKEや全文検索用。GINインデックス＋トライグラムでテキストフィールドのフィルタが速くなる。
6. product.product(category_id)のインデックス
カテゴリやサブカテゴリでの絞り込みが多い（カタログカテゴリフィルタ参照）。インデックスで指定カテゴリの商品を素早く取得。
7. product.category(parent_id)のインデックス
カテゴリ階層やナビゲーションツリー構築でparent_idでの検索が多い。インデックスで再帰的階層クエリが速くなる。
8. product.review(product_id)のインデックス
商品のレビュー取得はすべてproduct_idでフィルタ（平均評価やレビューリスト）。インデックスで集計や取得が超速くなる。
9. product.review(product_id, created_at DESC)のインデックス
最新レビュー取得（ORDER BY createdat DESC）やproductidでのフィルタに複合インデックスが効く。
10. product.question(product_id, created_at DESC)のインデックス
特定商品のQ&A取得や作成日時ソートに使う。両条件をカバーしてQ&Aセクションの高速表示。
11. product.answer(question_id, created_at)のインデックス
商品の質問への回答取得はquestion_idでの高速アクセス＋日付ソートが多い。インデックスでQ&A生成の遅延を最小化。
12. product.price_history(variant_id, changed_at DESC)のインデックス
バリエーションごとの価格変更履歴や最近の変更取得に。価格推移や「旧/新価格」分析クエリが速くなる。
13. product.status_history(product_id, changed_at DESC)のインデックス
商品のステータス変更履歴取得や監査、ライフサイクル管理で使う。複合インデックスでこういうクエリが超速くなる。
14. product.certificate(product_id)のインデックス
商品IDで証明書を探すのはB2Bや認証ショーケースでよくある。インデックスでチェックが速い。
15. product.license(product_id)のインデックス
商品のライセンス検索や、ライセンスタイプでのフィルタ用。
16. product.product_tag(tag_id)のインデックス
特定タグの商品取得（または逆）でよく使う。インデックスでタグクラウドやフィルタが高速化。
17. product.product_tag(product_id)のインデックス
特定商品のタグ取得を速くして、タグでの絞り込みも快適に。
18. logistics.inventory(product_id, warehouse_id)のインデックス
倉庫ごとの商品在庫や全倉庫集計に即アクセス — 物流やリアルタイムショーケースに必須。
19. logistics.inventory(variant_id)のインデックス
バリエーション（色/サイズ）ごとの在庫管理や横断レポート用。
20. logistics.stock_level(product_id, warehouse_id)のインデックス
倉庫での最小在庫チェック（自動発注や在庫警告用）。inventoryとの比較にも必要。
21. logistics.inventory_movement(product_id, changed_at DESC)のインデックス
最近の入出庫履歴（監査）取得に。ミス防止や損失分析、納品管理に便利。
22. logistics.transfer(product_id, requested_at DESC)のインデックス
倉庫間移動の分析や商品別・リクエスト日時ソート用。
23. logistics.shipping_rate(shipping_method_id, destination_zone)のインデックス
配送コスト計算時、方法IDと配送先ゾーンで料金を選ぶ。インデックスで注文時の計算が速い。
24. "order".order(user_id, placed_at DESC)のインデックス
ユーザー注文履歴取得はuser_idでフィルタ＋注文日ソート。複合インデックスでマイページの履歴表示が超速い。
25. "order".order(status, placed_at)のインデックス
注文レポートや分析、ステータス（例：「処理中」/「完了」）での検索用。
26. "order".order_item(order_id)のインデックス
注文IDで全アイテム取得は超頻出。注文詳細表示に必須。
27. "order".order_item(product_id)のインデックス
商品別売上分析や統計で、注文アイテムの高速取得が必要。
28. "order".return(order_id)のインデックス
返品と注文の紐付けはサポートや返品分析で使う。インデックスで注文番号から即検索。
29. "order".cancellation(order_id)のインデックス
返品と同じく、注文キャンセルの分析やサポート用に高速化。
30. "order".cart(user_id, updated_at DESC)のインデックス
ユーザーの最新カート検索（例：「放置」カート検索）にuser_id＋更新日ソートのインデックスが便利。
31. payment.payment_transaction(order_id)のインデックス
支払い履歴のほとんどは注文ごとのフィルタ。インデックスで注文トランザクションに即アクセス。
32. payment.refund(transaction_id)のインデックス
特定トランザクションの返金検索を効率化。サポートやレポート、不正防止に。
33. payment.wallet(user_id)のインデックス
ユーザーウォレットへの高速アクセスで残高や履歴チェックが楽。
34. payment.wallet_transaction(wallet_id, created_at DESC)のインデックス
ウォレット取引履歴の取得や日付ソート（例：履歴表示）用。
35. support.support_ticket(user_id, created_at DESC)のインデックス
特定ユーザーのサポート履歴（マイページ/カスタマーサービス）に複合インデックスで最適化。
36. support.ticket_message(ticket_id, sent_at)のインデックス
チケットごとの全やりとり表示に、チケットID＋送信日時インデックスでソートが速い。
37. support.ticket_sla_tracking(ticket_id)のインデックス
SLA分析や各チケットの管理に、ticket_idインデックスでSLAデータに即アクセス。
38. marketing.promo_usage(user_id, used_at DESC)のインデックス
プロモコード利用分析や悪用防止に、user_id＋利用日時で高速検索。
39. analytics.product_view(user_id, viewed_at DESC)のインデックス
ユーザーの商品閲覧履歴（パーソナライズや推薦）分析に、user_id＋閲覧日時で即アクセス。
40. analytics.search_query_log(query_text)のインデックス
人気検索クエリや利用頻度集計は検索分析のキー。インデックスでクエリテキストの集計やカウントが速い。
注意
ILIKE検索にはpg_trgm拡張のGINインデックス推奨。部分一致やあいまい検索に強い。日付での集計やソートが多い大規模テーブルには、日付のDESCインデックスが最新レコード取得を高速化するよ。
実際の実行プランや負荷統計に合わせてインデックスを調整するのがベストだけど、上記のインデックスでマーケットプレイスの主要なクエリシナリオはカバーできる。
関数を追加しよう
まだ疲れてない？じゃあ、今ある＆これからのクエリをもっと楽に書くために、いくつか関数も作ろう。キーとなるクエリの実装を速くしたり、アプリのコード重複を減らしたり、ビジネスロジックをDB側に集中させるためだよ。
1. タグやブランドも考慮したキーワード商品検索
なぜ必要？
普通の名前・説明検索だけじゃ足りない。タグやブランドでも探したいことが多い。万能関数で拡張検索ロジックを集中化、コード重複を減らしてフロント連携も楽になる。
2. 商品IDで完全な商品カード取得（カード用全データ）
なぜ必要？
フロントでは商品情報（基本項目、ブランド、カテゴリ、画像、タグ、属性、平均評価、レビュー数）が一気に必要なことが多い。関数で一発取得してDBアクセス回数を減らせる。
3. ネスト付きカテゴリ階層取得
なぜ必要？
ショーケースやフィルタ、パンくずリスト用にカテゴリツリー（またはパス）構築が必要。クライアント側の再帰クエリの代わりに、関数で全部まとめて返す。
4. カテゴリごとの平均・最小・最大価格計算
なぜ必要？
カタログフィルタや分析で、カテゴリ内商品の価格範囲や平均値を取得したい。関数でサブクエリの重複を省ける。
5. 全倉庫の在庫自動チェック＆集計
なぜ必要？
商品（や各バリエーション）の全体在庫を即座に知れる。ショーケースや倉庫、物流に便利。計算を集中化してビジネスロジックの重複を防ぐ。
6. ユーザー注文履歴（詳細付き）取得
なぜ必要？
ユーザーの注文リスト（アイテム、合計、ステータス含む）を一発で返して、フロントでマイページをすぐ作れる。
7. ユーザーの平均評価（出品者/購入者）取得
なぜ必要？
プラットフォームでの信頼や評判表示に、出品者・購入者としての平均評価が大事。関数で集計計算をやる。
8. ユーザーによるプロモコード利用（全条件バリデータ）
なぜ必要？
プロモコードの有効性・利用制限・日付など、全部のビジネスロジックを一つの関数に集中。アプリロジックがシンプルになって、条件重複のバグも防げる。
9. ユーザーイベントの汎用ログ関数
なぜ必要？
分析や監査のために、イベントログを集中管理。コード重複やユーザーアクションデータの取りこぼしリスクを減らせる。
10. ボーナスウォレット残高＆累計付与額取得関数
なぜ必要？
一発でユーザーの現在残高と累計付与額を取得。ダッシュボード表示やSQLクエリ数削減に便利。
11. ログ付き注文ステータス変更汎用関数
なぜ必要？
注文ステータスを変更しつつ、履歴ログも追加。アプリのいろんな場所でのステータス変更ミスを最小化。
12. サポートダイアログ（チケット＋全メッセージ）取得関数
なぜ必要？
チケットの全やりとり（申請詳細＋各メッセージ）を返す。フロントでチケット履歴を作るのが楽になる。
13. emailまたは電話でユーザー存在チェック
なぜ必要？
登録やパスワードリセット時に使う。フロント・バック両方のロジック重複を防ぐ。
注意
この関数セットで主要なビジネスシナリオをカバーできて、データ操作が楽になり、ロジックも最適化、フロントや連携開発も速くなるよ。気に入ってくれると嬉しい :)
解決策を含むファイル
GO TO FULL VERSION