求优化：基于CTE实现按ID统计M/F高频性别值的SQL代码

按ID统计每个ID下出现频率最高的性别值（仅匹配'M'/'F'，大小写不敏感，其他值忽略），规则如下：

• 非'M'/'F'的性别值不参与频率计算；
• 若频率统计无唯一最高值，结果为'inconclusive'；
• 若有唯一最高值，返回该性别值。

当前使用CTE实现的代码可得到正确结果，但结构较为繁琐：

WITH cte1 AS (
                SELECT 
             id,
            sex,
            RANK() OVER (PARTITION BY id ORDER BY count(*)) rn
        FROM dataset
        WHERE sex ~* '(F|M)' AND sex IS NOT NULL
        GROUP BY id, sex
        ),
    cte2 AS (
         SELECT id,
                       max(rn) AS max
        FROM cte1
        GROUP BY id
        ),
    cte3 AS (
        SELECT cte2.id,
                       sex
        FROM cte2
        LEFT JOIN cte1 ON cte2.id=cte1.id AND max=rn
        WHERE cte1.id IS NOT NULL 
        ),
    cte4 AS (   
        SELECT id,
                      count(*) as cnt
        FROM cte3
        GROUP BY id
        )
SELECT DISTINCT cte4.id,
               CASE 
               WHEN cnt>1 THEN 'inconclusive'
               WHEN cnt=1 AND SEX IN ('F', 'M') THEN sex
               END AS sex
FROM cte4
LEFT JOIN cte3 ON cte4.id=cte3.id

希望寻求更简洁的实现方案。曾尝试用DISTINCT ON()，但无法正确返回ID=1的'inconclusive'结果（会因排序返回F或M）。

可以通过简化CTE层级，利用窗口函数直接完成统计与判断：

WITH gender_stats AS (
    SELECT
        id,
        sex,
        COUNT(*) AS freq,
        RANK() OVER (PARTITION BY id ORDER BY COUNT(*) DESC) AS rnk
    FROM dataset
    WHERE sex ~* '^(F|M)$'  -- 精确匹配纯F/M，避免误判含F/M的其他字符串
      AND sex IS NOT NULL
    GROUP BY id, sex
),
top_gender_count AS (
    SELECT
        id,
        sex,
        COUNT(*) OVER (PARTITION BY id) AS top_count
    FROM gender_stats
    WHERE rnk = 1
)
SELECT DISTINCT
    id,
    CASE
        WHEN top_count > 1 THEN 'inconclusive'
        ELSE sex
    END AS sex
FROM top_gender_count
ORDER BY id;

方案说明 #

1. gender_stats：先筛选有效性别，按ID+性别分组统计频率，同时用RANK()计算每个性别在对应ID下的频率排名（降序）。
2. top_gender_count：筛选出每个ID下排名第一的性别，同时统计该ID下排名第一的性别数量。
3. 最终通过CASE逻辑判断：若排名第一的性别数量大于1，返回'inconclusive'；否则返回对应性别值。

该方案压缩了CTE层级，逻辑更直观，同时优化了正则匹配规则，避免误匹配包含F/M的非目标字符串。

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