SELECT seg + 1 AS CURTERMSPASTDUE
,con_no
FROM (SELECT con_no
,to_number(term_no) - rn AS term
,MIN(to_number(term_no)) AS start_term
,MAX(to_number(term_no)) AS end_term
,MAX(to_number(term_no)) - MIN(to_number(term_no)) AS seg
FROM (SELECT t.*
,row_number() over(PARTITION BY con_no, IF_DUR_TERM ORDER BY to_number(term_no)) AS rn
FROM W_TAB_LOAN_AMT t
WHERE IF_DUR_TERM = 1
AND REPAY_DATE <=v_etl_date)
GROUP BY to_number(term_no) - rn
,con_no
)
where (end_term,con_no ) IN (
SELECT MAX(term_no) as end_term,con_no
FROM W_TAB_LOAN_AMT
WHERE REPAY_DATE = v_etl_date
AND IF_DUR_TERM =1
group by con_no
)
--如果这段sql取不出数,就是0
2.累计逾期期数:
直接 sum(case when IF_DUR_TERM =1 then 1 else NULL end )
3.最高逾期期数
SELECT MAX(seg) + 1 AS maxtermspastdue
,con_no
FROM (SELECT con_no
,to_number(term_no) - rn AS term
,MIN(to_number(term_no)) AS start_term
,MAX(to_number(term_no)) AS end_term
,MAX(to_number(term_no)) - MIN(to_number(term_no)) AS seg
FROM (SELECT t.*
,row_number() over(PARTITION BY con_no,IF_DUR_TERM ORDER BY to_number(term_no)) AS rn
FROM W_TAB_LOAN_AMT t
WHERE IF_DUR_TERM = 1
)
GROUP BY to_number(term_no) - rn
,con_no
)
GROUP BY con_no