使用CREATE TABLE存储含LEAD函数的查询结果时数据异常原因排查

核心原因 #

你的问题根源在于窗口函数LEAD的OVER子句未指定窗口内的排序规则，同时混淆了查询末尾的ORDER BY和窗口函数的排序逻辑：

1. 窗口函数LEAD的计算依赖窗口内的行顺序，但你只写了PARTITION BY urs，没有添加ORDER BY定义窗口内的行排序规则。此时Hive会以数据的物理存储顺序处理窗口，这个顺序是随机且不稳定的。
2. 直接查询时末尾的ORDER BY urs, time只是对最终结果集做展示排序，并没有影响LEAD函数的计算过程——只是排序后的结果刚好让你觉得last_time > time正确，这其实是巧合。
3. 用CREATE TABLE AS SELECT写入表时，CTE里的ORDER BY不会改变窗口函数的计算逻辑，而且Hive写入表的数据是无序存储的。后续查询tmp12时如果不加ORDER BY，返回的行顺序随机，就会暴露LEAD计算时的顺序问题，出现last_time < time的错误。

修复方案 #

在LEAD函数的OVER子句中明确添加窗口内的排序规则，确保LEAD取的是当前行之后的下一个时间值：

CREATE TABLE IF NOT EXISTS tmp12 AS
WITH tmp_table AS
(
SELECT dt, time, 
       LEAD(time,1,'99999') OVER(PARTITION BY urs ORDER BY time) AS last_time, 
       urs, tag 
FROM
    (
        SELECT dt, urs, time, tag, count(1) FROM table0 LIMIT 10000
    ) t1
GROUP BY dt, urs, time, tag
)
SELECT * FROM tmp_table;

补充说明 #

• 窗口函数的排序必须在OVER()子句内通过ORDER BY定义，外部的ORDER BY只影响结果集的展示顺序，不影响窗口函数的计算。
• Hive的表存储本身是无序的，无论写入时是否排序，查询时如果需要特定顺序，必须显式添加ORDER BY。

内容的提问来源于stack exchange，提问作者qiu