MYSQL对不同行的sum()函数

Question

我正在寻求使用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"操作，这可以确保同一行不会被计算多次。

问题在于，由于"group by"的缘故，SUM(conversions.value)会将每一行的"value"计算多次。

我想要的基本上是对每个DISTINCT conversions.id 进行SUM(conversions.value)运算。是否可能？

- makeee

---

发布完整的查询将会很有帮助。如何在 GROUP BY 中复制值？ - Matthew

你在做连接查询吗？你应该发布你的查询。根据查询，你可以使用一些选项。 - Michael D. Irizarry

我已经更新了我的问题，并附上了完整的查询。 - makeee

显然，你可以只需执行 SUM(DISTINCT column)。 - phoenix

Answer 1

我可能错了，但据我所知，

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

因此，对于每个 conversions.id，您最多只有一个受到影响的 links.id。

您的请求有点像对2个集合进行笛卡尔积操作：

[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]) 行数据。

正如你所提到的，在你的请求中获取唯一转化数的数量可以通过

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

这个 distinct 命令成功地删除了笛卡尔积中所有 [clicks] 行，但是...

请注意：这里的 "distinct" 可能是指 SQL 语言中的 DISTINCT 关键字，用于从结果集中去除重复行。

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

- Jerome WAGNER

---

太好了，这个解决方案非常完美，而且在你不想处理依赖子查询的情况下非常通用，对于大数据集来说不可接受。 - Luke Adamczewski

Jerome的解决方案实际上是错误的，可能会产生不正确的结果！请参见我的下面的答案。 - Clemens Valiente

@ClemensValiente，Jerome的解决方案是正确的，前提是conversions表中的conversions.id是唯一的列。这可能是一个重要的区别，并应在答案中注明。编辑--实际上，已经说明了(conversions.id是您的表格conversions的主键)。 - Jonathan

长话短说：将原始表格总和除以连接表格的不同行数，忘记了并且差点要去谷歌两次。 - BIOHAZARD

这并不是一个充分的答案，因为它是通过除以连接中的总行数来计算的，如果父表在连接表中有多个关联，这将会导致不想要的结果。 - kjdion84

Answer 2

Jerome的解决方案实际上是错误的，可能会产生不正确的结果！！

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

假设以下表格：

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

不同 ID 值的正确总和应为 8。Jerome 公式得出的结果为：

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

- Clemens Valiente

---

假设conversions.id是一个唯一的字段，那么在conversions.id = 1的情况下JOIN操作不可能产生3行结果，在conversions.id = 2的情况下只能产生1行结果。假定conversions.id是唯一的这一前提是隐含的，可能需要明确表达出来，但除此以外，这个公式是可靠的。 - Jonathan

是 9.6 还是 10.8。顺便问一下，除了多级子查询或连接（子查询）之外，还有其他解决方案吗？ - James

@Jonathan，如果是根据OP的确切查询条件，你是正确的。如果这种情况是在一个更大的查询中，有其他连接导致相同的conversions.id有多行，那么Clemens是正确的。不幸的是，这正是我现在正在面对的情况。 - Rikaelus

@Rikaelus 说得好。我想我没有考虑到的另一个隐含假设是使用的确切JOINs。在OP查询中，GROUP BY基于主键links.id，有一个连接的表/字段conversions.value，并且没有其他连接从转换表中脱离。这个最后一条非常重要。如果你所处的情况涉及更多直接从links表中连接的连接，你可能能够做类似的技巧来反向计算总和。否则，你可能需要退后一步，进行依赖子查询或者只是多个SQL查询。祝你好运！ - Jonathan

Answer 3

如果您发现数字不正确，请参阅此文进行解释。

我认为Jerome已经掌握了导致错误的原因。Bryson的查询可以正常工作，但是在SELECT中使用子查询可能会效率低下。

- TehShrike

---

谢谢您的回答！我建议每个来到这个页面的人都阅读链接的文章。它是一个简洁、傲慢和幽默的连接和分组解释，并提供了一个“适当”的解决方案。 - Blaise

Answer 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

- Bryson

Answer 5

我使用子查询来完成这个任务。它可以消除分组时的问题。 因此，查询应该像这样：

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

- Dave

---

我更新了我的问题并提供了完整的查询。我不确定如何将子查询集成到现有查询中，以及它将如何影响性能。 - makeee

子查询通常会对性能产生负面影响。为了最小化影响，请确保任何子查询都在索引上执行。 - Dave

Answer 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  

- Quesi

Answer 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

- Dipu Raj

Answer 8

Select sum(x.value) as conversion_value,count(x.clicks),count(x.conversions)
FROM
(SELECT links.id, 
       count(DISTINCT stats.id) as clicks, 
       count(DISTINCT conversions.id) as conversions,
       conversions.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) x
GROUP BY x.id 
ORDER BY x.created desc;

我相信这将为你提供你正在寻找的答案。

- Nikhil Kumar Singh