Hive综合案例练习（中级）第二题：查询至少连续三天下单的用户

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

题目需求

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

user_id
101

题目解析

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

去重方法如下：

--方法一：使用distinct
select
    distinct
    user_id,
    create_date
from order_info;

--方法二：使用group by
select
    user_id,
    create_date
from order_info
group by user_id, create_date;

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

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

代码实现

• 方案一

  select
      distinct user_id
  from
  (
      select
          user_id
      from
      (
          select
              user_id,
              create_date,
              date_sub(create_date, row_number() over (partition by user_id order by create_date)) diff
          from
          (
              select
                  user_id,
                  create_date
              from order_info
              group by user_id, create_date
          )t1
      )t2
      group by user_id
      having count(diff) >= 3
  )t3
  

• 方案二

  select
      distinct user_id
  from
  (
      select
          user_id,
          datediff(lead2, create_date) diff
      from
      (
          select
              user_id,
              create_date,
              lead(create_date, 2, '9999-12-31') over (partition by user_id order by create_date) lead2
          from
          (
              select
                  user_id,
                  create_date
              from order_info
              group by user_id, create_date
          )t1
      )t2
  )t3
  where diff=2;
  

• 方案三

  select
      distinct user_id
  from
  (
      select
          user_id,
          ts,
          count(*) over (partition by user_id order by ts range between 86400 preceding and 86400 following) cnt
      from
      (
          select
              user_id,
              unix_timestamp(create_date, 'yyyy-MM-dd') ts
          from
          (
              select
                  user_id,
                  create_date
              from order_info
              group by user_id, create_date
          )t1
      )t2
  )t3
  where cnt=3;