如何分析粉丝兴趣？

【题目】

有一张“粉丝关注表”，包含3个字段：用户id、关注媒体id、日期。

【问题】“粉丝关注表”中存在一个用户同时关注多个媒体的情况存在，比如：用户id为A001的用户，对应关注媒体id数据为1010,1020,1031。为了便于后期分析粉丝兴趣，请将该表中的这种情况进行拆分为多条。

比如对于用户A001，其转换如下：

【解题思路】

这类问题称为“列转行”，在MySQL里的处理方式一般分为三步：

1）创建一个“序列表”；

2）进行多表联结，将原表的每一条数据复制为多条；

3）使用substring_index函数获取最终结果。

第一步：创建序列表

“序列表”是指只有一个字段，存储的是数字序列，比如：

其中，“序列”的最大值就是该问题中一个用户关注媒体的最多个数。

select max(length(关注媒体id) - length(replace(关注媒体id,',','')) + 1) as 最多关注媒体个数
from 粉丝关注表;

返回结果为：

那我们需要新建的“序列表”就是：

第二步：多表联结

使用多表联结，可以通过“序列表”将“粉丝关注表”的每行变成多行。

此处有两个注意点：

1）为保证原表中的每一条数据不丢失，选择“左联结”，并以原表为左表；

2）联结条件里对复制条数进行限制，限制条件是用户关注媒体的数量，即“关注媒体id”字段下的逗号数量加1。

select t1.用户id,
       t1.关注媒体id,
       t1.日期,
       t2.序列
from 粉丝关注表 t1
left join 序列表 t2 on t2.序列 <= (length(关注媒体id) - length(replace(关注媒体id,',','')) + 1);

返回结果为：

第三步：使用函数获取结果

接下来就是将媒体id截取出来，需要用到字符串截取函数：SUBSTRING_INDEX。

SUBSTRING_INDEX(字符串,分隔符,参数)

其中，分隔符指本题中分割媒体id的“,”；2指按分隔符分开，从左往右截取几个媒体id；如果参数为负数时，表示从右往左截取几个媒体id。

select t1.用户id,
       substring_index(substring_index(t1.关注媒体id,',',t2.序列),',',-1) as 关注媒体id,
       t1.日期
from 粉丝关注表 t1
left join 序列表 t2 on t2.序列 <= (length(关注媒体id) - length(replace(关注媒体id,',','')) + 1);

返回结果为：

【本题考点】

1）考查对序列表的了解；

2）考查对字符串截取函数SUBSTRING_INDEX的了解；

3）考查对多表联结的了解。

▼ 点击「阅读原文」

▼ 解锁更多数据分析课程