【面试题】
小红书月活跃用户数已经过亿,用户在小红书上通过文字、图片、视频笔记分享生活,并创建相关商品链接,吸引相同爱好的用户进行收藏购买,用户的消费有什么行为特征呢?(小红书面试题)
现有用户订单表、用户收藏表。
用户订单表:记录用户id、购买的商品id、用户下单的时间及商品的种类。
问题:请用一句sql语句得出以下查询结果,得到所有用户的商品行为特征,其中用户行为分类为4种:已购买、购买未收藏、收藏未购买、收藏且购买。
【解题步骤】
题目要求得出查询表,需要增加两个表中都没有的4个字段,分别为4种用户行为特征:已购买、购买未收藏、收藏未购买、收藏且购买。
1.如何得到用户行为特征?
因为要通过用户id和商品id来判断用户在订单表和收藏表的情况,所以用用户id、商品id联结两表。
1)如果商品在用户订单表中出现,表示用户购买了该商品,属于“已购买”。
2)如果商品在用户订单表中出现,但是用户收藏商品表中没有出现该商品,属于“购买未收藏”。
3)如果商品在用户收藏商品表中出现,但是用户订单表中没有出现该商品,属于“收藏未购买“。
4)如果商品在用户收藏商品表中出现,同时用户订单表中也出现该商品,属于“收藏且购买”。
上诉用户行为特征可以从表的角度又分为2大类:
1)从用户订单表角度来看
如果商品在用户订单表中出现,表示用户购买了该商品,属于“已购买”。
如果商品在用户订单表中出现,但是用户收藏商品表中没有出现该商品,属于“购买未收藏”。
上面从A表角度来看B表,就是保留A表里的全部数据,所以要用到左连接(left join)保留左表里到数据。我们把用户订单表记录为a表,把用户收藏商品表记录为b表。
这种情况,我们使用用户订单表(表a)左联结用户收藏商品表(表b)可以判断出,也就是保留左表用户订单表(表a)里的全部数据。
2)从用户收藏商品表角度来看
如果商品在用户收藏商品表中出现,但是用户订单表中没有出现该商品,属于“收藏未购买“。
如果商品在用户收藏商品表中出现,同时用户订单表中也出现该商品,属于“收藏且购买”。
这种情况,我们使用用户收藏商品表(表b)左联结用户订单表(表a)可以判断出,也就是保留左表用户收藏商品表(表b)里的全部数据。
2.如何判断用户行为特征?
不同用户行为特征,需要用到多条件判断,这就要用到《猴子 从零学会SQL》里讲过的多条件判断(case表达式)。
3.从用户订单表角度来看
这种情况,我们使用用户订单表(表a)左联结用户收藏商品表(表b)可以判断出,也就是保留左表用户订单表(表a)里的全部数据。
select a.用户id,a.商品id
from 用户订单表 as a
left join 用户收藏商品表 as b
on a.用户id=b.用户id and a.商品id=b.商品id;
下面使用条件判断(case)新增4列分别表示4种用户行为特征。新增的列里用1表示有该用户行为特征,用0表示没有该用户特征。
1)如果商品在用户订单表中出现,表示用户购买了该商品,属于“已购买”。因为是左联结,所以是保留了左表用户订单(表a)里到全部数据。
因此这样的联结结果里的商品都来自左表用户订单表,所以都是“已购买”,在sql里用“1 as '已购买'”表示这一列都有该用户行为特征。
2)如果商品在用户订单表中出现,但是用户收藏商品表中没有出现该商品,属于“购买未收藏”,对应条件判断是:
(case when b.商品id is null
then 1
else 0
end) as '购买未收藏'
3)如果商品在用户收藏商品表中出现,但是用户订单表中没有出现该商品,属于“收藏未购买“。
前面说了,现在是用户订单表(表a)左联结用户收藏商品表(表b)可以判断出,也就是保留左表用户订单表(表a)里的全部数据。
因此这样的联结结果里的商品都来自左表用户订单表,所以这一列对应的没有该用户行为特征(用0表示)。在sql里用“0 as'收藏未购买'”表示。
4)如果商品在用户收藏商品表中出现,同时用户订单表中也出现该商品,属于“收藏且购买”,对应条件判断是:
(case when a.商品id=b.商品id
then 1
else 0
end) as '购买且收藏'
把上面4种用户行为特征内容写成完整的sql:
select a.用户id,a.商品id,
1 as '已购买',
(case when b.商品id is null then 1 else 0 end) as '购买未收藏',
0 as'收藏未购买',
(case when a.商品id=b.商品id then 1 else 0 end) as '购买且收藏'
from 用户订单表 as a
left join 用户收藏商品表 as b
on a.用户id=b.用户id and a.商品id=b.商品id;
4.从用户收藏商品表角度来看
这种情况,我们使用用户收藏商品表(表b)左联结用户订单表(表a)可以判断出,也就是保留左表用户收藏商品表(表b)里的全部数据。
select b.用户id,b.商品id
from 用户收藏商品表 as b
left join 用户订单表 as a
on b.用户id=a.用户id and b.商品id=a.商品id;
下面使用条件判断(case)新增4列分别表示4种用户行为特征。新增的列里用1表示有该用户行为特征,用0表示没有该用户特征。
1)如果商品在用户订单表(表a)中出现,表示用户购买了该商品,属于“已购买”,对应判断条件是:
(case when a.商品id is not null
then 1
else 0
end) as '已购买'
2)如果商品在用户订单表(表a)中出现,但是用户收藏商品表(表b)中没有出现该商品,属于“购买未收藏”。
因为是左联结,所以是保留了用户收藏商品表(表b)里到全部数据。
因此这样的联结结果里的商品都来自左表用户收藏商品表(表b),所以没有“购买未收藏”这样的用户特征,在sql里用“0 as'购买未收藏'”表示这一列都没有该用户行为特征。
3)如果商品在用户收藏商品表(表b)中出现,但是用户订单表(表a)中没有出现该商品,属于“收藏未购买“,对应条件判断是:
(case when a.商品id is null
then 1
else 0
end) as '收藏未购买'
4)如果商品在用户收藏商品表中出现,同时用户订单表中也出现该商品,属于“收藏且购买”,对应条件判断是:
(case when b.商品id=a.商品id
then 1
else 0
end) as '购买且收藏'
把上面4种用户行为特征内容写成完整的sql:
select b.用户id,b.商品id,
(case when a.商品id is not null then 1 else 0 end) as '已购买',
0 as'购买未收藏',
(case when a.商品id is null then 1 else 0 end) as '收藏未购买',
(case when b.商品id=a.商品id then 1 else 0 end) as '购买且收藏'
from 用户收藏商品表 as b
left join 用户订单表 as a
on b.用户id=a.用户id and b.商品id=a.商品id;
5.全部商品
因为上面两个查询结果分别只保留了左表的全部数据,并不是全部的商品,如果要得出全部用户的行为特征的话,需要将两个表合并起来(表的加法)(注意:用union语句连接两表字段格式必须一致)。
对应sql如下:
(select a.用户id,a.商品id,
1 as '已购买',
(case when b.商品id is null then 1 else 0 end) as '购买未收藏',
0 as'收藏未购买',
(case when a.商品id=b.商品id then 1 else 0 end) as '购买且收藏'
from 用户订单表 as a
left join 用户收藏商品表 as b
on a.用户id=b.用户id and a.商品id=b.商品id)
union
(select b.用户id,b.商品id,
(case when a.商品id is not null then 1 else 0 end) as '已购买',
0 as'购买未收藏',
(case when a.商品id is null then 1 else 0 end) as '收藏未购买',
(case when b.商品id=a.商品id then 1 else 0 end) as '购买且收藏'
from 用户收藏商品表 as b
left join 用户订单表 as a
on b.用户id=a.用户id and b.商品id=a.商品id);
【本题考点】
1.用多维度拆解分析方法,将复杂的业务问题拆解为可以解决的简单问题。
2.遇到多条件判断的问题,要想到用case语句来实现。
3.遇到只有一个表且只能用一条SQL语句完成,可以联想到用多表联结,来实现复杂的业务。
4.使用外连接union注意连接表格必须字段格式一致方可连接成功。
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
注意:UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 SELECT 语句中的列的顺序必须相同。Union因为要进行重复值扫描,所以效率低。如果合并没有刻意要删除重复行,那么就使用Union All,两个要联合的SQL语句字段个数必须一样,而且字段类型要“相容”(一致)。
含义:如果我们需要将两个select语句的结果作为一个整体显示出来,我们就需要用到union或者union all关键字。union(或称为联合)的作用是将多个结果合并在一起显示出来。
区别
union和union all的区别是:union会自动压缩多个结果集合中的重复结果,而union all则将所有的结果全部显示出来,不管是不是重复。
Union:对两个结果集进行并集操作,不包括重复行,同时进行默认规则的排序;
Union All:对两个结果集进行并集操作,包括重复行,不进行排序;