Bagaimanakah saya Boleh Menggunakan DISTINCT ON PostgreSQL dengan Betul dengan Klausa ORDER BY yang Berbeza?

Memahami PERBEZAAN PostgreSQL dan ORDER BY Interaksi

Klausa

PostgreSQL DISTINCT ON direka untuk memilih baris pertama daripada setiap kumpulan baris yang mempunyai nilai yang sama dalam ungkapan yang ditentukan. Perkara yang penting ialah pemilihan baris "pertama" bergantung sepenuhnya pada klausa ORDER BY. Mereka mesti selaras.

Kesilapan biasa ialah menggunakan klausa DISTINCT ON dengan klausa ORDER BY yang tidak termasuk ungkapan DISTINCT ON. Ini membawa kepada hasil yang tidak dapat diramalkan kerana pilihan pangkalan data bagi baris "pertama" menjadi sewenang-wenangnya.

Membetulkan Isu Pesanan dengan DISTINCT ON

Ralat timbul apabila medan dalam DISTINCT ON tidak sepadan dengan medan utama dalam ORDER BY. Untuk membetulkannya, pastikan klausa ORDER BY bermula dengan ungkapan yang sama seperti DISTINCT ON. Ini menjamin pemilihan baris pertama yang konsisten dan boleh diramal dalam setiap kumpulan.

Pendekatan Alternatif untuk Masalah "Greatest N Per Group"

Jika objektifnya adalah untuk mencari pembelian terkini bagi setiap address_id, yang dipesan mengikut tarikh pembelian, ini ialah pertanyaan klasik "N terbaik setiap kumpulan". Berikut ialah dua penyelesaian yang cekap:

Penyelesaian SQL Am:

Pendekatan ini menggunakan subkueri untuk mencari purchased_at maksimum bagi setiap address_id dan kemudian menggabungkannya kembali ke jadual asal untuk mendapatkan semula baris lengkap.

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

Pengoptimuman Khusus PostgreSQL:

PostgreSQL menawarkan penyelesaian yang lebih ringkas dan berpotensi lebih pantas menggunakan pertanyaan DISTINCT ON bersarang:

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

Alternatif ini menyediakan penyelesaian yang lebih bersih dan lebih cekap berbanding dengan bergantung semata-mata pada DISTINCT ON apabila berhadapan dengan senario "N terbaik setiap kumpulan". Mereka mengelakkan pengisihan yang tidak perlu dan meningkatkan prestasi pertanyaan.

Atas ialah kandungan terperinci Bagaimanakah saya Boleh Menggunakan DISTINCT ON PostgreSQL dengan Betul dengan Klausa ORDER BY yang Berbeza?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!