一道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;
腾讯云开发者
