使用AVG()的MySQL查询出现问题 [英] Trouble with MySQL query using AVG()

问题描述

我正在使用一个查询，该查询平均获取每个给定ID的所有记录...

I am using a query that takes an average of all the records for each given 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条记录.

These records are each updated once daily. I would like to use only the 10 most recent records for each id in my average.

任何帮助都会受到赞赏.

Any help would be qreatly appreciated.

blf表结构为:

id | bline_id | flow | date

推荐答案

另一个选项是模拟ROW_NUMBER().

Another option is to simulate ROW_NUMBER().

此语句创建一个计数器，并在每次遇到新的bline_id时将其重置.然后，它会过滤掉不在前10行中的所有记录.

This statement creates a counter and resets it every time it encounters a new bline_id. It then filters out any records that aren't in the first 10 rows.

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 

演示

值得一提的是，删除分组并查看中间结果

It's worthwhile seeing this in action by removing the group by and looking at intermediate results

这篇关于使用AVG()的MySQL查询出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！