HiveSQL题-用户行为分析full join的应用

数据准备

数据分析

SELECT DISTINCT if(t1.user_id IS NOT NULL,t1.user_id,t2.user_id) AS user_id,
		if(t1.goods_id IS NOT NULL,t1.goods_id,t2.goods_id) AS good_id,
		if(t1.user_id IS NOT NULL,1,0) AS buy,
		if(t1.user_id IS NOT NULL AND t2.user_id IS NULL ,1,0) AS buy_not_coll,
		if(t1.user_id IS NULL AND t2.user_id IS NOT NULL ,1,0) AS coll_not_nuy,
		if(t1.user_id IS NOT NULL AND t2.user_id IS NOT NULL ,1,0) AS buy_and_coll	
FROM userorder t1
FULL JOIN usercollect t2
ON t1.user_id =t2.user_id AND t1.goods_id=t2.goods_id
ORDER BY user_id,good_id;

思路总结

全外连接，取两个所有情况的并集。

参考：HiveSQL面试题21--用户行为分析join的应用【小红书面试题】_要求:请用一句sql取出所有用户对商品的行为特征, 特征分为:已购买、购买未收藏、-CSDN博客