题目一:有趣的电影
题目要求:
编写解决方案,找出所有影片描述为 非
boring
(不无聊) 的并且 id 为奇数 的影片。返回结果按
rating
降序排列。
表结构:
运行结果示例:
思路:
本题所需要限定的条件是描述为非boring且id为奇数,并要求结果降序排序。难点在于如何筛选出“奇数”的id,在SQL中,我们可以使用以下几种方式判断:
- mod(x, 2) = 1 ,如果余数是 1 就是奇数。
- power(-1, x) = -1 , 如果结果是 -1 就是奇数
- x % 2 = 1 ,如果余数是 1 就是奇数。
- x & 1 = 1 ,如果是 1 就是奇数
- x regexp '[1, 3, 5, 7, 9]$' = 1 如果为 1 就是奇数
- x>>1<<1 != x 如果右移一位在左移一位不等于原值,就是奇数
运行代码示例:
select * from cinema
where description != "boring" and id%2 = 1
order by rating desc
题目二:平均售价
题目要求:
编写解决方案以查找每种产品的平均售价。
average_price
应该 四舍五入到小数点后两位。返回结果表 无顺序要求 。
表结构:
运行结果示例:
思路:
这道题目虽然是简单难度,但是坑非常多,相应的知识点也很多,值得细细品味。
首先我们先来分析大致思路,题目的难点在于寻找平均售价,但是这里的均值不能简单的使用avg()函数,而是要自己构建表达式计算,并保留两位小数。根据示例中给出的计算公式,不难得出这部分应该使用(round((sum(price * units) / sum(units)),2)来完成。当然这仅仅是开始。因为我们还需要关联两表,将售卖日期与商品的价格时间区间相对应,这里我们稍加思考,可以想到用左外连接+where条件限定完成,基于此,我们可以写出如下代码:
select p.product_id,round((sum(price * units) / sum(units)),2) as average_price from Prices p left join UnitsSold u on p.product_id = u.product_id where u.purchase_date >= p.start_date and u.purchase_date <= p.end_date group by product_id
试着运行一下,测试通过,问题解决,提交下一题!等等....解答错误?看看测试用例,哦,是因为id为3的商品没有被售卖,所以在连接时,where子句就过滤掉了这行数据,我们得出的结果就少了一条。
然后我们尝试在where子句中进行一些修改,但是发现结果都不尽如人意,这就涉及到了第一个知识点:WHERE 子句是在连接后引用的,任何 unitssold 表中 purchase_date 不在 start_date 和 end_date 之间的记录都将被排除。所以,在where子句中做任何修改都没有用,只能把对时间的限定调整到on子句中,这样,由于是左连接查询,所以在price表中的数据都会被返回,无论UnitsSold表中是否存在对应的数据。然而,解答依然错误,这次是因为我们计算得出id=3的数据结果为null。所以我们要用到另一个函数——ifnull()函数。
IFNULL()函数语法
IFNULL(expression, alt_value) --- expression:需要检查是否为NULL的字段名或者表达式。 --- alt_value:如果expression的值是NULL,则返回的备用值。
通过该函数,我们可以替换null值,并可以避免计算中可以由于null值产生的错误,是SQL中处理null问题非常常用的函数。
运行代码示例:
select p.product_id,ifnull(round((sum(price * units) / sum(units)),2),0) as average_price
from Prices p
left join UnitsSold u on p.product_id = u.product_id
and u.purchase_date >= p.start_date and u.purchase_date <= p.end_date
group by product_id
题目三:项目员工I
题目要求:
请写一个 SQL 语句,查询每一个项目中员工的 平均 工作年限,精确到小数点后两位。
以 任意 顺序返回结果表。
表结构:
运行结果示例:
思路:
有了上一题的铺垫,这道题就显得尤为简单了,甚至题目非常贴心的保证了experience_years 非空(感动),均值的计算也是直接使用avg()函数就可以。
运行代码示例:
select project_id ,round(avg(experience_years),2) as average_years
from Project left join Employee using(employee_id)
group by project_id
题目四:各赛事的用户注册率
题目要求:
编写解决方案统计出各赛事的用户注册百分率,保留两位小数。
返回的结果表按
percentage
的 降序 排序,若相同则按contest_id
的 升序 排序。
表结构:
运行结果示例:
思路:
这道题同样难度不算太高,关键点在于对结果的处理,也就是将结果转化为百分数(只需要乘以100再保留两位小数即可)。但是需要注意,计算时的分母要进行一次子查询,也就是count(r.user_id)/(SELECT COUNT(*) FROM Users)*100,不能直接使用count(*),否则这样算出来的结果永远是1。可以参考下面这个连表后得出的新表,大家也可以自己试试,无论怎么连接,两个user_id都是一样的数量。所以本题根本不需要进行任何连表操作。
运行代码示例:
SELECT
r.contest_id,
ROUND(COUNT(r.user_id)/(SELECT COUNT(*) FROM Users) * 100,2) as percentage
FROM
Register AS r
GROUP BY
r.contest_id
ORDER BY
percentage DESC,
contest_id ASC;
题目五:查询结果的质量和占比
题目要求:
将查询结果的质量
quality
定义为:各查询结果的评分与其位置之间比率的平均值。
将劣质查询百分比
poor_query_percentage
为:评分小于 3 的查询结果占全部查询结果的百分比。
编写解决方案,找出每次的
query_name
、quality
和poor_query_percentage
。
quality
和poor_query_percentage
都应 四舍五入到小数点后两位 。以 任意顺序 返回结果表。
表结构:
运行结果示例:
思路:
这道题,一看题目要求,脑袋都大了,没关系我们直接去看结果示例,根据示例里的计算式去套通式。很容易得出SQL语句:
select query_name,ifnull(round(sum(rating/position)/count(*) ,2) ,0) as quality, ifnull(round(avg(rating <3) *100,2) ,0) as poor_query_percentage from Queries group by query_name
但是当我们提交,就会有一个测试用例无法通过,查看可知这是因为有的query-name为空,所以我们只需要再加一条where子句剔除这种情况的数据即可。
这道题的关键点在于对 poor_query_percentage的计算,更具体一点说是对其分子的计算,直接select count(*) from Queries where rating <3得出的结果是不按照query_name分组的,但是sum(*)又是无法筛选出 rating <3条件的,这时我们也可以使用case when或者if语句进行判断。满足条件的即为1,不满足条件的计为0,再计算和。
运行代码示例:
select query_name,round(sum(rating/position)/count(*) ,2) as quality,
round(avg(rating <3) *100,2) as poor_query_percentage
-- round(sum(case when rating<3 then 1 else 0 end) / count(result) * 100, 2) as poor_query_percentage
from Queries
where query_name is not null
group by query_name