☰

PostgreSQL DISTINCT ON と異なる ORDER BY 質問する

tag-icon • tag-icon sql postgresql sql-order-by distinct-on
PostgreSQL DISTINCT ON と異なる ORDER BY 質問する

次のクエリを実行します:

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

しかし、次のエラーが発生します:

PG::Error: エラー: SELECT DISTINCT ON 式は最初の ORDER BY 式と一致する必要があります

address_id最初の式としてを追加するORDER BYとエラーは消えますが、 でのソートは追加したくありませんaddress_id。 で並べ替えずに を行うことは可能ですかaddress_id?

ベストアンサー1

ドキュメントには次のように書かれています:

DISTINCT ON (式[, ...]) は、指定された式が等しいと評価される各行セットの最初の行のみを保持します。[...] 目的の行が最初に表示されるように ORDER BY を使用しない限り、各セットの「最初の行」は予測できないことに注意してください。[...] DISTINCT ON 式は、左端の ORDER BY 式と一致する必要があります。

公式ドキュメント

address_idしたがって、 order by に を追加する必要があります。

あるいは、各製品について最近購入した製品を含む行全体を探していてaddress_id、その結果が並べ替えられている場合はpurchased_at、グループあたりの最大 N 問題を解決しようとしていることになります。これは、次の方法で解決できます。

ほとんどの DBMS で機能する一般的なソリューション:

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

@hkf の回答に基づいた、より PostgreSQL 指向のソリューション:

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

ここで問題が明確化、拡張、解決されました:ある列で順序付けされ、別の列では異なる行を選択する

おすすめ記事