I come from MS SQL Server and I'm relatively new to MySQL / MariaDB 10 (at least in a deeper way than just "SELECT * FROM [Table]"). I now searched for several hours in Google and StackOverflow, but I haven't found a soluton to my problem yet. If it's relevant in any way: I use MySQL Workbench for writing my code.
The Background
I have a new data logging project for saving and displaying data from several temperature and humidity sensors within the house. I save it in following table:
| ID | Time | Device | Temperature | Humidity |
|---|---|---|---|---|
| 1 | 2022-01-09 13:34:00 | 1 | 20.1 | 52.3 |
| 2 | 2022-01-09 13:35:00 | 1 | 20.0 | 52.3 |
| 3 | 2022-01-09 13:36:00 | 1 | 20.1 | 52.4 |
| 4 | 2022-01-09 13:37:00 | 1 | 20.1 | 52.5 |
| 5 | 2022-01-09 13:38:00 | 1 | 20.0 | 52.5 |
| 6 | 2022-01-09 13:39:00 | 1 | 20.1 | 52.6 |
I query the needed data for a chart using a stored procedure. Especially the on 0.1°C rounded temperature values have the disadvantage that they naturally often change between a value of 0.1 when the temperature is pretty stable. So I thought of a moving average to smooth the values over the last 10 minutes which works perfectly with an average window function.
Here a simplified version of my procedure:
CREATE PROCEDURE `stpGetSensorData`(sensorId INT, startDate VARCHAR(8))
BEGIN
DECLARE FromDate DATE;
SET FromDate = STR_TO_DATE(startDate, '%Y%m%d');
SELECT
L.ID,
L.Time,
L.Device,
AVG(L.Temperature) OVER (ORDER BY L.Time ROWS BETWEEN 10 PRECEDING AND 0 FOLLOWING) AS Temperature,
AVG(L.Humidity) OVER (ORDER BY L.Time ROWS BETWEEN 10 PRECEDING AND 0 FOLLOWING) AS Humidity
FROM
LoggedData AS L
WHERE
Device = sensorId
AND Time < DATE_ADD(FromDate, INTERVAL 1 DAY)
AND Time >= FromDate
ORDER BY Time DESC;
END
The challenge
Now I thought I let the end user decide about the size of the window, i.e. an average over the last 5, 10, 30, 60, ... minutes. But when I try to insert a parameter in the window function, it leads to the error: "averageRows is not valid at this position".
Here the code:
CREATE PROCEDURE `stpGetSensorData`(sensorId INT, startDate VARCHAR(8), averageRows INT)
BEGIN
DECLARE FromDate DATE;
SET FromDate = STR_TO_DATE(startDate, '%Y%m%d');
SELECT
L.ID,
L.Time,
L.Device,
AVG(L.Temperature) OVER (ORDER BY L.Time ROWS BETWEEN averageRows PRECEDING AND 0 FOLLOWING) AS Temperature,
AVG(L.Humidity) OVER (ORDER BY L.Time ROWS BETWEEN averageRows PRECEDING AND 0 FOLLOWING) AS Humidity
FROM
LoggedData AS L
WHERE
Device = sensorId
AND Time < DATE_ADD(FromDate, INTERVAL 1 DAY)
AND Time >= FromDate
ORDER BY Time DESC;
END
I guess it's possible to solve this using Dynamic SQL, but I try to avoid Dynamic SQL whereever possible and thought there must be a 'normal' solution as well and I'm just too blind to see it.
Any smart ideas?
