Contexto
Estoy escribiendo una aplicación que realiza una variación del problema de generación de rutas para vehículos. La aplicación cuenta con rutas, paradas e indicaciones de conducción para las rutas. Necesito escribir una consulta para una vista que combine todos los atributos relevantes para una ruta. Por lo tanto, necesito unir la tabla de rutas a múltiples relaciones de muchos a muchos en una sola consulta.
Detalles de consulta
Está la tabla de rutas, la tabla route_stop_join y la tabla de direcciones de ruta. La relación entre rutas y paradas es realmente de muchos a muchos, pero solo necesitamos una lista de identificadores de paradas, por lo que basta con considerar la relación de uno a muchos con la tabla de combinación. La siguiente consulta cuenta las sumas n veces donde n es el número de paradas:
select r.id, array_agg(j.stop_id) as stops, sum(rd.time_elapsed) as total_time, sum(rd.drive_distance) as total_distance from routes_directions rd right join routes r on rd.route_id = r.id left join routes_stops_join j on r.id = j.route_id group by r.id;Puedo hacer esto usando una subselección como esta:
select rj.id, rj.stops, sum(rd.time_elapsed) as total_time, sum(rd.drive_distance) as total_distance from routes_directions rd right join (select r.id, array_agg(j.stop_id) as stops from routes r left join routes_stops_join j on r.id = j.route_id group by r.id) rj on rj.id = rd.route_id group by rj.id, rj.stops;pero me gustaría ver si hay una manera de hacer esto en una sola consulta sin subselecciones.
En cuanto a esta consulta devuelve el 99% de la información que necesita:
select rd.id, sum(rd.time_elapsed) as total_time, sum(rd.drive_distance) as total_distance from routes_directions rd group by rd.id;Sugeriría usar una subconsulta o un CTE, pero usando LEFT JOIN en lugar de RIGHT JOIN.
create table routes(id int); insert into routes values (1),(2); create table routes_stops(route_id int, stop_id int); insert into routes_stops values (1,1),(1,2),(2,1),(2,3),(2,4); create table routes_directions(route_id int, dir_id int, time_elapsed int, drive_distance int); insert into routes_directions values (1,1,100,40),(1,2,60,60),(2,1,15,14),(2,3,20,30);
select rj.id, rj.stops, sum(rd.time_elapsed) as total_time, sum(rd.drive_distance) as total_distance from routes_directions rd left join (select r.id, array_agg(j.stop_id) as stops from routes r left join routes_stops j on r.id = j.route_id group by r.id) rj on rj.id = rd.route_id group by rj.id, rj.stops;identificación | paradas | tiempo_total | distancia total -: | :------ | ---------: | -------------: 2 | {1,3,4} | 35 | 44 1 | {1,2} | 160 | 100
with stp as ( select r.id, array_agg(j.stop_id) as stops from routes r left join routes_stops j on r.id = j.route_id group by r.id ) select rd.route_id, stp.stops, sum(rd.time_elapsed) as total_time, sum(rd.drive_distance) as total_distance from routes_directions rd left join stp on stp.id = rd.route_id group by rd.route_id, stp.stops;id_ruta | paradas | tiempo_total | distancia total -------: | :------ | ---------: | -------------: 1 | {1,2} | 160 | 100 2 | {1,3,4} | 35 | 44
dbfiddle aquí