SQL实现高分申请者多维度约束分组抽样的技术方案咨询

Hey there! Let's break down your problem step by step since you're just starting out with SQL. First, we'll tackle selecting the top 400 applicants by score, then dive into the trickier part of grouping them while enforcing your constraints.

First, we need to isolate the 400 applicants with the highest scores. We'll use a window function to handle ties carefully: ROW_NUMBER() ensures we get exactly 400 people (even if multiple applicants have the same score), while RANK() would include all applicants tied at the 400th cutoff (which might result in more than 400 people). Choose the one that fits your needs:

WITH top_400 AS (
    SELECT
        *,
        -- Use RANK() instead if you want to include all tied applicants at the cutoff
        ROW_NUMBER() OVER (ORDER BY Score DESC) AS score_rank
    FROM Application
)
SELECT *
FROM top_400
WHERE score_rank <= 400;

Your idea of filling groups from highest to lowest score while enforcing constraints is perfect. The catch is that SQL is a set-based language, so it doesn't naturally handle row-by-row assignments like this. We'll use a recursive CTE to simulate this process, tracking each group's state as we add applicants one by one.

First, let's translate your constraints into concrete numbers to make them easier to code:

• Each group has exactly 40 people.
• No nationality makes up more than 20% of a group → max 8 people per nationality per group.
• Gender balance: each gender must be at least 40% of the group → min 16 people of one gender, max 24 of the other.
• Age split: we'll assume you want reasonable balance (no group is all under 28 or all over 28) → enforce that each age group has at least 10 people in the final group.

Recursive CTE Implementation #

This approach has 3 core parts:

1. Prepare the top 400 applicants with an order (so we process highest scores first).
2. Initialize 10 empty groups, tracking counts for nationality, gender, and age.
3. Recursively assign each applicant to the first group that meets all constraints.

Here's the code (note: this uses MySQL JSON functions—adjust JSON syntax for PostgreSQL/SQL Server if needed):

WITH top_applicants AS (
    -- Get top 400 applicants, ordered by score, and categorize age
    SELECT
        *,
        ROW_NUMBER() OVER (ORDER BY Score DESC) AS applicant_order,
        CASE WHEN Age < 28 THEN 'young' ELSE 'old' END AS age_group
    FROM Application
    ORDER BY Score DESC
    LIMIT 400
),
initial_groups AS (
    -- Create 10 empty groups with initial count tracking
    SELECT
        group_id,
        0 AS total_members,
        JSON_OBJECT() AS nationality_counts, -- Tracks how many of each nationality are in the group
        JSON_OBJECT('M', 0, 'F', 0) AS gender_counts, -- Adjust gender values to match your data
        JSON_OBJECT('young', 0, 'old', 0) AS age_counts
    FROM (
        SELECT 1 AS group_id UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
        SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
        SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
    ) AS group_ids
),
recursive_assignment AS (
    -- Base case: start with empty groups
    SELECT
        group_id,
        total_members,
        nationality_counts,
        gender_counts,
        age_counts,
        0 AS last_assigned_order
    FROM initial_groups

    UNION ALL

    -- Recursive step: assign the next applicant to a valid group
    SELECT
        g.group_id,
        g.total_members + 1 AS total_members,
        -- Update nationality count for the applicant's nationality
        JSON_SET(
            g.nationality_counts,
            CONCAT('$.', a.nationality),
            COALESCE(JSON_EXTRACT(g.nationality_counts, CONCAT('$.', a.nationality)), 0) + 1
        ) AS nationality_counts,
        -- Update gender count
        JSON_SET(
            g.gender_counts,
            CONCAT('$.', a.gender),
            JSON_EXTRACT(g.gender_counts, CONCAT('$.', a.gender)) + 1
        ) AS gender_counts,
        -- Update age group count
        JSON_SET(
            g.age_counts,
            CONCAT('$.', a.age_group),
            JSON_EXTRACT(g.age_counts, CONCAT('$.', a.age_group)) + 1
        ) AS age_counts,
        a.applicant_order AS last_assigned_order
    FROM recursive_assignment r
    -- Get the next applicant to assign
    CROSS JOIN (
        SELECT * FROM top_applicants WHERE applicant_order = r.last_assigned_order + 1
    ) a
    -- Join to find valid groups
    JOIN initial_groups g ON
        -- Group isn't full yet
        g.total_members < 40
        -- Nationality count won't exceed 8 after adding this applicant
        AND COALESCE(JSON_EXTRACT(g.nationality_counts, CONCAT('$.', a.nationality)), 0) < 8
        -- Gender balance is maintained: adding this gender won't push it below 16 or above 24
        AND (JSON_EXTRACT(g.gender_counts, CONCAT('$.', a.gender)) + 1) BETWEEN 16 AND 24
        -- Age balance: adding this age group won't make the other age group drop below 10
        AND (JSON_EXTRACT(g.age_counts, CONCAT('$.', a.age_group)) + 1) <= 30
    -- Prioritize groups with the fewest members first, then groups with the most remaining slots for this nationality
    ORDER BY g.total_members,
             (8 - COALESCE(JSON_EXTRACT(g.nationality_counts, CONCAT('$.', a.nationality)), 0)) DESC
    LIMIT 1 -- Pick the first valid group
),
-- Link final group assignments back to the original applicant data
final_groups AS (
    SELECT
        a.*,
        r.group_id
    FROM recursive_assignment r
    JOIN top_applicants a ON a.applicant_order = r.last_assigned_order
)
-- Final output: grouped applicants, sorted by group and score
SELECT * FROM final_groups ORDER BY group_id, Score DESC;

Key Tips for Beginners #

1. JSON for Dynamic Count Tracking: We use JSON to keep track of how many people from each nationality/gender/age are in each group. Different databases have slightly different JSON tools—adjust the syntax if you're using PostgreSQL (jsonb) or SQL Server.
2. Recursive Logic: The CTE starts with empty groups, then adds one applicant at a time, always picking the first valid group. The ORDER BY in the recursive step determines which group gets priority (we fill under-populated groups first).
3. Edge Cases: If you hit a scenario where an applicant can't be assigned to any group (e.g., all groups are already at 8 members of their nationality), check if your top 400 has an over-represented nationality (more than 80 people from one country—since 10 groups × 8 = 80). You may need to adjust constraints or your selection criteria.
4. Simpler Alternatives: If you don't need strict row-by-row assignment, you could use window functions to distribute applicants, but this won't enforce all your constraints. The recursive approach is the most reliable way to meet all your rules.

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