Hive综合案例练习（中级）第十一题：用户注册、登录、下单综合统计

用户注册、登录、下单综合统计

题目需求

从用户登录明细表（user_login_detail）和订单信息表（order_info）中查询每个用户的注册日期（首次登录日期）、总登录次数以及其在2021年的登录次数、订单数和订单总额。期望结果如下：

user_id(用户id)	register_date(注册日期)	total_login_count(累积登录次数)	login_count_2021(2021年登录次数)	order_count_2021(2021年下单次数)	order_amount_2021(2021年订单金额)
101	2021-09-21	5	5	4	143660.00
102	2021-09-22	4	4	4	177850.00
103	2021-09-23	2	2	4	75890.00
104	2021-09-24	4	4	4	89880.00
105	2021-10-04	1	1	4	120100.00
106	2021-10-04	2	2	4	119150.00
107	2021-09-25	4	4	4	124150.00
108	2021-10-06	2	2	4	155770.00
109	2021-09-26	3	3	4	153500.00
1010	2021-09-27	2	2	4	51950.00

代码实现

select 
	login.user_id,
	register_date,
	total_login_count,
	login_count_2021,
	order_count_2021,
	order_amount_2021
from 
(
	select 
		user_id,
		min(date_format(login_ts, 'yyyy-MM-dd')) register_date,
		count(1) total_login_count,
		-- sum(if(year(login_ts)='2021', 1, 0)) login_count_2021
		count(if(year(login_ts) = '2021', 1, null)) login_count_2021
	from user_login_detail 
	group by user_id
)login
join 
(
	select 
		user_id,
		count(distinct(order_id)) order_count_2021,
		sum(total_amount) order_amount_2021
	from order_info oi 
	where year(create_date)='2021'
	group by user_id
)oi
on login.user_id=oi.user_id