← Back to the chapter · Next detail →

Technical detail · Data Retention and Downsampling

A quality-aware 15-minute rollup

The example groups each site, device, and metric into 15-minute buckets. It accepts valid and late_valid rows for averages, extremes, and sample counts; the separate nonvalid count includes invalid and unclassified rows. Adapt table names and policy details to your platform.

-- Example rollup shape. Adapt table names and policy details to your platform.
SELECT
  time_bucket('15 minutes', observed_at) AS bucket_start,
  site_id,
  device_id,
  metric_name,
  avg(value) FILTER (WHERE quality_status IN ('valid', 'late_valid')) AS avg_value,
  min(value) FILTER (WHERE quality_status IN ('valid', 'late_valid')) AS min_value,
  max(value) FILTER (WHERE quality_status IN ('valid', 'late_valid')) AS max_value,
  count(*) FILTER (WHERE quality_status IN ('valid', 'late_valid')) AS sample_count,
  count(*) FILTER (WHERE (quality_status IN ('valid', 'late_valid')) IS NOT TRUE) AS nonvalid_count,
  max(received_at) AS latest_received_at
FROM readings
WHERE observed_at >= :source_start
  AND observed_at <  :source_end
GROUP BY bucket_start, site_id, device_id, metric_name;

← Back to the chapter · Next detail →