hive sql 练习

--每个用户中最喜爱的偏好
-- 自己写的
select user_name, max(goods_counts) from (
select user_name,goods_kind, count(distinct goods_kind) over (partition by user_name) as goods_counts from table_1
  ) a
--- 官方
select user_name, goods_kind from (
select user_name, goods_kind, row_number() over (partition by user_name order by count(goods_kind)) as rk from table_1
  ) a where rk = 1;
  
-- 1. 拿到全部的用户人数
-- 2. 拿到用户支付的排名
select distinct user_name from(
select user_name, row_number() over(partition by user_name order by sum(pay_amount)) as rk from table_1 ) a ---- 写不出来了
-- 窗口函数 ntile. 求百分比范围的数据  官方
select b.user_name from (
  select user_name, ntile(5) over(partition by sum(pay_amount) desc)) as level
  from table_1
 )b where level = 1
 
 -- 确定到某一天的连续7日登录的用户
 -- 自己写
  select user_name from(
 select distinct user_name, rank() over(partition by user_name order by date) as rk from table_1
 ) where to_date(date) - rk=yesterday
 group by user_name
 
 --计算所有连续7日登录的用户 ，起始时间不确定！  lead（） 函数 官方
 -- 下午编一下再
 HAVING count(user_name) >= 7
 

1. 求昨天登录今天没登录的用户

select userid from 
(select userid from table where date = Yesterday) a -- 昨天登录的用户
left join 
(select userid from table where date = today) b -- 今天登录的用户
on a.userid = b.userid 
where b.userid is null

以上会产生3个job：两个自查询-->2  一次join --->1

2. 求昨天登录今天也登录的用户

select userid from table a 
group by userid 
having 
count(case when date == Yesterday then 1 else 0) * count(case when date == today then 1 else 0) = 0 

以上产生1个job