--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
