数据分析
1.分析技术人员电脑内银行卡交易流水，给出转入的对手交易卡号有多少？[答案格式:10][★★☆☆☆]
99
解压财务流水.rar，得到一堆csv.
copy *.csv Merged.csv
写到.bat执行合并成一个csv Merged.csv
将Merged.csv导入数据库
SELECT COUNT(DISTINCT 交易对手账卡号) FROM Merged WHERE 交易是否成功 = 1 AND 收付标志 = '进'
2.分析技术人员电脑内银行卡交易流水，给出转出的对手交易卡号有多少个？[答案格式:1][★★☆☆☆]
9
SELECT COUNT(DISTINCT 交易对手账卡号) FROM Merged WHERE 交易是否成功 = 1 AND 收付标志 = '出'
3.分析技术人员电脑内银行卡交易流水，给出卡号"6233542760791453"金额转出比(保留两位有效小数)？[答案格式:10.21%[提示：注意文件编码][★★★★★]
11.11%
SELECT
SUM(CASE WHEN 交易是否成功 = 1 AND 收付标志 = '进' THEN CAST(交易金额 AS FLOAT) ELSE 0 END) AS 转入成功,
SUM(CASE WHEN 交易是否成功 = 1 AND 收付标志 = '出' THEN CAST(交易金额 AS FLOAT) ELSE 0 END) AS 转出成功,
SUM(CASE WHEN 交易是否成功 = 1 AND 收付标志 = '出' THEN CAST(交易金额 AS FLOAT) ELSE 0 END) / SUM(CASE WHEN 交易是否成功 = 1 AND 收付标志 = '进' THEN CAST(交易金额 AS FLOAT) ELSE 0 END) AS 转出比
FROM
Merged
WHERE
交易卡号='6233542760791453';
4.分析技术人员电脑内银行卡交易流水，给出金额转出比最大的卡号？[答案格式:xxxx][提示：注意文件编码][★★★★★]
6202628427760809
SELECT
交易卡号,
SUM(CASE WHEN 交易是否成功 = '01' AND 收付标志 = '进' THEN CAST(交易金额 AS FLOAT) ELSE 0 END) AS 转入成功,
SUM(CASE WHEN 交易是否成功 = '01' AND 收付标志 = '出' THEN CAST(交易金额 AS FLOAT) ELSE 0 END) AS 转出成功,
SUM(CASE WHEN (CASE WHEN 交易是否成功 = '01' THEN 1 ELSE 0 END) = 1 AND 收付标志 = '出' THEN CAST(交易金额 AS FLOAT) ELSE 0 END) / NULLIF(SUM(CASE WHEN (CASE WHEN 交易是否成功 = '01' THEN 1 ELSE 0 END) = 1 AND 收付标志 = '进' THEN CAST(交易金额 AS FLOAT) ELSE 0 END), 0) AS 转出比
FROM
Merged
GROUP BY
交易卡号
ORDER BY
转出比
DESC
5.分析技术人员电脑内银行卡交易流水，给出收益最大的卡号？[答案格式:xxxxx][提示：注意文件编码][★★★★★]
6264488446694651
SELECT
交易卡号,
SUM(CASE WHEN 收付标志 = '进' AND (CASE WHEN 交易是否成功 = '01' THEN 1 ELSE 0 END) = 1 THEN CAST(交易金额 AS FLOAT) ELSE 0 END) AS 转入成功总额
FROM
Merged
GROUP BY
交易卡号
ORDER BY
转入成功总额 DESC;
文章来源: http://mp.weixin.qq.com/s?__biz=Mzg3NTU3NTY0Nw==&mid=2247487379&idx=1&sn=1ccc9a55ba935b8a9d91b4a6cb4c37f1&chksm=cf3e2fa3f849a6b5319cdf6ad13ec278a46e98790eaf6b976bf452a1f0c167ce5ffcd49dfdca#rd
如有侵权请联系:admin#unsafe.sh
如有侵权请联系:admin#unsafe.sh