MYSQL sum()用于不同的行 [英] MYSQL sum() for distinct rows

问题描述

我正在SQL查询中使用sum()寻求帮助:

I'm looking for help using sum() in my SQL query:

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是因为我正在进行分组依据"，这可以确保同一行不会被重复计数.

I use DISTINCT because I'm doing "group by" and this ensures the same row is not counted more than once.

问题是SUM(conversions.value)对每行的值"计数超过一次(由于分组依据)

The problem is that SUM(conversions.value) counts the "value" for each row more than once (due to the group by)

我基本上想对每个DISTINCT转换执行SUM(conversions.value).

I basically want to do SUM(conversions.value) for each DISTINCT conversions.id.

有可能吗?

推荐答案

我可能是错的，但据我了解

I may be wrong but from what I understand

• conversions.id 是表 conversions
中的主键
• stats.id 是表 stats
中的主键

因此，对于每个conversions.id，您最多影响一个link.id.

Thus for each conversions.id you have at most one links.id impacted.

您的要求有点像做2组的笛卡尔积:

You request is a bit like doing the cartesian product of 2 sets :

[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([clicks])x sizeof([conversions])行

and for each link, you get sizeof([clicks]) x sizeof([conversions]) lines

正如您所指出的，可以通过

As you noted the number of unique conversions in your request can be obtained via a

count(distinct conversions.id) = sizeof([conversions])

这种独特的方法可以删除笛卡尔积中的所有[clicks]行

this distinct manages to remove all the [clicks] lines in the cartesian product

但很明显

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;

让我发布！ 杰罗姆(Jerome)

Keep me posted ! Jerome

这篇关于MYSQL sum()用于不同的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！