如何解決 PostgreSQL 中的「DISTINCT ON 表達式必須符合初始 ORDER BY 表達式」？

PostgreSQL 的 DISTINCT ON 子句簡化了檢索集合中每個唯一值的第一行。然而，當 DISTINCT ON 表達式與初始 ORDER BY 表達式不一致時，就會出現一個常見的陷阱。

DISTINCT ON 表達式不符錯誤

常出現此錯誤：

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

結果：

<code>PG::Error: ERROR:  SELECT DISTINCT ON expressions must match initial ORDER BY expressions</code>

解：對齊DISTINCT ON和ORDER BY

PostgreSQL 文件要求 DISTINCT ON 表達式必須鏡像最左邊的 ORDER BY 表達式。 解決方案很簡單：重新排序您的 ORDER BY 子句：

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

替代方法：繞過address_id訂購

如果您需要避免在address_id之前訂購，請考慮以下替代方案：

方法 1：「每組最大 N」方法

此方法可以有效地找到每個address_id的最近購買：

<code class="language-sql">SELECT t1.* 
FROM purchases t1
JOIN (
    SELECT address_id, max(purchased_at) as 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>

方法2：PostGreSQL的巢狀查詢

此方法使用子查詢來實現所需的結果，同時保持 purchased_at 順序：

<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>

當您不想在最終輸出排序中優先考慮 address_id 時，這些替代方案可以提供靈活性。 他們有效地解決了「每個地址最近購買」的問題，而沒有直接違反 DISTINCT ON 約束。

以上是如何解決 PostgreSQL 中的「DISTINCT ON 表達式必須符合初始 ORDER BY 表達式」？的詳細內容。更多資訊請關注PHP中文網其他相關文章！