如何正確使用 PostgreSQL 的 DISTINCT ON 和不同的 ORDER BY 子句？

瞭解 PostgreSQL 的 DISTINCT ON 與 ORDER BY 互動

PostgreSQL 的 DISTINCT ON 子句旨在從指定表達式中具有相同值的每組行中選擇第一行。 關鍵點是「第一」行的選擇完全取決於 ORDER BY 子句。 它們必須對齊。

一個常見的錯誤是將 DISTINCT ON 子句與不包含 ORDER BY 表達式的 DISTINCT ON 子句一起使用。這會導致不可預測的結果，因為資料庫對「第一」行的選擇變得任意。

使用 DISTINCT ON 修正訂單問題

當 DISTINCT ON 中的欄位與 ORDER BY 中的前導欄位不符時，就會出現錯誤。要解決此問題，請確保 ORDER BY 子句以與 DISTINCT ON 相同的表達式開頭。這保證了每組中第一行的選擇是一致且可預測的。

「每組最大 N」問題的替代方法

如果目標是找到每個 address_id 的最新購買（按購買日期排序），這是一個經典的「每組最大 N」查詢。 這裡有兩個有效的解決方案：

通用 SQL 解決方案：

此方法使用子查詢來尋找每個 purchased_at 的最大值 address_id，然後將其連接回原始表以檢索完整的行。

<code class="language-sql">SELECT t1.* 
FROM purchases t1
JOIN (
    SELECT address_id, max(purchased_at) max_purchased_at
    FROM purchases
    WHERE product_id = 1
    GROUP BY address_id
) t2
ON t1.address_id = t2.address_id AND t1.purchased_at = t2.max_purchased_at
ORDER BY t1.purchased_at DESC</code>

PostgreSQL 特定最佳化：

PostgreSQL 使用巢狀 DISTINCT ON 查詢提供了更簡潔且可能更快的解決方案：

<code class="language-sql">SELECT * FROM (
  SELECT DISTINCT ON (address_id) *
  FROM purchases 
  WHERE product_id = 1
  ORDER BY address_id, purchased_at DESC
) t
ORDER BY purchased_at DESC</code>

在處理「每組最大 N」場景時，與僅依賴 DISTINCT ON 相比，這些替代方案提供了更乾淨、更有效率的解決方案。 它們避免了不必要的排序並提高了查詢效能。

以上是如何正確使用 PostgreSQL 的 DISTINCT ON 和不同的 ORDER BY 子句？的詳細內容。更多資訊請關注PHP中文網其他相關文章！