数据分析师 Mysql练习题（附答案）

练习一 分组、过滤、连接

知识点：分组group by、过滤where…having、连接left/right…join…on

现有投资表和业务员表两张表，表结构及数据如下：

#创建invest表
create table invest(
created_at date,
user_id varchar(20),
invest_item varchar(100),
investamount decimal(38,10));
#给invest插入数据
insert into invest values
("2017/11/1","A123","CFH",100000),
("2017/12/5","A123","AX",450000),
("2017/12/11","A123","CH",700000),
("2017/12/6","B456","CFH",1500000),
("2017/12/26","B456","AX",800000),
("2018/1/6","C789","JUIN",300000);
#创建agent表
create table agent(
user_id varchar(20),
start_date date,
end_date date,
agent_id varchar(20));
#给agent插入数据
insert into agent values
("A123","2016/1/1","2017/12/4",10001),
("A123","2017/12/5","3001/12/31",10002),
("B456","2015/10/31","2016/12/15",10001),
("B456","2016/12/16","3001/12/31",10003),
("C789","2015/1/1","3001/12/31",10002);

开始做题：
1、计算2017年每笔投资均大于50万的用户

select user_id from invest  #从invest表中查询user_id
where year(created_at)=2017  #条件为created_at=2017
group by user_id   #对user_id分组
having min(investamount)>500000; #条件最小投资金额investamount小于50万

2、计算2017年仅投资过CFH和AX产品的用户

select user_id from invest
where year(created_at)=2017
group by user_id 
having sum(invest_item="CFH")*sum(invest_item="AX") #同时满足两个条件
and count(distinct invest_item)=2; #容易入坑的地方：投资项目要等于2

3、计算归属于10002业务员的投资金额

SELECT b.agent_id,SUM(investamount) 投资金额
FROM invest a JOIN agent b on a.user_id=b.user_id  #连接两表
and a.created_at BETWEEN b.start_date and b.end_date #投资年份需要在业务员的工作时间范围内
WHERE b.agent_id='10002';  #条件：业务员编号为10002