This is the table I am working with:
--------------------- -----------
| Field | Type |
--------------------- --------------
| ID | binary(17) |
| MiscSensor_ID | binary(17) |
| rawValue | varchar(100) |
| RawValueUnitType_ID | int |
| timestamp | timestamp |
--------------------- --------------
Now my goal is to implement an event which deletes all entries older than a month BUT for each week I want to leave one entry per MiscSensor_ID (the one with the lowest rawValue).
I am this far:
CREATE EVENT delete_old_miscsensordatahistory
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_DATE INTERVAL 1 DAY
DO
DELETE
FROM history
WHERE TIMESTAMPDIFF(DAY, timestamp,NOW()) > 31;
I need to do something like: delete if (value > minvalue) and group it in by MiscSensor_ID and 7 day periods but i am stuck right now on how to do that. Any help would be much appreciated.
CodePudding user response:
You can try using the ROW_NUMBER
window function to match the rows which you don't want to delete. Records having row number equal to 1 will be those rows with the minimum "rawValue" for each combination of (week, sensorId).
WITH cte AS (
SELECT *, ROW_NUMBER() OVER(
PARTITION BY MiscSensorId, WEEK(timestamp)
ORDER BY rawValue ) AS rn
FROM history
WHERE TIMESTAMPDIFF(DAY, timestamp,NOW()) > 31
)
DELETE
FROM history
INNER JOIN cte
ON history.ID = cte.ID
WHERE rn > 1;
CodePudding user response:
This is how i implemented the event right now:
CREATE EVENT delete_old_miscsensordatahistory
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_DATE INTERVAL 1 DAY
DO
WITH cte AS (
SELECT *, ROW_NUMBER() OVER(
PARTITION BY MiscSensor_ID, WEEK(timestamp)
ORDER BY CAST(rawValue AS SIGNED) ) AS rn
FROM MiscSensorDataHistory
WHERE TIMESTAMPDIFF(DAY, timestamp,NOW()) > 31
)
DELETE MiscSensorDataHistory
FROM MiscSensorDataHistory
INNER JOIN cte
ON cte.ID = MiscSensorDataHistory.ID
WHERE rn > 1
Testing my method I found out that there are still entries with the same MiscSensor_ID and less than 7 days apart:
| 0x3939333133303037343939353436393032 | 0x3439303031303031303730303030303535 | 554 | 30 | 2022-02-17 23:09:21 |
| 0x3939333133303037343939313631333039 | 0x3439303031303031303730303030303535 | 554 | 30 | 2022-02-06 16:52:48 |
| 0x3939333133303037343938383835353239 | 0x3439303031303031303730303030303535 | 553 | 30 | 2022-01-30 08:21:55 |
| 0x3939333133303037343938383639333436 | 0x3439303031303031303730303030303535 | 554 | 30 | 2022-01-29 22:48:06 |
| 0x3939333133303037343937303734353537 | 0x3439303031303031303730303030303535 | 444 | 30 | 2021-12-26 06:12:07 |
| 0x3939333133303037343937303530363738 | 0x3439303031303031303730303030303535 | 446 | 30 | 2021-12-25 21:53:03 |
| 0x3939333133303037343936333034343238 | 0x3439303031303031303730303030303535 | 0 | 30 | 2021-12-14 13:08:04 |
| 0x3939333133303037343935393934303832 | 0x3439303031303031303730303030303535 | 415 | 30 | 2021-12-08 12:56:43
Any suggestions would be much appreciated.