Average per hour adding an empty record if data is missing

I have this data:

temp_humid
+---------+------+-------+---------------------+
| source  | temp | humid | stamp               |
+---------+------+-------+---------------------+
| cuisine | 10.0 |  70.0 | 2019-07-21 20:21:56 |
| chambre | 20.0 |  80.0 | 2019-07-21 20:23:43 |
| cuisine | 11.0 |  71.0 | 2019-07-21 20:01:56 |
| chambre | 21.0 |  81.0 | 2019-07-21 20:04:11 |
| chambre | 20.0 |  72.0 | 2019-07-21 21:09:11 |
| cuisine | 30.0 |  92.0 | 2019-07-21 21:11:56 |
| cuisine | 22.0 |  72.0 | 2019-07-21 21:01:56 |
| chambre | 34.0 |  94.0 | 2019-07-21 21:04:12 |
| chambre | 20.0 |  72.0 | 2019-07-21 23:09:11 |
| cuisine | 30.0 |  92.0 | 2019-07-21 23:11:56 |
| cuisine | 22.0 |  72.0 | 2019-07-21 23:01:56 |
| chambre | 34.0 |  94.0 | 2019-07-21 23:04:12 |
+---------+------+-------+---------------------+

I need to store the average of each hour in a second table.
I don’t know if I can also insert a NULL record if there is no data for an hour, maybe in a second treatment.

temp_humid_total
+----+--------------+---------------+--------------+---------------+---------------------+
| id | cuisine_temp | cuisine_humid | chambre_temp | chambre_humid | stamp               |
+----+--------------+---------------+--------------+---------------+---------------------+
|  1 | 10.5         | 70.5          | 20.5         | 80.5          | 2019-07-21 20:00:00 |
|  2 | 26.0         | 82            | 27.0         | 83            | 2019-07-21 21:00:00 |
|  3 | NULL         | NULL          | NULL         | NULL          | 2019-07-21 22:00:00 |
|  4 | 26.0         | 82            | 27.0         | 83            | 2019-07-21 23:00:00 |
+----+--------------+---------------+--------------+---------------+---------------------+

I have tried with this first treatment but can’t do it.

INSERT INTO temp_humid_total(cuisine_temp, cuisine_humid, chambre_temp, chambre_humid, stamp) VALUES
(
    (SELECT AVG(temp), AVG(humid)
        FROM temp_humid
        WHERE source="cuisine"
        GROUP BY YEAR(stamp), MONTH(stamp), DAY(stamp), HOUR(stamp)
    ),
    (SELECT AVG(temp), AVG(humid), stamp
        FROM temp_humid
        WHERE source="chambre"
        GROUP BY YEAR(stamp), MONTH(stamp), DAY(stamp), HOUR(stamp)
    )
);

I get an error message and don’t know which operand it is.

ERROR 1241 (21000): Operand should contain 1 column(s)

Creation of datasets:

CREATE TABLE `temp_humid` (
  `source` enum('chambre','cuisine') NOT NULL,
  `temp` decimal(3,1) NOT NULL,
  `humid` decimal(4,1) NOT NULL,
  `stamp` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `temp_humid` (`source`, `temp`, `humid`, `stamp`) VALUES
('cuisine', '10.0', '70.0', '2019-07-21 20:21:56'),
('chambre', '20.0', '80.0', '2019-07-21 20:23:43'),
('cuisine', '11.0', '71.0', '2019-07-21 20:01:56'),
('chambre', '21.0', '81.0', '2019-07-21 20:04:11'),
('chambre', '20.0', '72.0', '2019-07-21 21:09:11'),
('cuisine', '30.0', '92.0', '2019-07-21 21:11:56'),
('cuisine', '22.0', '72.0', '2019-07-21 21:01:56'),
('chambre', '34.0', '94.0', '2019-07-21 21:04:12'),
('cuisine', '20.0', '72.0', '2019-07-21 23:09:11'),
('chambre', '30.0', '92.0', '2019-07-21 23:11:56'),
('cuisine', '22.0', '72.0', '2019-07-21 23:01:56'),
('chambre', '34.0', '94.0', '2019-07-21 23:04:12');

CREATE TABLE `temp_humid_total` (
  `id` int(11) NOT NULL,
  `cuisine_temp` decimal(3,1) DEFAULT NULL,
  `cuisine_humid` decimal(4,1) DEFAULT NULL,
  `chambre_temp` decimal(3,1) DEFAULT NULL,
  `chambre_humid` decimal(4,1) DEFAULT NULL,
  `stamp` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `temp_humid_total`
  ADD PRIMARY KEY (`id`);
ALTER TABLE `temp_humid_total`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

Answer

This looks like conditional aggregation:

select date_format(stamp, '%Y-%m-%d %H') as yyyymmddhh,
       avg(case when source = 'cuisine' then temp end) as cuisine_temp,
       avg(case when source = 'cuisine' then temp end) as cuisine_humid,
       avg(case when source = 'chambre' then temp end) as chambre_temp,
       avg(case when source = 'chambre' then temp end) as chambre_humid
from temp_humid
group by yyyymmdd;

Advertisement

Answer