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

参考工作中的中的用法算连续逾期以及累积逾期

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;

样例数据： 

FS201610213258  4,5,6      3       3

FS201608225113  6,9          2       1