大家好,我是云朵君!
一个数据工作者面试数据相关岗位,SQL查询语句是必不可少的笔试环节,今天云朵君给大家带来了某厂一道面试题,附上参考答案,希望能够帮到大家!
◎ 计算2017年每笔投资均大于50万的用户 ◎ 计算2017年仅投资过CFH和AX产品的用户 ◎ 计算归属于10002业务员的投资金额
字段名 | 注释 | 类型 |
---|---|---|
Created_at | 投资时间 | date |
User_id | 用户ID | String |
Inverst_item | 投资产品 | String |
Inverst_amount | 投资金额 | Decimal(38,10) |
字段名 | 注释 | 类型 |
---|---|---|
User_id | 用户ID | String |
Start_date | 开始时间 | date |
End_date | 结束时间 | date |
Agent_id | 业务员ID | String |
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 |
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年 -- 通过条件筛选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年 -- 通过条件筛选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 |
☆ 解析:
① 某个业务员的投资金额,涉及到业务员表和投资表,两张表格可以通过用户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;