如何用SQL计算符合条件条目占总数的百分比？——分组统计学生达标率异常问题求解

嘿，我完全懂你研究一整天的挫败感——这个SQL逻辑坑真的很容易踩！先帮你把问题拆明白，再给你几个实用的解决方案。

问题出在哪？ #

你原来的查询里，FROM group_counts, group_goodies这种逗号分隔的写法是旧式的交叉连接，相当于没有任何关联条件的CROSS JOIN，这会产生笛卡尔积：每个组的总人数会和所有组的达标人数两两配对计算。比如如果有3个组，你会得到3×3=9行结果，计算出来的百分比自然是乱的，既会出现超过100%的错误值，行数也远多于实际符合条件的组数。

而且你还没处理“某个组完全没有达标学生”的情况，这种组会在group_goodies里不存在，直接用内连接的话会被漏掉（不过你原来的写法因为笛卡尔积反而没体现这个问题，但逻辑上是缺陷）。

解决方案1：修复你的CTE写法 #

给两个CTE加上正确的关联条件，用LEFT JOIN确保所有组都被考虑，再用COALESCE处理没有达标学生的情况：

WITH group_goodies AS (
    SELECT group_n, COUNT(id) AS goodies
    FROM students
    WHERE rating >= 4
    GROUP BY group_n
),
group_counts AS (
    SELECT group_n, COUNT(id) AS total_count
    FROM students
    GROUP BY group_n
)
SELECT 
    gc.group_n,
    -- 用COALESCE把NULL转成0，避免没有达标学生时计算出错
    CAST(COALESCE(gg.goodies, 0) AS FLOAT) / gc.total_count AS percentage_of_goodies
FROM group_counts gc
-- 左连接确保所有组都被保留
LEFT JOIN group_goodies gg ON gc.group_n = gg.group_n
-- 筛选百分比≥60%的组
WHERE CAST(COALESCE(gg.goodies, 0) AS FLOAT) / gc.total_count >= 0.6
ORDER BY gc.group_n;

解决方案2：更简洁的单聚合查询（推荐） #

其实完全不需要两个CTE，用CASE表达式在同一个聚合里同时计算达标数和总人数，一步到位：

SELECT 
    group_n,
    -- 统计达标人数/总人数
    CAST(SUM(CASE WHEN rating >= 4 THEN 1 ELSE 0 END) AS FLOAT) / COUNT(id) AS percentage_of_goodies
FROM students
GROUP BY group_n
-- 用HAVING筛选聚合后的结果（WHERE是筛选行，HAVING是筛选分组）
HAVING CAST(SUM(CASE WHEN rating >= 4 THEN 1 ELSE 0 END) AS FLOAT) / COUNT(id) >= 0.6
ORDER BY group_n;

这个写法更高效，逻辑也更清晰：SUM(CASE...)会给每个达标学生计1，不达标计0，求和就是达标人数；COUNT(id)是组内总人数，直接相除得到百分比，最后用HAVING过滤出符合条件的组。

解决方案3：用窗口函数实现 #

如果需要保留更多中间数据（比如每个学生的行同时显示组内百分比），窗口函数是个好选择：

-- 适用于支持QUALIFY的数据库（PostgreSQL、BigQuery、Snowflake等）
SELECT DISTINCT
    group_n,
    CAST(SUM(CASE WHEN rating >=4 THEN 1 ELSE 0 END) OVER (PARTITION BY group_n) AS FLOAT) 
    / COUNT(id) OVER (PARTITION BY group_n) AS percentage_of_goodies
FROM students
QUALIFY percentage_of_goodies >= 0.6
ORDER BY group_n;

如果是MySQL这类不支持QUALIFY的数据库，改成子查询过滤：

SELECT group_n, percentage_of_goodies
FROM (
    SELECT 
        group_n,
        CAST(SUM(CASE WHEN rating >=4 THEN 1 ELSE 0 END) OVER (PARTITION BY group_n) AS FLOAT) 
        / COUNT(id) OVER (PARTITION BY group_n) AS percentage_of_goodies
    FROM students
) AS sub_query
WHERE percentage_of_goodies >= 0.6
GROUP BY group_n, percentage_of_goodies
ORDER BY group_n;

最后验证下结果 #

这几种写法都会输出你想要的格式：

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