主页 / dba / 问题 / 221255
Accepted
Stepan
Asked: 2018-10-30 05:08:02 +0800 CST

通过对路段进行分组，根据道路长度构建道路速度直方图

  • 772

数据集如下所示：

在此处输入图像描述

该表还没有 sum(segLength)，但可以通过将sum(segLength) from road_table group by roadID A roadID(ei Lake Street) 属于 a roadType(ei St) 并由路段组成（segID对于给定的路障，ei 为 2005）来计算。

我想绘制按道路长度分组的道路平均速度，进一步按道路类型分组，如下面的示例输出所示。

在此处输入图像描述

如何为每个添加列roadType，在每个roadType组中将道路放入箱中，在每个箱中计算平均值maxSpeed？

我正在使用 PL/SQL。如有必要，我可以翻译来自 MySQL 或 PostgreSQL 的查询。

oracle group-by

1 个回答

  1. Best Answer

    也许以下想法将为您提供开发查询的起点。我们首先需要一些测试数据。您问题中的最后一个 SEGID 是：4007。在示例中，我们使用的表包含 4500 个随机 SEGLENGTH、ID、最大速度等，与您所描述的类似（在您的示例表中）。

    TEST 表和数据 (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 表：前 5 行，后 5 行

    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

    要将 SEGLENGTH 划分为“桶”，您可以使用 WIDTH_BUCKET()（参见文档），并使用 GROUP BY 找到每个“桶”的平均速度。

      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

    将 PIVOT() 添加到此查询中，以便将 ROADTYPES 转换为列，并使用 ROUND() 或 TRUNC() 获取最终值。

    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

    要获得标准偏差值，只需使用 STDDEV() 而不是 AVG()。

    • 1

相关问题