求最近连续问题SQL

-- 第四步:求出现最多的diff
SELECT
    uid,
    MAX(count_uv)

FROM(
    -- 第三步：计算日期与排序的diff
    SELECT
        uid,
        DATE_SUB(dt, interval row_rank day ) AS new_dt,
        COUNT(1) AS count_uv

    FROM (
        -- 第二步：将每个用户的登录，按时间排序
        SELECT
            uid,
            dt,
            row_number() over( PARTITION BY uid ORDER BY dt) AS row_rank

        FROM(
            -- 第一步：筛选出每个用户最近7天的记录，每个用户每天只保留一条记录
            SELECT
                DISTINCT uid,
                dt
            FROM con_login
            WHERE TIMESTAMPDIFF(DAY,dt,NOW()) <= 7
        ) t1
    ) t2
    GROUP BY uid,DATE_SUB(dt, interval row_rank day)
) t3
GROUP BY uid;

 -- 车险最近连续投保年数
/*
    1,按身份证分组按起保时间排序
    2,起保时间-排序号，如果连续会相等，不连续不相等，根据这个分组求连续个数
    3,按身份证分组取最近连续投保年数
    整合如下：
    SELECT IDENTIFYNUMBER,COUNT_UV FROM
    (SELECT IDENTIFYNUMBER,DATE_SUB(START_DATE, INTERVAL ROW_RANK YEAR) AS NEW_DT,COUNT(1) AS COUNT_UV FROM
    (SELECT IDENTIFYNUMBER,APPL_DT,START_DATE,ROW_NUMBER() OVER(PARTITION BY IDENTIFYNUMBER ORDER BY START_DATE) AS ROW_RANK FROM
    (SELECT DISTINCT IDENTIFYNUMBER,SUBSTRING(APPL_FORM_NO,4,5) AS APPL_DT,START_DATE,RENEW_FLAG FROM BDPMART.DM_CUSTPORTRAIT_CAR_BASE WHERE RENEW_FLAG='1') T1) T2 GROUP BY IDENTIFYNUMBER,DATE_SUB(START_DATE, INTERVAL ROW_RANK YEAR)) T3 ORDER BY NEW_DT DESC LIMIT 1
*/
DROP TABLE IF EXISTS BDPMART.DM_CUSTPORTRAIT_CAR_LTOU;
CREATE TABLE BDPMART.DM_CUSTPORTRAIT_CAR_LTOU DISTRIBUTED BY ('IDENTIFYNUMBER') AS
(
SELECT IDENTIFYNUMBER,APPL_DT,START_DATE,ROW_NUMBER() OVER(PARTITION BY IDENTIFYNUMBER ORDER BY START_DATE) AS ROW_RANK FROM
(SELECT DISTINCT IDENTIFYNUMBER,SUBSTRING(APPL_FORM_NO,4,5) AS APPL_DT,START_DATE,RENEW_FLAG FROM BDPMART.DM_CUSTPORTRAIT_CAR_BASE WHERE RENEW_FLAG='1') T1
);

DROP TABLE IF EXISTS BDPMART.DM_CUSTPORTRAIT_CAR_LTOU_01;
CREATE TABLE BDPMART.DM_CUSTPORTRAIT_CAR_LTOU_01 DISTRIBUTED BY ('IDENTIFYNUMBER') AS
(
SELECT IDENTIFYNUMBER,DATE_SUB(START_DATE, INTERVAL ROW_RANK YEAR) AS NEW_DT,COUNT(1) AS COUNT_UV FROM
BDPMART.DM_CUSTPORTRAIT_CAR_LTOU GROUP BY IDENTIFYNUMBER,DATE_SUB(START_DATE, INTERVAL ROW_RANK YEAR)
);

DROP TABLE IF EXISTS BDPMART.DM_CUSTPORTRAIT_CAR_LTOU_YEAR;
CREATE TABLE BDPMART.DM_CUSTPORTRAIT_CAR_LTOU_YEAR DISTRIBUTED BY ('IDENTIFYNUMBER') AS
(
SELECT IDENTIFYNUMBER,
CASE
WHEN COUNT_UV=0 THEN '0年'
WHEN COUNT_UV=1 THEN '1年'
WHEN COUNT_UV=2 THEN '2年'
ELSE '3年及以上' END AS LTOU_YEAR
FROM
(SELECT IDENTIFYNUMBER,NEW_DT,COUNT_UV,ROW_NUMBER() OVER(PARTITION BY IDENTIFYNUMBER ORDER BY NEW_DT DESC) AS RN FROM BDPMART.DM_CUSTPORTRAIT_CAR_LTOU_01) T WHERE T.RN=1
);