查询至少连续三天下单的用户

题目需求

查询订单信息表(order_info)中最少连续3天下单的用户id，期望结果如下：

题目解析

• 第一步：用户一天可能存在多次下单的情况，所以需要对用户进行去重

去重方法如下：

--方法一：使用distinct
selectdistinctuser_id,create_date
from order_info;--方法二：使用group by
selectuser_id,create_date
from order_info
group by user_id, create_date;--方法二：使用窗口函數
selectuser_id,create_date
from
(selectuser_id,create_date,row_number() over (partition by user_id,create_date) rnfrom order_info
)t1
where rn=1;

• 第二步：具体见各个方案。

代码实现

• 方案一

  selectdistinct user_id
  from
  (selectuser_idfrom(selectuser_id,create_date,date_sub(create_date, row_number() over (partition by user_id order by create_date)) difffrom(selectuser_id,create_datefrom order_infogroup by user_id, create_date)t1)t2group by user_idhaving count(diff) >= 3
  )t3
  

• 方案二

  selectdistinct user_id
  from
  (selectuser_id,datediff(lead2, create_date) difffrom(selectuser_id,create_date,lead(create_date, 2, '9999-12-31') over (partition by user_id order by create_date) lead2from(selectuser_id,create_datefrom order_infogroup by user_id, create_date)t1)t2
  )t3
  where diff=2;
  

• 方案三

  selectdistinct user_id
  from
  (selectuser_id,ts,count(*) over (partition by user_id order by ts range between 86400 preceding and 86400 following) cntfrom(selectuser_id,unix_timestamp(create_date, 'yyyy-MM-dd') tsfrom(selectuser_id,create_datefrom order_infogroup by user_id, create_date)t1)t2
  )t3
  where cnt=3;