一道SQL笔试题

????点击关注｜选择星标｜干货速递????

---

大家好，我是云朵君！

一个数据工作者面试数据相关岗位，SQL查询语句是必不可少的笔试环节，今天云朵君给大家带来了某厂一道面试题，附上参考答案，希望能够帮到大家！

◎ 计算2017年每笔投资均大于50万的用户
◎ 计算2017年仅投资过CFH和AX产品的用户
◎ 计算归属于10002业务员的投资金额

数据字典

投资表 (idwopr.cmn_investment_request)

字段名	注释	类型
Created_at	投资时间	date
User_id	用户ID	String
Inverst_item	投资产品	String
Inverst_amount	投资金额	Decimal(38,10)

业务员表 (idwopr.dim_agent)

字段名	注释	类型
User_id	用户ID	String
Start_date	开始时间	date
End_date	结束时间	date
Agent_id	业务员ID	String

样例数据

投资表(idwopr.cmn_investment_request)

Created_at	User_id	invest_item	invest_amount
2017/11/1 1:32	A123	CFH	100000
2017/12/25 3:42	A123	AX	450000
2017/12/11 17:42	A123	CH	700000
2017/12/6 20:06	B456	CFH	1500000
2017/12/16 14:32	B456	AX	800000
2017/12/26 17:22	B456	AX	600000
2018/11/1 14:32	C789	JUIN	300000

业务员表(idwopr.dim_agent)

User_id	Start_date	End_date	Agent_id
A123	2016/1/1 0:00	2017/12/4 23:59	10001
A123	2017/12/5 0:00	3001/12/31 23:59	10002
B456	2015/10/31 0:00	2016/12/15 23:59	10001
B456	2016/12/16 0:00	3001/12/31 23:59	10003
C789	2015/1/1 0:00	3001/12/31 23:59	10002

参考答案

※ 计算2017年每笔投资均大于50万的用户

☆ 解析：

① 计算2017年 -- 通过条件筛选where以及时间函数year()筛选出来

② 每笔投资均大于50万 -- 当最小投资金额都大于50万时，就可以满足条件

SELECT user_id, MIN(invest_amount)
FROM cmn_investment_request
WHERE YEAR(created_at)=2017
GROUP BY user_id
HAVING MIN(invest_amount) > 500000;

☆ 结果：

user_id	min(invest_amount)
B456	600000

---

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

☆ 解析：

① 计算2017年 -- 通过条件筛选where以及时间函数year()筛选出来

② 仅投资过CFH和AX产品 -- 仅 说明没有其它，可以通过group_concat()函数，配合聚合操作group by将同一个用户、投资过的所有产品汇总起来。得到如下结果

user_id	投资产品
A123	CH,AX,CFH
B456	CFH,AX

③ 利用聚合后筛选函数having设置条件投资产品 = 'CFH,AX' 筛选出最终结果。

SELECT user_id , 
GROUP_CONCAT(DISTINCT invest_item ORDER BY invest_amount DESC) 投资产品
FROM cmn_investment_request
WHERE year(created_at)=2017
GROUP BY user_id
HAVING 投资产品 = 'CFH,AX';

☆ 结果：

user_id	投资产品
B456	CFH,AX

---

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

☆ 解析：

① 某个业务员的投资金额，涉及到业务员表和投资表，两张表格可以通过用户IDuser_id字段链接，并且投资时间需要在该业务员值班时间(开始时间～结束时间)内，才能算是归属于。

② 业务员的总投资金额 -- 需要通过聚合操作 group by，聚合字段是业务员ID agent_id ，并通过聚合求和函数sum() 求出总投资金额。

③ 10002业务员 -- 通过聚合后筛选操作 having 进行筛选。

④ 另外，如果题目中数据不需要聚合，可以使用 where 进行筛选。

-- 方法一
SELECT agent_id, SUM(invest_amount) 
FROM dim_agent 
LEFT JOIN cmn_investment_request 
ON dim_agent.user_id = cmn_investment_request.user_id 
AND created_at BETWEEN start_date AND end_date
GROUP BY agent_id
HAVING agent_id = '10002';

-- 方法二
SELECT agent_id, sum(invest_amount) 
FROM dim_agent 
LEFT JOIN cmn_investment_request 
ON dim_agent.user_id = cmn_investment_request.user_id 
AND created_at BETWEEN start_date AND end_date
WHERE agent_id = '10002';

☆ 结果

agent_id	sum(invest_amount)
10002	1450000

建表与导数

为方便大家联系，这里贴出了数据库、表创建及数据样例导入代码。

CREATE DATABASE STUDIO;
USE STUDIO;

CREATE TABLE cmn_investment_request(
Created_at DATETIME,
User_id VARCHAR(10),
invest_item VARCHAR(10),
invest_amount DECIAL(38,10)
);

CREATE TABLE dim_agent(
User_id VARCHAR(10),
Start_date DATETIME,
End_date DATETIME,
Agent_id VARCHAR(10)
);

INSERT INTO cmn_investment_request VALUES
('2017-11-01 01:32:00','A123','CFH',100000),
('2017-12-25 03:42:00','A123','AX',450000),
('2017-12-11 17:42:00','A123','CH',700000),
('2017-12-06 20:06:00','B456','CFH',1500000),
('2017-12-16 14:32:00','B456','AX',800000),
('2017-12-26 17:22:00','B456','AX',600000),
('2018-11-01 14:32:00','C789','JUIN',300000);

INSERT INTO dim_agent VALUES
('A123','2016-01-01 00:00:00','2017-12-04 23:59:59',10001),
('A123','2017-12-05 00:00:00','3001-12-31 23:59:59',10002),
('B456','2015-10-31 00:00:00','2016-12-15 23:59:59',10001),
('B456','2016-12-16 00:00:00','3001-12-31 23:59:59',10003),
('C789','2015-01-01 00:00:00','3001-12-31 23:59:59',10002);

SELECT * FROM cmn_investment_request;
SELECT * FROM dim_agent;

OK，今天的分享就到这里啦！没看够？点赞在看走起来～后续更精彩～

云朵出品｜必属精品

推荐阅读

一文搞定MySQL多表查询中的表连接(join)

2021-01-27

MySQL高频面试题：一维表转二维表

2021-02-25

擅用子查询，让复杂问题简单化

2021-03-01

MySQL中的通配符与正则表达式

2021-03-04

长按????关注- 数据STUDIO - 选择星标，干货速递