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

![](/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)


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET 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?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.