如何用SQL计算两组累计和并实现预算优先人员统计？

需求说明 #

在50000的总预算内，优先容纳senior（资深人员），剩余预算再分配给junior（初级人员），统计最终可容纳的两类人员数量。现有已按position和value升序排序的表数据：

id   position    value
5    senior      10000
6    senior      20000
8    senior      30000
9    junior      5000
4    junior      7000
3    junior      10000

示例逻辑 #

• 优先累加senior的费用：前2名累计花费30000，第3名加入后累计60000超出预算，因此容纳2名senior，剩余预算为50000-30000=20000
• 用剩余预算累加junior的费用：前3名累计花费19000（未超过剩余预算），因此容纳3名junior

期望输出：

juniors    seniors
3          2

实现方案 #

思路 #

1. 计算senior的累计花费，筛选出不超总预算的最大人数，同时算出剩余预算
2. 基于剩余预算计算junior的累计花费，筛选出可容纳的最大人数
3. 合并两类人员的统计结果

SQL代码 #

WITH senior_cum AS (
    SELECT 
        value,
        SUM(value) OVER (ORDER BY value) AS cum_sum,
        COUNT(*) OVER (ORDER BY value) AS count_senior
    FROM your_table_name
    WHERE position = 'senior'
),
senior_stats AS (
    SELECT 
        MAX(count_senior) AS seniors,
        50000 - MAX(cum_sum) AS remaining_budget
    FROM senior_cum
    WHERE cum_sum <= 50000
    UNION ALL
    -- 处理无符合条件senior的情况
    SELECT 0, 50000
    FROM (SELECT 1) AS dummy
    WHERE NOT EXISTS (SELECT 1 FROM senior_cum WHERE cum_sum <= 50000)
),
junior_cum AS (
    SELECT 
        value,
        SUM(value) OVER (ORDER BY value) AS cum_sum,
        COUNT(*) OVER (ORDER BY value) AS count_junior
    FROM your_table_name
    WHERE position = 'junior'
),
junior_stats AS (
    SELECT 
        MAX(count_junior) AS juniors
    FROM junior_cum, senior_stats
    WHERE cum_sum <= senior_stats.remaining_budget
    UNION ALL
    -- 处理无符合条件junior的情况
    SELECT 0
    FROM (SELECT 1) AS dummy
    WHERE NOT EXISTS (SELECT 1 FROM junior_cum, senior_stats WHERE cum_sum <= senior_stats.remaining_budget)
)
SELECT 
    COALESCE((SELECT juniors FROM junior_stats), 0) AS juniors,
    COALESCE((SELECT seniors FROM senior_stats), 0) AS seniors
FROM (SELECT 1) AS dummy;

代码说明 #

• senior_cum：生成senior的累计花费和累计人数，按value升序确保优先选费用低的人员
• senior_stats：提取能容纳的最大senior数量，并计算剩余预算；如果没有senior能纳入预算，返回0和全额预算
• junior_cum：同理生成junior的累计花费和累计人数
• junior_stats：基于剩余预算提取能容纳的最大junior数量；如果无符合条件的junior，返回0
• 最后通过子查询合并结果，COALESCE处理空值场景

注意：替换your_table_name为实际表名；MySQL中无需dual表，用(SELECT 1) AS dummy替代即可。

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