2026年银行金融业数据分析进阶练习题涵盖SQL与Python分析方法

2026年银行金融业数据分析进阶练习题涵盖SQL与Python分析方法一、SQL查询题（共5题，每题10分）背景：某商业银行数据库包含以下表结构：-`accounts`（账户表）：`account_id`（账户ID，主键），`customer_id`（客户ID），`branch_id`（分行ID），`balance`（余额），`open_date`（开户日期）-`transactions`（交易表）：`transaction_id`（交易ID，主键），`account_id`（账户ID），`amount`（金额），`type`（类型，'Deposit'或'Withdrawal'），`date`（交易日期）-`customers`（客户表）：`customer_id`（客户ID，主键），`name`（姓名），`age`（年龄），`city`（城市），`branch_id`（常用分行ID）-`branches`（分行表）：`branch_id`（分行ID，主键），`name`（分行名称），`region`（区域，'North','South','East','West'）题目1：查询2025年12月开设账户的客户数量及平均年龄，按区域分组，结果按平均年龄降序排列。题目2：查询每个分行的存款总额和取款总额，只显示存款总额大于取款总额的分行信息，结果按存款总额降序排列。题目3：查询年龄在30-40岁之间的客户，统计每个城市的客户数量及平均账户余额，只显示客户数量超过100的城市。题目4：查询每个账户在2026年1月的交易笔数，只显示交易笔数超过10的账户，结果按交易笔数降序排列，并显示账户的常用分行名称。题目5：查询每个城市的客户数量，并统计每个城市的交易总额，结果按交易总额降序排列。二、Python数据分析题（共5题，每题10分）背景：某银行2025年全年的客户交易数据已加载至PandasDataFrame中，字段包括：`transaction_id`（交易ID），`account_id`（账户ID），`amount`（金额），`type`（类型，'Deposit'或'Withdrawal'），`date`（交易日期），`customer_id`（客户ID），`branch_id`（分行ID），`city`（城市）题目1：计算每个城市的月度存款总额和取款总额，绘制柱状图，X轴为月份，Y轴为金额，不同颜色区分存款和取款。题目2：分析每个账户的交易频率（每月交易笔数），筛选出交易频率最高的10个账户，并绘制条形图。题目3：按城市分组，计算每个城市的客户交易总额，筛选出交易总额最高的3个城市，并绘制饼图展示占比。题目4：按月份分组，计算每月的交易总额，并绘制折线图，分析交易总额的季度趋势。题目5：对交易数据进行异常值检测，假设金额超过1百万为异常交易，统计每个城市的异常交易数量，并绘制柱状图。三、综合分析题（共2题，每题15分）背景：某银行需要分析2025年区域业务表现，数据包含账户表、交易表、客户表和分行表。题目1：分析每个区域的客户开户数量及交易活跃度（月均交易笔数），绘制雷达图展示各区域的优势和劣势。题目2：分析高净值客户（账户余额>500万）的交易行为，筛选出交易频率最高的3个城市，并绘制热力图展示交易类型分布（存款/取款）。答案与解析一、SQL查询题答案与解析题目1：sqlSELECTb.region,COUNT(DISTINCTc.customer_id)AScustomer_count,AVG(c.age)ASavg_ageFROMaccountsaJOINcustomerscONa.customer_id=c.customer_idJOINbranchesbONa.branch_id=b.branch_idWHEREa.open_dateBETWEEN'2025-12-01'AND'2025-12-31'GROUPBYb.regionORDERBYavg_ageDESC;解析：1.通过`JOIN`连接`accounts`、`customers`和`branches`表，获取客户信息及分行区域。2.使用`WHERE`过滤2025年12月开户的记录。3.`GROUPBY`按区域分组，统计客户数量和平均年龄。4.`ORDERBY`按平均年龄降序排列。题目2：sqlSELECTASbranch_name,SUM(CASEWHENt.type='Deposit'THENt.amountELSE0END)AStotal_deposit,SUM(CASEWHENt.type='Withdrawal'THENt.amountELSE0END)AStotal_withdrawalFROMaccountsaJOINtransactionstONa.account_id=t.account_idJOINbranchesbONa.branch_id=b.branch_idGROUPBYHAVINGtotal_deposit>total_withdrawalORDERBYtotal_depositDESC;解析：1.通过`JOIN`连接`accounts`、`transactions`和`branches`表。2.使用`SUM`和`CASE`计算每个分行的存款和取款总额。3.`HAVING`过滤存款总额大于取款总额的分行。4.`ORDERBY`按存款总额降序排列。题目3：sqlSELECTc.city,COUNT(DISTINCTc.customer_id)AScustomer_count,AVG(a.balance)ASavg_balanceFROMaccountsaJOINcustomerscONa.customer_id=c.customer_idWHEREc.ageBETWEEN30AND40GROUPBYc.cityHAVINGcustomer_count>100ORDERBYavg_balanceDESC;解析：1.通过`JOIN`连接`accounts`和`customers`表。2.使用`WHERE`过滤30-40岁的客户。3.`GROUPBY`按城市分组，统计客户数量和平均余额。4.`HAVING`过滤客户数量超过100的城市。5.`ORDERBY`按平均余额降序排列。题目4：sqlSELECTa.account_id,COUNT(t.transaction_id)AStransaction_count,ASbranch_nameFROMaccountsaJOINtransactionstONa.account_id=t.account_idJOINbranchesbONa.branch_id=b.branch_idWHEREt.dateBETWEEN'2026-01-01'AND'2026-01-31'GROUPBYa.account_id,HAVINGtransaction_count>10ORDERBYtransaction_countDESC;解析：1.通过`JOIN`连接`accounts`、`transactions`和`branches`表。2.使用`WHERE`过滤2026年1月的交易记录。3.`GROUPBY`按账户ID和分行名称分组，统计交易笔数。4.`HAVING`过滤交易笔数超过10的账户。5.`ORDERBY`按交易笔数降序排列。题目5：sqlSELECTc.city,COUNT(DISTINCTc.customer_id)AScustomer_count,SUM(t.amount)AStotal_amountFROMcustomerscJOINaccountsaONc.customer_id=a.customer_idJOINtransactionstONa.account_id=t.account_idGROUPBYc.cityORDERBYtotal_amountDESC;解析：1.通过`JOIN`连接`customers`、`accounts`和`transactions`表。2.`GROUPBY`按城市分组，统计客户数量和交易总额。3.`ORDERBY`按交易总额降序排列。二、Python数据分析题答案与解析题目1：pythonimportpandasaspdimportmatplotlib.pyplotasplt假设df是加载的交易数据df['date']=pd.to_datetime(df['date'])df['month']=df['date'].dt.monthdf['type']=df['type'].apply(lambdax:'Deposit'ifx=='Deposit'else'Withdrawal')pivot=df.pivot_table(index='month',columns='type',values='amount',aggfunc='sum')pivot.plot(kind='bar',figsize=(10,6))plt.xlabel('月份')plt.ylabel('金额')plt.title('月度存款与取款总额')plt.show()解析：1.将`date`转换为日期格式，提取月份。2.将交易类型统一为'Deposit'或'Withdrawal'。3.使用`pivot_table`按月份和类型分组，计算总额。4.绘制柱状图，X轴为月份，Y轴为金额。题目2：pythondf['month']=df['date'].dt.monthmonthly_transactions=df.groupby(['account_id','month']).size().reset_index(name='transaction_count')top_accounts=monthly_transactions.groupby('account_id').sum().nlargest(10,'transaction_count').indextop_data=monthly_transactions[monthly_transactions['account_id'].isin(top_accounts)]plt.figure(figsize=(12,6))plt.bar(top_data['account_id'],top_data['transaction_count'])plt.xlabel('账户ID')plt.ylabel('交易笔数')plt.title('交易频率最高的10个账户')plt.show()解析：1.提取月份并按账户ID和月份分组，统计交易笔数。2.筛选出交易频率最高的10个账户。3.绘制条形图，X轴为账户ID，Y轴为交易笔数。题目3：pythoncity_total=df.groupby('city')['amount'].sum().sort_values(ascending=False).head(3)plt.pie(city_total.values,labels=city_total.index,autopct='%1.1f%%')plt.title('交易总额最高的3个城市占比')plt.show()解析：1.按城市分组，计算交易总额并排序，取前3名。2.绘制饼图展示占比。题目4：pythondf['month']=df['date'].dt.monthmonthly_total=df.groupby('month')['amount'].sum()plt.figure(figsize=(10,6))plt.plot(monthly_total.index,monthly_total.values,marker='o')plt.xlabel('月份')plt.ylabel('交易总额')plt.title('月度交易总额趋势')plt.grid(True)plt.show()解析：1.提取月份并按月份分组，计算交易总额。2.绘制折线图，分析季度趋势。题目5：pythondf['anomaly']=df['amount'].apply(lambdax:1ifx>1000000else0)city_anomalies=df.groupby('city')['anomaly'].sum().sort_values(ascending=False)plt.figure(figsize=(10,6))plt.bar(city_anomalies.index,city_anomalies.values)plt.xlabel('城市')plt.ylabel('异常交易数量')plt.title('各城市异常交易数量')plt.xticks(rotation=45)plt.show()解析：1.标记金额超过1百万的交易为异常。2.按城市分组，统计异常交易数量。3.绘制柱状图。三、综合分析题答案与解析题目1：pythonimportnumpyasnpimportmatplotlib.pyplotaspltdf['month']=df['date'].dt.monthmonthly_open=df[df['type']=='Deposit'].groupby(['region','month']).size().reset_index(name='open_count')monthly_transactions=df.groupby(['region','month']).size().reset_index(name='transaction_count')monthly_open_pivot=monthly_open.pivot(index='month',columns='region',values='open_count')monthly_transactions_pivot=monthly_transactions.pivot(index='month',columns='region',values='transaction_count')计算月均交易笔数monthly_avg_transactions=monthly_transactions_pivot.mean(axis=0)绘制雷达图labels=np.array(monthly_avg_transactions.index)stats=np.array(monthly_avg_transactions.values)stats=np.transpose([stats])fig=plt.figure(figsize=(6,6))ax=fig.add_subplot(111,polar=True)angles=np.linspace(0,2np.pi,len(labels),endpoint=False)angles=np.concaten