【SQL高频练习带刷】day3：聚合查询

题目一：有趣的电影

题目要求：

编写解决方案，找出所有影片描述为 非 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