数据分析
数据分析
2024-12-8
容器密码
mW7@B!tRp*Xz46Y9#KFUV^J2&NqoHqTpLCE%8rvGW(AX#1k@YL3$M5!bWY*9HLFq7UZR6^T!XoVmPK28J&CY9%6(Arz#tbU4oXYKLp7Wq^FV9H
检材:通过网盘分享的文件:团队赛检材容器 链接: https://pan.baidu.com/s/1GjitvFChM3VmOzKA1Gz46Q?pwd=znck 提取码: znck
这里推荐使用mysql+AI,其实基本都是问的AI
1. 分析数据库检材,请分别计算该数据库中每个用户审核通过的提现总金额,写出审核通过的提现总金额最大值; (2.0分)
343916.84
SELECT uid,sum(money) as all_money FROM `user_tcash` where status =1 GROUP BY uid ORDER BY all_money desc
2. 数据库中用户真实名称为“祝钦”的有效银行卡号是多少; (2.0分)
72282800515037280116
select * from user_bank where uid =(SELECT uid from user_info where real_name='祝钦') and status =1
3. 请计算数据库中地址在江苏省的启用用户数量; (2.0分)
803
SELECT count(*) from user_address where province='江苏省' and status =1
4. 请计算手机号字段中的号码数字“8”的数量大于等于3的用户数量; (4.0分)
2809
这里有个坑注意那个88888888888
,当时用的SELECT * from user_info where tel like '%8%8%8%'
因此在实际算的时候开头要为1
SELECT count(*) from user_info where tel like '1%8%8%8%'
5. 计算每个用户审核通过的总提现金额与其总消费金额的比值,该比值大于等于0.5的用户数量是多少? (4.0分)
323
SELECT
COUNT(*) AS qualified_count
FROM
(SELECT
w.uid,
(w.total_withdrawn/c.total_consumed) AS ratio
FROM
(SELECT uid, SUM(money) AS total_withdrawn
FROM user_tcash
WHERE status = 1
GROUP BY uid) w
JOIN
(SELECT uid, SUM(money) AS total_consumed
FROM log_cash
WHERE `change` = 2
GROUP BY uid) c
ON
w.uid = c.uid
WHERE
(w.total_withdrawn/c.total_consumed) >= 0.5) AS subquery;