A CO₂ sensor takes four readings. Your query averages six rows. Which number goes on the dashboard?
Networks are unreliable, so devices retry. If the sensor sends a reading and doesn't hear back in time, it sends it again, even when the first copy got through. The database ends up with one row per delivery, but what you care about is one row per measurement.
This exercise has six rows and one bug, and you can see it in a single AVG().
Adapted with permission from Sensor retries by telemetry.sh. The readings are fictional.
One sensor reports every 10 seconds. Reading r4 was delivered three times because of retries:
DROP TABLE IF EXISTS sensor_deliveries;
CREATE TABLE sensor_deliveries (
reading_id VARCHAR(10) NOT NULL, -- assigned by the sensor, one per measurement
measured_s INT NOT NULL, -- second the sensor took the reading
co2_ppm INT NOT NULL, -- CO2 concentration, parts per million
delivered_s INT NOT NULL -- second the row arrived at the server
);
INSERT INTO sensor_deliveries (reading_id, measured_s, co2_ppm, delivered_s) VALUES
('r1', 0, 600, 1),
('r2', 10, 650, 11),
('r3', 20, 700, 21),
('r4', 30, 750, 31),
('r4', 30, 750, 32),
('r4', 30, 750, 33);
Before running anything, work it out on paper. The four readings are 600, 650, 700 and 750. What's their average?
SELECT COUNT(*) AS row_count,
ROUND(AVG(co2_ppm), 1) AS avg_ppm
FROM sensor_deliveries;
Output:
row_count | avg_ppm
-----------+---------
6 | 700.0
AVG() doesn't know that three of those rows are the same measurement. It adds all six values (4200) and divides by six. Reading r4 is counted three times, which pulls the average up toward 750.
The correct answer is (600 + 650 + 700 + 750) / 4 = 675 ppm.
Before fixing anything, confirm what's duplicated. A reading should appear once, so any reading_id with more than one row is a retry:
SELECT reading_id,
COUNT(*) AS deliveries
FROM sensor_deliveries
GROUP BY reading_id
HAVING COUNT(*) > 1;
Output:
reading_id | deliveries
------------+------------
r4 | 3
It's good practice to also check that the copies really are identical. If two rows share a reading_id but have different values, you have a data conflict, not a retry:
SELECT reading_id
FROM sensor_deliveries
GROUP BY reading_id
HAVING COUNT(DISTINCT co2_ppm) > 1
OR COUNT(DISTINCT measured_s) > 1;
No rows means every duplicate is an exact copy, so it's safe to keep any one of them.
Your first instinct might be AVG(DISTINCT ...):
SELECT ROUND(AVG(DISTINCT co2_ppm), 1) AS avg_ppm
FROM sensor_deliveries;
avg_ppm
---------
675.0
It gives 675, the right number, but for the wrong reason. DISTINCT co2_ppm removes duplicate values, not duplicate readings. It only works here because the four readings happen to have four different values.
Add a fifth reading that also measured 650 ppm:
INSERT INTO sensor_deliveries VALUES ('r5', 40, 650, 41);
Now there are five readings (600, 650, 700, 750, 650) with a true average of 670. AVG(DISTINCT co2_ppm) still says 675, because it drops the second 650 even though it's a separate measurement.
Delete it again before moving on:
DELETE FROM sensor_deliveries WHERE reading_id = 'r5';
Rule of thumb: deduplicate on the thing that identifies a measurement (reading_id), never on the value you're measuring.
Collapse the deliveries into one row per reading first, then aggregate:
SELECT COUNT(*) AS readings,
ROUND(AVG(co2_ppm), 1) AS avg_ppm
FROM (
SELECT DISTINCT reading_id, measured_s, co2_ppm
FROM sensor_deliveries
) AS readings;
Output:
readings | avg_ppm
----------+---------
4 | 675.0
delivered_s is left out on purpose. It's the one column that differs between retries, so including it would keep all three r4 rows.
SELECT DISTINCT only works when the duplicates are identical in every column you select. A more flexible pattern is to number the deliveries of each reading and keep one:
SELECT COUNT(*) AS readings,
ROUND(AVG(co2_ppm), 1) AS avg_ppm
FROM (
SELECT reading_id,
co2_ppm,
ROW_NUMBER() OVER (
PARTITION BY reading_id
ORDER BY delivered_s
) AS delivery_no
FROM sensor_deliveries
) AS d
WHERE delivery_no = 1;
Output:
readings | avg_ppm
----------+---------
4 | 675.0
PARTITION BY reading_id restarts the count for each reading. ORDER BY delivered_s makes the earliest arrival number 1. You could keep the latest instead with ORDER BY delivered_s DESC, which matters when later copies can carry corrections.
For each reading that was retried, show how many extra deliveries it caused and how many seconds passed between the first and last arrival.
Expected output:
reading_id | extra_deliveries | retry_span_s
------------+------------------+--------------
r4 | 2 | 2
Hint: group by reading_id, keep only groups with more than one row, and use COUNT(*), MIN() and MAX() on delivered_s.
The same delivery-vs-event mismatch shows up whenever a system uses at-least-once delivery:
In each case the fix is the same as here: carry a stable ID from the source, and aggregate after deduplicating on it.
If the table should only ever hold one row per reading, enforce it:
ALTER TABLE sensor_deliveries
ADD CONSTRAINT uq_reading UNIQUE (reading_id);
Inserts can then use INSERT ... ON CONFLICT (reading_id) DO NOTHING (PostgreSQL) so retries are absorbed silently. Run this on a clean copy: on the six-row table above it fails, because r4 is already duplicated. Keep the raw deliveries table if you need to measure retry rates. Otherwise, dedupe on the way in.
SQL Output