sql - Hive SQL - 如何从每个人中删除最大(日期)？

Question

我正在努力编写最有效的编写 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; ```

Answer 1

只是对部分代码的第一个建议

如果你想要最大天数，你不应该在 group by 中提及天数

select listener_id, device_id, max(day) day 
from devicetable b
where vendor_id = 42
and category = 'something' 
group by listener_id, device_id

是指数据库列还是指max的别名