Mysql group by 15min 不匹配总和

Question

我有以下 2 个查询，但它们返回不同的结果。例如，如果我从一个终端请求总金额，我得到:456。

如果我每隔 15 分钟询问一次，计算所有向上的值，我得到 474...这怎么可能？

我获取总金额的查询如下

SELECT   SUM(logs.amount) * ei_relationships.itemprice as income
  ,      currency
FROM     ei_relationships
  RIGHT OUTER JOIN logs ON ei_relationships.id = logs.ei_relationship_id 
WHERE    ei_relationships.event_id = 1
     AND logs.serial = '5B:13:52:28:71:21:ED:F1'
ORDER BY currency, log_time ASC

这是我获取相同数据的查询，但现在将其划分为 15 分钟

SELECT   SUM(logs.amount) * ei_relationships.itemprice as income
  ,      UNIX_TIMESTAMP(log_time) - UNIX_TIMESTAMP(log_time)%(900) as hour
  ,      currency
FROM     ei_relationships
  RIGHT OUTER JOIN logs ON ei_relationships.id = logs.ei_relationship_id
WHERE    ei_relationships.event_id = 1
     AND logs.serial = '5B:13:52:28:71:21:ED:F1'
GROUP BY hour
ORDER BY currency, log_time ASC

Answer 1

作为the manual说:

If you use a group function in a statement containing no GROUP BY clause, it is equivalent to grouping on all rows.

它also说:

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.

因此，在这两个查询中，amount 列的总和乘以 itemprice 列中的值之一； MySQL 将使用哪些值是不确定的，因此，除非每条记录都具有相同的 itemprice，否则生成的 income 也是不确定的。

也许您打算GROUP BY currency 并选择SUM(logs.amount * ei_relationships.itemprice) AS income？