clean_outliers.sql

CREATE TEMPORARY TABLE newer_states (
  state_id INTEGER NOT NULL,
  entity_id VARCHAR(255),
  state INTEGER,
  last_updated INTEGER,
  PRIMARY KEY (state_id)
);
CREATE INDEX ix_entity_id ON newer_states (entity_id);
INSERT INTO newer_states
  SELECT state_id, entity_id, CAST(state AS INTEGER), CAST(STRFTIME('%s', last_updated) AS INTEGER)
  FROM states
  WHERE last_updated > DATETIME('now', '-2 days')
    AND entity_id IN ('sensor.printer_aqi_pm10', 'sensor.printer_aqi_pm2_5');
DELETE FROM states WHERE state_id IN (
  SELECT t.state_id
    FROM (
      SELECT t_cur.state_id, MAX(t_before_id.state_id) AS prev_id, MIN(t_after_id.state_id) AS next_id
        FROM newer_states t_cur
        INNER JOIN newer_states t_before_id
          ON t_cur.entity_id = t_before_id.entity_id
          AND t_before_id.state_id < t_cur.state_id
          AND t_cur.last_updated - t_before_id.last_updated < 900
        INNER JOIN newer_states t_after_id
          ON t_after_id.state_id > t_cur.state_id
          AND t_cur.entity_id = t_after_id.entity_id
          AND t_after_id.last_updated - t_cur.last_updated < 900
        GROUP BY t_cur.state_id
    ) t_check
    INNER JOIN newer_states t ON t_check.state_id = t.state_id
    INNER JOIN newer_states t_before ON t_before.state_id = t_check.prev_id
    INNER JOIN newer_states t_after ON t_after.state_id = t_check.next_id
    WHERE CAST(t_before.state AS REAL) * 1.5 + 20 < CAST(t.state AS REAL)
      AND CAST(t_after.state AS REAL) * 1.5 + 20 < CAST(t.state AS REAL));