数据分析师笔试试卷五：SQL、Python——（YH）

一、SQL笔试题目：
1、
a) 数据表：

b) 数据字典：

1.1-请用SQL语句查询2020年3月22日申请结果为通过的订单号（申请id），并写出结果：

SELECT DISTINCT ap_id 
FROM base_apl 
WHERE DATE_FORMAT(ap_time, ‘%Y/%m/%d’) = '2020/03/22' 
AND final_result = '通过';

1.2-请用SQL语句查询2020年3月22日放款成功的总金额以及放款件均(注：放款件均=放款成功总金额/放款成功的订单数)

SELECT 
    SUM(loan_amt) AS total_amt, 
    AVG(loan_amt) AS avg_amt 
FROM base_loan 
WHERE loan_date = '2020/03/22' 
AND due_type = '1';

1.3-请用SQL语句查询每一天申请的订单数量，通过的数量，通过率，放款总金额，放款件均（base_apl 左关联 base_loan）
注：通过率=通过的总订单数/申请的总订单数

SELECT ap_date, total_aps, ROUND(approved_aps/total_aps,2) as approved_rare, total_loan_amt, avg_loan_amt FROM(
SELECT 
    DATE_FORMAT(a.ap_time, ‘%Y/%m/%d’) AS ap_date
    COUNT(a.ap_id) AS total_aps,
    COUNT(CASE WHEN final_result=’通过’THEN a.ap_id) AS approved_aps,
    SUM(b.loan_amt) AS total_loan_amt,
    AVG(b.loan_amt) AS avg_loan_amt
FROM base_apl a
LEFT JOIN base_loan b ON a.id = b.ap_id
GROUP BY DATE_FORMAT(a.ap_time, ‘%Y/%m/%d’
) c
ORDER BY ap_date;

2-请描述SQL语句执行顺序
SELECT
t1.x
,count(1) as cnt
FROM tb as t1
INNER JOIN tx as t2
ON t1.x = t2.x
WHERE t1.y = 2
GROUP BY t1.x
HAVING count(1)>2
ORDER BY x

执行顺序如下：

1. FROM tb AS t1
2. INNER JOIN tx AS t2 ON t1.x = t2.x
3. WHERE t1.y = 2
4. GROUP BY t1.x
5. HAVING count(1) > 2
6. SELECT t1.x, count(1) AS cnt
7. ORDER BY x

3-存款客户偏好分析中，需要对用户购买存款产品的行为进行统计。现有用户购买行为表记录了客户ID、购买的产品ID和每次购买的金额，如下表，请统计每个产品购买金额前3的用户，输出产品ID、客户ID和购买金额。

假设表名为purchase_behaviors，其中客户ID为customer_id，产品ID为product_id，购买金额为purchase_amount

SELECT product_id, customer_id, total_purchase_amount
FROM (
    SELECT product_id, customer_id, SUM(purchase_amount) AS total_purchase_amount,
        RANK() OVER (PARTITION BY product_id ORDER BY SUM(purchase_amount) DESC) AS rank
    FROM purchase_behaviors
    GROUP BY product_id, customer_id
) subquery
WHERE rank <= 3;

二、Python题目

4.1-请用python代码统计表a中每月通过率

import pandas as pd

# 假设表a数据已经加载到DataFrame且名称为df
df['apl_dt'] = pd.to_datetime(df['apl_dt'])
df['month'] = df['apl_dt'].dt.to_period('M')

monthly_accept_rate=df[df['decision']=='Accept'].groupby('month').size() / df.groupby('month').size()
print(monthly_accept_rate)

4.2-请用python代码统计表a中拒绝客户命中规则的条数分布

reject_rules_count=df[df['decision']=='Reject'].groupby('apl_no').size()
print(reject_rules_count.value_counts())

4.3-请用python代码统计表a中拒绝客户的拒绝原因合并列为一列并用“/”链接，最终结果如下表：

rejects=df[df['decision']=='Reject'].groupby(['apl_no','apl_dt'])['rule_name'].apply(lambda x: '/'.join(x)).reset_index()
rejects.columns = ['apl_no', 'apl_dt', 'rule_name']
print(rejects)

5-以base_spl为主表、base_loan为副表，主键ap_id，使用Python内链接生成新的数据表：（10分）

merged_df = pd.merge(base_spl, base_loan, on='ap_id', how='inner')
print(merged_df)

6-假设存在数据df_data,其中目标变量为label(好、坏占比95:5)，请使用Python按照7:3的比例划分train/test数据集且train/test数据量目标变量label中好、坏标签占比依旧保持95:5，随机种子设定为1234。

from sklearn.model_selection import train_test_split

# 假设数据已经加载到DataFrame且名称为df_data
train,test=train_test_split(df_data,test_size=0.3,stratify=df_data['label'], random_state=1234)
print(train['label'].value_counts(normalize=True))
print(test['label'].value_counts(normalize=True))

7-请描述Python中深拷贝与浅拷贝的区别：
浅拷贝：创建一个新的对象，但新的对象中的元素是原对象中元素的引用。修改一个对象中的元素会影响另一个对象。
深拷贝：创建一个新的对象，并递归地拷贝所有原对象中的元素。两个对象完全独立，互不影响。

三、附加题：
8-不使用工具的前提下，如何估算 取值（精确到0.01），介绍一下你的计算思路。
取值约等于 1.73，计算思路如下：
首先，我们知道 1 的平方是 1，2 的平方是 4，所以 的值在 1 到 2 之间。
接下来，可以先尝试 1.5 的平方，即 1.5×1.5 = 2.25，因为 2.25 小于 3，所以 大于 1.5 。
再尝试 1.6 的平方，1.6×1.6= 1.96，因为 2.56 小于 3，所以 在 1.6 到 2 之间。
然后计算 1.7 的平方，1.7×1.7 = 2.89，2.89 小于 3，所以 在 1.7 到 2之间。
继续计算 1.8 的平方，1.8×1.8 = 3.24，3.24大于 3，所以 在 1.7 到 1.8 之间。
再计算 1.75 的平方，1.75×1.75 = 3.0625，3.0625大于 3，所以 在 1.7 到 1.75 之间。
再计算 1.74的平方，1.74×1.74 = 3.0276，3.0276大于 3，所以 在 1.7 到 1.754之间。
最后计算 1.73的平方，1.73×1.73= 2.9929，2.9929 小于 3，所以根号 3 约为 1.73 。