SQL 基于距离的平均数据

Question

我是 SQL 的新手。我有一个数据库，其中包含基于道路/里程点的记录。我的目标是沿着道路每 52.8 英尺获得一个平均值。我的相关表每隔 15 英尺就有一个数据，这个表当然有一个与主表相关的外键。

如果我想沿着给定的里程碑每 52.8 英尺提取平均值，我该怎么做？

示例数据:

    RecID   Begin_MP    End_MP    100  0  0.56    RecID    MP Value1  Value2    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

第一个数据是道路的一个例子。第二个数据子集是我需要每 52.8 英尺查询出的值。

谢谢

Answer 1

您可以将数据分组为 52.8 英尺的 block 。一种方法是将距离除以 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 英尺 block 的平均值:

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