Comment calculer la somme précise des valeurs de conversion pour des conversions distinctes dans MySQL ?

Agrégation de lignes distinctives dans MySQL avec sum()

Les requêtes SQL impliquent souvent des fonctions d'agrégation telles que sum() pour calculer des résumés numériques. Lorsque vous traitez des données groupées, il est essentiel de compter chaque enregistrement unique une seule fois pour éviter des résultats gonflés.

Considérez la requête suivante :

<code class="sql">SELECT links.id,
       count(DISTINCT stats.id) as clicks,
       count(DISTINCT conversions.id) as conversions,
       sum(conversions.value) as conversion_value
FROM links
LEFT OUTER JOIN stats ON links.id = stats.parent_id
LEFT OUTER JOIN conversions ON links.id = conversions.link_id
GROUP BY links.id
ORDER BY links.created desc;</code>

L'objectif est de compter le nombre de clics distincts. , conversions et additionnez les valeurs de conversion correspondantes pour chaque lien. Cependant, l'agrégation sum(conversions.value) compte chaque valeur de conversion plusieurs fois en raison du regroupement.

Pour calculer avec précision la somme de chaque conversion unique, nous devons ajuster l'expression. En supposant que conversions.id est la clé primaire du tableau des conversions, nous pouvons exploiter la relation entre le nombre de conversions distinctes et le nombre total d'enregistrements.

Pour chaque conversions.id, il y a au plus un lien .id affecté. Par conséquent, la somme de toutes les valeurs de conversion pour une conversion distincte peut être calculée en multipliant la somme réelle (conversions.value) par le nombre de conversions distinctes et en la divisant par le nombre total d'enregistrements dans le groupe :

<code class="sql">sum(conversions.value) * count(DISTINCT conversions.id) / count(*)</code>

L'intégration de cet ajustement dans la requête entraîne :

<code class="sql">SELECT links.id,
       count(DISTINCT stats.id) as clicks,
       count(DISTINCT conversions.id) as conversions,
       sum(conversions.value)*count(DISTINCT conversions.id)/count(*) as conversion_value
FROM links
LEFT OUTER JOIN stats ON links.id = stats.parent_id
LEFT OUTER JOIN conversions ON links.id = conversions.link_id
GROUP BY links.id
ORDER BY links.created desc;</code>

Cette requête modifiée additionne efficacement les valeurs de conversion pour chaque conversion unique, fournissant ainsi des résultats globaux précis pour les données groupé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!