给出以下数据：

CREATE TABLE dat (dt STRING, uxt BIGINT, temp FLOAT, city STRING);
INSERT INTO dat VALUES
('1/1/2000 0:53', 946687980, 100, 'A'),
('1/1/2000 0:59', 946688340, 28.9, 'A'),
('1/1/2000 13:54', 946734840, -1, 'A'),
('1/1/2000 13:55', 946734900, 30.9, 'A'),
('1/1/2000 22:53', 946767180, 30.9, 'A'),
('1/1/2000 22:59', 946767540, 30, 'A'),
('1/2/2000 1:25', 946776300, 121, 'A'),
('1/2/2000 1:53', 946777980, 28.9, 'A'),
('1/2/2000 2:53', 946781580, 28.9, 'A'),
('1/3/2000 1:53', 946864380, 10, 'A'),
('1/3/2000 11:20', 946898400, 15.1, 'A'),
('1/3/2000 11:53', 946900380, 18, 'A'),
('1/3/2000 21:00', 946933200, 17.1, 'A'),
('1/3/2000 21:53', 946936380, 16, 'A');

我正在使用一些窗口功能查找每24小时的maxtemp、mintemp等：

select dt, uxt, maxtemp, mintemp, ABS(maxtemp - mintemp) as tempDiff, city
from(
select dt, uxt, max(temp) over (w) as maxtemp, min(temp) over (w) as 
mintemp, city
from dat
WINDOW w as (partition by city order by uxt range between CURRENT ROW and 
86400 FOLLOWING))t1
order by tempDiff desc;

这将提供以下输出（第一行）：

dt                   uxt      maxtemp   mintemp tempdiff    city
2000-01-01 13:54    946734840   121.0   -1.0    122.0        A

我想添加'dt'的时候，maxtemp发生的输出，并努力找到一个解决方案。
输出的第一行如下所示：

dt                   uxt      maxtemp   mintemp tempdiff    city   maxdt   
2000-01-01 13:54    946698780   121        -1    122         A     '2000-01-02 01:25'

使用第一个\u值查询：

select dt
  ,uxt
  ,max(temp) over w as maxtemp
  ,min(temp) over w as mintemp
  ,abs(max(temp) over w - min(temp) over w) as tempDiff
  ,first_value(dt) over (w order by temp desc) as maxdt
  ,city
from dat
order by tempDiff desc
WINDOW w as (partition by city order by uxt 
         range between CURRENT ROW and 86400 FOLLOWING);

正在生成此输出（前两行）：

dt               uxt        maxtemp mintemp tempdiff maxdt          city
1/1/2000 0:59    946688340  121.0   -1.0    122.0    1/2/2000 1:53  A
1/1/2000 0:53    946687980  121.0   -1.0    122.0    1/1/2000 0:53   A

它给出的maxtemp不在24小时日期范围内。另外，第二行1/1/2000 0:53的温度不是121.0