淘宝用户行为分析

1，数据预处理

1.1检查空值

代码块

select * from userbehavior where user_id is null;​
select * from userbehavior where item_id is null;​
select * from userbehavior where category_id is null;​
select * from userbehavior where behavior_type is null;​
select * from userbehavior where timestamps is null;​

注：在后面分析过程中，发现存在时间值-timestamps小于0的情况，前面处理脏数据没有清理干净，在此补充脏数据的清理​

代码块

delete userbehavior ​
from userbehavior​
where timestamps<0​

1.2去重

1.2.1检查重复值：

分组后每组多于1项，就表示该项是重复值，以user_id,item_id,timestamps三列识别重复值​

代码块

select user_id,item_id,timestamps from userbehavior​
group by user_id, item_id, timestamps​
having count(*)>1;​

1.2.2第一列建立从1开始的主键，并命名为id

代码块

alter table userbehavior add id int first;​
select * from userbehavior limit 5;​
alter table userbehavior modify id int primary key auto_increment;​

1.2.3去重：

保留userbehavior表内重复值id更小的该行，利用userbehavior.id>u2.id仅去除重复重复值（保证相同两行有一行被保留）​

法1：

代码块

with u2 as ​
(​
        select user_id,item_id,timestamps,min(id) as id from userbehavior​
        group by user_id,item_id,timestamps​
        having count(*)>1​
        )​
​
delete userbehavior from userbehavior​
inner join u2 ​
        on  userbehavior.user_id=u2.user_id​
        and userbehavior.item_id=u2.item_id​
        and userbehavior.timestamps=u2.timestamps​
where userbehavior.id>u2.id;​

法2

代码块

delete userbehavior from​
userbehavior​
(​
        select user_id,item_id,timestamps,min(id) id from userbehavior​
        group by user_id,item_id,timestamps​
        having count(*)>1​
        )u2​
        where userbehavior.user_id=u2.user_id​
        and userbehavior.item_id=u2.item_id​
        and userbehavior.timestamps=u2.timestamps​
        and userbehavior.id>u2.id;​

1.3增加日期描述列

淘宝用户行为分析​

淘宝用户行为分析