2021-05-26

Queries Quality and Percentage

需求：编写一组 SQL 来查找每次查询的名称(query_name)、质量(quality) 和 劣质查询百分比(poor_query_percentage)。

质量(quality) 和劣质查询百分比(poor_query_percentage) 都应四舍五入到小数点后两位。

展示效果：

query_name	quality	poor_query_percentage
Dog	2.50	33.33
Cat	0.66	33.33

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

最终SQL:

select
      query_name,
      round(avg(rating/position), 2) as quality ,
      round((count(if(rating<3, True, null)) / count(query_name)) *100 , 2) as poor_query_percentage
from
      61_Queries
group by 
      query_name
order by query_name desc;