mysql - mysql中一张表的重复数据的平均值

Question

我有一个这样的表:

+----+---------------------+----------+
| ID | startDate           | uvIndex  | 
+----+---------------------+----------+
|  1 | 2014-10-29 06:37:21 | 120      | 
|  2 | 2014-10-29 08:57:00 | 135      | 
|  3 | 2014-10-28 05:37:21 | 120      |  
|  4 | 2014-10-28 09:30:21 | 160      |  
|  5 | 2014-10-28 10:28:21 | 150      |  
|  6 | 2014-10-26 16:27:01 | 150      |  
|  7 | 2014-10-26 17:57:21 | 110      | 
+----+----------+-----+---------------+ 

从那里我想要不同的值，但如果数据是重复的，那么应该得出该数据的平均值。像这样:

+----+-------------+---------------+
| ID | startDate   | AVG(uvIndex)  | 
+----+-------------+---------------+
|  1 | 2014-10-29  | 127.5         | 
|  2 | 2014-10-28  | 98.33         | 
|  3 | 2014-10-26  | 130           |  
+----+----------+-----+------------+

到目前为止我的尝试:

SELECT AVG(accumulatedLux), startTime FROM `blutooth_accumulated_data` WHERE `startTime` BETWEEN '2014-10-25' AND '2014-10-30' GROUP BY startTime

请帮帮我。

Answer 1

据我所知，您的查询应该生成您想要的数据。唯一的问题是 id，您可以使用变量获取它。这符合你的要求吗？

SELECT (@id := @id + 1) as id, startTime, AVG(accumulatedLux)
FROM `blutooth_accumulated_data` CROSS JOIN
      (SELECT @id := 0) vars
WHERE `startTime` BETWEEN '2014-10-25' AND '2014-10-30'
GROUP BY startTime
ORDER BY startTime;

一个可能的复杂情况是 StartTime 是否有一个时间组件。如果是这样，您想要这个:

SELECT (@id := @id + 1) as id, date(startTime) as startDate, AVG(accumulatedLux)
FROM `blutooth_accumulated_data` CROSS JOIN
      (SELECT @id := 0) vars
WHERE date(startTime) BETWEEN '2014-10-25' AND '2014-10-30'
GROUP BY date(startTime)
ORDER BY date(startTime);

还有一张小纸条。因此，您可以在 startTime 上使用索引，您应该将 where 子句编写为:

WHERE startTime >= date('2014-10-15') and startTime < date('2014-10-30') + interval 1 day