題幹與適用情境
使用 PostgreSQL 查詢 2026 年第二季每個品類營收最高的前三個級距；並列第三名全部回傳。請說明資料口徑、寫出 SQL，並解釋 ROWNUMBER、RANK 與 DENSERANK 為什麼會得到不同結果。
資料表結構如下：
orders( order_id bigint primary key, ordered_at timestamptz, status text )
order_items( order_id bigint, category_id bigint, product_id bigint, quantity integer, unit_price numeric(12, 2) )
本題採用以下已確認口徑：只統計 COMPLETED 訂單；季度使用 UTC 的半開區間 [2026-04-01, 2026-07-01)；商品營收是所有有效明細的 quantity * unit_price 總和；退款不在給定模型中；「前三」代表每個品類前三個不同營收級距，因此第三個級距有並列商品時全部回傳。沒有有效銷售的商品不出現，少於三個級距的品類回傳現有全部級距。
這是一道適合資料分析、資料工程，以及需要撰寫分析 SQL 的後端職缺之中等難度題。2026 年公開 SQL 面試資料仍把每組 Top-N、視窗函式和並列處理列為獨立練習。題目沒有可核實的公司歸屬，因此以代表性 SQL 面試題處理。
面試官考察點
第一項是能否先說清楚輸出粒度。原始 order_items 一列是一筆訂單明細，目標卻是一列一個「品類—商品」。如果直接在明細粒度排名，同一商品會占據多個名次，結果即使語法正確也沒有回答商業問題。
第二項是能否把「前三」翻譯成明確的並列語意。ROWNUMBER、RANK 和 DENSERANK 都能在品類內排序，但它們回答三個不同問題。好的回答會先確認要固定三列、競賽式名次，或前三個不同營收級距，再選函式。
第三項是理解 SQL 的邏輯執行順序。視窗函式在 WHERE、GROUP BY、HAVING 和一般彙總之後計算，因此要先彙總商品營收，再在下一層排名，並於外層篩選排名。把視窗別名直接寫進同層 WHERE 在 PostgreSQL 中不成立。
最後要看結果是否可驗證、可維護。好的回答會定義時間邊界、訂單狀態、金額型別、最終穩定排序與測試資料，並說明資料規模變大時先減少參與排名的資料列，而不是只說「加索引」。
回答前需要釐清的問題
- 「前三」是三列、三個競賽名次，還是三個不同金額級距？ 三列用
ROWNUMBER；競賽式名次可用RANK；本題要求三個不同營收級距並保留並列，因此用DENSERANK。 - 營收依哪個事件與時區歸屬？ 下單、付款、完成和退款時間會產生不同報表。這裡使用
orders.ordered_at的 UTC 時間，並採不重疊的半開區間。 - 哪些訂單狀態有效？ 若未排除取消、測試或失敗訂單，彙總值會被高估。本題只統計
COMPLETED。 - 退款、折扣、稅金和幣別在哪裡？ 目前模型只有數量與成交單價，無法推導退款或匯率。若面試官補充退款表或多幣別欄位，必須先計算淨額或換算為統一幣別再排名。
- 一項商品能否跨品類？ 本題以明細上的
(categoryid, productid)為統計鍵；如果品類來自目前商品維度表，歷史訂單可能被今天的分類覆寫，需改用訂單當下快照或有效期間維度表。 - 零銷售商品是否要顯示？ 本題不顯示。若要顯示，需要從品類—商品全集左連接彙總結果，並明確定義零值是否參與前三個級距。
- 最終輸出是否要求固定順序？ 排名只由營收決定，不能把
productid放進排名視窗而破壞並列；顯示層另用ORDER BY categoryid, revenuerank, productid保證穩定輸出。
30 秒回答框架
「我先把 UTC 第二季的完成訂單彙總成一列一個品類—商品，再按品類分割、營收降冪使用 DENSERANK，因為題目要三個不同金額級距並保留並列。排名在 CTE 中計算，外層篩 revenuerank <= 3，最後排序只負責穩定顯示。我會用並列金額、季度邊界、取消訂單和不足三個級距的品類驗證。」
完整回答還應解釋為什麼先彙總、三種排名函式的差異，以及大量資料下如何驗證執行計畫與報表口徑。
分步深入解答
第一步：把商業問題寫成輸出粒度與不變量
目標結果的主鍵不是 orderid，而是 (categoryid, product_id)。每個鍵在彙總層只能出現一列，revenue 等於時間與狀態篩選後所有對應明細金額的總和。排名層不會改變這個粒度，只會替每列增加品類內級距。
可以先寫出三個不變量：每筆有效明細只貢獻一次；不同訂單中的同一商品必須合併；任何品類的 revenue_rank 只由該品類內的營收集合決定。它們能快速發現重複連接、明細級排名和全域排名三類錯誤。
第二步：先篩選事實，再彙總到商品粒度
時間條件使用 >= 起點和 < 下一季起點。相較於 BETWEEN 或寫成 2026-06-30 23:59:59，半開區間不會遺漏更高精度時間，也能與下一季無縫銜接。timestamptz 常數明確寫出 +00，讓口徑不依賴資料庫連線的時區設定。
連接後先篩選訂單狀態與時間，再按 categoryid, productid 分組。quantity * unit_price 在目前 PostgreSQL 模型中得到精確數值型別，SUM 保留精確金額計算；不要為了方便轉成浮點數。正式報表還需要幣別與退款口徑，但給定欄位無法憑空補出這些事實。
第三步：依並列規則選擇排名函式
假設某品類四項商品的營收依序為 100, 100, 90, 80：
ROW_NUMBER給出1, 2, 3, 4，兩個 100 的相對順序若沒有額外鍵就不確定；目前例子會回傳兩個 100 和一個 90，但若並列橫跨第 3 列，它會任意保留其中一項。它保證三列，不保證並列完整。RANK給出1, 1, 3, 4；篩到 3 會回傳金額 100 和 90，只涵蓋兩個不同金額級距。DENSE_RANK給出1, 1, 2, 3；篩到 3 會回傳四項商品，正好涵蓋前三個不同金額級距。
因此本題使用 DENSERANK。視窗中的 ORDER BY 只能是 revenue DESC。若加入 productid，相同營收的資料列不再屬於同一 peer group，所謂「保留並列」就被悄悄改成拆分名次。
第四步：用兩層 CTE 表達彙總、排名和篩選
完整 PostgreSQL 查詢如下：
WITH product_revenue AS ( SELECT oi.category_id, oi.product_id, SUM(oi.quantity * oi.unit_price) AS revenue FROM orders AS o JOIN order_items AS oi ON oi.orderid = o.orderid WHERE o.status = 'COMPLETED' AND o.ordered_at >= TIMESTAMPTZ '2026-04-01 00:00:00+00' AND o.ordered_at < TIMESTAMPTZ '2026-07-01 00:00:00+00' GROUP BY oi.categoryid, oi.productid ), ranked AS ( SELECT category_id, product_id, revenue, DENSE_RANK() OVER ( PARTITION BY category_id ORDER BY revenue DESC ) AS revenue_rank FROM product_revenue ) SELECT categoryid, productid, revenue, revenue_rank FROM ranked WHERE revenue_rank <= 3 ORDER BY categoryid, revenuerank, product_id;
第一層把許多訂單明細收斂成商品級事實；第二層只在各品類的商品集合中排序；最外層才能看見視窗結果並篩選。三層分別對應商業口徑、排名規則和輸出契約，除錯時可逐層檢查列數與粒度。
第五步：解釋正確性，不只展示語法
對任意品類，第一層為每項商品產生唯一總額。PARTITION BY categoryid 保證其他品類不會參與比較，ORDER BY revenue DESC 把相同總額定義為同一 peer group。DENSERANK 依序替 peer group 編號且不留空號，因此 <= 3 恰好選出前三個不同總額及其全部商品。
最後的 ORDER BY 不參與排名，只決定回傳列的顯示順序。這個區分很重要：排名視窗的排序定義商業名次，查詢結尾的排序定義可重複呈現。兩者可以使用不同的鍵。
第六步：為規模與執行計畫保留證據邊界
若輸入有 R 筆有效明細、彙總後有 G 個品類—商品組合，邏輯工作包括掃描與彙總 R 列，以及在各品類內排序 G 列。排序量可寫成各分割區 Gc log Gc 的總和，但資料庫可能選擇雜湊彙總、排序彙總、平行執行或磁碟溢寫，不能把這個表示式說成固定執行計畫。
先用 EXPLAIN (ANALYZE, BUFFERS) 在正式規模副本驗證篩選列數、連接方式、彙總列數、排序記憶體與暫存檔。orders(status, orderedat, orderid) 和 orderitems(orderid) 可能有助於篩選與連接，是否有效取決於狀態選擇性、資料分布和分割方式。若季度明細非常大，按時間分割可減少掃描；若查詢頻繁，再考慮經過對帳的日級商品彙總表。預先彙總以新鮮度、回補和修正成本換取查詢速度，不能只寫「建立實體化檢視」。
第七步：用小資料證明語意，再用大資料證明代價
最小正確性測試資料至少包含：品類 10 的金額 100, 100, 90, 80, 70，預期回傳前四項商品且排名為 1, 1, 2, 3；品類 20 只有 50, 40 兩個級距，預期全部回傳；一筆取消訂單，預期不計入；一筆恰好位於 2026-07-01 00:00:00+00 的訂單，預期排除。
再加入同一商品跨多筆訂單與多條明細，確認只輸出一個商品總額；加入兩個不同商品 ID 的相同總額，確認最終順序穩定但名次相同。規模測試則核對掃描分割區、彙總後列數、排序是否溢寫、執行時間與資源峰值。正確結果與可接受代價是兩套證據，不能互相取代。
高品質示範回答
「我會先確認『前三』的並列規則、時間口徑和有效訂單狀態。這裡要求每個品類前三個不同營收級距，第三個級距並列全部回傳，所以我選 DENSERANK；若只要固定三列才會用 ROWNUMBER。
目標粒度是一列一個品類—商品，而原表是一列訂單明細。因此第一層 CTE 連接訂單與明細，只保留 UTC 第二季的 COMPLETED 訂單，再按 categoryid, productid 彙總 SUM(quantity * unit_price)。時間使用大於等於 4 月 1 日、小於 7 月 1 日，避免季度邊界重疊。
第二層在彙總結果上執行 DENSERANK() OVER (PARTITION BY categoryid ORDER BY revenue DESC)。排名視窗不能加入 productid，否則相同金額不再並列。PostgreSQL 也不能在同層 WHERE 使用視窗結果，所以最外層篩 revenuerank <= 3，最後再按品類、排名和商品 ID 排序，穩定顯示。
正確性方面，金額 100, 100, 90, 80 會得到 1, 1, 2, 3，四項商品都回傳。測試還要涵蓋取消訂單、季度右邊界、同一商品多條明細和不足三個級距的品類。資料量大時，我會先看篩選與彙總把 R 列縮到多少個商品組合，再用執行計畫判斷分割區裁剪、索引、排序溢寫或預先彙總是否真的需要。」
這段回答先確定商業語意，再給查詢結構、正確性證據和效能驗證。它沒有把函式名稱當成答案，也沒有用未經測量的索引建議取代執行計畫。
常見錯誤
- 直接在訂單明細上排名 → 同一商品占據多個名次，輸出粒度錯誤 → 先按品類與商品彙總，再排名彙總結果。
- 使用全域
ORDER BY ... LIMIT 3→ 只回傳全表前三，不是每個品類前三 → 用PARTITION BY category_id建立獨立排名。 - 未確認並列規則就選
ROW_NUMBER→ 並列商品可能被任意截斷 → 先定義三列、競賽名次或三個金額級距，再選函式。 - 把
productid加進DENSERANK的視窗排序 → 相同營收被拆成不同 peer group → 視窗只按商業排名鍵排序，顯示穩定性放在最後的ORDER BY。 - 同層
WHERE revenue_rank <= 3→ 視窗函式尚未計算，PostgreSQL 無法參照 → 在 CTE 或子查詢中排名，外層篩選。 - 季度結束寫成 6 月 30 日 23:59:59 → 更高精度時間可能遺漏，下一週期也難銜接 → 使用
[start, next_start)半開區間。 - 連接商品目前維度表取得品類卻未說明歷史語意 → 商品改類會覆寫歷史歸屬 → 使用訂單當下快照或有效期間維度表，並聲明口徑。
- 只建議加索引 → 索引可能因低選擇性而無效，也不會解決錯誤粒度 → 先驗證列數、計畫、排序溢寫和實際瓶頸。
- 用浮點數累計金額 → 捨入誤差可能改變並列關係 → 保留精確數值型別並定義幣別與淨額規則。
追問及應對
追問一：如果業務只允許每個品類回傳恰好三列呢？
改用 ROWNUMBER，並為完全相同營收定義可解釋且確定的次要順序，例如 productid ASC 或業務明確指定的銷量、首次上架時間。這個規則會主動打破並列，因此必須寫進輸出契約。品類不足三項商品時仍只能回傳現有列，除非業務要求補占位資料。
追問二：如果「前三名」採用競賽排名呢？
使用 RANK。金額 100, 100, 90, 80 會得到 1, 1, 3, 4，篩 <= 3 回傳 100 和 90 兩個級距。它保留並列後跳號，適合「下一名是第三名」的語意；與 DENSE_RANK 的前三個不同金額級距不是同一需求。
追問三：能否不用 CTE，直接在 HAVING 中篩排名？
不能在同一查詢層的 HAVING 或 WHERE 中篩視窗結果，因為視窗函式邏輯上晚於這些子句。可以改成等價的子查詢；某些資料倉儲支援 QUALIFY，但 PostgreSQL 18 沒有此子句。本題使用 CTE 是為了讓彙總、排名和篩選邊界清楚可見。
追問四：退款發生在下一季怎麼辦？
先定義財務口徑。訂單歸屬報表可能把退款回寫原訂單季度；現金流報表可能按退款發生季度記錄負數。兩者需要不同的事件時間與事實表。不能只在目前查詢中扣除一張沒有時間與金額欄位的退款表；應建立可追溯的訂單、退款事實，並聲明重算或調整策略。
追問五：十億筆季度明細如何最佳化？
先用日期分割區裁剪和狀態篩選減少掃描，再確認連接鍵索引、彙總平行度和排序是否溢寫。若報表頻繁且允許延遲，可維護按日、品類、商品的精確彙總，查詢季度時再加總排名；遲到訂單、退款與修正必須能冪等回補並與原始事實對帳。只有執行計畫和代表性負載能決定索引、分割或預先彙總哪一項有效。
追問六：如何發現連接造成營收加倍？
在每一層檢查粒度與守恆關係：有效明細連接前後列數是否符合基數，order_id 在訂單表是否唯一，商品彙總金額總和是否等於篩選後明細金額總和。再建立一筆訂單含兩條明細、維度表含重複鍵的反例。若連接到非唯一維度，先按有效期間與版本選出唯一記錄，不能用最後的 DISTINCT 掩蓋重複貢獻。