Top50 資料庫查詢語句

當你把所有資料表都串起來之後，就是時候來寫幾個查詢語句啦。雖然說幾個，這只是給新手的。你現在已經是專業的了，所以你需要寫 50(!) 個查詢語句給你的資料庫。而且這還只是最重要的那些。

資料庫查詢語句

1. 取得展示櫃商品清單

這個查詢會回傳所有啟用中的商品，包含主要價格和圖片，用來在首頁和目錄顯示。這樣可以快速組成展示櫃，並保持商品資訊的即時性。

2. 用關鍵字搜尋商品

讓使用者可以用名稱或描述的關鍵字來找商品。這是 catalog 快速搜尋很重要的功能。

3. 用 ID 查詢商品卡片

回傳某個商品的詳細資訊，包括品牌和分類。這是顯示商品詳細頁必備的。

4. 商品所有變體清單

列出商品所有可用的變體（SKU）：尺寸、顏色、庫存、價格。用來在商品頁選擇不同規格。

5. 商品圖片集

完整顯示商品卡片時，所有照片都很重要。這個查詢會回傳所有圖片，並標記主圖。

6. 商品平均評分和評論數

用來顯示商品評分和評論數，這對買家信任和商品聲譽很重要。

7. 商品詳細評論清單

商品卡片的評論區：評分、內容、作者和評論日期。幫助新買家決定要不要買。

8. 商品問答

查詢每個商品的問答，這對 FAQ 區塊很重要。

9. 商品分類階層

可以視覺化 catalog 結構，建立 filter 和 menu 的導覽樹。

10. 依分類與子分類查詢商品

幫你列出某個分類或其「子分類」下的所有商品（多層級）。

11. 品牌清單

用來做品牌 filter、品牌清單和 landing page。

12. 熱門標籤及商品數量

分析最常用的 tag，顯示熱門商品和建立 tag cloud。

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. 依 cohort 的用戶留存

評估忠誠度和回購的關鍵指標。

49. 首頁新聞/文章

用來在部落格顯示新聞和文章，提高用戶參與度。

50. 活躍網站頁面與相關內容區塊

檢查網站內容完整性、CMS 運作和頁面資料顯示。

加上 index

查詢語句很重要，但只有在它們跑得快才有用。所以你還需要在資料庫加點 index。你應該在專案的主要資料表加上 40 個 index，來提升查詢效能和方便維護。

1. product.product(status) 的 index

幾乎所有商品查詢都會用 status 過濾（例如展示櫃、搜尋等）。index 會加速特定狀態商品的查詢，減少全表掃描。

2. product.variant(product_id, is_active) 的 index

商品變體（SKU）和展示櫃查詢會用商品關聯和變體啟用狀態過濾。這個複合 index 可以最佳化查詢某商品所有啟用變體。

3. product.image(product_id, is_main DESC) 的 index

取得商品主圖（或全部圖片）時會用商品過濾並依「主圖」排序。index 加速這類查詢，讓圖片集資料回傳更快。

4. product.product(name text_pattern_ops) 的 index

用 ILIKE '%...%' 關鍵字搜尋商品名稱時，這個 name text_pattern_ops 的專用 index 會大幅提升搜尋效率，特別是資料量大時。

5. product.product(description gin_trgm_ops) 的 index

同上，搜尋商品描述（ILIKE 或全文搜尋）。GIN index 加 trigram 可以加速文字欄位過濾。

6. product.product(category_id) 的 index

常常會依分類或子分類查詢商品（見分類 filter 查詢）。index 讓你能快速找到指定分類的所有商品。

7. product.category(parent_id) 的 index

建立分類階層和導覽樹時，常用 parent_id 查詢。index 會加速這些遞迴階層查詢。

8. product.review(product_id) 的 index

所有商品評論查詢都會用 product_id 過濾（不論平均評分還是評論清單）。index 會讓聚合和查詢評論快很多。

9. product.review(product_id, created_at DESC) 的 index

快速取得商品最新評論（ORDER BY createdat DESC），尤其搭配 productid 過濾時，複合 index 很有用。

10. product.question(product_id, created_at DESC) 的 index

熱門查詢：某商品的問答，依建立時間排序。index 同時覆蓋兩個條件，加速商品卡片 Q&A 區塊。

11. product.answer(question_id, created_at) 的 index

查詢商品問答時，需要依 question_id 快速存取，常常還要依日期排序。這個 index 可以最小化 Q&A 產生延遲。

12. product.price_history(variant_id, changed_at DESC) 的 index

價格變動歷史會依商品變體和最近變動查詢。這個 index 加速價格動態分析和「舊價/新價」查詢。

13. product.status_history(product_id, changed_at DESC) 的 index

查詢商品狀態變更歷史並依時間排序，對稽核和控管商品生命週期很重要。複合 index 會大幅加速這類查詢。

14. product.certificate(product_id) 的 index

依商品 id 查詢證書 — B2B 和認證展示櫃常見操作。index 會加速這類檢查。

15. product.license(product_id) 的 index

查詢商品授權，特別是依授權類型過濾時。

16. product.product_tag(tag_id) 的 index

常見查詢 — 取得某 tag 下所有商品（或反查）。index 讓商品和 tag 交集查詢（tag cloud 或 filter）很快。

17. product.product_tag(product_id) 的 index

可以快速查出某商品有哪些 tag，加速 tag 查詢。

18. logistics.inventory(product_id, warehouse_id) 的 index

即時查詢商品在某倉庫的庫存（或全部倉庫）— 對物流、stock level 檢查和即時展示櫃很關鍵。

19. logistics.inventory(variant_id) 的 index

記錄某商品變體（顏色/尺寸）庫存和橫向報表。

20. logistics.stock_level(product_id, warehouse_id) 的 index

快速檢查商品在倉庫的最低門檻（自動下單或低庫存警示）。這個 index 用來和 inventory 比較。

21. logistics.inventory_movement(product_id, changed_at DESC) 的 index

快速取得商品移動歷史（稽核）— 防止錯誤、分析損失和控管供應。

22. logistics.transfer(product_id, requested_at DESC) 的 index

分析倉庫間物流調撥，依商品過濾和申請日期排序。

23. logistics.shipping_rate(shipping_method_id, destination_zone) 的 index

計算運費時，常依配送方式 id 和目的地區選擇費率。index 會加速結帳時的運費計算。

24. "order".order(user_id, placed_at DESC) 的 index

所有用戶訂單歷史查詢都會依 user_id 過濾並依下單日期排序。複合 index 讓個人中心訂單歷史回傳很快。

25. "order".order(status, placed_at) 的 index

訂單報表和分析，還有依狀態查詢（例如「處理中」/「已完成」）。

26. "order".order_item(order_id) 的 index

依訂單 id 查詢所有訂單項目 — 訂單明細最常見操作。

27. "order".order_item(product_id) 的 index

銷售分析和商品統計需要依商品 id 快速查詢訂單項目。

28. "order".return(order_id) 的 index

退貨和訂單關聯，用於客服和退貨分析。index 會加速依訂單號查詢退貨。

29. "order".cancellation(order_id) 的 index

同退貨 — 加速查詢訂單取消，方便分析和客服。

30. "order".cart(user_id, updated_at DESC) 的 index

查詢用戶最近的購物車（例如「遺棄」購物車），用 user_id 和最後更新時間排序的 index 很方便。

31. payment.payment_transaction(order_id) 的 index

大部分付款歷史查詢都會依訂單過濾。index 讓訂單交易查詢即時。

32. payment.refund(transaction_id) 的 index

可以有效查詢某交易的退款，方便客服、報表和防詐。

33. payment.wallet(user_id) 的 index

快速查詢用戶錢包，檢查餘額和交易歷史。

34. payment.wallet_transaction(wallet_id, created_at DESC) 的 index

查詢用戶錢包交易並依日期排序（例如顯示交易歷史）。

35. support.support_ticket(user_id, created_at DESC) 的 index

查詢某用戶的客服申請歷史（個人中心/客服）。複合 index 最佳化這類查詢。

36. support.ticket_message(ticket_id, sent_at) 的 index

查詢某 ticket 所有訊息時，依 ticket 和時間排序的 index 會加速訊息排序。

37. support.ticket_sla_tracking(ticket_id) 的 index

SLA 分析和控管每個 ticket，依 ticket_id 查詢 SLA 資料很快。

38. marketing.promo_usage(user_id, used_at DESC) 的 index

分析用戶促銷碼活動（分析和防濫用），需要依 user_id 和時間排序快速查詢。

39. analytics.product_view(user_id, viewed_at DESC) 的 index

儲存和分析用戶商品瀏覽歷史（個人化、推薦）需要依 user_id 和瀏覽時間排序快速查詢。

40. analytics.search_query_log(query_text) 的 index

熱門查詢和使用頻率 — 搜尋分析的關鍵工具。index 會加速依查詢文字聚合和統計。

備註

用 ILIKE 做文字搜尋時，建議用 pg_trgm 擴充的 GIN index，這對子字串和模糊搜尋很有效。大表做日期聚合或排序時，建議日期 DESC index — 這會加速最新資料查詢。

實際 index 設定要根據執行計畫和負載統計調整，但上面列的 index 已經涵蓋我們 marketplace 主要的生產查詢場景。

加上 function

還沒累吧？那我們再寫幾個 function 來簡化現有和未來查詢的撰寫。這樣可以加速關鍵查詢的實作，減少應用程式重複 code，並把 business logic 集中在資料庫端。

1. 支援 tag 和品牌的關鍵字商品搜尋

為什麼需要：

只用名稱和描述搜尋太有限了。常常還要依 tag 和品牌找商品。這個通用 function 集中進階搜尋邏輯，減少重複 code，也方便和前端整合。

2. 依 ID 取得完整商品卡片（所有卡片資料）

為什麼需要：

前端常常一次就要所有商品資訊：主要欄位、品牌、分類、圖片、tag、屬性、平均評分和評論數。function 一次組好完整商品卡片，減少 DB 查詢次數。

3. 取得分類階層（含巢狀）

為什麼需要：

展示櫃、filter 和 breadcrumbs 都要分類樹（或路徑）。不用在 client 端做遞迴查詢，function 一次回傳完整階層。

4. 計算分類的平均、最低/最高價格

為什麼需要：

catalog filter 和分析時，方便取得分類下商品的價格範圍和平均。function 省去重複子查詢。

5. 檢查並自動計算所有倉庫商品庫存

為什麼需要：

可以即時知道商品（和每個變體）的總庫存，對展示櫃、倉庫和物流都很有用。集中計算，避免 business logic 重複。

6. 取得用戶訂單歷史（含明細）

為什麼需要：

function 回傳用戶所有訂單，包含訂單項目、金額、狀態，讓前端一次就能組好個人中心。

7. 取得用戶作為賣家/買家的平均評分

為什麼需要：

顯示用戶在平台上的信任和聲譽很重要，要知道他作為賣家或買家的平均評分。function 做聚合計算。

8. 用戶使用促銷碼（含所有條件的驗證器）

為什麼需要：

所有促銷碼驗證和扣除的 business logic（啟用、限制、日期等）都集中在一個 function。這樣應用程式邏輯更簡單，也避免條件重複出錯。

9. 通用用戶事件 log function

為什麼需要：

做全程分析和稽核時，集中 log event 可以減少重複 code，也降低遺漏用戶行為資料的風險。

10. 取得紅利錢包餘額和累積獎勵總額的 function

為什麼需要：

一次就能取得用戶目前餘額和累積獎勵總額。這對 dashboard 顯示很方便，也減少 SQL 查詢數量。

11. 通用訂單狀態變更 function（含 log）

為什麼需要：

變更訂單狀態時，自動寫入狀態歷史 log，減少在不同應用程式區塊出錯的機會。

12. 取得客服對話所有訊息（ticket + 所有訊息）

為什麼需要：

function 回傳 ticket 的所有對話，包含申請細節和每則訊息。這讓前端組 ticket 歷史更容易。

13. 依 email 或電話檢查用戶是否存在

為什麼需要：

註冊和重設密碼時用，避免前後端重複邏輯。

備註

這組 function 覆蓋了主要 business 場景，提升資料處理便利性，優化邏輯，加速前端和整合開發。希望你會喜歡 :)

包含解決方案的檔案