hive 7天内连续3天

--buyStart 是 t_id在一段连续时间内的起始购买时间
--buyEnd 是 t_id在一段连续时间内的最后购买时间
select 
    t_id
    , date_add(max_date, min_rk) as t_buyStart
    , date_add(min_date, max_rk) as t_buyEnd
from (
    select
        t_id
        , date_diff
        , max(date_diff) as max_date
        , min(date_diff) as min_date
        , count(*) as buy_num
        , sum(t_amt) as t_amt
        , min(rk) as min_rk
        , max(rk) as max_rk
    from (
        SELECT
            t_id
            , t_date
            , date_sub(t_date, rk) date_diff
            , t_amt
            , rk
        from (
            SELECT 
                t_id
                , t_date
                , t_amt
                , rank() over(partition by t_id order by t_date) rk
            FROM (
                select
                    t_id
                    , t_date
                    , sum(t_amt) as t_amt
                from (
                    SELECT 
                        user_mobile as t_id
                        , to_date(order_bill_date) as t_date
                        , goods_sale_tax_act_amount as t_amt
                    from xxx.xxx
                    WHERE dt = '2021-08-09'
                ) as t1    
                group by t_id, t_date
            ) as t2
        ) as t3
    ) as t4
    group by t_id, date_diff
) as t5