I have the following query that brings the information of temperature and relative humidity per minute of a particular area:
SELECT plands.land AS Land,
ROUND(AVG(meteorology.temperature),2) AS Temperatureday,
ROUND(AVG(meteorology.humidity),2) AS Humidityday,
WEEKOFYEAR(meteorology.date) AS Week
FROM meteorology
INNER JOIN plands ON plands.id = meteorology.id_land
WHERE DATE(meteorology.date) BETWEEN '2016-09-12' AND '2016-09-23'
AND TIME(meteorology.date) >= '06:00:00'
AND TIME(meteorology.date) <= '18:00:00'
GROUP BY meteorology.id_land,
WEEKOFYEAR(meteorology.date),
YEAR(meteorology.date)
UNION ALL
SELECT plands.land AS Land,
ROUND(AVG(meteorology.temperature),2) AS TemperatureNight,
ROUND(AVG(meteorology.humidity),2) AS HumidityNight,
WEEKOFYEAR(meteorology.date) AS Week
FROM meteorology
INNER JOIN plands ON plands.id = meteorology.id_land
WHERE DATE(meteorology.date) BETWEEN '2016-09-12' AND '2016-09-23'
AND (TIME(meteorology.date) < '06:00:00'
OR TIME(meteorology.date) > '18:00:00')
GROUP BY meteorology.id_land,
WEEKOFYEAR(meteorology.date),
YEAR(meteorology.date)
It shows the following:
----------------------------------------------------------
Land | Temperatureday | Humidityday | Week |
----------------------------------------------------------
LAND1 | 16.13 | 92.03 | 37 |
----------------------------------------------------------
LAND1 | 16.46 | 84.35 | 38 |
----------------------------------------------------------
LAND1 | 9.37 | 99.0 | 37 |
----------------------------------------------------------
LAND1 | 9.95 | 99.0 | 38 |
----------------------------------------------------------
but I what to show the data as follows:
------------------------------------------------------------------------------
Land | Temperatureday | Humidityday |TemperatureNight | HumidityNigh |Week|
------------------------------------------------------------------------------
LAND1 | 16.13 | 92.03 | 9.37 | 99.0 | 37 |
------------------------------------------------------------------------------
LAND1 | 16.46 | 84.35 | 9.95 | 99.0 | 38 |
------------------------------------------------------------------------------
I know that my query is wrong, but I don´t know what I do.
Thanks!
Another way to join the data involves a subquery for the day data, to which you JOIN the night data as additional details with a second outer query. This works well in query engines that materialize a subquery into a temporary table, such as MySQL.
SELECT wxday.Land, Temperatureday, Humidityday,
ROUND(AVG(meteorology.temperature),2) AS TemperatureNight,
ROUND(AVG(meteorology.humidity),2) AS HumidityNight,
wxday.Week
FROM (
SELECT plands.land AS Land,
ROUND(AVG(meteorology.temperature),2) AS Temperatureday,
ROUND(AVG(meteorology.humidity),2) AS Humidityday,
WEEKOFYEAR(meteorology.date) AS Week,
YEAR(meteorology.date) AS Year
FROM meteorology
INNER JOIN plands ON plands.id = meteorology.id_land
WHERE DATE(meteorology.date) BETWEEN '2016-09-12' AND '2016-09-23'
AND TIME(meteorology.date) >= '06:00:00'
AND TIME(meteorology.date) <= '18:00:00'
GROUP BY meteorology.id_land,
WEEKOFYEAR(meteorology.date),
YEAR(meteorology.date)
) AS wxday
INNER JOIN meteorology
ON (wxday.Land = meteorology.id_land
AND WEEKOFYEAR(meteorology.date) = wxday.Week
AND YEAR(meteorology.date) = wxday.Year)
WHERE DATE(meteorology.date) BETWEEN '2016-09-12' AND '2016-09-23'
AND (TIME(meteorology.date) < '06:00:00'
OR TIME(meteorology.date) > '18:00:00')
GROUP BY wxday.Land, wxday.Week, wxday.Year
A second approach keeps the UNION, putting day and night data in separate output columns and using MIN to combine them afterward. This works because the MIN of a group with one nonnull value and one NULL is the one nonnull value. It may work better in cases where some weeks have night data but no day data or vice versa:
SELECT Land,
MIN(Temperatureday) AS Temperatureday,
MIN(Humidityday) AS Humidityday,
MIN(TemperatureNight) AS TemperatureNight,
MIN(HumidityNight) AS HumidityNight,
Week
FROM (
SELECT plands.land AS Land,
ROUND(AVG(meteorology.temperature),2) AS Temperatureday,
ROUND(AVG(meteorology.humidity),2) AS Humidityday,
NULL AS TemperatureNight,
NULL AS HumidityNight,
WEEKOFYEAR(meteorology.date) AS Week,
YEAR(meteorology.date) AS Year
FROM meteorology
INNER JOIN plands ON plands.id = meteorology.id_land
WHERE DATE(meteorology.date) BETWEEN '2016-09-12' AND '2016-09-23'
AND TIME(meteorology.date) >= '06:00:00'
AND TIME(meteorology.date) <= '18:00:00'
GROUP BY meteorology.id_land,
WEEKOFYEAR(meteorology.date),
YEAR(meteorology.date)
UNION ALL
SELECT plands.land AS Land,
NULL AS TemperatureNight,
NULL AS HumidityNight,
ROUND(AVG(meteorology.temperature),2) AS TemperatureNight,
ROUND(AVG(meteorology.humidity),2) AS HumidityNight,
WEEKOFYEAR(meteorology.date) AS Week,
YEAR(meteorology.date) AS Year
FROM meteorology
INNER JOIN plands ON plands.id = meteorology.id_land
WHERE DATE(meteorology.date) BETWEEN '2016-09-12' AND '2016-09-23'
AND (TIME(meteorology.date) < '06:00:00'
OR TIME(meteorology.date) > '18:00:00')
GROUP BY meteorology.id_land,
WEEKOFYEAR(meteorology.date),
YEAR(meteorology.date)
) AS itsownalias
GROUP BY Land, Week, Year
A third approach makes one pass through the data and uses AVG, relying on its identical NULL-skipping behavior. This can also be expressed without a subquery, but IsDay makes it more readable and more in line with the "Don't repeat yourself" principle.
SELECT Land,
ROUND(AVG(CASE IsDay WHEN 0 THEN NULL ELSE temperature END),2) AS Temperatureday,
ROUND(AVG(CASE IsDay WHEN 0 THEN NULL ELSE humidity END),2) AS Humidityday,
ROUND(AVG(CASE IsDay WHEN 0 THEN temperature ELSE NULL END),2) AS TemperatureNight,
ROUND(AVG(CASE IsDay WHEN 0 THEN humidity ELSE NULL END),2) AS HumidityNight,
WEEKOFYEAR(meteorology.date) AS Week
FROM (
SELECT plands.land AS Land,
meteorology.temperature,
meteorology.humidity,
WEEKOFYEAR(meteorology.date) AS Week,
YEAR(meteorology.date) AS Year,
CASE WHEN TIME(meteorology.date) >= '06:00:00'
AND TIME(meteorology.date) <= '18:00:00'
THEN 1 ELSE 0 END AS IsDay
FROM meteorology
INNER JOIN plands ON plands.id = meteorology.id_land
WHERE DATE(meteorology.date) BETWEEN '2016-09-12' AND '2016-09-23'
) AS itsownalias
GROUP BY Land,
WEEKOFYEAR(meteorology.date),
YEAR(meteorology.date)
Join your 2 queries instead of unioning them:
SELECT day.Week, day.land,
day.Temperature, day.Humidity,
night.Temperature, night.Humidity,
FROM
(
SELECT plands.land AS Land,
ROUND(AVG(meteorology.temperature),2) AS Temperature,
ROUND(AVG(meteorology.humidity),2) AS Humidity,
WEEKOFYEAR(meteorology.date) AS Week
FROM meteorology
INNER JOIN plands ON plands.id = meteorology.id_land
WHERE DATE(meteorology.date) BETWEEN '2016-09-12' AND '2016-09-23'
AND TIME(meteorology.date) >= '06:00:00'
AND TIME(meteorology.date) <= '18:00:00'
GROUP BY meteorology.id_land,
WEEKOFYEAR(meteorology.date),
YEAR(meteorology.date)
) day LEFT JOIN (
SELECT plands.land AS Land,
ROUND(AVG(meteorology.temperature),2) AS Temperature,
ROUND(AVG(meteorology.humidity),2) AS Humidity,
WEEKOFYEAR(meteorology.date) AS Week
FROM meteorology
INNER JOIN plands ON plands.id = meteorology.id_land
WHERE DATE(meteorology.date) BETWEEN '2016-09-12' AND '2016-09-23'
AND (TIME(meteorology.date) < '06:00:00'
OR TIME(meteorology.date) > '18:00:00')
AND meteorology.id_tenant = 1
GROUP BY meteorology.id_land,
WEEKOFYEAR(meteorology.date),
YEAR(meteorology.date)
) night
ON day.land=night.land
AND day.Week=Night.Week
Note that this is not very efficient, and a better solution would be to implement a single pass through the data - but the code for this is rather messy....something along the lines of....
SELECT plands.land as Land,
ROUND(SUM(CASE WHEN TIME(meteorology.date) >= '06:00:00'
AND TIME(meteorology.date) <= '18:00:00'
THEN meteorology.temperature
ELSE 0 END)
/
SUM(CASE WHEN TIME(meteorology.date) >= '06:00:00'
AND TIME(meteorology.date) <= '18:00:00'
THEN 1
ELSE 0 END), 2) AS DayTemperature,
...