row_number() over (partition by idno order by id desc)
在获取分区信息的时候,row_number比group by 好用多了。因为row_number可以返回分区多少行以内的整行信息。
eg:
%hive
drop table if exists yll_tmp.loan_consumer_application_idno_mob;
create table yll_tmp.loan_consumer_application_idno_mob as
select
loan.*
from
(select
loan.id,
loan.aid as yuanshi_aid,–ke yi bu yao
loan.idno,
loan.mob,
loan.apt,
loan.apply_time,
row_number() over (partition by loan.idno, loan.mob order by loan.id desc) rn
from
yll_tmp.repay_by_idno_mob loan –寻找相关订单
left outer join
yll_tmp.loan_app_flow_loan fl
on loan.id = fl.aid) loan
where loan.rn = 1