Calculate average for multiple subsets simultaneously

Question

I have a MySQL database (for testing: through WAMP) with among other things a collection of temperatures for a bunch of likewise devices. Now I want to calculate the average temperature for each device individually. I can do so for one device at a time, but it would greatly reduce the number of queries if more can be done simultaneously.

Small DB example:

ID	RecdAtUnix	dev_eui*	temperature
0	1683528566	A	19.8
1	1683528567	A	20.5
2	1683528568	B	22.3
3	1683528569	C	21.0
4	1683528570	B	24.9
5	1683528571	C	23.6
6	1683528572	A	18.4

* dev_eui is a string that uniquely identifies a device

An example query:

SELECT theInterval,
round(avg(temperature), 4) AS temperature
FROM (
 SELECT (RecdAtUnix - (RecdAtUnix % (:interval))) as theInterval,
 temperature
 FROM Uplinks
 WHERE RecdAtUnix >= :start AND RecdAtUnix < :end AND dev_eui = :dev_eui
 ORDER BY RecdAtUnix
) AS someData
GROUP BY theInterval ORDER BY theInterval;

This query returns (among others) the average temperature over a specified interval for a specific dev_eui. For dev_eui = A this would return average temperature 19.5667 for the whole interval. (How) can I calculate the average for all / a number of dev_euis at once? All possible values of dev_euis are known through another table.

• mustaccio: Please consider following these suggestions.
• StevenCellist: this should help a bit - please let me know if I missed some crucial information / example

Answer 1

You can group by two fields. So in this case you can start with:

SELECT
 theInterval,
 dev_eui,
 round(avg(temperature), 4) AS temperature
FROM (
 SELECT
 (RecdAtUnix - (RecdAtUnix % (:interval))) as theInterval,
 dev_eui,
 temperature
 FROM Uplinks
 WHERE RecdAtUnix >= :start AND RecdAtUnix < :end
) AS someData
GROUP BY theInterval, dev_eui
ORDER BY theInterval, dev_eui;

And do not include order by in a subquery - waste of performance.

EDIT: The MySQL can do a group by over calculated value, so this can be reduced to:

 SELECT
 (RecdAtUnix - (RecdAtUnix % (:interval))) as theInterval,
 dev_eui,
 temperature
 FROM Uplinks
 WHERE RecdAtUnix >= :start AND RecdAtUnix < :end
 GROUP BY theInterval, dev_eui
 ORDER BY theInterval, dev_eui;

• StevenCellist: good point regarding the ordering - remnants of some older query but irrelevant here. Is there a more elegant way to expand this to ~50 dev_euis?
• White Owl: If you do not need to split data by "theInterval", then simple select dev_eui, avg(temperature) from uplinks group by dev_eui will be enough.
• StevenCellist: thanks for the addition! it is somewhat okay, but in the ideal world I would like to plot a figure where the data is averaged per "theInterval" for each device. do you have any decent suggestion or is that not really achievable?
• White Owl: @StevenCellist it is possible in MySQL. I edited the answer.