LeetCode 1211.查询结果的质量和占比

最新推荐文章于 2024-07-26 14:20:58 发布

奋斗哼哼

最新推荐文章于 2024-07-26 14:20:58 发布

阅读量59

点赞数

文章标签：数据库 sql leetcode mysql

本文链接：https://blog.csdn.net/weixin_51696882/article/details/130485913

版权

数据准备

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');

需求

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

输入

在这里插入图片描述

输出

-- 查询质量
select query_name,
       round(sum((rating / Queries.position)/3),2) as quality
from Queries
group by query_name;

-- 劣质查询百分比
select query_name,
       round(count(1)/3*100,2) as poor_query_percentage
from Queries
where rating<3
group by query_name;

-- 合并
with t1 as (
    select query_name,
       round(sum((rating / Queries.position)/3),2) as quality
    from Queries
    group by query_name
),t2 as (
    select query_name,
       round(count(1)/3*100,2) as poor_query_percentage
    from Queries
    where rating<3
    group by query_name
)
select t1.query_name,t1.quality,t2.poor_query_percentage
from t1,t2
where t1.query_name=t2.query_name