信息茧房研究-sql数据清洗实战语句记录

---

前言

本文主要记录信息茧房研究-sql数据清洗实战语句操作的相关笔记

---

数据集内容

数据集为表all_info，有500多万行数据，大致内容示例见下表：

uid	Gender	full_content	root_uid
75a673e5c61	男	网上车市了解到，哈弗某款新车型预计5月中旬上市	None
673e5c675a1	女	随着三月份的到来，各地的高校也在陆续开学	None
f37fae1c9ce1	女	【人大代表#建议鼓励生育要进一步为家长减负#[思考]】	None
e137ff1c9cae	男	顶不住了@f37fae1c9ce1:【人大代表#建议鼓励生育要进一步为家长减负#[思考]】	f37fae1c9ce1
…	…	…

需求1. 找出所有根微博用户id去重保存到表“t”

create table t (select distinct root_uid from all_info)

需求2. 发博id有属于表“t”的用户id去重保存到“t1”

create table t1 (select distinct uid from all_info 
where uid in (select * from t))

需求3. 转博的根微博用户uid属于“t1”的用户id去重保存到“t2”

create table t2 (select distinct uid from all_info 
where root_uid in (select * from t1))

需求4. 有效用户uid

有效用户uid定义为能够在数据集中形成关系链，即除了只有原创行为或转发的根微博用户uid不在用户id里这两种情况外的所有数据。对“t1”表和“t2”表求并集即可

create table useid (select distinct uid from(select uid from t1 union
select uid from t2)t3)

需求5. 根据有效用户uid进行数据清洗

数据清洗可以直接采用下列语句进行提取，删除不属于有效用户uid的无效数据。

delete from all_info where uid not in (select * from useid)

因速度过慢，所以采用了先新建一个和原表格结构相同的新表“all_info1”，然后再在原“all_info”表中查找符合条件的数据插入到新表中。

create table all_info1 select * from all_info where 1=0

insert into all_info1 select * from all_info where uid in (select * from useid)

插入完成后删除原表格，将新表重命名即可。

需求6. 用户uid的替换-基于多表连接

因为用户uid都是采用一串较长的字符，为了加快检索速度，便于直观观察，将对数据集的所有用户uid进行替换，具体操作是先对“useid”表内的数据进行排序，并新建一列“rankn”记录这个编号，然后使用表连接再将“all_info”与“useid”两表连接起来，并将用户uid相应的编号写入到“all_info”新增的ranknum列（对应uid）和ranknum1（对应root_uid）列。

with t as(select id,ROW_NUMBER()over(order by uid)ro 
from useid)
update useid t1 left join t on t1.uid=t.uid set rankn= t.ro

update all_info t left join useid u on t.uid=u.uid set ranknum=u.rankn
update all_info t left join useid u on t.root_uid=u.uid set ranknum1=u.rankn

需求7. 性别编码-基于源数据处理

因为源数据集有男女标识，为了进一步减少数据集大小，使用数字0代表男，1代表女。

update all_info
set `性别`=replace(`性别`,'男',0)
update all_info 
set `性别`=replace(`性别`,'女',1)

需求8. 原创/转发数据聚合

需要将所有用户转发的文本合并到一起，原创的文本合并到一起，然后将结果导出到新表

CREATE table case_text(
select rank1,`原创/转发`,group_concat(`全文内容`)group_text 
from all_info group by rank1,`原创/转发`)

报错Row row 8 was cut by group_concat()

单独查询聚合结果时不会报错，但导入到新表的时候会显示这个错误，此时，可以更改group_concat()的最大限制长度来解决这个问题。从命令行进入mysql，运行下列语句即可。

-- 查询当前长度
SELECT @@global.group_concat_max_len;

-- 修改全局长度(临时修改)
SET GLOBAL group_concat_max_len=102400000;

SET group_concat_max_len=102400000;

修改完成后即可正常运行。