查看数据
查看文件行数
[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 ;