Hive SQL 练习

1、统计每天首次登录的人数

-- 统计每天首次登录的人数
-- 步骤：先找出第一次登录的时间，再按首次登录时间分组求人数-- 方法1：
select first_login_time, count(user_id) as cnt
from 
(select user_id, date_formate(min(login_ts), 'yyyy-MM-dd') as first_login_timefrom user_login_detailgroup by user_id
) t
group by first_login_time;-- 方法2：
select date_format(t.login_ts, 'yyyy-MM-dd') as first_login_time, count(user_id) as cnt
from
(select user_id, login_ts,lag(login_ts) over(partition by user_id order by login_ts) as flagfrom user_login_detailwhere flag is NULL
) t
group by first_login_time;

2、查询用户注册日期（首次登录日期）、登录总次数、2021年登录总次数

-- 用户注册日期（首次登录日期）、登录总次数、2021年登录总次数
select user_id,date_format(min(login_ts), 'yyyy-MM-dd') as first_login_time,count(1) as login_cnt,count(if(year(login_ts)=2021, 1, null)) as 2021_login_cnt
from user_login_detail
group by user_id;

3、查询每个用户累积访问次数

-- 3、每个用户累积访问次数
select user_id, visit_date, month_visit_cnt,sum(month_visit_cnt) over(partion by user_id order by visit_date) as cum_month_visit_cnt    
from 
(   -- 先求出用户每个月累计访问次数select user_id, date_format(visit_date, 'yyyy-MM') as visit_date,sum(visit_cnt) as month_visit_cntfrom tb_1group by user_id, date_format(visit_date, 'yyyy-MM')
) t;

4、各直播间最大的同时在线人数

-- 各直播间最大的同时在线人数
select room_id, max(cnt) as max_cnt
from 
(select room_id, sum(flag) over(partion by room_id order by dt) as cntfrom (select room_id, user_id, login_time as dt,1 as flagfrom tbunionselect room_id, user_id, logout_time as dt,-1 as flagfrom tb) t1
) t2
group by t2.room_id;

5、查询累计销量排名第二的商品

-- 查询累计销量排名第二的商品
select t2.sku_id
from 
(select t1.sku_id, t1.sum_cnt,dense_rank() over(order by t1.sum_cnt desc) as rkfrom(select sku_id, sum(sku_num) sum_cntfrom order_detailgroup by sku_id) t1
) t2
where t2.rk=2;

6、向用户推荐朋友收藏的商品

-- 向用户推荐朋友收藏的商品
-- 步骤：1、首先，单向好友变双向，得到（用户、朋友）；再关联用户收藏表，得到（用户、朋友收藏商品）；
-- 2、求出（用户、用户收藏集合）
-- 3、由（用户、朋友收藏商品）和（用户、用户收藏商品集合），找出朋友收藏的且不在用户收藏的商品select t3.user_id, t3.sku_id
from
(select distinct t1.user_id, t2.sku_id  -- 同一个用户的多个朋友都收藏了同一个商品，所以要去重from(select user1_id user_id,user2_id friend_idfrom friendship_infounionselect user2_id,user1_idfrom friendship_info) t1join favor_info t2on t1.friend_id=t2.user_id
) t3
left join
(select user_id,collect_set(sku_id) sku_setfrom favor_infogroup by user_id
) t4
on t3.user_id=t4.user_id
where not array_contains(t4.sku_set, t3.sku_id);