PieCloudDB员工招聘预算SQL查询异常排查及优化求助

问题场景 #

某公司招聘新员工，薪资预算为80000，招聘规则：

• 优先招聘尽可能多的Senior员工；
• 招聘完最多的Senior员工后，用剩余预算招聘尽可能多的Junior员工。

示例数据1 #

预期结果：1名Senior员工和2名Junior员工

示例数据2 #

预期结果：0名Senior员工和2名Junior员工

问题现象 #

在PieCloudDB中执行以下SQL时，示例1结果正确，但示例2得到错误结果：

错误结果：

原SQL语句：

with t1 as (
    select
    *,
    sum(salary) over(partition by experience order by salary) total
    from Candidates
),
t2 as (
    select 'Senior' experience, count(employee_id) accepted_candidates
    from t1
    where experience = 'Senior' and total <=80000
),
t3 as (
    select 'Junior' experience, count(employee_id) accepted_candidates
    from t1
    where experience = 'Junior' and total <=80000 - (
        select max(total)
        from t1
        where experience = 'Senior' and total <=80000
    )   
)
select * 
from t2
union 
select * 
from t3

问题原因排查 #

核心问题出在Junior员工的预算计算逻辑上：
当没有符合条件的Senior员工时（比如示例2中所有Senior薪资都超过80000），子查询select max(total) from t1 where experience = 'Senior' and total <=80000会返回NULL。此时80000 - NULL的结果也是NULL，而t1中Junior的累计薪资total都是正数，正数与NULL做<=比较的结果为UNKNOWN，导致Junior的筛选条件无法匹配任何数据，最终返回0。

优化方案 #

方案1：修复原SQL逻辑 #

用COALESCE函数处理NULL情况，将无符合条件Senior时的预算差值转为0：

with t1 as (
    select
    *,
    sum(salary) over(partition by experience order by salary) total
    from Candidates
),
t2 as (
    select 'Senior' experience, count(employee_id) accepted_candidates
    from t1
    where experience = 'Senior' and total <=80000
),
t3 as (
    select 'Junior' experience, count(employee_id) accepted_candidates
    from t1
    where experience = 'Junior' and total <=80000 - COALESCE(
        (select max(total) from t1 where experience = 'Senior' and total <=80000),
        0
    )   
)
select * 
from t2
union 
select * 
from t3

方案2：更简洁高效的写法 #

先计算Senior可招聘的最大总薪资，再推导Junior可用预算，最后分别统计两类员工的招聘人数：

with candidate_totals as (
    select
        employee_id,
        experience,
        salary,
        sum(salary) over(partition by experience order by salary) as running_total
    from Candidates
),
senior_max_total as (
    select coalesce(max(running_total), 0) as total
    from candidate_totals
    where experience = 'Senior' and running_total <= 80000
)
select
    'Senior' as experience,
    count(employee_id) as accepted_candidates
from candidate_totals
where experience = 'Senior' and running_total <= 80000
union all
select
    'Junior' as experience,
    count(employee_id) as accepted_candidates
from candidate_totals, senior_max_total
where experience = 'Junior' and running_total <= 80000 - senior_max_total.total;

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