hive账单表算连续逾期以及累计逾期

样例数据： 

FS201610213258  4,5,6      3       3

FS201608225113  6,9          2       1

SELECT src.buss_no,

    concat_ws(',',collect_set(src.periods))    AS overude_periods,  #逾期中所有期数

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

    MAX(src.contiue_cnt)        AS contiue_cnt    #连续逾期

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, #订单号

            b.periods,  # 账单中期数

            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;