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;
©著作权归作者所有,转载或内容合作请联系作者
平台声明:文章内容(如有图片或视频亦包括在内)由作者上传并发布,文章内容仅代表作者本人观点,简书系信息发布平台,仅提供信息存储服务。

推荐阅读更多精彩内容