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

数据仓库晨曦

发布于 2026-07-08 14:16:00

文章被收录于专栏：数据仓库技术数据仓库技术

一、题目背景

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

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

二、题目

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

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

员工薪资表 t9_emp_salary：

+---------+----------+-----------+
| emp_id  | dept_id  |  salary   |
+---------+----------+-----------+
| 001     | D01      | 30000.00  |
| 002     | D01      | 25000.00  |
| 003     | D01      | 28000.00  |
| 004     | D02      | 20000.00  |
| 005     | D02      | 22000.00  |
+---------+----------+-----------+

三、思路分析

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

正向排序：ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary ASC) 获得升序行号 rn
反向排序：ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) 获得降序行号 rn_desc
取交叉位置：WHERE abs(rn - rn_desc) <= 1 筛选出中间1行（奇数）或2行（偶数）
求平均：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

执行结果

+----------+-----------+-----+----------+
| dept_id  |  salary   | rn  | rn_desc  |
+----------+-----------+-----+----------+
| D01      | 30000.00  | 3   | 1        |
| D01      | 28000.00  | 2   | 2        |
| D01      | 25000.00  | 1   | 3        |
| D02      | 22000.00  | 2   | 1        |
| D02      | 20000.00  | 1   | 2        |
+----------+-----------+-----+----------+
5 rows selected (0.932 seconds)(https://www.dwsql.com)

步骤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       |
+----------+----------------+
2 rows selected (9.11 seconds)(https://www.dwsql.com)

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。

六、举一反三

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

七、知识点总结

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

八、建表语句和数据插入

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);

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2026-07-04，如有侵权请联系 cloudcommunity@tencent.com 删除

sql