一份登陆日志,查到今天每个人倒数第二次登陆的时间、地点、ip等信息?
问题解法 :避免开窗函数导致的数据倾斜,先求最大,再求次大
正确解法
with
tmp_max as (
select
uid,
max(appr_time) as appr_time
from table_a
group by uid
)
select
uid,
max(app_time) as sec_appr_time
from table_a
left join top_max
on table_a.uid = tmp_max.uid
where table_a.appr_time != tmp_max.appr_time
group by
uid