我有一个表,按日期时间记录天气数据变量,如下所示:
|------------------|------------| ----
| LogDateTime | Temp | ...
+------------------|------------| ----
| 2020-01-01 00:00 | 20.1 | ...
| 2020-01-01 00:05 | 20.1 | ...
| 2020-01-01 00:10 | 19.9 | ...
| 2020-01-01 00:15 | 19.8 | ...
---------------------------------------
从该表中,我想像这样返回每天最高温度的最早时间(只是日期时间值的时间部分(:
|------------|----------------------
| LogDate | LogTime| MaxTemp
+---------------------|--------------
| 2020-01-01 | 14:00 | 24.5
| 2020-01-02 | 15:12 | 23.2
| 2020-01-03 | 10:12 | 25.1
| 2020-01-04 | 12:14 | 28.8
--------------------------------
到目前为止,我必须返回的查询如下,但它返回每天的最早温度,而不是每天最高温度的最早出现
SELECT TIME(a.LogDateTime), a.Temp
FROM Monthly a
INNER JOIN (
SELECT TIME(LogDateTime), LogDateTime, MAX(Temp) Temp
FROM Monthly
GROUP BY LogDateTime
) b ON a.LogDateTime = b.LogDateTime AND a.Temp= b.Temp
GROUP BY DATE(a.LogDateTime)
然后,我想使用该查询每天更新一行的表,该表使用如下所示的查询汇总最小值和最大值,但更新时间而不是实际最高温度:
UPDATE Dayfile AS d
JOIN (
SELECT DATE(LogDateTime) AS date, MAX(Temp) AS Temps
FROM Monthly
GROUP BY date
) AS m ON DATE(d.LogDate) = m.date
SET d.MaxTemp = m.Temps
您的 MariaDB 版本支持窗口函数,因此请使用ROW_NUMBER()
:
select LogDateTime, Temp
from (
select *,
row_number() over (partition by date(LogDateTime) order by Temp desc, LogDateTime) rn
from Monthly
) t
where t.rn = 1
查看简化的演示。
使用它来更新Dayfile
,如下所示:
update Dayfile d
inner join (
select LogDateTime, Temp
from (
select *,
row_number() over (partition by date(LogDateTime) order by Temp desc, LogDateTime) rn
from Monthly
) t
where t.rn = 1
) m on date(d.LogDate) = m.date
set d.MaxTemp = m.Temp