Styjun

I want to find the intersection of multiple queries on the time attribute.

I tried this inner joining of all of them, but it takes an extreme amount of time and never finishes.

 SELECT 
 pdx.time, 
 demand,
 pdx.temperature, 
 sea.temperature, 
 boi.temperature, 
 geg.temperature
 FROM capstone.aggregate_power_demands
 INNER JOIN 
 ( 
 SELECT time, temperature
 FROM capstone.weather
 WHERE location_name='PDX' AND time >= '1993' AND time <= '2018'
 ) as pdx
 ON aggregate_power_demands.time = pdx.time 
 INNER JOIN 
 ( 
 SELECT time, temperature 
 FROM capstone.weather
 WHERE location_name='SEA' AND time >= '1993' AND time <= '2018'
 ) as sea
 ON pdx.time = sea.time 
 INNER JOIN 
 ( 
 SELECT time, temperature 
 FROM capstone.weather
 WHERE location_name='BOI' AND time >= '1993' AND time <= '2018'
 ) as boi
 ON sea.time = boi.time
 INNER JOIN 
 ( 
 SELECT time, temperature
 FROM capstone.weather
 WHERE location_name='GEG' AND time >= '1993' AND time <= '2018'
 ) as geg
 ON boi.time = geg.time

Try this:

SELECT 
 aggregate_power_demands.time, 
 demand,
 pdx.temperature, 
 sea.temperature, 
 boi.temperature, 
 geg.temperature
FROM capstone.aggregate_power_demands
LEFT JOIN 
 (SELECT time, temperature
 FROM capstone.weather
 WHERE location_name='PDX' AND YEAR(time) BETWEEN '1993' AND '2018' 
GROUP BY location_name,time) as pdx
ON aggregate_power_demands.time = pdx.time 
LEFT JOIN 
 (SELECT time, temperature 
 FROM capstone.weather
 WHERE location_name='SEA' AND YEAR(time) BETWEEN '1993' AND '2018' 
GROUP BY location_name,time) as sea
ON aggregate_power_demands.time = sea.time 
LEFT JOIN 
 (SELECT time, temperature 
 FROM capstone.weather
 WHERE location_name='BOI' AND YEAR(time) BETWEEN '1993' AND '2018' 
GROUP BY location_name,time) as boi
ON aggregate_power_demands.time = boi.time
LEFT JOIN 
 (SELECT time, temperature
 FROM capstone.weather
 WHERE location_name='GEG' AND YEAR(time) BETWEEN '1993' AND '2018' 
GROUP BY location_name,time) as geg
ON aggregate_power_demands.time = geg.time;

You can also try this. it has only one join:

SELECT 
 agd.time
 , agd.demand
 , MAX( IF( w.location_name = 'PDX', w.temperature, NULL)) as pdx_temperature
 , MAX( IF( w.location_name = 'SEA', w.temperature, NULL)) as sea_temperature
 , MAX( IF( w.location_name = 'BOI', w.temperature, NULL)) as boi_temperature
 , MAX( IF( w.location_name = 'GEG', w.temperature, NULL)) as geg_temperature
FROM capstone.aggregate_power_demands agd
LEFT JOIN capstone.weather w ON agd.time = w.time
WHERE w.location_name IN ( 'PDX' , 'SEA' , 'BOI' , 'GEG' )
AND agd.time BETWEEN '1993-01-01 00:00:00' AND '2018-12-31 23:59:59'
GROUP BY w.location_name;