MySQL基础练习题37-查找结果的质量和占比

目录

题目

准备数据

分析数据

总结

---

题目

找出每次的 query_name 、 quality 和 poor_query_percentage。

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

准备数据

## 创建库
create database db;
use db;## 创建表
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');

分析数据

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

selectquery_name,round(avg(rating/position),2) as quality,round(avg(rating<3)*100,2) as poor_query_percentage
from queries
group by query_name
having query_name is not null;

总结

我们先逐个分析

前提是需要分组，分完组之后才可以使用聚合函数。

1.avg(rating/position)是把所有的rating除以所有的position。

selectquery_name,sum(position),sum(rating),avg(rating/position),round(avg(rating/position),2) as quality
from queries
group by query_name
having query_name is not null;

2.avg(rating<3)*100，过滤rating<3

selectquery_name,count(rating),sum(rating<3),avg(rating<3),round(avg(rating<3)*100,2) as poor_query_percentage
from queries
group by query_name
having query_name is not null;

注意：count（）函数是不支持直接添加大于或者小于。因为count函数是用来统计满足特定条件的记录数，而不是用来比较或筛选数据。