- 学习:知识的初次邂逅
- 复习:知识的温故知新
- 练习:知识的实践应用
目录
一,原题力扣链接
二,题干
表:
Salary
+-------------+------+ | 列名 | 类型 | +-------------+------+ | id | int | | employee_id | int | | amount | int | | pay_date | date | +-------------+------+ 在 SQL 中,id 是该表的主键列。 该表的每一行表示一个员工一个月的薪资。 employee_id 是来自 Employee 表的外键(reference 列)。表:
Employee
+---------------+------+ | 列名 | 类型 | +---------------+------+ | employee_id | int | | department_id | int | +---------------+------+ 在 SQL 中,employee_id 是该表的主键列。 该表的每一行表示一个员工所属的部门。找出各个部门员工的平均薪资与公司平均薪资之间的比较结果(更高 / 更低 / 相同)。
以 任意顺序 返回结果表。
结果格式如下所示。
示例 1:
输入: Salary 表: +----+-------------+--------+------------+ | id | employee_id | amount | pay_date | +----+-------------+--------+------------+ | 1 | 1 | 9000 | 2017/03/31 | | 2 | 2 | 6000 | 2017/03/31 | | 3 | 3 | 10000 | 2017/03/31 | | 4 | 1 | 7000 | 2017/02/28 | | 5 | 2 | 6000 | 2017/02/28 | | 6 | 3 | 8000 | 2017/02/28 | +----+-------------+--------+------------+ Employee 表: +-------------+---------------+ | employee_id | department_id | +-------------+---------------+ | 1 | 1 | | 2 | 2 | | 3 | 2 | +-------------+---------------+ 输出: +-----------+---------------+------------+ | pay_month | department_id | comparison | +-----------+---------------+------------+ | 2017-02 | 1 | same | | 2017-03 | 1 | higher | | 2017-02 | 2 | same | | 2017-03 | 2 | lower | +-----------+---------------+------------+ 解释: 在三月,公司的平均工资是 (9000+6000+10000)/3 = 8333.33... 部门 '1' 的平均薪资是 9000,因为该部门只有一个员工,其员工号为 '1'。因为 9000 > 8333.33,所以比较结果为 'higher' 部门 '2' 的平均薪资是(6000 + 10000)/ 2 = 8000,该平均薪资是员工号 '2' 和 '3' 的薪资的平均值。因为 8000 < 8333.33,比较结果为 'lower'。 根据同样的公式,对于二月份的平均薪资比较,结果为 'same',因为部门 '1' 和 '2' 都与公司的平均薪资相同,即为 7000。
三,建表语句
import pandas as pd
data = [[1, 1, 9000, '2017/03/31'], [2, 2, 6000, '2017/03/31'], [3, 3, 10000, '2017/03/31'], [4, 1, 7000, '2017/02/28'], [5, 2, 6000, '2017/02/28'], [6, 3, 8000, '2017/02/28']]
salary = pd.DataFrame(data, columns=['id', 'employee_id', 'amount', 'pay_date']).astype({'id':'Int64', 'employee_id':'Int64', 'amount':'Int64', 'pay_date':'datetime64[ns]'})
data = [[1, 1], [2, 2], [3, 2]]
employee = pd.DataFrame(data, columns=['employee_id', 'department_id']).astype({'employee_id':'Int64', 'department_id':'Int64'})
四,分析
题解:
表:薪资表
字段:薪资id,员工id,部门id,日期
表 员工表
字段: 员工id,部门id
求每个月的部门平均薪资和每个月公式的平均薪资 比较
代码实现表格分析的逻辑:
第一步 拼接2个表;
第二步:扩展一个日期列,截取年和月;
第三步: 分别以日期分组 计算工资每个月的平均薪资 和以日期与部门分组 计算公式每个部门 每个月的平均薪;
第四步,映射指定的列, 部门,日期,部门平均薪资列,公式平均薪资列,然后去重;
最后一步:用每个月的部门平均薪资和 每个月公式的平均薪资比较 ;
最后修改列名。
五,Pandas解答
import pandas as pd
def average_salary(salary: pd.DataFrame, employee: pd.DataFrame) -> pd.DataFrame:
# 内连接连接2个表 为啥用内连接 每薪资的要了干嘛?
df = pd.merge(salary,employee,how='inner',on='employee_id')
#拿到日期列
df['date1'] = df['pay_date'].astype(str).str[:7]
#拿到公司 在不同月份的平均薪资
df['g_avg'] = df.groupby('date1')['amount'].transform('mean')
#拿到公司中 不同部门 在不同月的平均薪资
df['dep_avg'] = df.groupby(['date1','department_id'])['amount'].transform('mean')
#只要指定的列
df1 = df[['date1','department_id','g_avg','dep_avg']]
df1 = df1.drop_duplicates().reset_index()
def fun(x):
if x['dep_avg']>x['g_avg']:
return 'higher'
elif x['dep_avg'] == x['g_avg']:
return 'same'
elif x['dep_avg'] < x['g_avg']:
return 'lower'
df1['comparison'] = df1.apply(fun,axis=1)
df2 = df1[['date1','department_id','comparison']]
df2.columns=['pay_month','department_id','comparison']
return df2
average_salary(salary,employee)
六,验证
七,知识点总结
- Pandas中 内连接的运用 API: inner ...how...on
- Pandas中 从日期中截取年-月的运用 API astype.str[]
- Pandas中 分组聚合的运用 API garoupby...transfrom 实现开窗的效果
- Pandas中 自定义函数的运用 API apply
- Pandas中 改名的运用 API columns
- 比较 每个月 每个部门的平均薪资 和每个月 公式的平均薪资
- 学习:知识的初次邂逅
- 复习:知识的温故知新
- 练习:知识的实践应用