I have state data from IoT devices (home assistant) logged multiple times a day (depending on device) and retained for 1 year. I'm trying (for devices that log frequently) to keep 1 month of every state change, but for data older than 1 month delete every other state change.

Excerpt from database:

My query so far :

SELECT state_id, entity_id, last_updated
FROM "states"
WHERE entity_id IN ("sensor.climate_outside_temperature","sensor.climate_outside_humidity")
AND last_updated < date('now', '-30 day')
ORDER BY entity_id,state_id DESC

I'm sorting by entity_id, then state_id (as proxy for last_updated as it should be faster). This gives data older than 30 days I'd like to delete every nth row from:

I could add modulus of rowid to WHERE, but rowid isn't row number in result but primary index on source table. So that wouldn't work. I need a computed index column on result set, but I don't know how.

CodePudding user response：

Use ROW_NUMBER() window function to get the ranking of the rows returned by your query, filter the results to get only the even rows and delete only the filtered rows:

WITH 
  numbers AS (
    SELECT state_id, ROW_NUMBER() OVER (ORDER BY state_id DESC) AS rn
    FROM states 
    WHERE entity_id IN ('sensor.climate_outside_temperature', 'sensor.climate_outside_humidity') 
      AND last_updated < date('now', '-30 day') 
  ),
  ids AS (SELECT state_id FROM numbers WHERE rn % 2 = 0)
DELETE
FROM states
WHERE state_id IN ids;

See the demo.