【面试题】
小红书月活跃用户数已经过亿，用户在小红书上通过文字、图片、视频笔记分享生活，并创建相关商品链接，吸引相同爱好的用户进行收藏购买，用户的消费有什么行为特征呢？（小红书面试题）
现有用户订单表、用户收藏表。
用户订单表：记录用户id、购买的商品id、用户下单的时间及商品的种类。
用户收藏商品表：记录用户id、用户收藏的商品id及收藏时间。
问题：请用一句sql语句得出以下查询结果，得到所有用户的商品行为特征，其中用户行为分类为4种：已购买、购买未收藏、收藏未购买、收藏且购买。
【解题步骤】
题目要求得出查询表，需要增加两个表中都没有的4个字段，分别为4种用户行为特征：已购买、购买未收藏、收藏未购买、收藏且购买。
1.如何得到用户行为特征？
因为要通过用户id和商品id来判断用户在订单表和收藏表的情况，所以用用户id、商品id联结两表。
两表联结后，会出现下面4种用户行为特征。
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注意连接表格必须字段格式一致方可连接成功。
转载：https://blog.csdn.net/zhongyangzhong/article/details/112975908