# Write your MySQL query statement below
1731. 每位经理的下属员工数量
Table: Employees
+-------------+----------+ | Column Name | Type | +-------------+----------+ | employee_id | int | | name | varchar | | reports_to | int | | age | int | +-------------+----------+ employee_id 是这个表的主键. 该表包含员工以及需要听取他们汇报的上级经理的ID的信息。 有些员工不需要向任何人汇报（reports_to 为空）。
对于此问题，我们将至少有一个其他员工需要向他汇报的员工，视为一个经理。
编写SQL查询需要听取汇报的所有经理的ID、名称、直接向该经理汇报的员工人数，以及这些员工的平均年龄，其中该平均年龄需要四舍五入到最接近的整数。
返回的结果集需要按照 employee_id 进行排序。
查询结果的格式如下：
Employees table: +-------------+---------+------------+-----+ | employee_id | name | reports_to | age | +-------------+---------+------------+-----+ | 9 | Hercy | null | 43 | | 6 | Alice | 9 | 41 | | 4 | Bob | 9 | 36 | | 2 | Winston | null | 37 | +-------------+---------+------------+-----+ Result table: +-------------+-------+---------------+-------------+ | employee_id | name | reports_count | average_age | +-------------+-------+---------------+-------------+ | 9 | Hercy | 2 | 39 | +-------------+-------+---------------+-------------+ Hercy 有两个需要向他汇报的员工, 他们是 Alice and Bob. 他们的平均年龄是 (41+36)/2 = 38.5, 四舍五入的结果是 39.
原站题解
pythondata 解法, 执行用时: 502 ms, 内存消耗: 67.8 MB, 提交时间: 2024-05-27 11:15:17
import pandas as pd
def count_employees(employees: pd.DataFrame) -> pd.DataFrame:
#分组，统计人数，年龄平均值，改列名
a = employees.groupby('reports_to').agg({'employee_id':'count','age':'mean'}).rename(columns={'age':'average_age','employee_id':'reports_count'})
#连接表，经理名称
a = a.merge(employees,how='left',left_on='reports_to',right_on='employee_id')
#int(x+0.5) 四舍五入
a['average_age'] = a['average_age'].apply(lambda x:int(x+0.5))
return a[['employee_id','name','reports_count','average_age']]
import decimal
# 利用decimal库进行四舍五入
def count_employees2(employees: pd.DataFrame) -> pd.DataFrame:
#round函数规则为“四舍六入五成双”
D = decimal.Decimal
decimal.getcontext().rounding = "ROUND_HALF_UP" # 设置舍入方式为四舍五入
df = employees.loc[~employees["reports_to"].isna()]
df = df.groupby("reports_to").agg(
reports_count = ("employee_id",'count'),
average_age = ("age",lambda x: D(x.mean()).quantize(D('0')))
).reset_index()
df = df.merge(employees,left_on="reports_to",right_on="employee_id")
return df[["employee_id","name","reports_count","average_age"]]
mysql 解法, 执行用时: 424 ms, 内存消耗: 0 B, 提交时间: 2023-04-02 11:30:14
# Write your MySQL query statement below
select
t2.employee_id,
t2.name,
count(1) as reports_count,
round(avg(t1.age)) as average_age
from Employees t1, Employees t2
where t1.reports_to = t2.employee_id
group by 1
order by 1;
mysql 解法, 执行用时: 393 ms, 内存消耗: 0 B, 提交时间: 2023-04-02 11:29:37
# Write your MySQL query statement below
select m.employee_id,
m.name,
count(*) as reports_count,
round(avg(e.age),0) as average_age
from Employees e
join Employees m
on e.reports_to = m.employee_id
group by m.employee_id
order by employee_id;