不良情况:
CREATE TABLE IF NOT EXISTS tmp12 AS
WITH tmp_table AS
(
SELECT dt, time, LEAD(time,1,'99999') OVER(PARTITION BY urs) AS last_time, urs, tag FROM
(
SELECT dt, urs, time, tag, count(1) FROM table0 LIMIT 10000
) t1
GROUP BY dt, urs, time, tag
ORDER BY urs, time
)
然后
select * from tmp12
我得到错误的数据,例如:
WRONG RESULT(dt, time, last_time, urs)
有一些last_time〈time。
当我删除CREATE TABLE时,我得到了一个很好的案例:
SELECT dt, time, LEAD(time,1,'99999') OVER(PARTITION BY urs) AS last_time, urs, tag FROM
(
SELECT dt, urs, time, tag, count(1) FROM table0 LIMIT 10000
) t1
GROUP BY dt, urs, time, tag
ORDER BY urs, time
正确结果如下:
GOOD RESULT(dt, time, last_time, urs)
所有的最后一次。
为什么?我只是创建了一个表,然后从表中选择,但是last_time出错了?
1条答案
按热度按时间hmae6n7t1#
你能试试这个吗:
我猜窗口函数中缺少的order by是问题所在。