大数据技术之Hive SQL题库-中级5

-- 判断是否为连续两个月
select
  distinct t3.sku_id
from
  (
    select
      t2.sku_id,
      count(*)over(partition by t2.sku_id,t2.rymd) cn
    from
      (
        select
          t1.sku_id,
          add_months(t1.ymd,-row_number()over(partition by t1.sku_id order by t1.ymd)) rymd
        from
          (
           select
             sku_id,
             concat(substring(create_date,0,7),'-01') ymd,
             sum(price*sku_num)  sku_sum
           from
             order_detail
           where 
             sku_id=1 or sku_id=2
           group by
             sku_id,substring(create_date,0,7)
           having
             (sku_id=1 and sku_sum>=21000) or (sku_id=2 and sku_sum>=10000)
        )t1
    )t2
)t3
where 
  t3.cn>=2

--求出偶数品类的中位数
select
  distinct t1.category_id,
  avg(t1.price)over(partition by t1.category_id) medprice
from
  (
select
  sku_id,
  category_id,
  price,
  row_number()over(partition by category_id order by price desc) rk,
  count(*)over(partition by category_id) cn,
  count(*)over(partition by category_id)%2 falg
from
  sku_info
)t1
where 
  t1.falg=0 and (t1.rk=cn/2  or t1.rk=cn/2+1)

--求出奇数品类的中位数
select
  t1.category_id,
  t1.price
from
  (
    select
      sku_id,
      category_id,
      price,
      row_number()over(partition by category_id order by price desc) rk,
      count(*)over(partition by category_id) cn,
      count(*)over(partition by category_id)%2 falg
    from
      sku_info
)t1
where 
  t1.falg=1 and t1.rk=round(cn/2)

-- 竖向拼接
select
  distinct t1.category_id,
  avg(t1.price)over(partition by t1.category_id) medprice
from
  (
select
  sku_id,
  category_id,
  price,
  row_number()over(partition by category_id order by price desc) rk,
  count(*)over(partition by category_id) cn,
  count(*)over(partition by category_id)%2 falg
from
  sku_info
)t1
where 
  t1.falg=0 and (t1.rk=cn/2  or t1.rk=cn/2+1)
union
select
  t1.category_id,
  t1.price/1
from
  (
    select
      sku_id,
      category_id,
      price,
      row_number()over(partition by category_id order by price desc) rk,
      count(*)over(partition by category_id) cn,
      count(*)over(partition by category_id)%2 falg
    from
      sku_info
)t1
where 
  t1.falg=1 and t1.rk=round(cn/2)

--  判断连续三天以上
select
  distinct t3.sku_id
from
  (
    select
      t2.sku_id,
      count(*)over(partition by t2.sku_id,t2.date_drk) cdrk
    from
      (
        select
          t1.sku_id,
          t1.create_date,
          date_sub(t1.create_date,rank()over(partition by t1.sku_id order by t1.create_date)) date_drk
        from
          (
            select
              sku_id,
              create_date,
              sum(price*sku_num) sku_sum
            from
              order_detail
            group by
              sku_id,create_date
            having 
              sku_sum>=100
        )t1
    )t2
)t3
where
  t3.cdrk>=3

-- 新增数量和留存率
select
  t3.first_login,
  t3.register,
  t3.remain_1/t3.register retention
from
  (
   select
     t1.first_login,
     count(t1.user_id) register,
     count(t2.user_id) remain_1
   from
     (
      select
        user_id,
        date_format(min(login_ts),'yyyy-MM-dd')   first_login
      from
        user_login_detail
      group by
        user_id
      )t1
   left join
     user_login_detail t2
   on
     t1.user_id=t2.user_id and datediff(date_format(t2.login_ts,'yyyy-MM-dd'),t1.first_login)=1
   group by
     t1.first_login
)t3

-- 拿到每次售卖的区间
select
  distinct
  sku_id,
  first_value(t1.create_date)over(partition by t1.sku_id,t1.ddrk order by t1.create_date  rows between unbounded preceding and unbounded following) start_date,
  last_value(t1.create_date)over(partition by t1.sku_id,t1.ddrk order by t1.create_date  rows between unbounded preceding and unbounded following) end_date
from
  (
   select
     sku_id,
     create_date,
     date_sub(create_date,rank()over(partition by sku_id order by create_date)) ddrk
   from
     order_detail
   group by
     sku_id,create_date
)t1

-- 拿到每个用户每天的交易次数
select
  t1.user_id,
  t1.login_date,
  collect_set(t1.login_count)[0] login_count ,
  count(di.user_id) order_count
from
  (
   select
     user_id,
     date_format(login_ts,'yyyy-MM-dd') login_date,
     count(*) login_count
   from
     user_login_detail
   group by
     user_id,date_format(login_ts,'yyyy-MM-dd')
)t1
left join
  delivery_info di
on
  t1.user_id=di.user_id and t1.login_date=di.order_date
group by
  t1.user_id,t1.login_date

-- 最近一次修改的价格
select
  t1.sku_id,
  t1.price_change
from
  (
    select
      sku_id,
      new_price-lead(new_price,1,0)over(partition by sku_id order by change_date desc) price_change,
      rank()over(partition by sku_id order by change_date desc) rk
    from
      sku_price_modify_detail
)t1
where 
  rk=1
order by
  t1.price_change