Oracle查询按性别均衡分布排序的实现方案求助

需求核心逻辑 #

• 全局按Age升序排列
• 同年龄组内，优先排列当前年龄之前全局累计出现次数更少的性别

实现SQL #

假设目标表名为person，包含字段Name、Age、Sex，可通过以下查询实现需求：

WITH age_sex_summary AS (
    -- 统计每个年龄组内各性别的人数
    SELECT 
        Age,
        Sex,
        COUNT(*) AS group_sex_count
    FROM person
    GROUP BY Age, Sex
),
cumulative_sex_totals AS (
    -- 计算到当前年龄为止，各性别的全局累计次数（不包含当前年龄组）
    SELECT 
        Age,
        Sex,
        -- 累计男性出现次数
        SUM(CASE WHEN src.Sex = 'M' THEN src.group_sex_count ELSE 0 END) 
            OVER (ORDER BY Age ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS total_male,
        -- 累计女性出现次数
        SUM(CASE WHEN src.Sex = 'F' THEN src.group_sex_count ELSE 0 END) 
            OVER (ORDER BY Age ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS total_female
    FROM age_sex_summary src
    -- 确保每个年龄的每个性别都能获取到累计值
    CROSS JOIN (SELECT DISTINCT Sex FROM person) sex_list
)
SELECT 
    p.Name,
    p.Age,
    p.Sex
FROM person p
JOIN cumulative_sex_totals ct 
    ON p.Age = ct.Age AND p.Sex = ct.Sex
ORDER BY 
    p.Age ASC,
    -- 按当前性别的累计次数升序，次数少的优先排列
    CASE p.Sex WHEN 'M' THEN ct.total_male ELSE ct.total_female END ASC,
    -- 同性别同累计次数时，可按姓名补充排序（可根据需求调整）
    p.Name ASC;

逻辑拆解 #

1. age_sex_summary子查询：先按年龄和性别分组，统计每个年龄组内各性别的人数，为后续累计计算打基础。
2. cumulative_sex_totals子查询：
   • 用窗口函数SUM() OVER()按年龄升序遍历，计算当前年龄之前所有年龄组的男/女性累计出现次数（ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING限定只取当前行之前的所有数据）。
   • 通过CROSS JOIN关联所有性别，避免出现某个年龄组缺少对应性别的累计值。
3. 最终查询：关联原表和累计统计结果，按年龄升序排序，同年龄组内根据性别累计次数升序排列；若累计次数相同，可按姓名或其他字段补充排序（可按需修改）。

示例匹配验证 #

• 21岁场景：若此前累计男性1次、女性0次，女性累计次数更少，Emma(F)会排在William(M)之前。
• 22岁场景：若此前累计男性2次、女性1次，女性累计次数更少，Sophia(F)会排在Oliver(M)之前。
• 24岁场景：若此前累计男性3次、女性4次，男性累计次数更少，James(M)会排在Olivia(F)之前。

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