目前,我有这张桌子:
CREATE TABLE `plant_data` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`plant_id` BIGINT(20) UNSIGNED NOT NULL,
`temperature` DECIMAL(5,1) UNSIGNED NOT NULL,
`light` SMALLINT(5) UNSIGNED NOT NULL,
`created_at` TIMESTAMP NULL DEFAULT NULL,
`updated_at` TIMESTAMP NULL DEFAULT NULL,
PRIMARY KEY (`id`),
)
使用以下示例数据:
INSERT INTO `plant_data` (`id`, `plant_id`, `temperature`, `light`, `created_at`, `updated_at`) VALUES (1623, 14, 22.2, 35, '2020-02-16 09:00:06', '2020-02-16 09:00:06');
INSERT INTO `plant_data` (`id`, `plant_id`, `temperature`, `light`, `created_at`, `updated_at`) VALUES (1622, 5, 22.8, 33, '2020-02-16 09:00:06', '2020-02-16 09:00:06');
INSERT INTO `plant_data` (`id`, `plant_id`, `temperature`, `light`, `created_at`, `updated_at`) VALUES (1621, 14, 22.8, 36, '2020-02-16 08:00:07', '2020-02-16 08:00:07');
INSERT INTO `plant_data` (`id`, `plant_id`, `temperature`, `light`, `created_at`, `updated_at`) VALUES (1620, 5, 23.3, 33, '2020-02-16 08:00:07', '2020-02-16 08:00:07');
INSERT INTO `plant_data` (`id`, `plant_id`, `temperature`, `light`, `created_at`, `updated_at`) VALUES (1619, 14, 23.1, 36, '2020-02-15 07:00:11', '2020-02-15 07:00:11');
INSERT INTO `plant_data` (`id`, `plant_id`, `temperature`, `light`, `created_at`, `updated_at`) VALUES (1618, 5, 23.8, 34, '2020-02-15 07:00:11', '2020-02-15 07:00:11');
INSERT INTO `plant_data` (`id`, `plant_id`, `temperature`, `light`, `created_at`, `updated_at`) VALUES (1617, 14, 24.4, 38, '2020-02-15 06:00:09', '2020-02-15 06:00:09');
INSERT INTO `plant_data` (`id`, `plant_id`, `temperature`, `light`, `created_at`, `updated_at`) VALUES (1616, 5, 24.6, 34, '2020-02-15 06:00:09', '2020-02-15 06:00:09');
我想将最后 X 天的平均值作为每一天的一行。我可以用每天的 X 查询来做到这一点,比如
SELECT plant_id, avg(temperature) FROM plant_data WHERE created_at >= '2020-02-16 00:00:00' AND created_at <= '2020-02-17 00:00:00' GROUP BY plant_id;
但我想知道,是否可以通过一次查询获取数据以获得如下结果:
+----------+------------------+------------+
| plant_id | avg(temperature) | day |
+----------+------------------+------------+
| 5 | 24.58000 | 2020-02-16 |
| 14 | 24.42000 | 2020-02-16 |
| 5 | 23.58000 | 2020-02-15 |
| 14 | 23.42000 | 2020-02-15 |
+----------+------------------+------------+
如果有人有这样做的好主意来节省我的查询时间,那就太好了。