1# mob3 提取
create table test.cqg5 as
select max(e.overday),id,min(months),name,phone from
(select bill_no,max(overdays) as overday from (
select
bill_no,settle_time,grance_date,pre_repay_date,pre_repay_amt,repayment_amt,dates,
(case when dict_repay_plan_status in (2,9) then datediff(dates,grance_date)
when dict_repay_plan_status in (6,8) and settle_time <= dates then datediff(settle_time,grance_date)
when dict_repay_plan_status in (6,8) and settle_time > dates then datediff(dates,grance_date)
else 0 end)as overdays from(
select a.*,b.dates from
(SELECT bill_no,current_period,dict_repay_plan_status,
from_unixtime(unix_timestamp(settle_time,'yyyy-MM-dd HH:mm:ss'),'yyyy-MM-dd') as settle_time,
from_unixtime(unix_timestamp(grance_date,'yyyy/MM/dd'),'yyyy-MM-dd') as grance_date,
from_unixtime(unix_timestamp(pre_repay_date,'yyyy/MM/dd'),'yyyy-MM-dd') as pre_repay_date,
pre_repay_amt,repayment_amt FROM clx_loan.dwd_fund_plat_repay_plan where
product_id ="2eedd3aa930d44d788fe9912d27d1ef6" or
product_id ="937f29750ea54192b51702ecfa6a785c" group by
bill_no,current_period,dict_repay_plan_status,settle_time,grance_date,
pre_repay_date,pre_repay_amt,repayment_amt) as a
left join
(select bill_no,add_months(grance_date,3) as dates from
(SELECT bill_no,current_period,dict_repay_plan_status,
from_unixtime(unix_timestamp(settle_time,'yyyy-MM-dd HH:mm:ss'),'yyyy-MM-dd') as settle_time,
from_unixtime(unix_timestamp(grance_date,'yyyy/MM/dd'),'yyyy-MM-dd') as grance_date,
from_unixtime(unix_timestamp(pre_repay_date,'yyyy/MM/dd'),'yyyy-MM-dd') as pre_repay_date,
pre_repay_amt,repayment_amt FROM clx_loan.dwd_fund_plat_repay_plan where
product_id ="2eedd3aa930d44d788fe9912d27d1ef6" or
product_id ="937f29750ea54192b51702ecfa6a785c" group by
bill_no,current_period,dict_repay_plan_status,settle_time,grance_date,
pre_repay_date,pre_repay_amt,repayment_amt) as b1 where current_period =1) as b
on a.bill_no=b.bill_no) as c where grance_date <= dates) as d group by bill_no) as e
left join
(SELECT bill_no,SHA2(borrower_id_no,256) as id,
SHA2(borrower_name,256) as name,SHA2(borrower_phone,256) as phone,
date_format(loan_date,'yyyy-MM-dd') as months
FROM clx_loan.dwd_fund_plat_bill where flow_name = "乐信" group by
bill_no,SHA2(borrower_id_no,256),
SHA2(borrower_name,256),SHA2(borrower_phone,256),
date_format(loan_date,'yyyy-MM-dd')) as h
on e.bill_no=h.bill_no group by id,name,phone

2、历史提取

create table test.cqg4 as
select max(e.overday),id,min(months),name,phone from
(select bill_no,max(overdays) as overday from (
select
bill_no,settle_time,grance_date,pre_repay_date,pre_repay_amt,repayment_amt,dates,
(case when dict_repay_plan_status in (2,9) then datediff(dates,grance_date)
when dict_repay_plan_status in (6,8) and settle_time <= dates then datediff(settle_time,grance_date)
when dict_repay_plan_status in (6,8) and settle_time > dates then datediff(dates,grance_date)
else 0 end)as overdays from(
select a.*,b.dates from
(SELECT bill_no,current_period,dict_repay_plan_status,
from_unixtime(unix_timestamp(settle_time,'yyyy-MM-dd HH:mm:ss'),'yyyy-MM-dd') as settle_time,
from_unixtime(unix_timestamp(grance_date,'yyyy/MM/dd'),'yyyy-MM-dd') as grance_date,
from_unixtime(unix_timestamp(pre_repay_date,'yyyy/MM/dd'),'yyyy-MM-dd') as pre_repay_date,
pre_repay_amt,repayment_amt FROM clx_loan.dwd_fund_plat_repay_plan where
product_id ="2eedd3aa930d44d788fe9912d27d1ef6" or
product_id ="937f29750ea54192b51702ecfa6a785c" group by
bill_no,current_period,dict_repay_plan_status,settle_time,grance_date,
pre_repay_date,pre_repay_amt,repayment_amt) as a
left join
(select bill_no,max(grance_date) as dates from
(SELECT bill_no,current_period,dict_repay_plan_status,
from_unixtime(unix_timestamp(settle_time,'yyyy-MM-dd HH:mm:ss'),'yyyy-MM-dd') as settle_time,
from_unixtime(unix_timestamp(grance_date,'yyyy/MM/dd'),'yyyy-MM-dd') as grance_date,
from_unixtime(unix_timestamp(pre_repay_date,'yyyy/MM/dd'),'yyyy-MM-dd') as pre_repay_date,
pre_repay_amt,repayment_amt FROM clx_loan.dwd_fund_plat_repay_plan where
product_id ="2eedd3aa930d44d788fe9912d27d1ef6" or
product_id ="937f29750ea54192b51702ecfa6a785c" group by
bill_no,current_period,dict_repay_plan_status,settle_time,grance_date,
pre_repay_date,pre_repay_amt,repayment_amt) as b1 group by bill_no) as b
on a.bill_no=b.bill_no) as c where grance_date <= dates) as d group by bill_no) as e
left join
(SELECT bill_no,SHA2(borrower_id_no,256) as id,
SHA2(borrower_name,256) as name,SHA2(borrower_phone,256) as phone,
date_format(loan_date,'yyyy-MM-dd') as months
FROM clx_loan.dwd_fund_plat_bill where flow_name = "乐信" group by
bill_no,SHA2(borrower_id_no,256),
SHA2(borrower_name,256),SHA2(borrower_phone,256),
date_format(loan_date,'yyyy-MM-dd')) as h
on e.bill_no=h.bill_no group by id,name,phone

3Hive中的两个知识

select *,row_number()over() as 序号 from test.cqg25
collect_set()