小红书面试题：如何分析用户行为？

【面试题】

小红书月活跃用户数已经过亿，用户在小红书上通过文字、图片、视频笔记分享生活，并创建相关商品链接，吸引相同爱好的用户进行收藏购买，用户的消费有什么行为特征呢？（小红书面试题）

现有用户订单表、用户收藏表。

用户订单表：记录用户 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);