← 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;