Comment puis-je utiliser correctement DISTINCT ON de PostgreSQL avec différentes clauses ORDER BY ?

Comprendre les interactions DISTINCT ON et ORDER BY de PostgreSQL

La clause DISTINCT ON de PostgreSQL est conçue pour sélectionner la première ligne de chaque groupe de lignes qui ont les mêmes valeurs dans la ou les expressions spécifiées. Le point crucial est que la sélection de la « première » ligne dépend entièrement de la clause ORDER BY. Ils doivent s'aligner.

Une erreur courante consiste à utiliser une clause DISTINCT ON avec une clause ORDER BY qui n'inclut pas la ou les expressions DISTINCT ON. Cela conduit à des résultats imprévisibles car le choix de la « première » ligne par la base de données devient arbitraire.

Correction des problèmes de commande avec DISTINCT ON

L'erreur se produit lorsque les champs de DISTINCT ON ne correspondent pas aux champs de début de ORDER BY. Pour résoudre ce problème, assurez-vous que la clause ORDER BY commence par les mêmes expressions que DISTINCT ON. Cela garantit une sélection cohérente et prévisible de la première ligne au sein de chaque groupe.

Approches alternatives pour les problèmes du « plus grand N par groupe »

Si l'objectif est de trouver le dernier achat pour chaque address_id, classé par date d'achat, il s'agit d'une requête classique du "plus grand N par groupe". Voici deux solutions efficaces :

Solution SQL générale :

Cette approche utilise une sous-requête pour trouver le maximum purchased_at pour chaque address_id, puis la joint à la table d'origine pour récupérer la ligne complète.

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

Optimisation spécifique à PostgreSQL :

PostgreSQL offre une solution plus concise et potentiellement plus rapide en utilisant une DISTINCT ON requête imbriquée :

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

Ces alternatives offrent des solutions plus propres et plus efficaces que de s'appuyer uniquement sur DISTINCT ON lorsqu'il s'agit de scénarios du « plus grand N par groupe ». Ils évitent les tris inutiles et améliorent les performances des requêtes.

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!