8，SQL训练之:力扣1211,查询结果的质量和占比

目录

一，原题力扣链接

二，题干

三，建表语句

四，分析

五，SQL解答

解法一 邪道流  avg的妙用 直接求平均值

解法二 正规解法 case when 语法判定

 解法三，子查询，大肠包小肠

六，验证

七，知识点总结

---

一，原题力扣链接

. - 力扣（LeetCode）

二，题干

Queries 表： 

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| query_name  | varchar |
| result      | varchar |
| position    | int     |
| rating      | int     |
+-------------+---------+
此表可能有重复的行。
此表包含了一些从数据库中收集的查询信息。
“位置”（position）列的值为 1 到 500 。
“评分”（rating）列的值为 1 到 5 。评分小于 3 的查询被定义为质量很差的查询。

将查询结果的质量 quality 定义为：

各查询结果的评分与其位置之间比率的平均值。

将劣质查询百分比 poor_query_percentage 为：

评分小于 3 的查询结果占全部查询结果的百分比。

编写解决方案，找出每次的 query_name 、 quality 和 poor_query_percentage。

quality 和 poor_query_percentage 都应 四舍五入到小数点后两位 。

以 任意顺序 返回结果表。

结果格式如下所示：

示例 1：

输入：
Queries table:
+------------+-------------------+----------+--------+
| 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 | poor_query_percentage |
+------------+---------+-----------------------+
| Dog        | 2.50    | 33.33                 |
| Cat        | 0.66    | 33.33                 |
+------------+---------+-----------------------+
解释：
Dog 查询结果的质量为 ((5 / 1) + (5 / 2) + (1 / 200)) / 3 = 2.50
Dog 查询结果的劣质查询百分比为 (1 / 3) * 100 = 33.33

Cat 查询结果的质量为 ((2 / 5) + (3 / 3) + (4 / 7)) / 3 = 0.66
Cat 查询结果的劣质查询百分比为 (1 / 3) * 100 = 33.33

三，建表语句

Create table If Not Exists Queries (query_name varchar(30), result varchar(50), position int, rating int);
Truncate table Queries;
insert into Queries (query_name, result, position, rating) values ('Dog', 'Golden Retriever', '1', '5');
insert into Queries (query_name, result, position, rating) values ('Dog', 'German Shepherd', '2', '5');
insert into Queries (query_name, result, position, rating) values ('Dog', 'Mule', '200', '1');
insert into Queries (query_name, result, position, rating) values ('Cat', 'Shirazi', '5', '2');
insert into Queries (query_name, result, position, rating) values ('Cat', 'Siamese', '3', '3');
insert into Queries (query_name, result, position, rating) values ('Cat', 'Sphynx', '7', '4');
insert into Queries (query_name, result, position, rating) values ('Cat1', 'Sphynx11', '2', '2');
insert into Queries (query_name, result, position, rating) values ('Cat2', 'Sphynx11', '4', '5');

四，分析

*
题解
一张表 查询表
表中的字段：第一列 是查询姓名 查询结果 查询位置  查询分数
题目要求的字段 1，查询分数和查询位置之间的比率的平均值
2，查询小于3 在该组内的占比

分析：
第一步，开一个新列 查询评分/位置 得到新的列 取名 比率  quality
第二步，一拆下来名字分组 ，求比率的平均值
第三步  当评分小于3为一个 / 改组的总次数  四舍五入到小数点后两位   poor_query_percentage
 第三步有三个解法
解法一 暴力解法 avg的邪道用法
avg(rating<3)  AVG(rating<3)实际上计算的是所有行中rating<3为TRUE（即1）的行数与总行数的比例


解法二 大肠包小肠的用法
拿到条件为  where rating<3  的分组 次数
拿到分组的 全部次数
然后连接这2个表 互相除一下  拿到比 之后拼接到主表里面去
期间还要考虑 如果它只有一组 它的评分仅仅大于3分 他们它的第一个就是null null和任何做运算都是null 所以我们需要边一下
这个比较麻烦  不推荐
第一步，拿到 名称，和前面的一个比率结果

第二步开始造第三个字段
先拿到小于3 的次数  在拿到大于3的次数 然后做一个除法

最后一步 拼接表

或者提取拼接表


解法三 正规解法
用case when then
sum（但他满足条件给它标记为1 反之标记为0 ） 统计一下满足的数量  然后/ 总数
在四舍五入一下  得到最终的结果


*/

五，SQL解答

解法一 邪道流  avg的妙用 直接求平均值

select query_name,
       round(avg(rating/position),2) as quality,
       round(avg(rating<3),2) as poor_query_percentage
       from queries group by query_name;

解法二 正规解法 case when 语法判定

select
    query_name,
    round(AVG(rating / position), 2) AS quality,
    round(100.0 * SUM(CASE WHEN rating < 3 THEN 1 ELSE 0 END) / COUNT(*), 2) AS poor_query_percentage
from
    queries
WHERE
    query_name IS NOT NULL
GROUP BY
    query_name;

 解法三，子查询，大肠包小肠

with  t1 as (
    select query_name,
       round(avg(rating/position),2) as quality
from Queries where query_name is not null group by query_name
), t2 as(
    select query_name as q2,count(*) as c_2 from Queries where rating<3 group by query_name
    ),
    t3 as(
        select query_name as q3,count(*) as c_3 from queries group by query_name
    ),
    t4 as (
        select t2.q2,ifnull(round( t2.c_2/t3.c_3 *100,2) ,0)as poor_query_percentage from t2 right join t3 on t2.q2=t3.q3
    ), t5 as(
    select query_name,quality,ifnull(poor_query_percentage,0) poor_query_percentage
from t1 left join t4 on t1.query_name=t4.q2
    )
select * from t5;

六，验证

邪道：

正规解法

大肠包小产解法

七，知识点总结

总结

• avg(条件)  邪道流拿到比率  条件为布尔值 转为1或者0  然后满足的 在总的里面的比率

正规

• case 条件 when 1 else 0 / 总次数

麻烦

• 满足条件的一个表 的次数  / 不需要条件的表 的次数
• avg 函数的用法
• case then 的用法
• if not null的用法
• 分组 聚合 求平均的用法
• 左连接 查询的用法