一、表结构
二、题目要求
创建数据库exam,并创建相应的表:
CREATE DATABASE exam;
USE exam;
CREATE TABLE `UserBehavior` (
`user_id` int NOT NULL,
`item_id` int DEFAULT NULL,
`ategory_id` int NOT NULL,
`behavior_type` varchar(255) NOT NULL,
`time` DATETIME NOT NULL
);
设置‘权限准备导入数据
set global local_infile=on;
exit;
mysql -uroot --local-infile=1 -p
123QWEasd!
use userbehavior;
导入数据
load data local infile '/root/ml-1m/UserBehavior.dat' into table UserBehavior
CHARACTER SET utf8
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY ''
ESCAPED BY '\\'
LINES TERMINATED BY '\n';
然后完成下列题目:
1统计 uv 值(一共有多少用户访问淘宝)(10 分)
SELECT COUNT(DISTINCT user_id) AS uv FROM `userbehavior`;
2 分别统计浏览行为为点击,收藏,加入购物车,购买的总数量(10 分)
SELECT behavior_type,COUNT(behavior_type) FROM userbehavior GROUP BY behavior_type;
3.统计用户最近购买时间。以 2017-12-03 为当前日期,计算时间范围
为一个月,计算用户最近购买时间,时间的区间为 0-30 天,将其分为 5 档,0-6 天,7-12
天,13-18 天,19-24 天,25-30 天分别对应评分 4 到 0(15 分)
WITH
temp AS(
SELECT user_id,FROM_UNIXTIME(TIME,'%Y-%m-%d') times FROM userbehavior WHERE behavior_type="pv"
),
temp1 AS(
SELECT user_id,MAX(TIMESTAMPDIFF(DAY,times,'2017-12-03'))times FROM temp GROUP BY user_id
)
SELECT user_id,
CASE WHEN times>='0' AND times<='6' THEN '4'
WHEN times>='7' AND times<='12' THEN '3'
WHEN times>='13' AND times<='18' THEN '2'
WHEN times>='19' AND times<='24' THEN '1'
ELSE '0' END AS grade
FROM temp1
4 统计用户的消费频率。以 2017-12-03 为当前日期,计算时间范围为
一个月,计算用户的消费次数,用户中消费次数从低到高为 1-161 次,将其分为 5
档,1-32,33-64,65-96,97-128,129-161 分别对应评分 0 到 4(15 分)
select count(count) 0档 from (select user_id,count(user_id) count from user where behavior_type = "buy" group by user_id) a where count<=32;
select count(count) 1档 from (select user_id,count(user_id) count from user where behavior_type = "buy" group by user_id) a where count between 33 and 64;
select count(count) 2档 from (select user_id,count(user_id) count from user where behavior_type = "buy" group by user_id) a where count between 65 and 96;
select count(count) 3档 from (select user_id,count(user_id) count from user where behavior_type = "buy" group by user_id) a where count between 97 and 128;
select count(count) 4档 from (select user_id,count(user_id) count from user where behavior_type = "buy" group by user_id) a where count between 129 and 161;
WITH cte AS
(
SELECT user_id,COUNT(user_id) LEVEL
FROM userbehavior
WHERE FROM_UNIXTIME(TIME,%Y-%m-%d)>='2017-11-03' AND FROM_UNIXTIME(TIME,%Y-%m-%d)<='2017-12-03'
AND behavior_type='buy'
GROUP BY user_id
)
SELECT user_id,CASE
WHEN LEVEL>1 AND LEVEL <=32 THEN 0
WHEN LEVEL>1 AND LEVEL <=32 THEN 1
WHEN LEVEL>1 AND LEVEL <=32 THEN 2
WHEN LEVEL>1 AND LEVEL <=32 THEN 3
WHEN LEVEL>1 AND LEVEL <=32 THEN 4
ELSE 0 END AS LEVEL
FROM cte
ORDER BY LEVEL