PostgreSQL の「DISTINCT ON 式は最初の ORDER BY 式と一致する必要がある」を解決する方法?

PostgreSQL の DISTINCT ON 句を使用すると、セット内の各一意の値の最初の行の取得が簡素化されます。ただし、DISTINCT ON 式が最初の ORDER BY 式と一致しない場合、一般的な落とし穴が発生します。

DISTINCT ON 式の不一致エラー

このエラーは頻繁に発生します:

SELECT DISTINCT ON (address_id) purchases.address_id, purchases.*
FROM purchases
WHERE purchases.product_id = 1
ORDER BY purchases.purchased_at DESC

結果:

<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 句を並べ替えます:

SELECT DISTINCT ON (address_id) purchases.address_id, purchases.*
FROM purchases
WHERE purchases.product_id = 1
ORDER BY address_id, purchases.purchased_at DESC

代替方法: address_id 注文をバイパス

address_id による注文を避ける必要がある場合は、次の代替案を検討してください。

方法 1: 「グループあたりの最大 N」アプローチ

このメソッドは、各 address_id の最新の購入を効率的に見つけます:

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

方法 2: PostGreSQL のネストされたクエリ

このアプローチでは、サブクエリを使用して、purchased_at の順序を維持しながら目的の結果を達成します。

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

これらの代替手段は、最終的な出力順序で address_id を優先したくない場合に柔軟性を提供します。 これらは、DISTINCT ON 制約に直接違反することなく、「アドレスごとの最新の購入」問題に効果的に対処します。

以上がPostgreSQL の「DISTINCT ON 式は最初の ORDER BY 式と一致する必要がある」を解決する方法?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。