mysql 淘宝数据分析

查看数据

淘宝用户数据文件

查看文件行数

[root@three package]# wc -l UserBehavior.csv 
561294 UserBehavior.csv

查看文件

[root@three package]# head UserBehavior.csv 
1,2268318,2520377,pv,1511544070
1,2333346,2520771,pv,1511561733
1,2576651,149192,pv,1511572885
1,3830808,4181361,pv,1511593493
1,4365585,2520377,pv,1511596146
1,4606018,2735466,pv,1511616481
1,230380,411153,pv,1511644942
1,3827899,2920476,pv,1511713473
1,3745169,2891509,pv,1511725471
1,1531036,2920476,pv,1511733732

登录 mysql

mysql --local_infile=1 -u user -p password

set global local_infile = 1;

建表

主键要唯一（数据中各属性不唯一）

create table user(
user_id int,
item_id int,
category_id int,
behavior_type varchar(10),
time varchar(20)
);

Load data

load data local infile '/root/package/UserBehavior.csv' into table user
CHARACTER SET utf8 
FIELDS TERMINATED BY ',' 
	OPTIONALLY ENCLOSED BY '' 
	ESCAPED BY '\\' 
LINES TERMINATED BY '\n' 
(user_id,item_id,category_id,behavior_type,@time)
set time=FROM_UNIXTIME(@time,'%Y-%m-%d %H:%i:%s');

查看导入了多少条数据

select count(*) from user;

1 查看一共多少人访问淘宝

select count(distinct user_id) from user;

2 分别统计浏览行为为点击，收藏，加入购物车，购买的总数量

select behavior_type,count(*) 
from user
group by behavior_type;

3.统计用户最近购买时间。以 2017-12-03 为当前日期，计算时间范围为一个月，计算用户最近购买时间，时间的区间为 0-30 天，将其分为 5 档，0-6 天,7-12天,13-18 天,19-24 天,25-30 天分别对应评分 4 到 0（15 分）;

date_format（） 取年月日
datediff（）    取时间天数
min（）		   取最近
case when      分等级

WITH cat AS(
SELECT user_id,MIN(datediff('2017-12-03',DATE_FORMAT(TIME,'%Y-%m-%d'))) days FROM user
WHERE behavior_type='buy' AND 
datediff('2017-12-03',DATE_FORMAT(TIME,'%Y-%m-%d'))<=30
GROUP BY user_id)

SELECT user_id,CASE
WHEN days BETWEEN 0 AND 6 THEN 4
WHEN days BETWEEN 7 AND 12 THEN 3
WHEN days BETWEEN 13 AND 18 THEN 2
WHEN days BETWEEN 19 AND 24 THEN 1
ELSE 0 END AS level from cat;

4 统计用户的消费频率。以 2017-12-03 为当前日期，计算时间范围为一个月，计算用户的消费次数，用户中消费次数从低到高为 1-161 次，将其分为 5档，1-32，33-64，65-96，97-128，129-161 分别对应评分 0 到 4

先得到一个月内的数据
得到单个用户消费次数
分等级

WITH cat AS(
SELECT USER_ID,DATEDIFF('2017-12-03',date_format(time,'%Y-%m-%d')) days from USER
WHERE DATEDIFF('2017-12-03',date_format(time,'%Y-%m-%d'))<=30 AND behavior_type='buy'
),
cou AS(
SELECT user_id,COUNT(user_id) hz from cat GROUP BY USER_ID
)
SELECT user_id,CASE 
WHEN hz>=1 AND hz<=32 THEN 0
WHEN hz>=33 AND hz<=64 THEN 1
WHEN hz>=65 AND hz<=96 THEN 2
WHEN hz>=97 AND hz<=128 THEN 3
WHEN hz>=98 AND hz<=161 THEN 4
ELSE 0 END AS hp FROM cou 
ORDER BY hp DESC limit 10 ;