I have a database of weather values (1 row per minute) for the last year containing more than 1/2 million rows. I have written a series of SQL queries which find the max or min etc of a column for each day and then finds the time at which it occurs. The data is then written to a temp table while it finds the same info for the next variable. One all the variables have been written to temp tables the query joins the tables to provide a daily highs and lows table. This takes about 4 min to run. is there a way to be more efficient in my coding? This was the best I could come up with so far as i have not training in this area. Thanks! Daniel
CREATE TEMPORARY TABLE IF NOT EXISTS table1 AS (SELECT a.date, a.time AS max_temp_time, a.temperature as max_temp
FROM Live a
INNER JOIN (
SELECT DATE, MAX( temperature ) MTotal
FROM Live
GROUP BY DATE
)aMax ON a.date = aMax.date
AND a.temperature = aMax.MTotal
GROUP BY DATE
);
Drop table if exists records.Daily_Records;
CREATE TEMPORARY TABLE IF NOT EXISTS table2 AS (SELECT a.date as mindewdate, a.time AS min_dewpoint_time, a.dew_point_temperature as min_dew
FROM Live a
INNER JOIN (
SELECT DATE, MIN( dew_point_temperature ) MTotal
FROM Live
GROUP BY DATE
)aMax ON a.date = aMax.date
AND a.dew_point_temperature = aMax.MTotal
GROUP BY mindewdate
);
CREATE TEMPORARY TABLE IF NOT EXISTS table3 AS (SELECT a.date as maxdewdate, a.time AS max_dewpoint_time, a.dew_point_temperature as max_dew
FROM Live a
INNER JOIN (
SELECT DATE, MAX( dew_point_temperature ) MTotal
FROM Live
GROUP BY DATE
)aMax ON a.date = aMax.date
AND a.dew_point_temperature = aMax.MTotal
GROUP BY maxdewdate
);
CREATE TEMPORARY TABLE IF NOT EXISTS table4 AS (SELECT a.date as mintempdate, a.time AS min_temp_time, a.temperature as min_temp
FROM Live a
INNER JOIN (
SELECT DATE, MIN( temperature ) MTotal
FROM Live
GROUP BY DATE
)aMax ON a.date = aMax.date
AND a.temperature = aMax.MTotal
GROUP BY mintempdate
);
CREATE TEMPORARY TABLE IF NOT EXISTS table5 AS (SELECT a.date as wchilldate, a.time AS windchill_time, a.windchill
FROM Live a
INNER JOIN (
SELECT DATE, MIN( windchill ) MTotal
FROM Live
GROUP BY DATE
)aMax ON a.date = aMax.date
AND a.windchill = aMax.MTotal
GROUP BY wchilldate
);
CREATE TEMPORARY TABLE IF NOT EXISTS table6 AS (SELECT a.date as heat_index_date, a.time AS heat_index_time, a.heat_index
FROM Live a
INNER JOIN (
SELECT DATE, MAX( heat_index ) MTotal
FROM Live
GROUP BY DATE
)aMax ON a.date = aMax.date
AND a.heat_index = aMax.MTotal
GROUP BY heat_index_date
);
CREATE TEMPORARY TABLE IF NOT EXISTS table7 AS (SELECT a.date as max_outdoor_humidity_date, a.time AS max_hum_time, a.outdoor_humidity as max_hum
FROM Live a
INNER JOIN (
SELECT DATE, MAX( outdoor_humidity ) MTotal
FROM Live
GROUP BY DATE
)aMax ON a.date = aMax.date
AND a.outdoor_humidity = aMax.MTotal
GROUP BY max_outdoor_humidity_date
);
CREATE TEMPORARY TABLE IF NOT EXISTS table8 AS (SELECT a.date as min_outdoor_humidity_date, a.time AS min_hum_time, a.outdoor_humidity as min_hum
FROM Live a
INNER JOIN (
SELECT DATE, MIN( outdoor_humidity ) MTotal
FROM Live
GROUP BY DATE
)aMax ON a.date = aMax.date
AND a.outdoor_humidity = aMax.MTotal
GROUP BY min_outdoor_humidity_date
);
CREATE TEMPORARY TABLE IF NOT EXISTS table9 AS (SELECT a.date as max_rain_rate_date, a.time AS rainrate_time, a.rain_rate as rainrate
FROM Live a
INNER JOIN (
SELECT DATE, MAX( rain_rate ) MTotal
FROM Live
GROUP BY DATE
)aMax ON a.date = aMax.date
AND a.rain_rate = aMax.MTotal
GROUP BY max_rain_rate_date
);
CREATE TEMPORARY TABLE IF NOT EXISTS table10 AS (SELECT a.date as max_daily_rainfall_date, a.time AS max_daily_rainfall_time, a.daily_rainfall as rainfall
FROM Live a
INNER JOIN (
SELECT DATE, MAX( daily_rainfall ) MTotal
FROM Live
GROUP BY DATE
)aMax ON a.date = aMax.date
AND a.daily_rainfall = aMax.MTotal
GROUP BY max_daily_rainfall_date
);
CREATE TEMPORARY TABLE IF NOT EXISTS table11 AS (SELECT a.date as max_gust_windspeed_date, a.time AS gust_time, a.gust_windspeed as gust
FROM Live a
INNER JOIN (
SELECT DATE, MAX( gust_windspeed ) MTotal
FROM Live
GROUP BY DATE
)aMax ON a.date = aMax.date
AND a.gust_windspeed = aMax.MTotal
GROUP BY max_gust_windspeed_date
);
CREATE TEMPORARY TABLE IF NOT EXISTS table12 AS (SELECT a.date as max_average_windspeed_date, a.time AS wind_time, a.average_windspeed as wind
FROM Live a
INNER JOIN (
SELECT DATE, MAX( average_windspeed ) MTotal
FROM Live
GROUP BY DATE
)aMax ON a.date = aMax.date
AND a.average_windspeed = aMax.MTotal
GROUP BY max_average_windspeed_date
);
CREATE TEMPORARY TABLE IF NOT EXISTS table13 AS (SELECT a.date as max_barometer_date, a.time AS max_barometer_time, a.barometer as max_barometer
FROM Live a
INNER JOIN (
SELECT DATE, MAX( barometer ) MTotal
FROM Live
GROUP BY DATE
)aMax ON a.date = aMax.date
AND a.barometer = aMax.MTotal
GROUP BY max_barometer_date
);
CREATE TEMPORARY TABLE IF NOT EXISTS table14 AS (SELECT a.date as min_barometer_date, a.time AS min_barometer_time, a.barometer as min_barometer
FROM Live a
INNER JOIN (
SELECT DATE, MIN( barometer ) MTotal
FROM Live
GROUP BY DATE
)aMax ON a.date = aMax.date
AND a.barometer = aMax.MTotal
GROUP BY min_barometer_date
);
CREATE TEMPORARY TABLE IF NOT EXISTS table15 AS (SELECT a.date as max_actual_solar_reading_date, a.time AS max_solar_time, a.actual_solar_reading as solar
FROM Live a
INNER JOIN (
SELECT DATE, MAX( actual_solar_reading ) MTotal
FROM Live
GROUP BY DATE
)aMax ON a.date = aMax.date
AND a.actual_solar_reading = aMax.MTotal
GROUP BY max_actual_solar_reading_date
);
CREATE TEMPORARY TABLE IF NOT EXISTS table16 AS (SELECT a.date as davis_vp_uv_date, a.time AS max_uv_time, a.davis_vp_uv as uv
FROM Live a
INNER JOIN (
SELECT DATE, MAX( davis_vp_uv ) MTotal
FROM Live
GROUP BY DATE
)aMax ON a.date = aMax.date
AND a.davis_vp_uv = aMax.MTotal
GROUP BY davis_vp_uv_date
);
DROP TABLE IF EXISTS records.Daily_Records;
CREATE TABLE records.Daily_Records AS (SELECT date, max_temp, max_temp_time,min_temp,min_temp_time, windchill,windchill_time, max_dew, max_dewpoint_time, min_dew,min_dewpoint_time,heat_index,heat_index_time,max_hum,max_hum_time,min_hum,min_hum_time,rainrate,rainrate_time,rainfall,max_daily_rainfall_time,gust,gust_time,wind,wind_time,max_barometer,max_barometer_time,min_barometer,min_barometer_time,solar,max_solar_time,uv,max_uv_time
FROM `table1`
JOIN `table2` on table2.mindewdate = table1.date
JOIN `table3` on table3.maxdewdate = table1.date
JOIN `table4` on table4.mintempdate = table1.date
JOIN `table5` on table5.wchilldate = table1.date
JOIN `table6` on table6.heat_index_date = table1.date
JOIN `table7` on table7.max_outdoor_humidity_date = table1.date
JOIN `table8` on table8.min_outdoor_humidity_date = table1.date
JOIN `table10` on table10.max_daily_rainfall_date = table1.date
JOIN `table11` on table11.max_gust_windspeed_date = table1.date
JOIN `table12` on table12.max_average_windspeed_date = table1.date
JOIN `table13` on table13.max_barometer_date = table1.date
JOIN `table14` on table14.min_barometer_date = table1.date
JOIN `table15` on table15.max_actual_solar_reading_date = table1.date
JOIN `table16` on table16.davis_vp_uv_date = table1.date);
