新网站怎样做优化,wordpress全球销量主题,wordpress 客户端配置文件,洛阳网站建设电话文章目录 【LeetCode高频SQL50题-基础版】打卡第3天#xff1a;第16~20题⛅前言 平均售价#x1f512;题目#x1f511;题解 项目员工I#x1f512;题目#x1f511;题解 各赛事的用户注册率#x1f512;题目#x1f511;题解 查询结果的质量和占比#x1f512;题目第16~20题⛅前言 平均售价题目题解 项目员工I题目题解 各赛事的用户注册率题目题解 查询结果的质量和占比题目题解 每月交易I题目题解 【LeetCode高频SQL50题-基础版】打卡第3天第16~20题
⛅前言 在这个博客专栏中我将为大家提供关于 LeetCode 高频 SQL 题目的基础版解析。LeetCode 是一个非常受欢迎的编程练习平台其中的 SQL 题目涵盖了各种常见的数据库操作和查询任务。对于计算机科班出身的同学来说SQL 是一个基础而又重要的技能。不仅在面试过程中经常会遇到 SQL 相关的考题而且在日常的开发工作中掌握 SQL 的能力也是必备的。 本专栏的目的是帮助读者掌握 LeetCode 上的高频 SQL 题目并提供对每个题目的解析和解决方案。我们将重点关注那些经常出现在面试中的题目并提供一个基础版的解法让读者更好地理解问题的本质和解题思路。无论你是准备找工作还是提升自己的技能在这个专栏中你可以学习到很多关于 SQL 的实践经验和技巧从而更加深入地理解数据库的操作和优化。 我希望通过这个专栏的分享能够帮助读者在 SQL 的领域里取得更好的成绩和进步。如果你对这个话题感兴趣那么就跟随我一起开始我们的 LeetCode 高频 SQL 之旅吧 博客主页知识汲取者的博客LeetCode高频SQL100题专栏LeetCode高频SQL100题_知识汲取者的博客-CSDN博客Gitee地址知识汲取者 (aghp) - Gitee.com题目来源高频 SQL 50 题基础版 - 学习计划 - 力扣LeetCode全球极客挚爱的技术成长平台 平均售价
题目 题目来源1251.平均售价 题解
考查知识点sum()、ifnull()
sum(column)对表中某一列进行求和round(number, n)对number保留n位小数采用四舍五入的方式ifnull(expression_1, expression_2)如果表达式1为null则结果为expression_2如果表达式不为null则结果为expression_1对 null 值的处理
这里我选择采用一步一步调整SQL的方式来解题因为这种方式能够让我们更加清晰的了解SQL的执行过程
1先做一个左连接
select *
from Prices p left join UnitsSold u on p.product_id u.product_id;| product_id | start_date | end_date | price | product_id | purchase_date | units |
| ---------- | ---------- | ---------- | ----- | ---------- | ------------- | ----- |
| 1 | 2019-02-17 | 2019-02-28 | 5 | 1 | 2019-03-01 | 15 |
| 1 | 2019-02-17 | 2019-02-28 | 5 | 1 | 2019-02-25 | 100 |
| 1 | 2019-03-01 | 2019-03-22 | 20 | 1 | 2019-03-01 | 15 |
| 1 | 2019-03-01 | 2019-03-22 | 20 | 1 | 2019-02-25 | 100 |
| 2 | 2019-02-01 | 2019-02-20 | 15 | 2 | 2019-03-22 | 30 |
| 2 | 2019-02-01 | 2019-02-20 | 15 | 2 | 2019-02-10 | 200 |
| 2 | 2019-02-21 | 2019-03-31 | 30 | 2 | 2019-03-22 | 30 |
| 2 | 2019-02-21 | 2019-03-31 | 30 | 2 | 2019-02-10 | 200 |2从上面的执行结果来看producet_id 发生了重复我们采用 group by进行去重
select *
from Prices p left join UnitsSold u on p.product_id u.product_id
group by p.product_id;| product_id | start_date | end_date | price | product_id | purchase_date | units |
| ---------- | ---------- | ---------- | ----- | ---------- | ------------- | ----- |
| 1 | 2019-02-17 | 2019-02-28 | 5 | 1 | 2019-03-01 | 15 |
| 2 | 2019-02-01 | 2019-02-20 | 15 | 2 | 2019-03-22 | 30 |3我们还需要通过按照产品销售日期计算每一个产品的总销售额
select p.product_id, SUM(units * price) as all_price, SUM(units) as all_units
from Prices p left join UnitsSold u on p.product_id u.product_id
where u.purchase_date between p.start_date and p.end_date
group by p.product_id;| product_id | all_price | all_units |
| ---------- | --------- | --------- |
| 1 | 800 | 115 |
| 2 | 3900 | 230 |product_id 1 的计算结果是 800(100 * 5)(15 * 20)11510015
4进行相除计算平均售价
select p.product_id, round(sum(units * price) / sum(units),2) as average_price
from Prices p left join UnitsSold u on p.product_id u.product_id
where u.purchase_date between p.start_date and p.end_date
group by p.product_id;| product_id | average_price |
| ---------- | ------------- |
| 1 | 6.96 |
| 2 | 16.96 |5对于销售额为 null 的商品采用上面的SQL会报错因为null与其它结果参数运算的结果可能为null而我们需要的结果是0
select p.product_id, round(ifnull(sum(units * price) / sum(units), 0), 2) as average_price
from Prices p left join UnitsSold u on p.product_id u.product_id
where u.purchase_date between p.start_date and p.end_date or u.product_id is null
group by p.product_id;注意
null与其它数值类型进行运算结果为null所以要通过 ifnull()函数计算过滤null不参与聚合函数的计算为了保障 sum() 函数的求和数量正确需要添加一个条件 or u.product_id is null 把 UnitsSold表中 null 的列也统计起来这样记录总数才是正确的否则记录总数会比真实值小
项目员工I
题目 题解
考察知识点左连接、group by、sum、count、round
1先进行左连接
select *
from Project p left join Employee e on p.employee_id e.employee_id; | project_id | employee_id | employee_id | name | experience_years |
| ---------- | ----------- | ----------- | ------ | ---------------- |
| 1 | 1 | 1 | Khaled | 3 |
| 1 | 2 | 2 | Ali | 2 |
| 1 | 3 | 3 | John | 1 |
| 2 | 1 | 1 | Khaled | 3 |
| 2 | 4 | 4 | Doe | 2 |2和目标结果表进行对比发现还需要对 project_id 进行去重
select *
from Project p left join Employee e on p.employee_id e.employee_id
group by p.project_id;| project_id | employee_id | employee_id | name | experience_years |
| ---------- | ----------- | ----------- | ------ | ---------------- |
| 1 | 1 | 1 | Khaled | 3 |
| 2 | 1 | 1 | Khaled | 3 |3和目标结果表进行对比发现还需要计算 每一个项目的员工的平均年限同时需要使用round()函数对平均值进行保留两位小数
一个项目员工的平均年限项目所有员工的年限/项目员工的数量
select p.project_id, round(sum(e.experience_years)/count(p.project_id), 2) average_years
from Project p left join Employee e on p.employee_id e.employee_id
group by p.project_id;----------------------------
| project_id | average_years |
----------------------------
| 1 | 2.00 |
| 2 | 2.50 |
----------------------------各赛事的用户注册率
题目 题目来源1633.各赛事的用户注册率 题解
考察知识点count、round、group by、order by
select contest_id , round(count(user_id) * 100/ (select count(*) from users), 2) percentage
from Register
group by contest_id
order by percentage desc, contest_id查询结果的质量和占比
题目 题目来源1211.查询结果的质量和占比 题解 考察知识点count、round、group by、sum、if、avg 方式一 select query_name, round((sum(rating/position)/count(query_name)), 2) quality,round(sum(if(rating 3, 1, 0)) * 100 / count(*), 2) poor_query_percentage
from Queries
group by query_name;其中if(rating 3, 1, 0)等价于case when rating 3 then 1 else 0 end 方式二 select query_name, round(avg(rating/position), 2) quality,round(avg(rating 3)*100, 2) poor_query_percentage
from Queries
group by query_name;其中avg(rating 3)这个表达式的含义是计算评分小于3占总数的百分比
备注这两个SQL第二个适用 avg 函数的SQL性能更好因为第一个 SQL 查询中使用了多个聚合函数和条件语句如 sum、round、if 等这可能会导致更多的计算和操作从而影响查询的性能。而第二个 SQL 查询中只使用了两个简单的聚合函数 avg 和 round没有使用条件语句所以计算的复杂度较低查询性能相对较好。
每月交易I
题目 题目来源1193.每月交易I 题解
考察知识点count、sum、data_formate、group by、order by
selectdate_format(trans_date, %Y-%m) month,country,count(*) trans_count,count(if(stateapproved, 1, null)) approved_count,sum(amount) trans_total_amount,sum(if(state approved, amount, 0)) approved_total_amount
from Transactions
group by country, month
order by month;还可以换一种写法
selectleft(trans_date, 7) month,country,count(*) trans_count,sum(if(stateapproved, 1, 0)) approved_count,sum(amount) trans_total_amount,sum(if(state approved, amount, 0)) approved_total_amount
from Transactions
group by country, month
order by month;
