目前,我有这张桌子:
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 |
+----------+------------------+------------+
如果有人有这样做的好主意来节省我的查询时间,那就太好了。
首先,一个非常好的第一篇文章。ddl 和示例数据都可以轻松重新创建设置。其次,您对查询所要做的就是添加日期维度:
您可能还对 BETWEEN 谓词感兴趣:
没查过,但是有风险说day是保留字。此外,我更喜欢为我的标识符添加更多含义,所以我可能会选择:
作为发布 ddl 和插入数据的补充,您还可以使用 - 例如 - Fiddle。像这样的网站还有很多,我这里选择了这个,没有什么特别的原因。