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

select
     user_id,
     count(*) as days_count     
from (select  
    sales_date,user_id ,
    row_number() over(partition by user_id order by sales_date) as rk
from sales_tb) t group by  user_id,date_sub(sales_date,interval rk day) 
        HAVING COUNT(*) >= 2;

首先分区内排序 得到每个user_id的sales_date排名

date_sub表示销售日期减去排名 --> 这样连续的sales_date会得到1个相同的日期 即min(sales_date)-1 ；

-----------------------------------------------------------------------------------------------------

user_id sales_date rk sub(sales_date,interval rk day)

------------------------------------------------------------------------------------------------------

1 2021-01-05 1 2021-01-04

1 2021-01-06 2 2021-01-04

1 2021-01-06 3 2021-01-03

2 2021-01-05 1 2021-01-04

2 2021-01-07 2 2021-01-05

--------------------------------------------------------------------------------------------------------

根据user_id和min(sales_date)-1进行group by分组 得到每个user_id和min(sales-date)-1的count(*)

譬如上面这个例子，user_id和sub(sales_date,interval rk day)得到的分组如下：

------------------------------------------------------------------------------------------------------

user_id sub(sales_date,interval rk day) count(*)

------------------------------------------------------------------------------------------------------

1 2021-01-04 2

1 2021-01-03 1

2 2021-01-04 1

2 2021-01-05 1

------------------------------------------------------------------------------------------------------

题目要求连续天数是多少让having count(*) > 多少；