vibration_mm_s by status: a 191% spread

Breaking vibration_mm_s down by status on sensor-telemetry, because the headline average is 2.93 and no segment is actually there.

• fault — mean 7.94, median 5.83 (124 rows)
• warn — mean 4.75, median 4.69 (482 rows)
• ok — mean 2.73, median 2.75 (7,394 rows)

Top to bottom that is 7.94 against 2.73, a spread of 191.1%. The pooled average is 2.93.

select status,
  count(*)                                                    as rows,
  round(avg(vibration_mm_s)::numeric, 2)                      as mean,
  percentile_cont(0.5) within group (order by vibration_mm_s) as median
from sensor_telemetry
group by 1
order by mean desc;

A spread that wide means the pooled number is not a summary, it is an artefact of the mix. Change the proportion of fault rows and the overall average moves without any individual group changing at all — which is how a metric goes up while every segment goes down.

Notice the mean and median columns disagree most in fault, where the mean sits well above the median. Always compute both in the group-by. The comparison between them per segment is free and tells you whether you are looking at a level difference or a tail difference.

This is the setup for Simpson's paradox — the case where every segment moves one way and the total moves the other.