基于距离的sql平均数据

你可以将数据分组到52.8英尺的数据块中。一种方法是将距离除以52.8,然后将其四舍五入为整数。这样,25属于组1,100属于组2,110属于组3,以此类推。

在SQL Server中,您可以这样编写:

select 
    52.8 * cast(dist/52.8 as int) as Distance
,   avg(value1)
,   avg(value2)
from YourTable
group by cast(dist/52.8 as int)

下面是您的数据示例。因为数据从0到0.04,所以我让它计算每0.01英尺块的平均值:

declare @Road table (RecID int, Begin_MP float, End_MP float)
insert into @Road select 100, 0, 0.56

declare @Values table (RecID int, MP float, Value1 float, Value2 float)
insert into @Values values
(100, 0    ,   159  ,   127.7),
(100, 0.003,   95.3 ,   115.3),
(100, 0.006,   82.3 ,   107),
(100, 0.009,   56.5 ,   74.5),
(100, 0.011,   58.1 ,   89.1),
(100, 0.014,   95.2 ,   78.8),
(100, 0.017,   108.9,   242.5),
(100, 0.02 ,   71.8 ,   73.3),
(100, 0.023,   84.1 ,   80.2),
(100, 0.026,   65.5 ,   66.1),
(100, 0.028,   122  ,   135.8),
(100, 0.031,   99.9 ,   230.7),
(100, 0.034,   95.7 ,   111.5),
(100, 0.037,   127.3,   74.3),
(100, 0.04 ,   140.7,   543.1);

select    
    r.RecID
,   cast(v.MP/0.01 as int)*0.01 as StartMP
,   AVG(v.Value1) as AvgVal1
,   AVG(v.Value2) as AvgVal2
from      @Road as r
left join @Values as v
on        r.RecID = v.RecID
group by  r.RecID, cast(v.MP/0.01 as int)

印刷品:

RecID  StartMP AvgVal1  AvgVal2
100    0.00    98,275   106,125
100    0.01    87,4     136,8
100    0.02    85,85    88,85
100    0.03    107,63   138,83
100    0.04    140,7    543,1