Hive中使用LAG()窗口函数过滤数据时如何保留首行记录

原因说明 #

分组内第一行记录通过lag()窗口函数获取相邻上一行的Event值时，返回结果为NULL，在SQL中NULL与任意值做不等比较的结果都为false，因此原有查询的过滤条件会直接把第一行排除，导致结果缺失。

调整方案 #

仅需修改外层查询的WHERE过滤条件，增加Prev_Event IS NULL的判断即可，修改后的完整SQL如下：

WITH mma AS
(
select VIN, 
       Mode, 
       Status,
       case when lower(Status) like '%authorized%' then 'Authorized' else 'Deauthorized' end Event,
       Start, 
       End 
  from ModemAuth 
 where VIN = 'ABC123456789'
 order by Start
 )

select mma2.*
from (select mma.*,
             lag(event) over (partition by VIN order by Start) as Prev_Event
        from mma
     ) mma2
where Prev_Event <> Event OR Prev_Event IS NULL

效果验证 #

修改后查询会同时保留分组第一行记录、以及所有Event值和上一行发生变化的记录，输出结果和你给出的预期结果完全一致。

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