题解 | #某宝店铺连续2天及以上购物的用户及其对应的天数#

#先求出下一天的日期及与上一天的偏移量，看是否等于1

select b.user_id user_id

,b.days_count days_count

from

(

select a.user_id user_id

,sum(if(datediff(a.dt2,a.dt1) = 1,1,0)) + 1 days_count

from

(

select user_id

,sales_date dt1

,lead(sales_date,1,sales_date) over(partition by user_id order by sales_date) dt2

from sales_tb s1

)a #下一次购物的日期表

group by 1

)b

where b.days_count >= 2

group by 1

优化代码

select b.user_id user_id

,b.days_count days_count

from

(

select a.user_id user_id

,sum(if(a.diff = 1,1,0)) + 1 days_count

from

(

select user_id

,sales_date dt1

,datediff(lead(sales_date,1,sales_date) over(partition by user_id order by sales_date),sales_date) diff

from sales_tb s1

)a #下一次购物的日期表

group by 1

)b

where b.days_count >= 2

group by 1

继续优化：报错

select user_id

,sum(if(datediff(lead(sales_date,1,sales_date) over(partition by user_id order by sales_date),sales_date)=1,1,0)) +1 days_count

from sales_tb s1

group by 1 #报错：You cannot use the window function 'lead' in this context.