mysql avg 过滤_使用AVG（）的MySQL查询出现问题

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";

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 table structure is:

id | bline_id | flow | date

解决方案

Another option is to simulate ROW_NUMBER().

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