如何计算路段的多个路段的平均速度? [英] How should I calculate the average speed by road segment for multiple segments?

问题描述

我有一个驾驶员速度和路段表:

I have a table of driver speeds and road segments:

driver_lpr    |   segment    |    speed
  0000001     |       A      |     30
  0000002     |       B      |     60
  0000003     |       A      |     50
  0000004     |       A      |     100
  0000005     |       B      |     60

我想有一张每段平均速度的表

And I want to have a table of average speed per segment

segment   |   average speed
   A      |         47.368
   B      |         60

如何在SQL中完成此操作?

How can this be done in SQL ?

推荐答案

平均速度时，谐波平均值是需要的.

When averaging speeds, the harmonic mean is in need.

直接的AVG()方法是错误的，算术平均值得出的平均速度结果是错误的.

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小提琴

这篇关于如何计算路段的多个路段的平均速度?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！