题目:
题解:
WITH tmp as(
SELECT
sku_id,
create_date,
--从日期中减去一个变量,连续登录的ds值相等
date_sub(create_date,rn) ds,
rn
FROM(
--统计商品下单日期,并对日期做排序
SELECT
sku_id,
create_date,
row_number() over(PARTITION by sku_id ORDER by create_date) rn
from order_detail
GROUP by sku_id,create_date
)t1
)
SELECT
sku_id,
date_add(ds,rn) start_date,--前面减去了,这里需要加回来
date_add(ds,rn - 1 + CAST(during_date as int)) end_date--during_date最小是1,需要从0开始加
from(
SELECT
sku_id,
ds,
min(rn) rn,--同一组取最小的排序值
count(1) during_date--如果连续登录则ds值相等,这里能算连续登录天数,起始+连续就是最终
from tmp
GROUP by sku_id,ds
)t1