sql用户行为分析

• 有订单事务表、收藏事务表,要求：请用一句SQL取出所有用户对商品的行为特征，特征分为已购买、
• 购买未收藏、收藏未购买、收藏且购买

# 数据准备
create table redbk_orders(
 `id` int(5),
 `user_id` varchar(10),
 `item_id` int(5),
 `par_time` varchar(30),
 `item_num` int(5)
);
insert into redbk_orders values(1,'001','201','2018-08-31 00:00:01',1);
insert into redbk_orders values(2,'002','203','2018-09-02 12:00:02',2);
insert into redbk_orders values(3,'003','203','2018-09-01 00:00:01',1);
insert into redbk_orders values(4,'003','203','2018-09-04 09:10:30',1);
create table redbk_favorites(
 `id` int(5),
 `user_id` varchar(10),
 `item_id` INT(5),
 `fav_time` varchar(30)
);
insert into redbk_favorites values(1,'001',201,'2018-08-31 00:00:01');
insert into redbk_favorites values(2,'002',202,'2018-09-02 12:00:02');
insert into redbk_favorites values(3,'003',204,'2018-09-01 00:00:01');

select 
o.user_id 
,o.item_id 
,1 as "已购买" 
,case when f.item_id is null then 1 else 0 end as "购买未收藏"
,0 as "收藏未购买" 
,case when f.item_id is not null then 1 else 0 end as "收藏且购买"
from myemployees.redbk_orders o 
left join myemployees.redbk_favorites f 
on o.user_id = f.user_id and o.item_id = f.item_id 
where o.user_id is not null 
union 
select 
o.user_id 
,o.item_id 
,case when o.item_id is not null then 1 else 0 end as "已购买" 
,0 as "购买未收藏"
,case when o.item_id is null then 1 else 0 end as "收藏未购买" 
,case when o.item_id is not null then 1 else 0 end as "收藏且购买"
from myemployees.redbk_favorites f 
left join myemployees.redbk_orders o 
on o.user_id = f.user_id and o.item_id = f.item_id
where o.user_id is not null;