MySQL で個別の変換の変換値の正確な合計を計算するにはどうすればよいですか?

sum() を使用した MySQL の独特の行集計

SQL クエリには、数値の集計を計算するための sum() のような集計関数が含まれることがよくあります。グループ化されたデータを扱う場合、結果が増大することを避けるために、各一意のレコードを 1 回だけカウントすることが重要です。

次のクエリを考えてみましょう:

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

目的は、個別のクリック数をカウントすることです。 、コンバージョンを計算し、各リンクの対応するコンバージョン値を合計します。ただし、sum(conversions.value) 集計では、グループ化により各コンバージョン値が複数回カウントされます。

一意のコンバージョンごとに合計を正確に計算するには、式を調整する必要があります。 conversions.id がコンバージョン テーブルの主キーであると仮定すると、個別のコンバージョンの数とレコードの合計数の関係を活用できます。

各conversions.idには、最大で1つのリンクがあります。 .id が影響を受けます。したがって、個別のコンバージョンのすべてのコンバージョン値の合計は、実際の sum(conversions.value) に個別のコンバージョンの数を乗算し、それをグループ内のレコードの総数で割ることによって計算できます。

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

この調整をクエリに組み込むと、次の結果が得られます。

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

この変更されたクエリは、一意のコンバージョンごとにコンバージョン値を効果的に合計し、グループ化されたデータの正確な集計結果を提供します。

以上がMySQL で個別の変換の変換値の正確な合計を計算するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。