PostgreSQL预算招聘SQL优化：一行展示两类人员招聘数量

表结构 #

CREATE TABLE people (
  id int,
  seniority_level varchar(255),
  salary int
);

需求 #

在40000的预算下，优先雇佣尽可能多的senior人员，剩余预算再雇佣尽可能多的junior人员。

现有SQL及问题 #

现有实现逻辑的SQL存在结果格式不符合要求、部分场景处理缺失的问题：

with total_cost AS
(
  SELECT 
    id,
    seniority_level,
    salary,
    SUM(salary) over (partition by seniority_level order by id ASC) as cost
  FROM people
),
senior_can_hire AS
(
  SELECT 
    id,
    seniority_level,
    salary
  FROM total_cost 
  WHERE seniority_level = 'senior' 
  AND cost <=40000
),
junior_can_hire AS
(
  SELECT 
    id, 
    seniority_level,
    salary
  FROM total_cost
  WHERE seniority_level = 'junior'
  AND cost <= 40000 - (SELECT SUM(salary) FROM senior_can_hire)
)
SELECT 
  seniority_level,
  COUNT(id) AS NUM_HIRES
FROM senior_can_hire
GROUP BY seniority_level
UNION
SELECT 
  seniority_level,
  COUNT(id) AS NUM_HIRES
FROM junior_can_hire
GROUP BY seniority_level

问题场景示例 #

CASE1

插入数据：

INSERT INTO people values(20, 'junior', 10000);
INSERT INTO people values(30, 'senior', 15000);
INSERT INTO people values(40, 'senior', 30000);

• 当前结果：两行数据，每行对应一类人员的招聘数量
• 期望结果：一行两列，分别显示senior_hires=1、junior_hires=1

CASE2

插入数据：

INSERT INTO people values(20, 'senior', 10000);
INSERT INTO people values(30, 'senior', 15000);
INSERT INTO people values(40, 'senior', 30000);

• 当前结果：仅显示senior可雇佣2人
• 期望结果：一行两列，显示senior_hires=2、junior_hires=0

优化后的SQL #

WITH senior_cumulative AS (
    SELECT 
        id,
        salary,
        SUM(salary) OVER (ORDER BY id ASC) AS running_total
    FROM people
    WHERE seniority_level = 'senior'
),
senior_hired AS (
    SELECT 
        COUNT(id) AS senior_hires,
        COALESCE(SUM(salary), 0) AS senior_total_cost
    FROM senior_cumulative
    WHERE running_total <= 40000
),
junior_cumulative AS (
    SELECT 
        id,
        salary,
        SUM(salary) OVER (ORDER BY id ASC) AS running_total
    FROM people
    WHERE seniority_level = 'junior'
),
junior_hired AS (
    SELECT 
        COUNT(id) AS junior_hires
    FROM junior_cumulative
    WHERE running_total <= (40000 - (SELECT senior_total_cost FROM senior_hired))
)
SELECT 
    (SELECT senior_hires FROM senior_hired) AS senior_hires,
    COALESCE((SELECT junior_hires FROM junior_hired), 0) AS junior_hires;

优化说明 #

1. 单独计算senior的累计薪资，筛选出预算内可雇佣人数和总花费，用COALESCE处理无senior的情况
2. 用剩余预算计算junior可雇佣人数，同样用COALESCE确保无junior时显示0
3. 最终将两个统计结果合并为一行两列，完全匹配期望格式

内容的提问来源于stack exchange，提问作者SQL Bot