StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I may be wrong but from what I understand </p> <ul> <li><strong>conversions.id</strong> is the <strong>primary key</strong> of your table <strong>conversions</strong></li> <li><strong>stats.id</strong> is the <strong>primary key</strong> of your table <strong>stats</strong></li> </ul> <p>Thus for each conversions.id you have at most one links.id impacted.</p> <p>You request is a bit like doing the cartesian product of 2 sets :</p> <pre><code>[clicks] SELECT * FROM links LEFT OUTER JOIN stats ON links.id = stats.parent_id [conversions] SELECT * FROM links LEFT OUTER JOIN conversions ON links.id = conversions.link_id </code></pre> <p>and for each link, you get sizeof([clicks]) x sizeof([conversions]) lines</p> <p>As you noted the number of unique conversions in your request can be obtained via a </p> <pre><code>count(distinct conversions.id) = sizeof([conversions]) </code></pre> <p>this distinct manages to remove all the [clicks] lines in the cartesian product</p> <p>but clearly</p> <pre><code>sum(conversions.value) = sum([conversions].value) * sizeof([clicks]) </code></pre> <p>In your case, since</p> <pre><code>count(*) = sizeof([clicks]) x sizeof([conversions]) count(*) = sizeof([clicks]) x count(distinct conversions.id) </code></pre> <p>you have</p> <pre><code>sizeof([clicks]) = count(*)/count(distinct conversions.id) </code></pre> <p>so I would test your request with </p> <pre><code>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></pre> <p>Keep me posted ! Jerome</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload