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().