我有來自 IoT 設備（家庭助理）的狀態資料，每天記錄多次（取決于設備）并保留 1 年。我正在嘗試（對于頻繁記錄的設備）將每次狀態更改保留 1 個月，但對于超過 1 個月的資料，洗掉所有其他狀態更改。
資料庫摘錄：
| state_id | 物體編號 | 最近更新時間 |
|---|---|---|
| 2342932 | 傳感器.climate_outside_humidity | 2022-11-12 04:13:46.598786 |
| 2063613 | 傳感器.climate_outside_humidity | 2022-10-28 03:02:47.756064 |
| 1984952 | 傳感器.climate_outside_temperature | 2022-10-20 07:32:51.674016 |
| 925115 | 傳感器.climate_outside_humidity | 2022-07-25 09:54:01.095297 |
| 1897854 | 傳感器.climate_outside_humidity | 2022-10-11 17:28:13.448728 |
| 2005628 | 傳感器.climate_outside_temperature | 2022-10-22 12:37:21.027465 |
| 1071454 | 傳感器.climate_outside_humidity | 2022-08-04 13:16:02.885636 |
| 1663793 | 傳感器.climate_outside_temperature | 2022-09-17 14:36:05.900979 |
| 1756081 | 傳感器.climate_outside_temperature | 2022-09-27 23:17:25.688069 |
| 2372362 | 傳感器.climate_outside_temperature | 2022-11-14 02:28:16.034873 |
到目前為止我的查詢：
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
我正在排序entity_id，然后（作為它應該更快state_id的代理）。last_updated這給出了超過 30 天的資料，我想從以下位置洗掉每第 n 行：
| state_id | 物體編號 | 最近更新時間 |
|---|---|---|
| 1897854 | 傳感器.climate_outside_humidity | 2022-10-11 17:28:13.448728 |
| 1071454 | 傳感器.climate_outside_humidity | 2022-08-04 13:16:02.885636 |
| 925115 | 傳感器.climate_outside_humidity | 2022-07-25 09:54:01.095297 |
| 1756081 | 傳感器.climate_outside_temperature | 2022-09-27 23:17:25.688069 |
| 1663793 | 傳感器.climate_outside_temperature | 2022-09-17 14:36:05.900979 |
rowid我可以添加to的模數WHERE，但rowid不是結果中的行號而是源表上的主索引。所以那是行不通的。我需要在結果集上計算索引列，但我不知道如何做。
uj5u.com熱心網友回復：
使用ROW_NUMBER()視窗函式獲取查詢回傳的行的排名，過濾結果以僅獲取偶數行并僅洗掉過濾后的行：
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;
請參閱演示。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/536621.html
上一篇：無法使用for回圈處理大量資料
下一篇：如何比較不同時期的資料？