> For the complete documentation index, see [llms.txt](https://houzidata.gitbook.io/sql/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://houzidata.gitbook.io/sql/di-5-zhang-xiang-mu-shi-zhan/xiao-hong-shu-mian-shi-ti-ru-he-fen-xi-yong-hu-hang-wei.md).

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

![](/files/-MWHxCSuwKG_4tJRZI2S)

**【面试题】**

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

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

用户订单表：记录用户 id、购买的商品 id、用户下单的时间及商品的种类。

![](/files/-MWHxHvWO6GDgztNGjOy)

用户收藏商品表：记录用户 id、用户收藏的商品 id 及收藏时间。

![](/files/-MWHxL85ad8XYTTpsPQP)

问题：请用一句 sql 语句得出以下查询结果，得到所有用户的商品行为特征，其中用户行为分类为 4 种：已购买、购买未收藏、收藏未购买、收藏且购买。

![](/files/-MWHxR4xccYAMAEWV3z0)

**【解题步骤】**

题目要求得出查询表，需要增加两个表中都没有的 4 个字段，分别为 4 种用户行为特征：已购买、购买未收藏、收藏未购买、收藏且购买。

![](/files/-MWHxVcQ1mkmBCRnqAmt)

**1. 如何得到用户行为特征？**

因为要通过用户 id 和商品 id 来判断用户在订单表和收藏表的情况，所以用用户 id、商品 id 联结两表。

![](/files/-MWHxYUjg2RWpiHq-6bW)

两表联结后，会出现下面 4 种用户行为特征。

![](/files/-MWHxaK4cR0ut2ZGD24b)

1）如果商品在**用户订单表**中出现，表示用户购买了该商品，属于 “已购买”。

2）如果商品在**用户订单表**中出现，但是用户收藏商品表中没有出现该商品，属于 “购买未收藏”。

3）如果商品在**用户收藏商品表**中出现，但是用户订单表中没有出现该商品，属于 “收藏未购买 “。

4）如果商品在**用户收藏商品表**中出现，同时用户订单表中也出现该商品，属于 “收藏且购买”。

**上诉用户行为特征可以从表的角度又分为 2 大类：**

**1）从用户订单表角度来看**

如果商品在**用户订单表**中出现，表示用户购买了该商品，属于 “已购买”。

如果商品在**用户订单表**中出现，但是用户收藏商品表中没有出现该商品，属于 “购买未收藏”。

上面从 A 表角度来看 B 表，就是保留 A 表里的全部数据，所以要用到左连接（left join）保留左表里到数据。我们把**用户订单表**记录为 a 表，把**用户收藏商品表**记录为 b 表。

![](/files/-MWHxki8EXmOij1vhhrp)

这种情况，我们使用**用户订单表**（表 a）左联结**用户收藏商品表**（表 b）可以判断出，也就是保留左表**用户订单表**（表 a）里的全部数据。

**2）从用户收藏商品表角度来看**

如果商品在**用户收藏商品表**中出现，但是用户订单表中没有出现该商品，属于 “收藏未购买 “。

如果商品在**用户收藏商品表**中出现，同时用户订单表中也出现该商品，属于 “收藏且购买”。

这种情况，我们使用**用户收藏商品表**（表 b）左联结**用户订单表**（表 a）可以判断出，也就是保留左表**用户收藏商品表**（表 b）里的全部数据。

**2. 如何判断用户行为特征？**

不同用户行为特征，需要用到多条件判断，这就要用到[《猴子 从零学会 SQL》](http://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==\&mid=2649249645\&idx=2\&sn=d1295b268eff974fe52a2c0f8bdadccb\&chksm=835fdf5db428564b7d42f733cdd45c6ce1aeeff0da8cdfac2f5047354bc815895f1a7fd282de\&scene=21#wechat_redirect)里讲过的多条件判断（case 表达式）。

![](/files/-MWHxp2eLy2nWbE3AeWG)

**3. 从用户订单表角度来看**

这种情况，我们使用**用户订单表**（表 a）左联结**用户收藏商品表**（表 b）可以判断出，也就是保留左表**用户订单表**（表 a）里的全部数据。

```sql
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）如果商品在用户订单表中出现，但是用户收藏商品表中没有出现该商品，属于 “购买未收藏”，对应条件判断是：

```sql
(case when b.商品id is null 
           then 1 
           else 0 
           end) as '购买未收藏'
```

3）如果商品在**用户收藏商品表**中出现，但是用户订单表中没有出现该商品，属于 “收藏未购买 “。

前面说了，现在是**用户订单表**（表 a）左联结**用户收藏商品表**（表 b）可以判断出，也就是保留左表**用户订单表**（表 a）里的全部数据。

因此这样的联结结果里的商品都来自左表用户订单表，所以这一列对应的没有该用户行为特征（用 0 表示）。在 sql 里用 “0 as'收藏未购买'” 表示。

4）如果商品在**用户收藏商品表**中出现，同时用户订单表中也出现该商品，属于 “收藏且购买”，对应条件判断是：

```sql
(case when a.商品id=b.商品id 
           then 1 
           else 0 
           end) as '购买且收藏'
```

把上面 4 种用户行为特征内容写成完整的 sql：

```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;
```

查询结果

![](/files/-MWHy6aa3qNe-XhXf4H3)

**4. 从用户收藏商品表角度来看**

这种情况，我们使用**用户收藏商品表**（表 b）左联结**用户订单表**（表 a）可以判断出，也就是保留左表**用户收藏商品表**（表 b）里的全部数据。

```sql
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）**中出现，表示用户购买了该商品，属于 “已购买”，对应判断条件是：

```sql
(case when a.商品id is not null 
           then 1 
           else 0 
           end) as '已购买'
```

2）如果商品在用户订单表（表 a）中出现，但是用户收藏商品表（表 b）中没有出现该商品，属于 “购买未收藏”。

因为是左联结，所以是保留了**用户收藏商品表**（表 b）里到全部数据。

因此这样的联结结果里的商品都来自左表**用户收藏商品表**（表 b），所以没有 “购买未收藏” 这样的用户特征，在 sql 里用“0 as'购买未收藏'” 表示这一列都没有该用户行为特征。

3）如果商品在**用户收藏商品表（表 b）**中出现，但是用户订单表（表 a）中没有出现该商品，属于 “收藏未购买 “，对应条件判断是：

```sql
(case when a.商品id is null 
           then 1 
           else 0 
           end) as '收藏未购买'
```

4）如果商品在**用户收藏商品表**中出现，同时用户订单表中也出现该商品，属于 “收藏且购买”，对应条件判断是：

```sql
(case when b.商品id=a.商品id 
           then 1 
           else  0 
           end) as '购买且收藏'
```

把上面 4 种用户行为特征内容写成完整的 sql：

```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;
```

查询结果：

![](/files/-MWHyigNdq8MhX4vRwec)

**5. 全部商品**

因为上面两个查询结果分别只保留了左表的全部数据，并不是全部的商品，如果要得出全部用户的行为特征的话，需要将两个表合并起来（表的加法）（注意：用 union 语句连接两表字段格式必须一致）。

对应 sql 如下：

```scala
(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);
```

查询结果：

![](/files/-MWHyr4mlJfEJEuXrgeT)

**【本题考点】**

1. 用[多维度拆解分析方法](http://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==\&mid=2649248793\&idx=2\&sn=1fe86f3083bd9c8ad1a427898199c71c\&chksm=835fdc29b428553fabf47f23dc05f8f6c296cbcc06ce7e409ff712e81141fbf9ad0aa9b2b99d\&scene=21#wechat_redirect)，将复杂的业务问题拆解为可以解决的简单问题。
2. 遇到[多条件判断](http://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==\&mid=2649249645\&idx=2\&sn=d1295b268eff974fe52a2c0f8bdadccb\&chksm=835fdf5db428564b7d42f733cdd45c6ce1aeeff0da8cdfac2f5047354bc815895f1a7fd282de\&scene=21#wechat_redirect)的问题，要想到用 case 语句来实现。
3. 遇到只有一个表且只能用一条 SQL 语句完成，可以联想到用多表联结，来实现复杂的业务。
4. 使用外连接 union 注意连接表格必须字段格式一致方可连接成功。

![](/files/-MWHyvaIbGha8uCGTWNY)

[推荐：如何从零学会 SQL？](http://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==\&mid=2649249645\&idx=2\&sn=d1295b268eff974fe52a2c0f8bdadccb\&chksm=835fdf5db428564b7d42f733cdd45c6ce1aeeff0da8cdfac2f5047354bc815895f1a7fd282de\&scene=21#wechat_redirect)

![](/files/-MWHz8uv1NnFnbMI1fgi)