Comment résoudre « Les expressions DISTINCT ON doivent correspondre aux expressions ORDER BY initiales » dans PostgreSQL ?

La clause DISTINCT ON de PostgreSQL simplifie la récupération de la première ligne pour chaque valeur unique dans un ensemble. Cependant, un piège courant survient lorsque l'expression DISTINCT ON ne s'aligne pas avec l'expression ORDER BY initiale.

DISTINCT ON Erreur de non-concordance d'expression

Cette erreur se produit fréquemment :

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

Résultant en :

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

La solution : aligner DISTINCT ON et ORDER BY

La documentation de PostgreSQL exige que la ou les expressions DISTINCT ON doivent refléter la ou les expressions ORDER BY les plus à gauche. La solution est simple : réorganisez votre clause 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>

Méthodes alternatives : contourner address_id Commande

Si vous devez éviter de commander avant address_id, envisagez ces alternatives :

Méthode 1 : L'approche « Le plus grand N par groupe »

Cette méthode trouve efficacement l'achat le plus récent pour chaque 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>

Méthode 2 : Requête imbriquée pour PostGreSQL

Cette approche utilise une sous-requête pour obtenir le résultat souhaité tout en conservant le purchased_at ordre :

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

Ces alternatives offrent une flexibilité lorsque vous ne souhaitez pas prioriser address_id dans l'ordre de sortie final. Ils résolvent efficacement le problème de « l’achat le plus récent par adresse » sans violer directement la contrainte DISTINCT ON.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!