-6

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

How can this be done in SQL ?

Improve this question
3
  • 3
    Under meta discussion How can I demonstrate to voters that a question is less trivial than it seems? Commented Jan 15, 2016 at 9:33
  • 2
    It probably would have helped if the OP had mentioned that the 'average' he expects is not 'your everyday average'. TBH, I had never even heard of the term Harmonic mean and it took me a while to realise his example result was 'strange' =) Commented Jan 17, 2016 at 11:28
  • 2
    He may not have known, @deroby (Alejandro, can you clarify?) - wouldn't be the first time someone's used arithmetic mean for averaging rates and gotten incorrect results without realizing why. Commented Jan 18, 2016 at 20:17

2 Answers 2

Reset to default
21
+50

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

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

This is true if the segments are the same distance, if the time interval is the same then a standard arithmetic mean suffices, if neither then a weighted harmonic or arithmetic mean is required.
3

This is little bit complex, but will take care of 0 speed also. I have 2 similar queries to do it based on different scenarios.

Assume your source table like below.

+-------------+----------+-------+ | driver_lpr | segment | speed | +-------------+----------+-------+ | 0000001 | A | 30 | | 0000002 | B | 60 | | 0000003 | A | 50 | | 0000004 | A | 100 | | 0000005 | B | 60 | | 0000006 | B | 0 | | 0000007 | C | 0 | +-------------+----------+-------+

I have added 2 new rows with 0 speed.

Case 1:

  • Addition of a 0 speed in segment B, will give average speed as 40 (60*2+0*1)/(2+1).
  • Addition of a new segment, C with 0 speed will give average speed as 0

So output would be 

+----------+-------------------+ | segment | average_speed | +----------+-------------------+ | A | 47.36842105263158 | | B | 40 | | C | 0 | +----------+-------------------+

SQLFiddle Demo CASE 1

Case 2:

  • There will be no change in average speed of B with addition of a 0.
  • However, a new segment C will have 0 average speed.

Output would be

+----------+-------------------+ | segment | average_speed | +----------+-------------------+ | A | 47.36842105263158 | | B | 60 | | C | 0 | +----------+-------------------+

SQLFiddle Demo CASE 2

Query for Case 1:

with tbl1 as (SELECT segment, case when speed = 0 then cast(0 as float) else cast(1 as float)/cast(speed as float) end as newspeed FROM T ), tbl2 as ( select segment,cast(1 as float)/avg(newspeed) as avgspeed,count(*) as cnt from tbl1 where newspeed <> 0 group by segment union select segment,0 as avgspeed,count(*) as cnt from tbl1 where newspeed =0 group by segment ) select segment, sum(avgspeed*cnt)/sum(cnt) as "average_speed" from tbl2 group by segment

Query for Case2

with tbl1 as (SELECT segment, case when speed = 0 then cast(0 as float) else cast(1 as float)/cast(speed as float) end as newspeed FROM T ), tbl2 as ( select segment,cast(1 as float)/avg(newspeed) as avgspeed,count(*) as cnt from tbl1 where newspeed <> 0 group by segment union select segment,0 as avgspeed,count(*) as cnt from tbl1 where newspeed =0 group by segment ) select segment, sum(avgspeed) as "average_speed" from tbl2 group by segment
Improve this answer

2 Comments

How can there be a speed of 0? That would mean they are stationary and therefore could not complete any road segments.
Just wanted to cover all possible scenarios.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.