ระยะห่างระหว่างเริ่มและวันสิ้นสุด BigQuery

Question 1

ใน BigQuery ฉันกำลังพยายามเขียน query นั่น calculates ระยะห่างระหว่างที่เริ่มและวันสิ้นสุดสำหรับแต่ละบัตรประจำตัวครรับ ฉันไม่สนใจคะแนนระหว่างพวกนั้นวันที่ช่วง. ฉันแค่ต้องหยิบ lat/นานของคนเริ่มและวันสิ้นสุดแล้วคำนวณ euclidean รักษาระยะห่าง

โต๊ะ 1 ดูเหมือนนี้:

    ID     startdate     enddate
    A      2016-9-16     2016-10-9
    A      2017-3-18     2017-4-9

โต๊ะ 2 มีพิกัดและดูเหมือนนี้:

ID  Date        Latitude      Longitude
A   2016-9-16   40.76        -109.33
A   2016-9-17   40.72        -109.33
A   2016-10-9   40.75        -109.33
A   2017-3-18   40.81        -109.33
A   2017-4-8    40.83        -109.33
A   2017-4-9    40.96        -109.32

ฉันปรารถนาผลลัพธ์ของมันคงดูเหมือน:

ID t1.startdate   t2.Latitude t2.Longitude t1.enddate t2.Latitude t2.Longitude  distance
A   2017-3-18    40.81       -109.33      2017-4-9    40.96       -109.32      150
A   2016-9-16    40.76       -109.33      2016-10-9   40.75      -109.33       200

นี่ใกล้ที่สุดเท่าที่ฉันเคยได้แต่งตั้งแต่นั้นสามารถเป็นหลายหมายเลขของมันไม่ตรงกับที่ถูกต้องวันเริ่มและวันสิ้นสุดสำหรับแต่ละตัว:

SELECT
t1,t2, 
ST_DISTANCE(Point1, Point2 ) as distance
from (
SELECT
    ID, 
      st_geogpoint(Longitude,Latitude) as Point1
      from `t2` AS t2 INNER JOIN  
      `t1` AS t1 ON t1.ID = t2.ID
        WHERE  t1.ID = t2.ID AND
        t2.Date = t1.startdate ) t1,
          (
   SELECT 
        ID, 
        st_geogpoint(Longitude, Latitude) as Point2
        from `t2` AS t2 INNER JOIN  
       `t1` AS t1 ON t1.ID = t2.ID
        WHERE  t1.ID = t2.ID AND
        t2.Date = t1.enddate
    ) t2
WHERE  t1.ID = t2.ID

ปัจจุบันผลตรวจดูเหมือน:

ID t1.startdate   t2.Latitude t2.Longitude t1.enddate t2.Latitude t2.Longitude  distance
A   2016-9-16    40.76       -109.33      2016-10-9    40.75       -109.33      150
A   2016-9-16    40.76       -109.33      2017-4-9     40.96      -109.32       250
A   2017-3-18    40.81       -109.33      2017-4-9     40.96      -109.32       200
A   2017-3-18    40.81       -109.33      2016-10-9    40.75      -109.33       250

Question 2

ดังนั้นนี่อาจจะแก้ปัญหาของคุณ:

ฉันใช้ t1 เป็นแหล่งข่าวแล้ว JOIN t2 สองครั้งในวันที่(เริ่มการสิ้นสุด)แล้วพวกนั้นอ้างอิงใน ST_DISTANCE. นั่นอาจเป็นเบนนี่ฮิลล์เลยวู้วไล่/อาจ performant ทางที่จะแก้ไขเรื่องนี้ได้

WITH t1 as (
        SELECT 'A' AS ID, '2016-9-16'AS startdate, '2016-10-9' AS enddate
        UNION ALL SELECT 'A','2017-3-18', '2017-4-9'
), t2 as (
SELECT 'A' AS ID,'2016-9-16' AS date, 40.76 AS Latitude, -109.33 AS Longitude    
UNION ALL SELECT 'A','2016-9-17',40.72,-109.33 
UNION ALL SELECT 'A','2016-10-9',40.75,-109.33
UNION ALL SELECT 'A','2017-3-18',40.81,-109.33
UNION ALL SELECT 'A','2017-4-8',40.83,-109.33
UNION ALL SELECT 'A','2017-4-9',40.96,-109.32
)

SELECT
  t1.ID, 
  start_point.date AS start_date,
  start_point.Longitude AS start_long,
  start_point.Latitude AS start_lat,
  end_point.date AS end_date,
  end_point.Longitude AS end_long,
  end_point.Latitude AS end_lat,
  ST_DISTANCE(
    ST_GEOGPOINT(start_point.Longitude,start_point.Latitude), 
    ST_GEOGPOINT(end_point.Longitude,end_point.Latitude)) AS distance

FROM t1 
JOIN t2 AS start_point
ON t1.id = start_point.id AND t1.startdate = start_point.date

JOIN t2 AS end_point
ON t1.id = end_point.id AND t1.enddate = end_point.date

ผลตรวจ:

แถว	หมายเลข	start_date	start_long	start_lat	end_date	end_long	end_lat	ระยะห่าง
1	เป็	2016-9-16	-109.33	40.76	2016-10-9	-109.33	40.75	1111.9510117740244
2	เป็	2017-3-18	-109.33	40.81	2017-4-9	-109.32	40.96	16700.437093959285

Cleanbeans · Answer 1 · 2021-11-24T01:32:33

ดังนั้นนี่อาจจะแก้ปัญหาของคุณ:

ฉันใช้ t1 เป็นแหล่งข่าวแล้ว JOIN t2 สองครั้งในวันที่(เริ่มการสิ้นสุด)แล้วพวกนั้นอ้างอิงใน ST_DISTANCE. นั่นอาจเป็นเบนนี่ฮิลล์เลยวู้วไล่/อาจ performant ทางที่จะแก้ไขเรื่องนี้ได้

WITH t1 as (
        SELECT 'A' AS ID, '2016-9-16'AS startdate, '2016-10-9' AS enddate
        UNION ALL SELECT 'A','2017-3-18', '2017-4-9'
), t2 as (
SELECT 'A' AS ID,'2016-9-16' AS date, 40.76 AS Latitude, -109.33 AS Longitude    
UNION ALL SELECT 'A','2016-9-17',40.72,-109.33 
UNION ALL SELECT 'A','2016-10-9',40.75,-109.33
UNION ALL SELECT 'A','2017-3-18',40.81,-109.33
UNION ALL SELECT 'A','2017-4-8',40.83,-109.33
UNION ALL SELECT 'A','2017-4-9',40.96,-109.32
)

SELECT
  t1.ID, 
  start_point.date AS start_date,
  start_point.Longitude AS start_long,
  start_point.Latitude AS start_lat,
  end_point.date AS end_date,
  end_point.Longitude AS end_long,
  end_point.Latitude AS end_lat,
  ST_DISTANCE(
    ST_GEOGPOINT(start_point.Longitude,start_point.Latitude), 
    ST_GEOGPOINT(end_point.Longitude,end_point.Latitude)) AS distance

FROM t1 
JOIN t2 AS start_point
ON t1.id = start_point.id AND t1.startdate = start_point.date

JOIN t2 AS end_point
ON t1.id = end_point.id AND t1.enddate = end_point.date

ผลตรวจ:

แถว	หมายเลข	start_date	start_long	start_lat	end_date	end_long	end_lat	ระยะห่าง
1	เป็	2016-9-16	-109.33	40.76	2016-10-9	-109.33	40.75	1111.9510117740244
2	เป็	2017-3-18	-109.33	40.81	2017-4-9	-109.32	40.96	16700.437093959285

ระยะห่างระหว่างเริ่มและวันสิ้นสุด BigQuery

คำถาม

คำตอบที่ดีที่สุด

ในภาษาอื่นๆ

หน้านี้อยู่ในภาษาอื่นๆ

ดังอยู่ในนี้หมวดหมู่

ดังคำถามอยู่ในนี้หมวดหมู่