sql - 查找组中第一行和其余行之间的距离

我试图找出第一条记录与按ID1分组的组的其余3条记录之间的距离。
例如(33.6008949，-83.83803)和(33.604248，-83.86729)之间的距离; (33.6008949，-83.83803)和(33.60586，-83.8711)之间的距离和
(33.6008949，-83.83803)和(33.6043777，-83.86624)之间的距离。

    Row  Id1     Id2          StartTime               StopTime                Latitude    Longitude  DateTime
    1     71     34         2016-11-21 00:41:05 UTC 2016-11-21 00:47:06 UTC 33.6008949  -83.83803    2016-11-21 00:43:42 UTC
    2     71     44         2016-11-21 00:54:55 UTC 2016-11-21 00:56:28 UTC 33.604248     -83.86729  2016-11-21 00:55:18 UTC
    3     71     45         2016-11-21 02:08:17 UTC 2016-11-21 02:09:52 UTC 33.60586      -83.8711   2016-11-21 02:09:03 UTC
    4     71     67         2016-11-21 02:16:02 UTC 2016-11-21 02:17:21 UTC 33.6043777  -83.86624    2016-11-21 02:16:28 UTC

为了计算点之间的距离，我使用以下函数:

  from math import radians, cos, sin, asin, sqrt
  def haversine(lon1, lat1, lon2, lat2):
      lon1, lat1, lon2, lat2 = map(radians, [lon1, lat1, lon2, lat2])
      dlon = lon2 - lon1
      dlat = lat2 - lat1
      a = sin(dlat/2)**2 + cos(lat1) * cos(lat2) * sin(dlon/2)**2
      c = 2 * asin(sqrt(a))
      km = 6367 * c
      return km

最佳答案

试试下面

#standardSQL
CREATE TEMPORARY FUNCTION distance(lat1 FLOAT64, lon1 FLOAT64, lat2 FLOAT64, lon2 FLOAT64)
RETURNS FLOAT64 AS ((
WITH constants AS (
  SELECT 0.017453292519943295 AS p
)
SELECT 12742 * ASIN(SQRT(
  0.5 - COS((lat2 - lat1) * p)/2 +
  COS(lat1 * p) * COS(lat2 * p) *
  (1 - COS((lon2 - lon1) * p))/2))
FROM constants
));

WITH YourTable AS (
  SELECT 1 AS Row, 71 AS Id1, 34 AS Id2, '2016-11-21 00:41:05 UTC' AS StartTime, '2016-11-21 00:47:06 UTC' AS StopTime, 33.6008949 AS Latitude, -83.83803 AS Longitude, '2016-11-21 00:43:42 UTC' AS DateTime UNION ALL
  SELECT 2 AS Row, 71 AS Id1, 44 AS Id2, '2016-11-21 00:54:55 UTC' AS StartTime, '2016-11-21 00:56:28 UTC' AS StopTime, 33.604248 AS Latitude, -83.86729 AS Longitude, '2016-11-21 00:55:18 UTC' AS DateTime UNION ALL
  SELECT 3 AS Row, 71 AS Id1, 45 AS Id2, '2016-11-21 02:08:17 UTC' AS StartTime, '2016-11-21 02:09:52 UTC' AS StopTime, 33.60586 AS Latitude, -83.8711 AS Longitude, '2016-11-21 02:09:03 UTC' AS DateTime UNION ALL
  SELECT 4 AS Row, 71 AS Id1, 67 AS Id2, '2016-11-21 02:16:02 UTC' AS StartTime, '2016-11-21 02:17:21 UTC' AS StopTime, 33.6043777 AS Latitude, -83.86624 AS Longitude, '2016-11-21 02:16:28 UTC' AS DateTime
)
SELECT *,
  distance(Latitude, Longitude, FIRST_VALUE(Latitude) OVER(PARTITION BY Id1 ORDER BY Id2), FIRST_VALUE(Longitude) OVER(PARTITION BY Id1 ORDER BY Id2)) AS dist
FROM YourTable
ORDER BY Id1, Id2