华为面试题：
表：app_oper_log，
字段：userid,appid,operType,opertime
其中，opertype取值为install或者uninstall，代表安装和卸载
生成表spp_stage，存储每个用户安装了哪些应用
select userid,appid from
(select userid,appid,opertype,
row_number() over (partition by userid,appid order by opertime desc)paixu
from app_oper_log
)
where operType='install'
and paixu= 1
或者
select userid,appid
from
(select userid,appid,
first_value(opertype) over (partition by userid,appid order by opertime desc) opertypeend
from app_oper_log
group by userid,appid,opertypeend)
where opertypeend='install';
网友评论