Search code examples
sqlgoogle-bigquery

How to find the average distance between the locations

I have a table with source, destination and distance between the locations like as below

enter image description here

i want to calculate average distance between the locations like, for example if we take A to B route 1: 21 miles, route 2: 28 miles, route 3: 19 miles I am expecting results: A to B --> 22.66 miles enter image description here Thanks

Solution

  • Below is for BigQuery Standard SQL 

    #standardSQL
SELECT 
  LEAST(source, destination) source, 
  GREATEST(source, destination) destination, 
  AVG(distance) distance 
FROM `project.dataset.table`
GROUP BY source, destination

    You can test, play with above using sample data from your question as in example below 

    #standardSQL
WITH `project.dataset.table` AS (
  SELECT 'a' source, 'b' destination, 21 distance UNION ALL
  SELECT 'b', 'a', 28 UNION ALL
  SELECT 'a', 'b', 19 UNION ALL
  SELECT 'c', 'd', 15 UNION ALL
  SELECT 'c', 'd', 17 UNION ALL
  SELECT 'd', 'c', 16.5 UNION ALL
  SELECT 'd', 'c', 18 
)
SELECT 
  LEAST(source, destination) source, 
  GREATEST(source, destination) destination, 
  AVG(distance) distance 
FROM `project.dataset.table`
GROUP BY source, destination

    with result 

    Row source  destination distance     
1   a       b           22.666666666666668   
2   c       d           16.625