Time-series data and TimescaleDB optimization

-- Moving average over time
SELECT
  time,
  sensor_id,
  temperature,
  AVG(temperature) OVER (
    PARTITION BY sensor_id
    ORDER BY time
    ROWS BETWEEN 5 PRECEDING AND CURRENT ROW
  ) AS moving_avg_6_readings
FROM sensor_data
WHERE sensor_id = 1
ORDER BY time DESC;

-- Rate of change
SELECT
  time,
  temperature,
  temperature - LAG(temperature) OVER (ORDER BY time) AS temp_change,
  EXTRACT(EPOCH FROM (time - LAG(time) OVER (ORDER BY time))) AS seconds_elapsed,
  (temperature - LAG(temperature) OVER (ORDER BY time)) /
    NULLIF(EXTRACT(EPOCH FROM (time - LAG(time) OVER (ORDER BY time))), 0)
    AS rate_per_second
FROM sensor_data
WHERE sensor_id = 1
ORDER BY time DESC;

-- Time-weighted average
SELECT
  sensor_id,
  time_bucket('1 day', time) AS day,
  average(
    time_weight('LOCF', time, temperature)
  ) AS time_weighted_avg
FROM sensor_data
GROUP BY sensor_id, day;

-- Detect anomalies (values beyond 2 standard deviations)
WITH stats AS (
  SELECT
    sensor_id,
    AVG(temperature) AS mean_temp,
    STDDEV(temperature) AS stddev_temp
  FROM sensor_data
  WHERE time >= NOW() - INTERVAL '30 days'
  GROUP BY sensor_id
)
SELECT
  s.time,
  s.sensor_id,
  s.temperature,
  st.mean_temp,
  st.stddev_temp,
  ABS(s.temperature - st.mean_temp) / NULLIF(st.stddev_temp, 0) AS z_score
FROM sensor_data s
JOIN stats st ON s.sensor_id = st.sensor_id
WHERE ABS(s.temperature - st.mean_temp) > 2 * st.stddev_temp
ORDER BY s.time DESC;

-- Downsampling (reduce granularity)
SELECT
  time_bucket('5 minutes', time) AS bucket,
  sensor_id,
  AVG(temperature) AS avg_temp,
  first(temperature, time) AS first_temp,
  last(temperature, time) AS last_temp,
  MAX(temperature) AS max_temp,
  MIN(temperature) AS min_temp
FROM sensor_data
WHERE time >= NOW() - INTERVAL '1 day'
GROUP BY bucket, sensor_id
ORDER BY bucket DESC;

-- Find gaps in data
SELECT
  time AS gap_start,
  LEAD(time) OVER (PARTITION BY sensor_id ORDER BY time) AS gap_end,
  EXTRACT(EPOCH FROM (
    LEAD(time) OVER (PARTITION BY sensor_id ORDER BY time) - time
  )) / 60 AS gap_minutes
FROM sensor_data
WHERE sensor_id = 1
  AND time >= NOW() - INTERVAL '1 day'
HAVING EXTRACT(EPOCH FROM (
  LEAD(time) OVER (PARTITION BY sensor_id ORDER BY time) - time
)) > 600  -- Gaps > 10 minutes
ORDER BY time DESC;

-- Histogram of values
SELECT
  width_bucket(temperature, 15, 30, 10) AS bucket,
  COUNT(*) AS count,
  MIN(temperature) AS min_temp,
  MAX(temperature) AS max_temp
FROM sensor_data
WHERE time >= NOW() - INTERVAL '7 days'
GROUP BY bucket
ORDER BY bucket;

-- Cumulative sum over time
SELECT
  time,
  sensor_id,
  temperature,
  SUM(temperature) OVER (
    PARTITION BY sensor_id
    ORDER BY time
  ) AS cumulative_temp
FROM sensor_data
WHERE sensor_id = 1
ORDER BY time;

-- Regular time-series table (without TimescaleDB)
CREATE TABLE metrics (
  timestamp TIMESTAMP NOT NULL,
  metric_name VARCHAR(100) NOT NULL,
  value DOUBLE PRECISION NOT NULL,
  tags JSONB
);

-- Partition by month
CREATE TABLE metrics_2024_01 PARTITION OF metrics
  FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE INDEX idx_metrics_2024_01_time
  ON metrics_2024_01(timestamp DESC);

CREATE INDEX idx_metrics_2024_01_name_time
  ON metrics_2024_01(metric_name, timestamp DESC);

-- Query with time-series patterns
SELECT
  DATE_TRUNC('hour', timestamp) AS hour,
  metric_name,
  AVG(value) AS avg_value,
  PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY value) AS p95_value,
  MAX(value) AS max_value
FROM metrics
WHERE timestamp >= NOW() - INTERVAL '24 hours'
  AND metric_name = 'response_time_ms'
GROUP BY hour, metric_name
ORDER BY hour DESC;