Skip to main content

数据分析

数据分析​

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

image-20241208175502188

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

image-20241208175549969

2. 数据库中用户真实名称为“祝钦”的有效银行卡号是多少； (2.0分)​

72282800515037280116

select * from user_bank where uid =(SELECT uid from user_info where real_name='祝钦') and status =1

image-20241208175617611

3. 请计算数据库中地址在江苏省的启用用户数量； (2.0分)​

803

SELECT count(*) from user_address where province='江苏省' and status =1

image-20241208175644244

4. 请计算手机号字段中的号码数字“8”的数量大于等于3的用户数量； (4.0分)​

2809

这里有个坑注意那个88888888888，当时用的SELECT * from user_info where tel like '%8%8%8%'

image-20241208175851719

因此在实际算的时候开头要为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;

image-20241208180101632

6. 计算uid为“20257”的用户的下线最大层级数是多少（自己为第1层，若用户A是自己的直接下线，那么此时用户A所在层级=2）； (4.0分)​

25

mysql8.0以上的版本使用：

WITH RECURSIVE Subordinates AS (
  SELECT uid, parent, 1 AS level
  FROM user_info
  WHERE uid = '20257'
  UNION ALL
  SELECT u.uid, u.parent, s.level + 1
  FROM user_info u
  INNER JOIN Subordinates s ON u.parent = s.uid
)
SELECT MAX(level) AS subordinate_levels
FROM Subordinates;

当然，我当时是用的python，队友用的Mysql，结果都是25。

python用我在龙信杯里计算层级的脚本，修改一下即可

image-20241208180924055