如何从Home Assistant IoT设备历史数据结果集中删除每隔n行的数据？

我有来自Home Assistant物联网设备的状态数据，这些数据会根据设备不同每日被多次记录，数据保留1年。需要对记录频繁的设备做如下数据清理：

• 保留最近1个月的所有状态变更数据
• 对超过1个月的数据，删除其中每隔一行（即保留一行、删除一行循环）

数据库数据摘录 #

当前查询语句 #

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

解决方案 #

要实现按设备分组后对历史数据隔行删除，需要用窗口函数为每个设备组内的行生成序号，再筛选出要删除的行。

1. 先查询确认要删除的行 #

执行以下语句查看待删除的目标数据，确保符合预期：

WITH ranked_states AS (
  SELECT 
    state_id,
    entity_id,
    ROW_NUMBER() OVER (PARTITION BY entity_id ORDER BY state_id DESC) AS row_num
  FROM "states"
  WHERE 
    entity_id IN ('sensor.climate_outside_temperature','sensor.climate_outside_humidity')
    AND last_updated < date('now', '-30 day')
)
SELECT state_id, entity_id, row_num
FROM ranked_states
WHERE row_num % 2 = 0; -- 此处筛选偶数行删除，若要删奇数行改为row_num % 2 = 1

2. 执行删除操作 #

确认无误后，执行删除语句：

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

关键说明 #

• ROW_NUMBER()窗口函数会按entity_id分组，每个组内按state_id降序（和你原查询的排序逻辑一致）生成连续行号
• 通过行号取模（% 2）实现隔行筛选，你可以根据需求调整取模条件
• 执行删除前务必先用SELECT语句验证，避免误删数据
• Home Assistant使用的SQLite支持CTE（公共表表达式），上述语句可直接运行

内容的提问来源于stack exchange，提问作者user3171220