clickhouse计算前后两点间经纬度距离
问题
计算如图所示前后两点经纬度的距离？
方法
1、用开窗函数将如图所示数据下移一行
selectlongitude lon1,latitude lat1,min(longitude) over(order by time1 asc rows between 1 PRECEDING and 1 PRECEDING) lon2,min(latitude) over(order by time1 asc rows between 1 PRECEDING and 1 PRECEDING) lat2
fromtable1
完整sql
selecttime1 ,lon1,lat1,lon2,lat2,greatCircleDistance(a.lon1,a.lat1,a.lon2,a.lat2) distance
from(selecttime1,longitude lon1,latitude lat1,min(longitude) over(order by time1 asc rows between 1 PRECEDING and 1 PRECEDING) lon2,min(latitude) over(order by time1 asc rows between 1 PRECEDING and 1 PRECEDING) lat2fromtable1
3、简化版sql
selecttime1 ,longitude,latitude,greatCircleDistance(longitude , latitude ,min(longitude) over(order by time1 asc rows between 1 PRECEDING and 1 PRECEDING),min(latitude) over(order by time1 asc rows between 1 PRECEDING and 1 PRECEDING)) distance
fromtable1