Harish Vishwakarma Harish Vishwakarma - 4 months ago 15
SQL Question

mysql query to get the average from the latest N rows for distinct phone_numbers

My table is (phone_number, bpm, timestamp). I want to get the phone_number, avg(bpm) for the latest 10 rows for each phone_number. I have tried..

SELECT phone_number, AVG( bpm )
FROM (
SELECT *
FROM table_name
WHERE bpm !=0
ORDER BY timestamp DESC
) AS temp
GROUP BY phone_number
HAVING COUNT( * ) <=10


This query is giving empty result. I cannot use IN clause in my version of mysql.

Answer

This is a pain in MySQL. The most reasonable solution uses variables to enumerate the rows and then aggregation:

SELECT phone_number, AVG(bpm)
FROM (SELECT t.*,
             (@rn := if(@pn = phone_number, @rn + 1,
                        if(@pn := phone_number, 1, 1)
                       )
             ) as rn
      FROM table_name t CROSS JOIN
            (SELECT @pn := '', @rn := 0) params
      WHERE bpm <> 0
      ORDER BY phone_number, timestamp DESC
     ) t
WHERE rn <= 10
GROUP BY phone_number;