如何将Pandas按职位分组的无异常值薪资计算转换为SQL

问题背景 #

现有PostgreSQL的salary表，结构与样例数据如下：

原方案用Pandas实现，核心异常值判断逻辑为：按position分组后，薪资下限为中位数-25分位数，上限为中位数+75分位数，剔除不在该区间的异常值后计算各职位的薪资均值或中位数。但该方案需从数据库拉取3万条数据到应用端计算，希望直接在PostgreSQL中完成所有逻辑，仅返回600条职位的薪资结果。

已实现含异常值的均值计算SQL，但无法扩展实现剔除异常值后的逻辑，尝试的SQL均无法正常运行。

解决方案 #

核心思路 #

完全复刻Pandas的逻辑：先通过窗口函数按position分组计算中位数、25分位数、75分位数，筛选出非异常值后，再按职位聚合计算最终的均值或中位数。

1. 剔除异常值后计算薪资均值 #

SELECT 
    position,
    ROUND(AVG(CAST(salary AS NUMERIC)), 0) AS avg_salary
FROM (
    SELECT 
        position,
        salary,
        -- 按职位分组计算中位数
        MEDIAN(salary) OVER (PARTITION BY position) AS med,
        -- 按职位分组计算25分位数
        PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY salary) OVER (PARTITION BY position) AS q25,
        -- 按职位分组计算75分位数
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY salary) OVER (PARTITION BY position) AS q75
    FROM salary
    WHERE the_date >= '2023-08-26'
) AS sub
-- 筛选非异常值：薪资处于[中位数-25分位数, 中位数+75分位数]区间内
WHERE salary BETWEEN (med - q25) AND (med + q75)
GROUP BY position
ORDER BY avg_salary ASC;

2. 剔除异常值后计算薪资中位数 #

SELECT 
    position,
    ROUND(MEDIAN(CAST(salary AS NUMERIC)), 0) AS median_salary
FROM (
    SELECT 
        position,
        salary,
        MEDIAN(salary) OVER (PARTITION BY position) AS med,
        PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY salary) OVER (PARTITION BY position) AS q25,
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY salary) OVER (PARTITION BY position) AS q75
    FROM salary
    WHERE the_date >= '2023-08-26'
) AS sub
WHERE salary BETWEEN (med - q25) AND (med + q75)
GROUP BY position
ORDER BY median_salary ASC;

关键说明 #

• OVER (PARTITION BY position)实现按职位分组计算统计量，对应Pandas的groupby('position')
• PERCENTILE_CONT(0.25)/PERCENTILE_CONT(0.75)对应Pandas的s.quantile(0.25)/s.quantile(0.75)，MEDIAN()对应s.median()
• 子查询先为每条薪资记录计算所属职位的统计量，外层筛选非异常值后再聚合，完全匹配原Pandas逻辑
• CAST(salary AS NUMERIC)确保数值计算精度，避免整数运算误差

内容的提问来源于stack exchange，提问作者Marcel Suleiman