Comment résoudre l'erreur « Les expressions SELECT DISTINCT ON doivent correspondre aux expressions ORDER BY initiales » de PostgreSQL ?

PostgreSQL DISTINCT ON Erreur : ORDER BY Clauses incompatibles

L'utilisation de DISTINCT ON de PostgreSQL avec une clause ORDER BY contenant différentes expressions peut conduire à une erreur : "Les expressions SELECT DISTINCT ON doivent correspondre aux expressions ORDER BY initiales." Cela se produit parce que DISTINCT ON sélectionne des lignes uniques en fonction des colonnes spécifiées, tandis que ORDER BY dicte l'ordre de présentation de ces lignes uniques. Les colonnes de DISTINCT ON doivent donc correspondre aux premières colonnes de la clause ORDER BY.

Par exemple, si vous devez récupérer des valeurs address_id distinctes tout en hiérarchisant les lignes avec le dernier horodatage purchased_at, le simple tri par address_id dans la clause ORDER BY n'est pas suffisant lorsque vous utilisez DISTINCT ON (address_id). En effet, DISTINCT ON sélectionnera la première ligne pour chaque address_id rencontré selon la clause ORDER BY.

Voici deux solutions de contournement efficaces :

Méthode 1 : Sous-requête pour le maximum purchased_at

Cette approche utilise une sous-requête pour trouver le maximum purchased_at pour chaque address_id, puis joint ce résultat à la table d'origine pour sélectionner les lignes correspondantes :

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;

Méthode 2 : Requête imbriquée avec DISTINCT ON et ORDER BY

Cette solution spécifique à PostgreSQL utilise une requête imbriquée. La requête interne utilise DISTINCT ON pour sélectionner les lignes uniques souhaitées, classées de manière appropriée, et la requête externe réorganise les résultats :

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;

Les deux méthodes fournissent des résultats efficaces et précis, surmontant les limitations imposées par la combinaison directe de clauses DISTINCT ON et ORDER BY incompatibles. Choisissez la méthode qui convient le mieux à votre style de codage et aux exigences de performances de la base de données.

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!