Build a histogram of road speed based on road length by grouping road segments

Question

A data set look like this:

The table doesn't have sum(segLength yet), but can be computed by summing up sum(segLength) from road_table group by roadID A roadID (e.i. Lake Street) belongs to a roadType (e.i. St) and consists of segments (e.i. segID is 2005 for a given road block).

I want to plot average speeds of roads grouped by road lengths further grouped by road types like shown in example output below.

How to add columns for each roadType, within each roadType group roads into bins, within each bin compute average value of maxSpeed?

I'm using PL/SQL. I can translate a query from MySQL or PostgreSQL if necessary.

Answer 1

Maybe the following ideas will give you a starting point for developing your queries. We need some test data first. The last SEGID in your question is: 4007. In the example, we are using a table with 4500 random SEGLENGTHs, and IDs, maxspeeds etc similar to the ones you have described (in your sample table).

TEST table and data (Oracle 12c)

create table test ( 
  segid number generated always as identity primary key 
, roadid number
, roadtype varchar2( 64 )
, seglength number ( 10, 1 )
, maxspeed number
, check ( maxspeed between 0 and 100 )
); 

insert into test( roadid, roadtype, seglength, maxspeed )
select 
  roadid
, case
    when roadid < 200 then 'Ave'
    when roadid between 200 and 400 then 'Hwy'
    when roadid between 401 and 700 then 'St'
    when roadid between 701 and 900 then 'Rd'
  end roadtype
, round( dbms_random.value() * 10, 2 ) 
, case
    when roadid < 200 then ( mod( roadid, 4 ) + 1 ) * 10 
    when roadid between 200 and 400 then ( mod( roadid, 7 ) + 1 ) * 10 
    when roadid between 401 and 700 then ( mod( roadid, 3 ) + 1 ) * 10  
    when roadid between 701 and 900 then ( mod( roadid, 6 ) + 1 ) * 10  
  end maxspeed
from 
  ( select level roadid from dual connect by level <= 900 )
, ( select level segcount from dual connect by level <= 5 ) 
;

TEST table: first 5 rows, last 5 rows

SQL> select * from test order by segid fetch first 5 rows only;
SEGID  ROADID  ROADTYPE  SEGLENGTH  MAXSPEED  
1      1       Ave       1.6        20        
2      1       Ave       7.5        20        
3      1       Ave       3.8        20        
4      1       Ave       9.2        20        
5      1       Ave       2.8        20        

SQL> select * from test order by segid offset 4495 rows fetch next 5 rows only;
SEGID  ROADID  ROADTYPE  SEGLENGTH  MAXSPEED  
4496   900     Rd        4.2        10        
4497   900     Rd        4.2        10        
4498   900     Rd        0.7        10        
4499   900     Rd        7.1        10        
4500   900     Rd        2          10 

For dividing the SEGLENGTH into "buckets", you could use WIDTH_BUCKET() (see documentation), and use GROUP BY to find the average speed for each "bucket".

  select 
    roadtype, avg( maxspeed ), max( seglength ), wb
  from 
  (
      select roadtype, maxspeed, seglength
      , width_bucket( 
          seglength
        , ( select min( seglength ) from test )
        , ( select max( seglength ) from test ) + 1  -- upper bucket count: exclusive!
        , 5 
       ) wb    
      from test 
    )
  group by wb, roadtype 

-- result
ROADTYPE  AVG(MAXSPEED)                              MAX(SEGLENGTH)  WB  
Hwy       39.24170616113744075829383886255924170616  8.7             4   
Hwy       40.70866141732283464566929133858267716535  10              5   
St        18.91304347826086956521739130434782608696  8.7             4   
St        19.81132075471698113207547169811320754717  10              5   
Rd        34.70588235294117647058823529411764705882  6.5             3   
Ave       24.49275362318840579710144927536231884058  10              5   
Hwy       41.76744186046511627906976744186046511628  6.5             3   
Ave       25.52995391705069124423963133640552995392  2.1             1   
Ave       24.0796019900497512437810945273631840796   8.7             4   
Ave       25.27272727272727272727272727272727272727  4.3             2   
St        20.50632911392405063291139240506329113924  2.1             1   
Rd        34.51754385964912280701754385964912280702  8.7             4   
Rd        35.11811023622047244094488188976377952756  10              5   
Hwy       38.68312757201646090534979423868312757202  4.3             2   
Rd        35.26315789473684210526315789473684210526  2.1             1   
Rd        35.52995391705069124423963133640552995392  4.3             2   
Ave       25.70776255707762557077625570776255707763  6.5             3   
Hwy       40.28708133971291866028708133971291866029  2.1             1   
St        21.03658536585365853658536585365853658537  4.3             2   
St        19.65838509316770186335403726708074534161  6.5             3 

Add a PIVOT() to this query, in order to convert the ROADTYPES into columns, and use ROUND() or TRUNC() for obtaining the final values.

select msl, ave, hwy, st, rd
from (
  select 
    roadtype, trunc( avg( maxspeed ) ) ams, max( seglength ) msl, wb
  from 
  (
    select roadtype, maxspeed, seglength
    , width_bucket( 
        seglength
      , ( select min( seglength ) from test )
      , ( select max( seglength ) from test ) + 1  -- upper bucket count: exclusive!
      , 5 
     ) wb    
    from test 
  )
  group by wb, roadtype 
) pivot  (
    avg( ams ) for roadtype in (  -- pivot() requires an aggregate function
      'Ave' as ave
    , 'Hwy' as hwy
    , 'St'  as st
    , 'Rd'  as rd
   )
) P
order by wb
;

-- result    
       MSL        AVE        HWY         ST         RD
---------- ---------- ---------- ---------- ----------
       2.1         25         40         20         35
       4.3         25         38         21         35
       6.5         25         41         19         34
       8.7         24         39         18         34
        10         24         40         19         35

For getting the standard deviation values, just use STDDEV() instead of AVG().