题解 | #某宝店铺连续2天及以上购物的用户及其对应的天数#
某宝店铺连续2天及以上购物的用户及其对应的天数
http://www.nowcoder.com/practice/63ac3be0e4b44cce8dd2619d2236c3bf
连续问题我感觉我已经彻底悟了，嘻嘻，开心
--用row_number排序
select sales_date
,user_id
,ROW_NUMBER()over(PARTITION BY user_id ORDER BY sales_date) rk
from sales_tb
--作差
select *
,sales_date-rk diffdate
from(
select sales_date
,user_id
,ROW_NUMBER()over(PARTITION BY user_id ORDER BY sales_date) rk
from sales_tb
)a
--分组
select user_id,diffdate,count(diffdate)
from (
select *
,sales_date-rk diffdate
from(
select sales_date
,user_id
,ROW_NUMBER()over(PARTITION BY user_id ORDER BY sales_date) rk
from sales_tb
)a
)a
group by user_id,diffdate
having count(diffdate)>=2
