如何按毕业季度等条件快速计算目标群体的季度工资中位数

核心思路 #

通过筛选目标群体 → 计算群体内各季度工资中位数 → 将中位数关联回原数据的三步流程实现需求，同时支持快速调整筛选条件（毕业季度、种族、项目等）。

以PostgreSQL为例的实现代码 #

-- 1. 筛选目标群体（可灵活调整筛选条件）
WITH target_grads AS (
    SELECT * 
    FROM your_table 
    WHERE grad.date = '20121' -- 可添加更多筛选条件，比如 AND race = 'H' AND program = 'Health'
),
-- 2. 计算目标群体各季度的工资中位数，并转置为列
pivoted_medians AS (
    SELECT
        grad.date,
        -- 计算2012Q1的中位数
        PERCENTILE_CONT(0.5) FILTER (WHERE yrqtr = '20121') WITHIN GROUP (ORDER BY wage) AS "Med.20121",
        -- 计算2012Q2的中位数
        PERCENTILE_CONT(0.5) FILTER (WHERE yrqtr = '20122') WITHIN GROUP (ORDER BY wage) AS "Med.20122",
        -- 可继续添加2012Q3至2020Q4的季度计算，格式同上
        -- PERCENTILE_CONT(0.5) FILTER (WHERE yrqtr = 'XXXXQ') WITHIN GROUP (ORDER BY wage) AS "Med.XXXXQ"
    FROM target_grads
    GROUP BY grad.date
)
-- 3. 将中位数关联回原数据，输出最终结果
SELECT
    t.*,
    pm."Med.20121",
    pm."Med.20122"
    -- 对应添加上面定义的其他季度中位数列
FROM target_grads t
JOIN pivoted_medians pm ON t.grad.date = pm.grad.date;

适配其他数据库的调整 #

如果使用MySQL（8.0+），由于不支持FILTER子句，可改用CASE WHEN实现：

WITH target_grads AS (
    SELECT * 
    FROM your_table 
    WHERE grad_date = '20121'
),
pivoted_medians AS (
    SELECT
        grad_date,
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY CASE WHEN yrqtr = '20121' THEN wage END) AS `Med.20121`,
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY CASE WHEN yrqtr = '20122' THEN wage END) AS `Med.20122`
    FROM target_grads
    GROUP BY grad_date
)
SELECT
    t.*,
    pm.`Med.20121`,
    pm.`Med.20122`
FROM target_grads t
JOIN pivoted_medians pm ON t.grad_date = pm.grad_date;

快速调整筛选条件的方法 #

修改target_grads中的WHERE子句即可：

• 切换毕业季度：将grad.date = '20121'改为grad.date = '20122'
• 添加种族筛选：AND race = 'W'
• 添加项目筛选：AND program = 'Business'

结果验证 #

针对示例数据中的2012Q1毕业群体（PersonA、PersonD）：

• 2012Q1工资为50、100，中位数为75
• 2012Q2工资为50、100，中位数为75
  输出结果与需求完全匹配。

内容的提问来源于stack exchange，提问作者Connor Hill