MySQL面试题_两地多次通行平均距离计算

本文链接：https://blog.csdn.net/yixiu92/article/details/127028589

问题是这样的，有出发地到目的地的多次通行距离数据，需要通过SQL语句求出两地之间的平均通行距离。比如成都到重庆、重庆到成都都视为这两地之间的形成，需要合并计算，数据如下↓

下面用两种思路来解答，第一种思路是先以出发地和目的地进行聚合操作，求出合计的距离和次数；然后通过简单的窗口函数，对上面这个聚合的表格进行一次JOIN操作，通过简单的逻辑判断得到我们想要的结果。

先进行第一步的聚合操作，这一步就比较简单了，通过GROUP BY就完成了，只是我们还需要在加一个ROW_NUMBER函数来计算出编号后面使用，SQL语句和结果如下↓

SELECT  start,end,  SUM(distance) AS tot_distance,  COUNT(*) AS no_of_time,  ROW_NUMBER() OVER(ORDER BY start) as idFROM  `distance`GROUP BY  start,end

接下来就是对上面这个表格进行JOIN处理，使用两次上面这个结果，用第一个表格的start字段去JOIN第二个表格的end字段，我们就可以判断出相关两地之间的距离；但这里会出现两次，所有还需要加一个条件，第一个表格的id<第二个表格的id，SQL语句和结果如下↓

WITH cte AS  (SELECT    start,end,    SUM(distance) AS tot_distance,    COUNT(*) AS no_of_time,    ROW_NUMBER() OVER(ORDER BY start) as id  FROM    `distance`  GROUP BY    start,end)SELECT  t1.start,t1.end,  t1.tot_distance,t2.tot_distance,t1.no_of_time,t2.no_of_timeFROM  cte AS t1JOIN cte AS t2 ON t1.start = t2.end AND t1.id < t2.id

基本上就实现了，我们只需要把最后的距离和次数加起来然后相除就可以了，最后的结果如下↓

第一种思路就是实现了，但是我们这里的数据各地之间是不相重合的，如果我们再增加一个成都到上海的路径，就会出问题了，所以我们还有第二种思路。

我们先增加两行数据，成都-上海的数据，数据如下↓

然后我们通过连接语句，把出发和目的地连接，这样就是唯一的标识了，SQL语句和结果如下↓

SELECT  CONCAT(start,"-",end) AS start,  CONCAT(end,"-",start) AS end,  distanceFROM  distance

然后通过次互换出发地和目的地的连接，就可以得到两地之间的距离和次数，在稍微计算一下就得到了平均距离和，SQL语句和结果如下↓

WITH ts AS  (SELECT    CONCAT(start,"-",end) AS start,    CONCAT(end,"-",start) AS end,    distance  FROM    distance)SELECT start,SUM(distance),COUNT(*),SUM(distance)/COUNT(*) AS avg_dist FROM(SELECT start,distance FROM ts AS t1UNION ALLSELECT end,distance FROM ts AS t1) tssGROUP BY  start

当然最后我们还可以把第一列拆分一下，只需要在上面的基础上用字符拆分函数就行了，SQL语句和结果如下↓

(WITH ts1 AS  (WITH ts AS    (SELECT      CONCAT(start,"-",end) AS start,      CONCAT(end,"-",start) AS end,      distance    FROM      distance)  SELECT start,SUM(distance)/COUNT(*) AS avg_dist FROM  (SELECT start,distance FROM ts AS t1  UNION ALL  SELECT end,distance FROM ts AS t1) tss  GROUP BY    start)SELECT  SUBSTRING_INDEX(start,"-",1) AS start,  SUBSTRING_INDEX(start,"-",-1) AS end,  ROUND(avg_dist,2) AS avg_distFROM  ts1)

到了这里，我们是不是可以通过合并的方式使开始-出发地不重复，就可以通过第一种方式来解决了，SQL语句和结果如下↓

WITH cte AS  (SELECT    start,end,    SUM(distance) AS tot_distance,    COUNT(*) AS no_of_time,    ROW_NUMBER() OVER(ORDER BY start) as id  FROM    (SELECT    CONCAT(start,"-",end) AS start,    CONCAT(end,"-",start) AS end,    distance    FROM    distance) ts  GROUP BY    start,end)SELECT  t1.start,  (t1.tot_distance+t2.tot_distance)/(t1.no_of_time+t2.no_of_time) AS avg_distFROM  cte AS t1JOIN cte AS t2 ON t1.start = t2.end AND t1.id < t2.id