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

【面试题】

小红书月活跃用户数已超过1亿。用户通过文本、图片、视频笔记分享生活，创建相关商品链接，吸引相同爱好的用户收集和购买。用户消费的行为特征是什么？（小红书面试题）

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

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

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

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

【解题步骤】

问题需要查询表，需要增加两个表中没有的四个字段，分别是四个用户行为特征:购买、购买、收藏、收藏和购买。

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

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

以下四种用户行为特征将在两表连接后出现。

1）若商品出现在用户订单表中，则表示用户购买了该商品，属于已购买。

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

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

4）如果商品出现在用户收藏表中，商品也出现在用户订单表中，属于收藏和购买。

上诉用户的行为特征可分为两类：

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

若商品出现在用户订单表中，则表示用户购买了该商品，属于已购买。

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

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

在这种情况下，我们使用用户订单表(表)a）左联用户收藏表(表)b）可以判断，即保留左表用户订单表(表)a）里的全部数据。

2）从用户收集商品的角度来看

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

如果商品出现在用户收藏表中，商品也出现在用户订单表中，属于收藏和购买。

在这种情况下，我们使用用户收集商品表(表)b）左联用户订单表(表)a）可以判断，保留左表用户收集商品表(表)b）全部数据。

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

不同用户的行为特征需要多条件判断，这需要从零开始学习猴子 SQL》多条件判断（case表达式）。

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

在这种情况下，我们使用用户订单表(表)a）左联用户收藏表(表)b）可以判断，即保留左表用户订单表(表)a）全部数据。

1 select a.用户id,a.商品id2 from 用户订单表 as a3 left join 用户收集商品表 as b4 on a.用户id=b.用户id and a.商品id=b.商品id;

以下条件判断（case）新增4列分别表示4种用户行为特征。新列中1表示用户行为特征，0表示没有用户特征。

1）如果商品出现在用户订单表中，则表示用户购买了该商品，属于已购买。左表用户订单接，左表用户订单被保留（表）a）全部数据。

因此，这样的联合结果中的商品来自左表用户订单表，所以都是购买sql里用“1 as '已购买'说明这一列都有用户的行为特征。

2）如果商品出现在用户订单表中，但该商品没有出现在用户收集的商品表中，则属于购买未收集。相应条件判断如下：

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

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

前面说过，现在是用户订单表(表)a）左联用户收藏表(表)b）可以判断，即保留左表用户订单表(表)a）全部数据。

因此，该联合结果中的商品来自左表用户订单表，因此该列对应的用户行为特征（用0表示）。sql里用“0 as'收藏未购买'”表示。

4）如果商品出现在用户收藏的商品表中，商品也出现在用户订单表中，属于收藏和购买，相应条件判断如下：

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

完整地写下以上四种用户行为特征sql：

1 select a.用户id,a.商品id,2 as '已购买',3 (case when b.商品id is null then 1 else 0 end) as '购买未收藏',4 0 as'收藏未购买',5 (case when a.商品id=b.商品id then 1 else 0 end) as '购买且收藏'6 from 用户订单表 as a7 left join 用户收集商品表 as b8 on a.用户id=b.用户id and a.商品id=b.商品id;

查询结果

4.从用户收集商品的角度来看

在这种情况下，我们使用用户收集商品表(表)b）左联用户订单表(表)a）可以判断，保留左表用户收集商品表(表)b）全部数据。

1 select b.用户id,b.商品id2 from 用户收集商品表 as b3 left join 用户订单表 as a4 on b.用户id=a.用户id and b.商品id=a.商品id;

以下条件判断（case）新增4列分别表示4种用户行为特征。新列中1表示用户行为特征，0表示没有用户特征。

1）如果商品在用户订单表(表)a）其中，用户购买商品，属于已购买，相应的判断条件是：

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

2）如果商品在用户订单表(表)a）但用户收集商品表(表)b）商品中没有出现，属于购买未收藏。

因为是左连接，用户收藏的商品表保留了(表)b）全部数据。

因此，这种联合结果中的商品来自左表用户收集的商品表(表)b），因此，没有购买未收藏等用户特征sql里用“0 as'购买未收藏'该列表示没有用户行为特征。

3）如果用户收集商品表(表)b）但用户订单表(表)a）该商品中没有出现，属于收藏未购买

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

4）如果商品出现在用户收藏的商品表中，商品也出现在用户订单表中，属于收藏和购买，相应条件判断如下：

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

完整地写下以上四种用户行为特征sql：

1 select b.用户id,b.商品id,2 (case when a.商品id is not null then 1 else 0 end) as '已购买',3 0 as'购买未收藏',4 (case when a.商品id is null then 1 else 0 end) as '收藏未购买',5 (case when b.商品id=a.商品id then 1 else 0 end) as '购买且收藏'6 from 用户收集商品表 as b7 left join 用户订单表 as a8 on b.用户id=a.用户id and b.商品id=a.商品id;

查询结果：

5.全部商品

由于上述两个查询结果只保留左表的所有数据，而不是所有商品，如果您想获得所有用户的行为特征，您需要合并两个表（表的添加）（注：使用union语句连接两表字段格式必须一致)。

对应sql如下：

1 (select a.用户id,a.商品id,2 as '已购买',3 (case when b.商品id is null then 1 else 0 end) as '购买未收藏',4 0 as'收藏未购买',5 (case when a.商品id=b.商品id then 1 else 0 end) as '购买且收藏'6 from 用户订单表 as a7 left join 用户收集商品表 as b8 on a.用户id=b.用户id and a.商品id=b.商品id)9 union10 (select b.用户id,b.商品id,11 (case when a.商品id is not null then 1 else 0 end) as '已购买',12 0 as'购买未收藏',13 (case when a.商品id is null then 1 else 0 end) as '收藏未购买',14 (case when b.商品id=a.商品id then 1 else 0 end) as '购买且收藏'15 from 用户收集商品表 as b16 left join 用户订单表 as a17 on b.用户id=a.用户id and b.商品id=a.商品id);

查询结果：

【本题考点】

1.将复杂的业务问题分解成可以解决的简单问题，采用多维拆解分析方法。

2.遇到多条件判断的问题，要考虑使用case实现句子。

3.只遇到一个表，只能用一个SQL句子完成后，可以联想到多表连接，实现复杂的业务。

4.使用外连接union请注意，在连接成功之前，连接表格必须与字段格式一致。