樣例數(shù)據(jù)：?

FS201610213258 ?4,5,6 ? ? ?3 ? ? ? 3

FS201608225113 ?6,9 ? ? ? ? ?2 ? ? ? 1

SELECT src.buss_no,

? ? concat_ws(',',collect_set(src.periods))? ? AS overude_periods,? #逾期中所有期數(shù)

? ? MAX(src.cnt)? ? ? ? ? ? ? ? AS max_overdue_cnt,? #累積逾期

? ? MAX(src.contiue_cnt)? ? ? ? AS contiue_cnt? ? #連續(xù)逾期

FROM (

? ? SELECT ta.buss_no,

? ? ? ? ta.periods,

? ? ? ? ta.cnt,

? ? ? ? ROW_NUMBER() OVER(PARTITION BY ta.buss_no,(ta.periods-ta.cnt ) ORDER BY ta.cnt) AS contiue_cnt

? ? FROM (

? ? ? ? SELECT b.buss_no, #訂單號(hào)

? ? ? ? ? ? b.periods,? # 賬單中期數(shù)

? ? ? ? ? ? ROW_NUMBER() OVER(PARTITION BY b.buss_no ORDER BY b.periods) cnt? #排序

? ? ? ? FROM dw.dwd_fyd_bills b

? ? ? ? WHERE b.dt = '20170601'

? ? ? ? ? ? AND b.status IN ('03','04','05')

? ? ) ta

) src group by src.buss_no limit 100;