数据分析
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;
原文始发于微信公众号（数据取证杂谈）：首届“盘古石杯”全国电子数据取证大赛总决赛-数据分析
