11/24mysql测试题目

一、表结构

二、题目要求

创建数据库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