Сумма MYSQL() для разных строк

Я ищу помощь, используя sum() в моем 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, потому что я делаю "group by", и это гарантирует, что одна и та же строка не считается более одного раза.

Проблема заключается в том, что SUM (conversion.value) подсчитывает "значение" для каждой строки более одного раза (из-за группы by)

В основном я хочу сделать SUM(conversions.value) для каждого DISTINCT convertions.id.

Возможно ли это?

Ответ 1

Возможно, я ошибаюсь, но из того, что я понимаю

convertions.id является основным ключом вашей таблицы конверсий
stats.id - это первичный ключ вашей таблицы статистика

Таким образом, для каждого обращения.и вы имеете не более одного link.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])

этот отчет удаляет все [щелчки] строк в декартовом продукте

но ясно

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;

Держи меня в курсе! Джером

Ответ 2

Для объяснения причин появления неправильных номеров прочитать это.

Я думаю, что у Джерома есть ручка того, что вызывает вашу ошибку. Запрос Брайсона будет работать, хотя наличие этого подзапроса в SELECT может быть неэффективным.

Ответ 3

Решение Jeromes действительно неверно и может привести к неправильным результатам.

sum(conversions.value)*count(DISTINCT conversions.id)/count(*) as conversion_value

допустим следующую таблицу

conversions
id value
1 5
1 5
1 5
2 2
3 1

правильная сумма значений для различных идентификаторов будет равна 8. Формула Джерома производит:

sum(conversions.value) = 18
count(distinct conversions.id) = 3
count(*) = 5
18*3/5 = 9.6 != 8

Ответ 4

Используйте следующий запрос:

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

Ответ 5

Я использую подзапрос, чтобы сделать это. Это устраняет проблемы с группировкой. Таким образом, запрос будет выглядеть примерно так:

SELECT COUNT(DISTINCT conversions.id)
...
     (SELECT SUM(conversions.value) FROM ....) AS Vals

Ответ 6

Как насчет чего-то вроде этого:

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

Ответ 7

Это сделает трюк, просто разделите сумму на количество совпадений id, которые дублируются.

SELECT a.id,
       a.clicks,
       SUM(a.conversion_value/a.conversions) AS conversion_value,
       a.conversions
FROM (SELECT links.id, 
       COUNT(DISTINCT stats.id) AS clicks, 
       COUNT(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 conversions.id,links.id
      ORDER BY links.created DESC) AS a
GROUP BY a.id