广州某科技公司面试题 ——wang

其中一道为业务题，有懂得人帮忙提点一下，其他编程与sql感觉都没问题
数据分析师面试题

Excel方面：

1、 根据卡注册信息表，写函数匹配交易明细表中卡号对应的姓名和身份证。（结果可以不止一种）
答案：
姓名：=VLOOKUP(A4:A6,$H$3:$J$4,2,0)身份证：=VLOOKUP(A3,$H$3:$J$4,3,0)

或
姓名=INDEX($H$3:$J$4,MATCH(A3,$H$3:$H$4,0),MATCH($B$2,$H$2:$J$2,0))身份证=INDEX($H$3:$J$4,MATCH(A3,$H$3:$H$4,0),MATCH($C$2,$H$2:$J$2,0))

数据库方面：
2、已知表jymx为调取部分银行卡的交易数据，字段名如下：

下面为样例数据：

1）统计出查询卡号的交易情况，结果表要求如下显示：
查询卡号1、交易总金额1、进账总额1、进账次数1、出账总额1、出账次数1、进出账差额1、交易对手数1、进账对手数、出账对手数、最早交易日期、最晚交易日期
备注：进账：借贷标志位进的；出账：借贷标志为出的
答案：

drop PROCEDURE if EXISTS 查询交易情况;

CREATE  PROCEDURE 查询交易情况(in _cxkh int )
BEGIN 

SELECT cxkh 查询卡号,
sum(jyje)                  交易总金额 ,
sum(if(jdbz='进',jyje,0))     进账总额,
sum(if(jdbz='进',1,0))        进账次数,
sum(if(jdbz='出',jyje,0) )    出账总额,
sum(if(jdbz='出',1,0))        出账次数,
sum(if(jdbz='进',jyje,0) )-sum(if(jdbz='出',jyje,0))  进出账差额,
count(DISTINCT jydfzkh)           交易对手数,
COUNT(DISTINCT (if(jdbz='进',jydfzkh,NULL))) 进账对手数,
COUNT(DISTINCT (if(jdbz='出',jydfzkh,NULL)))   出账对手数,
min(jyrq)                    最早交易日期,
max(jyrq)                    最晚交易日期
FROM  jymx  WHERE cxkh=_cxkh ;
 
end;
call 查询交易情况(622**********1);

2）统计出查询卡号与每个对手的交易情况，并按交易金额从大到小计算排名，结果显示如下：
查询卡号、对手卡号、交易总金额、交易总金额排名
备注：交易总金额为查询卡号和对手卡号的总金额，对手存在多个，按总金额从大到小计算排名
答案：

drop PROCEDURE if EXISTS T2;

CREATE  PROCEDURE T2(in _cxkh int )
BEGIN 

SELECT b.* ,@rk :=@rk+1 交易总金额排名 FROM 
(SELECT  
cxkh 查询卡号,
jydfzkh 对手卡号,
SUM(jyje) 交易总金额 
FROM  jymx  WHERE cxkh=_cxkh
GROUP BY jydfzkh asc) b,(SELECT @rk :=0) r;
END;
CALL T2(622**********1);

3）根据jymx表列出可以体现查询卡号交易特征的指标，结果如下：
指标名称 指标作用 指标计算规则
月 总支出/总收入 衡量流水体量 每月账户总收入与总支出
月盈余 衡量用户资金是否健康，是如果总为负数可能需要贷款服务 月总收入-总支出
单周转帐频率 可定义大于平均值为活跃用户 观察期内某用户每日转账次数/7
平均单笔转账额度 用于衡量用户的资金能力 观察期内某用户转账总额/转账次数

编程方面：
3、有一份数据部分如下，比如：刘备和关羽有关系，说明他们是一个团伙，刘备和张飞也有关系，那么刘备、关羽、张飞归为一个团伙，以此类推。用自己熟悉的编程语言根据人员和相关人员计算出团伙分类；

答案：

SELECT DISTINCT 人员, if(人员 =‘刘备’,1,2) 团队 from names_1
WHERE 人员 in (‘曹操’,‘刘备’)
UNION
SELECT 相关人员 人员 ,
if(人员=‘刘备’,1,if(人员=‘曹操’,2,(SELECT if(人员=‘刘备’,1,2) FROM names_1 b WHERE b.相关人员=a.人员 ))) 团队
FROM names_1 a ORDER BY 团队;

python 实现：