SQL查询：计算Quality与低评分占比的子查询问题求助

现有表数据 #

+------------+-------------------+----------+--------+
| query_name | result            | position | rating |
+------------+-------------------+----------+--------+
| Dog        | Golden Retriever  | 1        | 5      |
| Dog        | German Shepherd   | 2        | 5      |
| Dog        | Mule              | 200      | 1      |
| Cat        | Shirazi           | 5        | 2      |
| Cat        | Siamese           | 3        | 3      |
| Cat        | Sphynx            | 7        | 4      |
+------------+-------------------+----------+--------+

需求 #

按query_name分组计算两个指标：

• Quality：rating与position比值的平均值，保留两位小数；
• poor_query_percentage：rating小于3的记录占该query_name总记录数的百分比，保留两位小数。

预期结果 #

+------------+---------+-----------------------+
| query_name | quality | poor_query_percentage |
+------------+---------+-----------------------+
| Dog        | 2.50    | 33.33                 |
| Cat        | 0.66    | 33.33                 |
+------------+---------+-----------------------+

当前问题SQL #

用户编写的SQL中，Quality计算正常，但poor_query_percentage的子查询无法关联主查询分组，导致计算错误：

SELECT query_name, 
CAST(AVG(rating/position+.0) AS DECIMAL(4,2)) as quality,
(select COUNT(q.rating)from queries as q join queries as b on q.result = b.result where q.rating < 3 GROUP BY q.query_name )/((select count(rating)+.0  from queries))*100

FROM queries
group by query_name

单独统计rating<3的记录数可得到分组结果，但无法关联主查询：

select count(rating)
FROM queries
WHERE rating < 3
GROUP BY query_name

解决方法 #

无需子查询关联，直接在分组聚合中使用条件聚合函数计算占比即可：

SELECT 
    query_name,
    CAST(AVG(rating / position + 0.0) AS DECIMAL(4, 2)) AS quality,
    CAST(
        SUM(CASE WHEN rating < 3 THEN 1 ELSE 0 END) / COUNT(*) * 100 
        AS DECIMAL(5, 2)
    ) AS poor_query_percentage
FROM queries
GROUP BY query_name;

说明 #

1. Quality计算：保留原逻辑，通过AVG(rating/position+.0)确保除法为浮点运算，再转换为两位小数；
2. poor_query_percentage计算：
   • SUM(CASE WHEN rating <3 THEN 1 ELSE 0 END)：统计当前分组中rating<3的记录数；
   • COUNT(*)：获取当前分组的总记录数；
   • 两者相除后乘以100得到百分比，再转换为两位小数。

这种方式直接在分组聚合中完成计算，逻辑清晰且结果准确。

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