[Solved] How should I calculate the average speed by road segment for multiple segments?

by


When averaging speeds, the harmonic mean is in need.

The straight forward AVG() approach is wrong, the arithmetic mean yields the wrong result for average velocity.

There is no predefined function for the harmonic mean, but it could be achieved with this query:

SELECT segment,
       COUNT(*)/SUM(1e0/speed) AS avg_speed
FROM T 
GROUP BY segment

SQL Fiddle

1

solved How should I calculate the average speed by road segment for multiple segments?