MYSQL sum() 用于不同的行

2021-11-20 00:00:00 sum mysql

我正在寻求在 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 conversions.id 做 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 is the primary key of your table conversions
  • stats.id is the primary key of your table stats

因此，对于每个 Conversions.id，您最多会受到一个 links.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;

给我发消息！杰罗姆

相关文章