如何使用PostGIS来选择最近X个地点的平均价格

Question

我想找到任何一个家庭的平均天然气价格。这是我目前的桌子。

home_id  | geocoordinates
1        | 0101000020E61000005BB6D617097544
2        | 0101000020E61000005BB6D617097545
3        | 0101000020E61000005BB6D617097546
4        | 0101000020E61000005BB6D617097547
5        | 0101000020E61000005BB6D617097548
gas_price   |   geocoordinates
1           |   0101000020E61000005BB6D617097544
1           |   0101000020E61000005BB6D617097545
1           |   0101000020E61000005BB6D617097546
2           |   0101000020E61000005BB6D617097547
2           |   0101000020E61000005BB6D617097548
2           |   0101000020E61000005BB6D617097544
2           |   0101000020E61000005BB6D617097545
3           |   0101000020E61000005BB6D617097546
3           |   0101000020E61000005BB6D617097547
3           |   0101000020E61000005BB6D617097548
3           |   0101000020E61000005BB6D617097544
4           |   0101000020E61000005BB6D617097545
4           |   0101000020E61000005BB6D617097546
4           |   0101000020E61000005BB6D617097547

对于每个家庭，我想找到X个最接近的汽油价格的平均汽油价格。X=5时的示例：

home_id     |   average_of_closest_five_gas_prices
1           |   1.5
2           |   2.5
3           |   2.1
4           |   1.5
5           |   1.5

我想到了使用一个单独的home_id，但我很难想出如何为所有人做到这一点。

select avg(gas_price) from (
SELECT *
FROM gas_price
ORDER BY gas_price.geocoordinates <-> '0101000020E61000005BB6D617097544'
LIMIT 5
) as table_a

Answer 1

您可以使用横向联接来限制group by中组的大小。

select home_id, avg(gas_price)
from home,
lateral (
select gas_price
from gas_price
order by gas_price.geocoordinates <-> home.geocoordinates
limit 5
) x
group by home_id;

另一种选择是使用窗口函数：按home_id分区，按距离排序，只选择具有row_number() <= 5的行。

select home_id, avg(gas_price)
from (
select row_number() over w as r, *
from home h, gas_price g
window w as (partition by home_id order by g.geocoordinates <-> h.geocoordinates)
) x
where r <= 5
group by home_id;