Fabian Sierra Fabian Sierra - 2 months ago 5
MySQL Question

Query to bring data day and night sql

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!

Answer

Subquery for the day data, to which night data is joined

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

Union of two tables with different nonnull columns

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

Excluding from an average using conditional null

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)