MySql Delete Data older than 1 month but leave one entry per week and id (the one with the lowest value)

toph 0
Viewed 36

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.

mysql database
2 Answers

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

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.

toph0
Related