我的问题将其缩减到其核心部分,如何根据表日期输入显示合并的输出。
这确实是温度(和数百万线路(以外的东西,但我在这里编写了一些数字,只是为了说明问题。我有几个传感器,每个传感器都有一个ID号,每个传感器以某种随机的间隔发送值信息。传感器可以四处移动,因此我有一个单独的表(Sensordscriptions(,以跟踪何时移动传感器。那里的每条线都说传感器何时移动并且必须将其视为旧位置(并且必须在SensorDections中的首次输入到该传感器的首先记录值之前(。
所需的输出是时间,传感器名称和值的列表,我将其输入了DesiredOutput表中,但这是手工制作的(Typos保留( - 我需要产生它的查询," Select ... Where ... sv_date sv_date$ startdate和$ enddate之间"。
下面是两个示例表的文本版本(所有手工制作的"随机"值(和输出(在https://pastebin.com/88g777kim上保存了mysqldump(。
那么,我写的是什么使我的mysql/mariadb sql查询合并sensorValues和sensordscriptions,以便输出就像deSiredOutput?
sensorValues
+---------------------+-------+----------+
| sv_date | sv_id | sv_value |
+---------------------+-------+----------+
| 2017-01-01 10:00:00 | 1 | 24 |
| 2017-01-01 10:01:00 | 2 | 13 |
| 2017-01-01 10:05:00 | 1 | 24.1 |
| 2017-01-01 10:05:00 | 2 | 13.1 |
| 2017-01-01 10:10:00 | 1 | 24.2 |
| 2017-01-01 10:11:00 | 2 | 13.2 |
| 2017-01-01 10:12:00 | 2 | 13.3 |
| 2017-01-01 10:15:00 | 1 | 24.3 |
| 2017-01-01 10:17:00 | 2 | 13.4 |
| 2017-01-01 10:20:00 | 1 | 24.4 |
| 2017-01-01 10:23:00 | 2 | -5 |
| 2017-01-01 10:25:00 | 1 | 24.5 |
| 2017-01-01 10:30:00 | 1 | 24.6 |
| 2017-01-01 10:33:00 | 2 | -5.1 |
| 2017-01-01 10:35:00 | 1 | -4.7 |
| 2017-01-01 10:37:00 | 2 | -5.2 |
| 2017-01-01 10:38:00 | 2 | -5.2 |
| 2017-01-01 10:40:00 | 1 | -4.8 |
| 2017-01-01 10:41:07 | 1 | -3.1 |
| 2017-01-01 10:41:07 | 2 | 15.1 |
| 2017-01-01 10:45:00 | 1 | -4.9 |
| 2017-01-01 10:50:00 | 1 | -5 |
| 2017-01-01 10:50:00 | 2 | 20 |
| 2017-01-01 10:51:00 | 2 | 20.1 |
+---------------------+-------+----------+
sensordescriptions
+-------+---------------------+----------------+
| sv_id | sd_date | sd_description |
+-------+---------------------+----------------+
| 1 | 2017-01-01 09:00:00 | Kitchen |
| 1 | 2017-01-01 10:32:00 | Garage |
| 2 | 2017-01-01 09:00:00 | Garage |
| 2 | 2017-01-01 10:20:00 | Outside |
| 2 | 2017-01-01 10:40:00 | Basement |
+-------+---------------------+----------------+
DesiredOutput
+---------------------+----------+-------+
| sv_date | Location | Value |
+---------------------+----------+-------+
| 2017-01-01 10:00:00 | Kitchen | 24 |
| 2017-01-01 10:01:00 | Garage | 13 |
| 2017-01-01 10:05:00 | Garage | 13.1 |
| 2017-01-01 10:05:00 | Kitchen | 24.1 |
| 2017-01-01 10:10:00 | Kitchen | 24.2 |
| 2017-01-01 10:11:00 | Garage | 13.2 |
| 2017-01-01 10:12:00 | Garage | 13.3 |
| 2017-01-01 10:15:00 | Kitchen | 24.3 |
| 2017-01-01 10:17:00 | Garage | 13.4 |
| 2017-01-01 10:20:00 | Kitchen | 24.4 |
| 2017-01-01 10:23:00 | Outside | -5 |
| 2017-01-01 10:25:00 | Kitchen | 24.5 |
| 2017-01-01 10:30:00 | Kitchen | 24.6 |
| 2017-01-01 10:33:00 | Outside | -5.1 |
| 2017-01-01 10:35:00 | Garage | -4.7 |
| 2017-01-01 10:37:00 | Outside | -5.2 |
| 2017-01-01 10:38:00 | Outside | -5.2 |
| 2017-01-01 10:40:00 | Garage | -4.8 |
| 2017-01-01 10:41:07 | Basement | 15.1 |
| 2017-01-01 10:41:07 | Garage | -3.1 |
| 2017-01-01 10:45:00 | Garage | -4.9 |
| 2017-01-01 10:50:00 | Basement | 20 |
| 2017-01-01 10:50:00 | Garage | -5 |
| 2017-01-01 10:51:00 | Basement | 20.1 |
+---------------------+----------+-------+
---编辑---
我尝试了一个答案,但最终遇到了错误,但是在phpmyadmin重新格式化之后,我找到了错字并开始工作,谢谢。
MariaDB [test]> SELECT
-> sv_date,
-> sd_description AS location,
-> sv_value
-> FROM
-> SensorValues sv
-> INNER JOIN
-> (
-> SELECT
-> sd1.sv_id,
-> sd1.sd_date,
-> sd1.sd_description,
-> COALESCE(
-> MIN(sd2.sd_date),
-> '9999-12-31 23:59:59'
-> ) AS next_sd_date
-> FROM
-> SensorDescriptions sd1
-> LEFT JOIN
-> SensorDescriptions sd2
-> ON
-> sd1.sv_id = sd2.sv_id AND sd1.sd_date < sd2.sd_date
-> GROUP BY
-> sd1.sv_id,
-> sd1.sd_date,
-> sd1.sd_description
-> ) t
-> ON
-> sv.sv_id = t.sv_id AND sv.sv_date >= t.sd_date AND sv.sv_date < t.next_sd_date;
+---------------------+----------+----------+
| sv_date | location | sv_value |
+---------------------+----------+----------+
| 2017-01-01 10:00:00 | Kitchen | 24 |
| 2017-01-01 10:05:00 | Kitchen | 24.1 |
| 2017-01-01 10:10:00 | Kitchen | 24.2 |
| 2017-01-01 10:15:00 | Kitchen | 24.3 |
| 2017-01-01 10:20:00 | Kitchen | 24.4 |
| 2017-01-01 10:25:00 | Kitchen | 24.5 |
| 2017-01-01 10:30:00 | Kitchen | 24.6 |
| 2017-01-01 10:35:00 | Garage | -4.7 |
| 2017-01-01 10:40:00 | Garage | -4.8 |
| 2017-01-01 10:41:07 | Garage | -3.1 |
| 2017-01-01 10:45:00 | Garage | -4.9 |
| 2017-01-01 10:50:00 | Garage | -5 |
| 2017-01-01 10:01:00 | Garage | 13 |
| 2017-01-01 10:05:00 | Garage | 13.1 |
| 2017-01-01 10:11:00 | Garage | 13.2 |
| 2017-01-01 10:12:00 | Garage | 13.3 |
| 2017-01-01 10:17:00 | Garage | 13.4 |
| 2017-01-01 10:23:00 | Outside | -5 |
| 2017-01-01 10:33:00 | Outside | -5.1 |
| 2017-01-01 10:37:00 | Outside | -5.2 |
| 2017-01-01 10:38:00 | Outside | -5.2 |
| 2017-01-01 10:41:07 | Basement | 15.1 |
| 2017-01-01 10:50:00 | Basement | 20 |
| 2017-01-01 10:51:00 | Basement | 20.1 |
+---------------------+----------+----------+
24 rows in set (0.00 sec)
MariaDB [test]>
对于SensorDescriptions
表中的每个记录,我将获得下一个位置的时间戳(ID Any(,该时间戳将提供可用于将该表连接到SensorValues
表的间隔:
select sv_date, sd_description as location, sv_value
from SensorValues sv
inner join
(select sd1.sv_id,
sd1.sd_date,
sd1.sd_description,
coalesce(min(sd2.sd_date),'9999-12-31 23:59:59') as next_sd_date
from SensorDescriptions sd1
left join SensorDescriptions sd2 on sd1.sv_id=sd2.sv_id and sd1.sd_date<sd2.sd_date
group by sd1.sv_id, sd1.sd_date, sd1.sd_description) t on sv.sv_id=t.sd_id and sv.sv_date>=t.sd_date and sv.sv_date<t.next_sd_date