Я ищу справку с помощью суммы () в моем 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;
Я использую DISTINCT
потому что я делаю "группу", и это гарантирует, что та же строка не считается несколько раз.
Проблема состоит в том, что СУММА (conversions.value) считает "значение" для каждой строки несколько раз (из-за группы)
Я в основном хочу сделать SUM(conversions.value)
для каждого ОТЛИЧНОГО conversions.id.
Это возможно?
Я могу ошибаться, но насколько я понимаю
Таким образом, для каждого conversions.id у вас есть не более одного затронутого links.id.
Вы запрашиваете это немного похоже на декартово произведение двух наборов:
[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
и для каждой ссылки вы получаете sizeof ([клики]) x sizeof ([конверсии]) строк
Как вы отметили количество уникальные преобразования в вашем запросе могут быть получены с помощью
count(distinct conversions.id) = sizeof([conversions])
, этому отдельному удается удалить все строки [clicks] в декартовом произведении
, но явно
sum(conversions.value) = sum([conversions].value) * sizeof([clicks])
В вашем случае, поскольку
count(*) = sizeof([clicks]) x sizeof([conversions])
count(*) = sizeof([clicks]) x count(distinct conversions.id)
у вас есть
sizeof([clicks]) = count(*)/count(distinct conversions.id)
, поэтому Я бы проверил ваш запрос с помощью
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;
. Держите меня в курсе! Джером
Для этого я использую подзапрос. Это устраняет проблемы с группировкой. Таким образом, запрос будет выглядеть примерно так:
SELECT COUNT(DISTINCT conversions.id)
...
(SELECT SUM(conversions.value) FROM ....) AS Vals
Используйте следующий запрос:
SELECT links.id
, (
SELECT COUNT(*)
FROM stats
WHERE links.id = stats.parent_id
) AS clicks
, conversions.conversions
, conversions.conversion_value
FROM links
LEFT JOIN (
SELECT link_id
, COUNT(id) AS conversions
, SUM(conversions.value) AS conversion_value
FROM conversions
GROUP BY link_id
) AS conversions ON links.id = conversions.link_id
ORDER BY links.created DESC
Как насчет чего-то вроде этого:
select l.id, count(s.id) clicks, count(c.id) clicks, sum(c.value) conversion_value
from (SELECT l.id id, l.created created,
s.id clicks,
c.id conversions,
max(c.value) conversion_value
FROM links l LEFT
JOIN stats s ON l.id = s.parent_id LEFT
JOIN conversions c ON l.id = c.link_id
GROUP BY l.id, l.created, s.id, c.id) t
order by t.created
Чтобы объяснить, почему вы видите неправильные числа, прочтите это .
Я думаю, что Джером знает причину вашей ошибки. Запрос Брайсона будет работать, хотя наличие этого подзапроса в SELECT может быть неэффективным.