请教大神,对MYSQL数据表TMPtable,想针对name 列,计算每5天的平均值,代码如下,但计算后一直为NULL,代码哪里错了?SELECT b.*,
(
SELECT avg(t1.`close`) from (
SELECT t.*,(@rownum := @rownum + 1) AS rownum FROM (SELECT @rownum := 0) r,`TMPtable` t ORDER BY `name`,`index` asc) t1
WHERE t1.rownum <= b.rownum + 5 and t1.rownum > b.rownum
)avg5
from
( select b.*,(@rownum := @rownum + 1) AS rownum FROM (SELECT @rownum := 0) r,`TMPtable` b ORDER BY `name`,`index` asc) b 计算前:计算后:
(
SELECT avg(t1.`close`) from (
SELECT t.*,(@rownum := @rownum + 1) AS rownum FROM (SELECT @rownum := 0) r,`TMPtable` t ORDER BY `name`,`index` asc) t1
WHERE t1.rownum <= b.rownum + 5 and t1.rownum > b.rownum
)avg5
from
( select b.*,(@rownum := @rownum + 1) AS rownum FROM (SELECT @rownum := 0) r,`TMPtable` b ORDER BY `name`,`index` asc) b 计算前:计算后:
我的思路是这样的:
先把时间取出前5天的数据,然后再按name进行分组取平均值,不过要看你数据的要求:
1. 是不是每天都有数据?SELECT name, avg(close)
FROM TMPtable
WHERE index >= SUBDATE(CURDATE(), INTERVAL 5 DAY)
GROUP BY name