数据分析师笔试试卷三：Excel、SQL、Python、算法

一、Excel找出某列重复值的三种方式

在 Excel 中找出某列重复值有以下三种方式：

1. 使用条件格式：选中需要查找重复值的列，在“条件格式”中选择“突出显示单元格规则”，再点击“重复值”，根据需求选择相应的颜色，点击“确定”后，重复值就会被标记出来；
2. 使用COUNTIF：在需要查找重复值的列旁边插入一列，输入公式=COUNTIF(范围, 单元格引用)>1，其中“范围”是要检查重复值的范围，“单元格引用”是当前单元格的引用。如果结果为 TRUE，则表示该单元格中的值是重复的，否则为非重复值；
3. 使用数据透视表：选中需要查找重复值的列，点击“插入”菜单栏，选择“数据透视表”。在弹出的对话框中，确认数据区域无误，并选择放置数据透视表的位置。在数据透视表字段列表中，将需要统计的字段拖到“行”区域，并再次拖到“值”区域。在值区域的下拉菜单中，选择“计数”或“计数项”，即可查看每个值的重复次数。
4. 使用VLOOKUP：选中需要查找重复值的列复制到新列进行删除重复项，再使用vlookup能V到的就是重复值。

二、SQL语法书写顺序和执行分别是什么？

在 SQL 中，语法的书写顺序通常如下：

SELECT [列名列表]
FROM [表名]
JOIN [关联表名] ON [关联条件]
WHERE [筛选条件]
GROUP BY [分组列名]
HAVING [分组后的筛选条件]
ORDER BY [排序列名] [排序方式]

然而，SQL 的执行顺序则有所不同，大致顺序如下：

1. FROM：首先确定要从哪些表中获取数据，并进行表之间的连接操作。
2. WHERE：对连接后的结果根据指定的条件进行筛选，过滤出符合条件的数据。
3. GROUP BY：按照指定的列对数据进行分组。
4. HAVING：对分组后的结果进行筛选。
5. SELECT：选择要显示的列和进行计算。
6. ORDER BY：对结果按照指定的列和排序方式进行排序。

例如，如果有一张名为 students 的表，包含 id、name、age 和 grade 列，要找出年龄大于 18 岁且平均成绩大于 80 分的学生姓名和平均成绩，并按照平均成绩降序排列，可以这样写 SQL 语句：

SELECT name, AVG(grade) AS average_grade
FROM students
WHERE age > 18
GROUP BY name
HAVING AVG(grade) > 80
ORDER BY average_grade DESC;

在这个例子中，首先从 students 表获取数据，然后根据 WHERE 条件筛选出年龄大于 18 岁的记录，接着按照 name 进行分组，再通过 HAVING 筛选出平均成绩大于 80 分的分组，然后在 SELECT 中选择要显示的列和计算平均成绩，最后按照平均成绩降序排列结果。

三、SQL中两个表进行连接，一个部门表，一个职工薪资表，找每个部门中薪资最高的为多少？

以下是使用 SQL 解决此问题的示例代码：

SELECT d.department_name, MAX(e.salary) AS max_salary
FROM departments d
JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;

上述代码的工作原理如下：

首先，使用 JOIN 操作将 departments 表和 employees 表根据部门 id 进行连接。

然后，使用 GROUP BY 按照部门名称对结果进行分组。

最后，在每个分组中使用 MAX 函数找出薪资的最大值，并将其命名为 max_salary 。

假设 departments 表包含 department_id 和 department_name 列，employees 表包含 employee_id 、department_id 和 salary 列。

四、Python中两个DataFrame进行连接，一个部门表，一个职工入职时间表，并且每个部门中入职时间降序排列？

以下是使用 Python 的 pandas 库来实现两个 DataFrame 的连接，并对每个部门的入职时间进行降序排列的示例代码：

import pandas as pd

# 假设部门表 departments 包含 'department_id' 和 'department_name' 列
departments = pd.DataFrame({
    'department_id': [1, 2, 3],
    'department_name': ['研发', '销售', '市场']
})

# 假设职工入职时间表 employees 包含 'employee_id', 'department_id', 'hire_date' 列
employees = pd.DataFrame({
    'employee_id': [101, 102, 103],
    'department_id': [1, 2, 1],
    'hire_date': ['2020-01-01', '2021-05-05', '2019-10-10']
})

# 首先将两个表按照 'department_id' 进行连接
merged_df = pd.merge(departments, employees, on='department_id')

# 然后按照 'department_id' 分组，并对每组的 'hire_date' 进行降序排列
result = merged_df.groupby('department_id')['hire_date'].apply(lambda x: x.sort_values(ascending=False))

print(result)

五、解释下Python中的解释下pd.merge()

pd.merge() 是 pandas 库中用于执行数据合并操作的函数。

它的主要作用是根据指定的键（列）将两个或多个 DataFrame 合并在一起。

以下是 pd.merge() 的一些关键参数和特点：

1. left 和 right：要合并的两个 DataFrame 对象。

2. on：指定用于连接的列名或列名列表。如果两个 DataFrame 中连接列的名称相同，可以直接使用 on 参数指定。

3. left_on 和 right_on：当左右两个 DataFrame 中的连接列名称不同时，可以分别使用 left_on 和 right_on 指定。

4. how：指定连接的方式，包括 'inner'（内连接，返回两表键的交集）、'outer'（外连接，返回两表键的并集）、'left'（左连接，以左表的键为准）、'right'（右连接，以右表的键为准）。

例如，假设有两个 DataFrame ：

import pandas as pd

df1 = pd.DataFrame({'id': [1, 2, 3], 'name': ['Alice', 'Bob', 'Charlie']})
df2 = pd.DataFrame({'id': [2, 3, 4], 'age': [25, 30, 35]})

# 内连接，只返回 id 相同的行
result_inner = pd.merge(df1, df2, on='id', how='inner') 

# 左连接，以 df1 的 id 为准
result_left = pd.merge(df1, df2, on='id', how='left') 

# 右连接，以 df2 的 id 为准
result_right = pd.merge(df1, df2, on='id', how='right') 

# 外连接，返回 id 的并集
result_outer = pd.merge(df1, df2, on='id', how='outer') 

通过灵活运用 pd.merge() 及其参数，可以满足不同的数据合并需求。

六、相关系数都有什么以及区别

常见的相关系数主要有以下几种：

1. 皮尔逊相关系数（Pearson Correlation Coefficient）：用于衡量两个连续变量之间的线性关系强度和方向。取值范围在 -1 到 1 之间。

   • 例如，研究身高和体重之间的线性关系。

2. 斯皮尔曼等级相关系数（Spearman’s Rank Correlation Coefficient）：基于数据的秩次（排序后的名次）来计算相关性，适用于非线性关系或数据不满足正态分布的情况。

   • 比如，比较学生的语文成绩排名和数学成绩排名之间的相关性。

3. 肯德尔等级相关系数（Kendall’s Rank Correlation Coefficient）：也是基于数据的秩次，常用于评估多个评判者对同一批对象的排序一致性等情况。

   • 例如，多位评委对选手表现的排名一致性。

4. 点二列相关系数（Point-Biserial Correlation Coefficient）：用于一个连续变量和一个真正的二分变量（如性别：男/女）之间的相关性。

5. 二列相关系数（Biserial Correlation Coefficient）：当二分变量是人为划分的（例如，考试成绩 60 分以上为及格，60 分以下为不及格），与连续变量之间的相关性可用二列相关系数。

6. 偏相关系数（Partial Correlation Coefficient）：在控制其他变量的影响后，计算两个变量之间的相关性。

例如，在研究教育程度与收入的关系时，控制工作年限这个变量的影响，计算出的就是偏相关系数。

举例说明：
以下是对上述各种相关系数的举例说明：

1. 皮尔逊相关系数：
   假设我们有一组学生的数学成绩（85, 90, 78, 95, 88）和物理成绩（80, 88, 75, 92, 85）。计算皮尔逊相关系数可以衡量这两门学科成绩之间的线性关系。如果计算得出的相关系数接近 1，说明数学成绩高的学生往往物理成绩也高，存在较强的正线性相关。

2. 斯皮尔曼等级相关系数：
   有一组运动员的短跑比赛排名（1, 3, 2, 5, 4）和跳远比赛排名（2, 4, 1, 5, 3）。由于排名是定序数据，使用斯皮尔曼等级相关系数来衡量这两项比赛成绩的相关性。

3. 肯德尔等级相关系数：
   三位评委对五名选手的评分排名分别为：
   评委 1：A > B > C > D > E
   评委 2：B > A > C > E > D
   评委 3：A > C > B > D > E
   通过计算肯德尔等级相关系数来评估三位评委评分的一致性。

4. 点二列相关系数：
   一组学生，其中一部分是男生，一部分是女生，同时有他们的数学考试成绩。男生和女生构成二分变量，数学成绩是连续变量，点二列相关系数可以衡量性别与数学成绩之间的相关性。

5. 二列相关系数：
   以考试成绩 60 分及以上为及格，60 分以下为不及格，将学生分为两组，同时有他们的数学成绩平均分。及格与不及格是人为划分的二分变量，与数学平均分构成二列相关。

6. 偏相关系数：
   研究学生的数学成绩、语文成绩和每天学习时间的关系。在控制每天学习时间这个变量的影响下，计算数学成绩和语文成绩之间的偏相关系数，以更准确地了解它们之间的直接关系。

七、连续变量与连续变量、连续变量与分类变量、分类变量与分类变量的相关性分别用什么？

对于连续变量和连续变量之间的相关性，常用的方法有：

1. 皮尔逊相关系数（Pearson correlation coefficient）：用于衡量两个连续变量之间的线性关系强度和方向。它的值介于 -1 到 1 之间，-1 表示完全负相关，1 表示完全正相关，0 表示无线性相关。
2. 斯皮尔曼等级相关系数（Spearman’s rank correlation coefficient）：不依赖于数据的分布，适用于非线性关系或数据不满足正态分布的情况。

对于连续变量和分类变量之间的相关性，可以使用以下方法：

1. 方差分析（Analysis of Variance，ANOVA）：如果分类变量有两个以上的类别，可以使用单因素方差分析来检验连续变量在不同类别之间的均值是否存在显著差异。
2. 独立样本 t 检验：当分类变量只有两个类别时，可以使用 t 检验来比较连续变量在这两个类别中的均值差异。

例如，有一组学生的数学成绩（连续变量）和性别（分类变量），要研究性别对数学成绩的影响，可以使用 t 检验；如果是研究不同年级（分类变量，如初一、初二、初三）学生的数学成绩差异，则可以使用方差分析。

对于分类变量与分类变量之间的相关性分析，可以使用以下方法：

1. 卡方检验（Chi-square test）：用于检验两个分类变量之间是否存在关联。它通过比较观察到的频数与期望频数的差异来判断两个变量之间的独立性。

例如，研究性别（男/女）和是否吸烟（是/否）之间的关系，可以构建一个二维列联表，然后进行卡方检验。

2. Cramer’s V 系数：卡方检验得出显著结果后，可以进一步计算 Cramer’s V 系数来衡量关联的强度。其值介于 0 到 1 之间，0 表示无关联，1 表示完全关联。

3. Phi 系数：当两个分类变量都是二元变量（只有两个类别）时，可以使用 Phi 系数来衡量关联程度。

假设我们有一个关于婚姻状况（已婚/未婚）和是否拥有房产（是/否）的调查数据，就可以通过这些方法来分析它们之间的相关性。

八、客户流失分析预测中得到的k-means聚类画像群体和决策树预测模型有什么关系？

在客户流失分析预测中，K-Means 聚类画像群体和决策树预测模型之间存在一定的关系：

K-Means 聚类画像群体：

• 作用：通过将客户数据分组，形成具有相似特征的客户群体画像。
• 优点：帮助直观地了解客户的自然分组模式，发现不同类型的客户群体，例如高价值且稳定的客户群、低价值但活跃的客户群等。
• 局限性：它主要是一种描述性的方法，不能直接用于预测客户是否会流失。

决策树预测模型：

• 作用：基于输入的特征变量来构建预测规则，从而预测客户是否会流失。
• 优点：能够生成易于理解和解释的规则，对新数据进行预测。
• 局限性：可能会过度拟合数据，对噪声敏感。

两者的关系：

• 数据准备：K-Means 聚类得到的群体特征可以作为决策树模型的输入特征之一，丰富数据的维度。
• 模型改进：聚类结果可以帮助我们更好地理解数据的分布和结构，为决策树模型的构建和优化提供参考。
• 结果解读：决策树的预测结果可以结合 K-Means 聚类的群体画像来更深入地解释客户流失的原因和模式。

例如，通过 K-Means 聚类发现某一群体客户具有较高的消费频率但较低的消费金额，将这个特征输入到决策树模型中，可能会发现这个群体的客户流失与近期服务满意度下降有关。