के बीच की दूरी के शुरू और अंत तिथि BigQuery

0

सवाल

BigQuery में, मैं कोशिश कर रहा हूँ लिखने के लिए एक क्वेरी के बीच दूरी की गणना शुरू और अंत की तारीख के लिए प्रत्येक आईडी. मैं नहीं कर रहा हूँ में रुचि के अंक के बीच में उन तारीख के बीच है । मैं केवल जरूरत हड़पने के लिए अक्षांश/लंबे समय के शुरू और अंत की तारीख और इयूक्लिडियन दूरी की गणना.

तालिका 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
google-bigquery sql
2021-11-23 19:03:52
1

सबसे अच्छा जवाब

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
2021-11-24 01:32:33

अन्य भाषाओं में

यह पृष्ठ अन्य भाषाओं में है

Русский
..................................................................................................................
Italiano
..................................................................................................................
Polski
..................................................................................................................
Română
..................................................................................................................
한국어
..................................................................................................................
Français
..................................................................................................................
Türk
..................................................................................................................
Česk
..................................................................................................................
Português
..................................................................................................................
ไทย
..................................................................................................................
中文
..................................................................................................................
Español
..................................................................................................................
Slovenský
..................................................................................................................

इस श्रेणी में लोकप्रिय

लोकप्रिय सवाल इस श्रेणी में