【LeetCode高频SQL50题-基础版】打卡第3天:第16~20题

【LeetCode高频SQL50题-基础版】打卡第3天:第16~20题

⛅前言

  在这个博客专栏中,我将为大家提供关于 LeetCode 高频 SQL 题目的基础版解析。LeetCode 是一个非常受欢迎的编程练习平台,其中的 SQL 题目涵盖了各种常见的数据库操作和查询任务。对于计算机科班出身的同学来说,SQL 是一个基础而又重要的技能。不仅在面试过程中经常会遇到 SQL 相关的考题,而且在日常的开发工作中,掌握 SQL 的能力也是必备的。

  本专栏的目的是帮助读者掌握 LeetCode 上的高频 SQL 题目,并提供对每个题目的解析和解决方案。我们将重点关注那些经常出现在面试中的题目,并提供一个基础版的解法,让读者更好地理解问题的本质和解题思路。无论你是准备找工作还是提升自己的技能,在这个专栏中,你可以学习到很多关于 SQL 的实践经验和技巧,从而更加深入地理解数据库的操作和优化。

  我希望通过这个专栏的分享,能够帮助读者在 SQL 的领域里取得更好的成绩和进步。如果你对这个话题感兴趣,那么就跟随我一起,开始我们的 LeetCode 高频 SQL 之旅吧!

平均售价

🔒题目

题目来源:1251.平均售价

image-20231006203715655

🔑题解

考查知识点: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),115=100+15,

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;

注意

  1. null与其它数值类型进行运算,结果为null,所以要通过 ifnull()函数计算过滤
  2. null不参与聚合函数的计算,为了保障 sum() 函数的求和数量正确需要添加一个条件 or u.product_id is null ,把 UnitsSold表中 null 的列也统计起来,这样记录总数才是正确的,否则记录总数会比真实值小

项目员工I

🔒题目

image-20231006204018146

🔑题解

  • 考察知识点左连接group bysumcountround

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.各赛事的用户注册率

image-20231006205016659

🔑题解

  • 考察知识点countroundgroup byorder 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.查询结果的质量和占比

image-20231006205321486

🔑题解

  • 考察知识点countroundgroup bysumifavg

  • 方式一

    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 查询中使用了多个聚合函数和条件语句,如 sumroundif 等,这可能会导致更多的计算和操作,从而影响查询的性能。而第二个 SQL 查询中只使用了两个简单的聚合函数 avground,没有使用条件语句,所以计算的复杂度较低,查询性能相对较好。

每月交易I

🔒题目

题目来源:1193.每月交易I

image-20231008203908324

🔑题解

  • 考察知识点countsumdata_formategroup byorder by
select
    date_format(trans_date, "%Y-%m") month,
    country,
    count(*) trans_count,
    count(if(state='approved', 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;

还可以换一种写法

select
    left(trans_date, 7) month,
    country,
    count(*) trans_count,
    sum(if(state='approved', 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;
  • 0
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

知识汲取者

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值