MySQL实战--用户行为数据分析--8小时跟做（1）

最新推荐文章于 2024-07-15 16:30:03 发布

数据分析从入门到入土:з)))」∠)_

最新推荐文章于 2024-07-15 16:30:03 发布

阅读量536

点赞数 12

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/Faracross/article/details/139575352

版权

【MySQL实战】基于100万真实电商用户的1亿条行为数据分析跟做

简介_哔哩哔哩_bilibili

SQL基础语法：select+from +where+group by+having+order by+limit

1.navicat导入数据到mysql.

因为一亿条数据太多了，所以选取一百万条直接用navicat导入，当然跟着作者用kettle导入也可以。

查询--建表

create table user_behavior (user_id int(9), item_id int(9), category_id int(9), behavior_type varchar(5), timestamp int(14) );

导入数据：

2.数据预处理

查空，查重

-- 检查空值 
select * from user_behavior 
where user_id is null OR item_id is null OR category_id is null or behavior_type is null OR timestamps is null;

-- 检查重复值 
select user_id,item_id,timestamps from user_behavior
group by user_id,item_id,timestamps
having count(*)>1;

Group by +非聚合字段

Having +聚合字段筛选条件（having 筛选大于1的相同组合）

发现前一百万条数据无重复，所以不用去重

新增日期

-- 新增日期：date time hour

-- datetime
alter table user_behavior add datetimes TIMESTAMP(0);
update user_behavior set datetimes=FROM_UNIXTIME(timestamps);
select * from user_behavior limit 5;
-- date
alter table user_behavior add dates char(10);
alter table user_behavior add times char(8);
alter table user_behavior add hours char(2);
-- update user_behavior set dates=substring(datetimes,1,10),times=substring(datetimes,12,8),hours=substring(datetimes,12,2);
update user_behavior set dates=substring(datetimes,1,10);
update user_behavior set times=substring(datetimes,12,8);
update user_behavior set hours=substring(datetimes,12,2);
select * from user_behavior limit 5;

update user_behavior set datetimes=FROM_UNIXTIME(timestamps);

将时间戳转化成日期形式，并存储在datetimes列中

去异常

-- 去异常 
select max(datetimes),min(datetimes) from user_behavior;
delete from user_behavior
where datetimes < '2017-11-25 00:00:00'
or datetimes > '2017-12-03 23:59:59';

最后可以查看一下数据，这里count(1) 和 count(*) 的结果是相同的

-- 数据概览 
desc user_behavior;
select * from user_behavior limit 5;
SELECT count(1) from user_behavior; -- 999529条记录

3.获客情况

选取十万数据分析

-- 创建临时表 
create table temp_behavior like user_behavior;

-- 截取 十万数据
insert into temp_behavior
select * from user_behavior limit 100000;

PV（总访问量）

根据日期分组（日浏览量，即每天的总访问量）

UV（总用户数）

根据日期分组（每天的独立访客）

-- 一条语句 
select dates
,count(*) 'pv'
,count(distinct user_id) 'uv'
,round(count(*)/count(distinct user_id),1) 'pv/uv'
from temp_behavior
where behavior_type='pv'
GROUP BY dates;

round(（），1)保留以为小数