mysql - MySQL:选择一个表的行具有相同的时间戳或最接近+小于另一个表中的时间戳
问题描述
我有TableA
和Table
B 如下所示。目前,正如您在下面的查询中看到的那样,TableA
连接TableB
基于1 分钟内的所有box_id
行。TableB
timestamp
TableA
timestamp
我想修改这个查询,而不是选择TableB
行的 1 分钟内的TableA
行,我想选择TableB
具有等价物timestamp
或timestamp
最接近 + 小于timestamp
in 的行TableA
。
下面是我想要TableA
的一个例子。一旦实现了这一点,我就不会分组了,因为这没有必要。任何想法/帮助将不胜感激。TableB
Desired Table
rainfall, hail, weather
表A:
id box_id timestamp
373001645 1 2020-05-07 06:00:20
373001695 1 2020-05-07 06:02:26
373001762 1 2020-05-07 06:05:17
373001794 1 2020-05-07 06:06:38
373001810 2 2020-05-07 06:07:21
表B:
id box_id timestamp data
373001345 1 2020-05-07 06:00:20 {"R": 0.114, "H": 20.808}
373001395 1 2020-05-07 06:02:26 {"R": 0.12, "H": 15.544}
373001462 1 2020-05-07 06:03:01 {"R": 0.006, "H": 55.469}
373001494 1 2020-05-07 06:04:38 {"R": 0.004, "H": 51.85}
373001496 1 2020-05-07 06:05:18 {"R": 0.02, "H": 5.8965}
373001497 1 2020-05-07 06:06:39 {"R": 0.12, "H": 54.32}
373001510 2 2020-05-07 06:07:09 {"R": 0.34, "H": 1.32}
373001511 2 2020-05-07 06:07:29 {"R": 0.56, "H": 32.7}
所需表:
id timestamp rainfall hail weather
373001345 2020-05-07 06:00:20 0.114 20.808 {"Rainfall": 0.114, "Hail": 20.808}
373001395 2020-05-07 06:02:26 0.12 15.544 {"Rainfall": 0.12, "Hail": 15.544}
373001462 2020-05-07 06:05:17 0.004 51.85 {"Rainfall": 0.004, "Hail": 51.85}
373001494 2020-05-07 06:06:38 0.02 5.8965 {"Rainfall": 0.02, "Hail": 5.8965}
373001496 2020-05-07 06:07:21 0.34 1.32 {"Rainfall": 0.34, "Hail": 1.32}
询问:
SELECT tableA.id, tableA.timestamp AS timestamp,
(tableB.data->'$.R') as rainfall, (tableB.data->'$.H') as hail,
JSON_OBJECT("Hail", (tableB.data->'$.H'),"Rainfall", (tableB.data->'$.R')) AS weather
FROM tableA tableA
LEFT JOIN tableB tableB ON tableA.box_id = tableB.box_id
AND TIMESTAMPDIFF(MINUTE, tableB.timestamp, tableA.timestamp) BETWEEN -1 AND 1
WHERE
tableA.timestamp BETWEEN '2020-05-07 00:00:00' AND '2020-05-07 23:59:59'
GROUP BY tableA.id,rainfall,hail,weather
ORDER BY tableA.timestamp ASC;
解决方案
您可以在条件中使用相关子查询来查找小于或等于当前行时间戳ON
的最大值timestamp
,并基于此:tableB
tableA
JOIN
SELECT A.id,
A.timestamp AS timestamp,
B.data->'$.R' as rainfall,
B.data->'$.H' as hail,
JSON_OBJECT("Hail", B.data->'$.H',
"Rainfall", B.data->'$.R'
) AS weather
FROM tableA A
LEFT JOIN tableB B ON B.box_id = A.box_id
AND B.timestamp = (SELECT MAX(timestamp)
FROM tableB b2
WHERE b2.box_id = A.box_id
AND b2.timestamp <= A.timestamp)
ORDER BY A.timestamp
输出:
id timestamp rainfall hail weather
373001645 2020-05-07 06:00:20 0.114 20.808 {"Hail": 20.808, "Rainfall": 0.114}
373001695 2020-05-07 06:02:26 0.12 15.544 {"Hail": 15.544, "Rainfall": 0.12}
373001762 2020-05-07 06:05:17 0.004 51.85 {"Hail": 51.85, "Rainfall": 0.004}
373001794 2020-05-07 06:06:38 0.02 5.8965 {"Hail": 5.8965, "Rainfall": 0.02}
373001810 2020-05-07 06:07:21 0.34 1.32 {"Hail": 1.32, "Rainfall": 0.34}
推荐阅读
- javascript - 反应:将多个过滤器应用于数组
- javascript - 如何仅在发生 2 个 DOM 事件时执行 javascript 代码?
- xamarin - 用户付费但无法访问付费内容 Xamarin Forms InAppBilling
- c# - EF 6 如何使用多级包含
- redis - 计算或列出 redis 流中的所有 ACKnowledged 消息
- ios - WKWebView:表单未在 WKWebView (Swift) 中提交
- linux - Chefspec 在 Linux 上删除 Win32::Service
- apache-kafka - Flink 检查点不断失败(等待 InitProducerId 时超时)
- mysql - 如何计算按周分组的日期之间的多条记录的单天
- amazon-web-services - 如何在 Amazon Linux 2 上配置 nginx?