故障使用AVG（）MySQL查询

Question

我使用的查询需要的所有记录每一个给定id的平均...

$query = "SELECT bline_id, AVG(flow) as flowavg 
      FROM blf 
      WHERE bline_id BETWEEN 1 AND 30 
      GROUP BY bline_id 
      ORDER BY bline_id ASC"; 

这些记录每天的更新一次。我想只用平均每个ID的10个最新记录。

任何帮助将被大力赞赏。

BLF表结构是：

id | bline_id | flow | date 

Answer 1

另一个选项是模拟ROW_NUMBER（）。

该语句创建一个计数器，并在每次遇到新的bline_id时将其复位。然后筛选出不在前10行的任何记录。

SELECT bline_id, 
     Avg(flow) avg 
FROM (SELECT id, 
       bline_id, 
       flow, 
       date, 
       CASE 
       WHEN @previous IS NULL 
         OR @previous = bline_id THEN @rownum := @rownum + 1 
       ELSE @rownum := 1 
       end rn, 
       @previous := bline_id 
     FROM blf, 
       (SELECT @rownum := 0, 
         @previous := NULL) t 
     WHERE bline_id > 0 and bline_id < 31 
     ORDER BY bline_id, 
        date DESC, 
        id) t 
WHERE rn < 11 
GROUP BY bline_id 

DEMO

这是值得的，通过去除组，看着intermediate results

Answer 2

如果这些每天都在真正的更新，然后使用日期计算：

SELECT bline_id, AVG(flow) as flowavg 
FROM blf 
WHERE bline_id BETWEEN 1 AND 30 and 
     date >= date_sub(now(), interval 10 day) 
GROUP BY bline_id 
ORDER BY bline_id ASC 

否则，你必须放在一个柜台，你可以做与相关的子查询：

SELECT bline_id, AVG(flow) as flowavg 
FROM (select blf.*, 
      (select COUNT(*) from blf blf2 where blf2.bline_id = blf.bline_id and blf2.date >= blf.date 
      ) seqnum 
     from blf 
    ) blf 
WHERE bline_id BETWEEN 1 AND 30 and 
     seqnum <= 10 
GROUP BY bline_id 
ORDER BY bline_id ASC