我正在努力用最有效的方式编写sql查询，即消除每个人的max（day）。我试过了 where where day < max(day) 但是我们的hadoop环境不允许这样。
基本上，我们的目标是选择一个人谁拥有相同类型的手机谁切换到同一供应商在过去570天。
有什么建议吗 table1 查询的一部分？

with table2 as 
(select listener_id, device_id, max(day) day from 
devicetable b
where vendor_id = 42
and category = 'something' 
group by listener_id, device_id, day) -- max day for each person

,table1 as 
(select listener_id, device_id, ROW_NUMBER () over (PARTITION BY listener_id, device_id order by day desc) rowno from
    (select listener_id, device_id, day from devicetable
        where vendor_id=42 and category = 'something'
        group by listener_id, device_id, day)
where rowno <> 1)

insert into finaltable
select a.listener_id
from table1 a
left join 
table2 b
on a.listener_id = b.listener_id
where datediff (a.day, b.day) <=570 and a.day <= b.day -- setting the difference 
and a.device_id <> b.device_id
 and b.listener_id is not null; ```