阿里巴巴大数据面试SQL-员工薪资中位数计算

⚠️ 待修正

一、题目背景

这道题来自阿里巴巴数据平台部的数据分析岗面试。薪资中位数是HR数据分析中的核心指标——相比平均数，中位数不受极端值（如高管天价年薪）的影响，更能反映"普通员工到底拿多少钱"。阿里每年做薪资对标和调薪预算时，都要按部门计算中位数做基准线。

业务场景：HRBP 拿到各部门薪资中位数后，可以快速判断哪些部门的薪资水平偏低（中位数低于行业50分位），是否需要启动专项调薪。SQL中位数计算是数据分析师的必会技能，在牛客网和LeetCode上也都是高频题。

二、题目

现有一张员工薪资表 t9_emp_salary，请计算每个部门的薪资中位数。

中位数定义：按薪资排序后，奇数行取中间值，偶数行取中间两个值的平均数。

员工薪资表 t9_emp_salary：

+--------+---------+--------+
| emp_id | dept_id | salary |
+--------+---------+--------+
| 001    | D01     | 30000  |
| 002    | D01     | 25000  |
| 003    | D01     | 28000  |
| 004    | D02     | 20000  |
| 005    | D02     | 22000  |
+--------+---------+--------+

三、思路分析

中位数的核心是双向定位中间位置：

1. 正向排序：ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary ASC) 获得升序行号 rn
2. 反向排序：ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) 获得降序行号 rn_desc
3. 取交叉位置：WHERE abs(rn - rn_desc) <= 1 筛选出中间1行（奇数）或2行（偶数）
4. 求平均：AVG(salary) 对筛选出的值取平均，奇数时等价于取自身

维度	评分
题目难度	⭐️⭐️⭐️⭐️
题目清晰度	⭐️⭐️⭐️⭐️
业务常见度	⭐️⭐️⭐️⭐️

四、逐步推导

步骤1：双向排序——同时生成升序和降序行号

对每个部门的员工，按薪资升序和降序分别生成两个行号。升序的 rn 从最低薪资=1 开始，降序的 rn_desc 从最高薪资=1 开始。

select dept_id, salary,
       row_number() over (partition by dept_id order by salary asc) as rn,
       row_number() over (partition by dept_id order by salary desc) as rn_desc
from t9_emp_salary

D01 部门（3人，奇数）的执行结果：

+---------+--------+----+---------+
| dept_id | salary | rn | rn_desc |
+---------+--------+----+---------+
| D01     | 25000  | 1  | 3       |
| D01     | 28000  | 2  | 2       |  ← |2-2|=0≤1，入选
| D01     | 30000  | 3  | 1       |
+---------+--------+----+---------+

D02 部门（2人，偶数）的执行结果：

+---------+--------+----+---------+
| dept_id | salary | rn | rn_desc |
+---------+--------+----+---------+
| D02     | 20000  | 1  | 2       |  ← |1-2|=1≤1，入选
| D02     | 22000  | 2  | 1       |  ← |2-1|=1≤1，入选
+---------+--------+----+---------+

关键观察：奇数行时只有中间一行满足 |rn - rn_desc| <= 1；偶数行时中间两行都满足。

步骤2：筛选中间位置 + 按部门取平均

执行SQL

select dept_id, round(avg(salary), 2) as median_salary
from (
    select dept_id, salary,
           row_number() over (partition by dept_id order by salary) as rn,
           row_number() over (partition by dept_id order by salary desc) as rn_desc
    from t9_emp_salary
) t
where abs(rn - rn_desc) <= 1
group by dept_id

执行结果

+---------+---------------+
| dept_id | median_salary |
+---------+---------------+
| D01     | 28000.00      |
| D02     | 21000.00      |
+---------+---------------+

D01 共3人，中位数 = 28000（正中间那个人）。D02 共2人，中位数 = (20000+22000)/2 = 21000。

五、常见坑点

坑1：AVG 对奇数行无效？不，正好相反

有人担心奇数行时 AVG 会错误计算——实际不会。因为奇数行时 |rn - rn_desc| <= 1 只筛选出唯一一行，AVG(salary) = salary 自身。AVG 可以同时处理奇数和偶数场景，无需 CASE WHEN 分支。

坑2：PERCENTILE 函数不是标准SQL

Spark SQL 有 PERCENTILE_APPROX(col, 0.5) 可以近似计算中位数，但它是近似算法，结果不如双向行号法精确。Hive 中需要用 PERCENTILE(col, 0.5) 且语法不同。面试中展示双向行号法更体现功底。

坑3：薪资为 NULL 的员工需要排除

如果 salary 有 NULL，NULL 在 ORDER BY 中默认排到最后（Spark SQL: NULLS LAST），会导致行号偏移。处理方式：WHERE salary IS NOT NULL。

六、举一反三

1. 不带分组的中位数：去掉 PARTITION BY dept_id，直接 ROW_NUMBER() OVER (ORDER BY salary) 计算全公司薪资中位数
2. 四分位数：把条件 <= 1 改为取特定分位点的行号 —— 25%分位取 rn = ceil(total * 0.25) 和相邻行
3. NTILE 分组法：NTILE(4) OVER (ORDER BY salary) 直接用四分位函数简化分组，但只能得到分位区间，不能得到精确值
4. 引入加权中位数：如果薪资按人头加权（如各部门人数不同），则需在排序前按人数扩展行再计算

七、知识点总结

考点	说明
ROW_NUMBER 双向排序	升序 + 降序双行号，通过差值筛选中间位置
ABS(rn - rn_desc) <= 1	奇数行取中间1行，偶数行取中间2行
AVG + GROUP BY	统一处理奇偶数场景，无需分支
窗口函数不缩行	同一 SELECT 中两个 ROW_NUMBER 并列计算，不互相干扰

八、建表语句和数据插入

点击展开 DDL & DML

CREATE TABLE t9_emp_salary (
    emp_id  string COMMENT '员工ID',
    dept_id string COMMENT '部门ID',
    salary  decimal(10,2) COMMENT '薪资'
) COMMENT '员工薪资表';

INSERT INTO t9_emp_salary VALUES
('001','D01',30000),('002','D01',25000),('003','D01',28000),
('004','D02',20000),('005','D02',22000);

📱关注公众号

「数据仓库技术」文章同步更新，不错过每一篇干货

💬加群交流

备注「数据仓库技术」加入社群，每日一道大厂SQL真题