当前位置: 首页 > news >正文

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

文章目录

  • 【LeetCode高频SQL50题-基础版】打卡第3天:第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.平均售价

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
selectdate_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;

还可以换一种写法

selectleft(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;

相关文章:

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

文章目录 【LeetCode高频SQL50题-基础版】打卡第3天&#xff1a;第16~20题⛅前言 平均售价&#x1f512;题目&#x1f511;题解 项目员工I&#x1f512;题目&#x1f511;题解 各赛事的用户注册率&#x1f512;题目&#x1f511;题解 查询结果的质量和占比&#x1f512;题目&am…...

系统压力测试:保障系统性能与稳定的重要措施

压力测试简介 在当今数字化时代&#xff0c;各种系统和应用程序扮演着重要角色&#xff0c;从企业的核心业务系统到在线服务平台&#xff0c;都需要具备高性能和稳定性&#xff0c;以满足用户的需求。然而&#xff0c;随着用户数量和业务负载的增加&#xff0c;系统可能会面临…...

常用数据结构和算法

提示&#xff1a;文章写完后&#xff0c;目录可以自动生成&#xff0c;如何生成可参考右边的帮助文档 文章目录 前言一、时间复杂度二、使用步骤 1.引入库2.读入数据总结 前言 提示&#xff1a;这里可以添加本文要记录的大概内容&#xff1a; 这里面有10个数据结构&#xff1…...

C++中使用引用避免内存复制

C中使用引用避免内存复制 引用让您能够访问相应变量所在的内存单元&#xff0c;这使得编写函数时引用很有用。典型的函数声明类似于下面这样&#xff1a; ReturnType DoSomething(Type parameter);调用函数 DoSomething() 的代码类似于下面这样&#xff1a; ReturnType Resu…...

计算机网络(第8版)-第4章 网络层

4.1 网络层的几个重要概念 4.1.1 网络层提供的两种服务 如果主机&#xff08;即端系统&#xff09;进程之间需要进行可靠的通信&#xff0c;那么就由主机中的运输层负责&#xff08;包括差错处理、流量控制等&#xff09;。 4.1.2 网络层的两个层面 4.2 网际协议 IP 图4-4 网…...

chromadb 0.4.0 后的改动

本文基于一篇上次写的博客&#xff1a;[开源项目推荐]privateGPT使用体验和修改 文章目录 一.上次改好的ingest.py用不了了&#xff0c;折腾了一会儿二.发现privateGPT官方更新了总结下变化效果 三.others 一.上次改好的ingest.py用不了了&#xff0c;折腾了一会儿 pydantic和c…...

Windows环境下下载安装Elasticsearch和Kibana

Windows环境下下载安装Elasticsearch和Kibana 首先说明这里选择的版本都是7.17 &#xff0c;为什么不选择新版本&#xff0c;新版本有很多坑&#xff0c;要去踩&#xff0c;就用7就够了。 Elasticsearch下载 Elasticsearch是一个开源的分布式搜索和分析引擎&#xff0c;最初由…...

机器学习:随机森林

集成学习 集成学习&#xff08;Ensemble Learning&#xff09;是一种机器学习方法&#xff0c;通过将多个基本学习算法的预测结果进行组合&#xff0c;以获得更好的预测性能。集成学习的基本思想是通过结合多个弱分类器或回归器的预测结果&#xff0c;来构建一个更强大的集成模…...

ctfshow-web11(session绕过)

php代码审计&#xff1a; function replaceSpecialChar($strParam){$regex "/(select|from|where|join|sleep|and|\s|union|,)/i";return preg_replace($regex,"",$strParam);} 首先定义了一个函数&#xff0c;主要是使用preg_replace函数对我们提交的内…...

状态模式:对象状态的变化

欢迎来到设计模式系列的第十七篇文章。在本文中&#xff0c;我们将深入探讨状态模式&#xff0c;这是一种行为型设计模式&#xff0c;用于管理对象的状态以及状态之间的变化。 什么是状态模式&#xff1f; 状态模式是一种允许对象在内部状态发生变化时改变其行为的设计模式。…...

解耦常用方法

1、类别 DIP依赖倒置、IoC控制反转、DI依赖注入&#xff08;Dependency Injection&#xff09; c11 实现依赖注入 控制反转、依赖注入、依赖倒置傻傻分不清楚&#xff1f; 我曾想深入了解的&#xff1a;依赖倒置、控制反转、依赖注入 2、方法 解耦基本方法 step1&#xff1a…...

根据二叉树创建字符串--力扣

&#x1f388;个人主页:&#x1f388; :✨✨✨初阶牛✨✨✨ &#x1f43b;强烈推荐优质专栏: &#x1f354;&#x1f35f;&#x1f32f;C的世界(持续更新中) &#x1f43b;推荐专栏1: &#x1f354;&#x1f35f;&#x1f32f;C语言初阶 &#x1f43b;推荐专栏2: &#x1f354;…...

代码事件派发机制(观察者模式)

事件派发机制主要用来解决: 代码解耦和维护,一般在代码中会要管理一些闭包函数 然后在指定的业务中触发运行闭包函数逻辑用了事件派发机制 就可以先把要处理的事件 挂在在一个事件管理类中 上面挂满要处理的闭包函数然后通过dispatch 出发要执行的任务 也就是闭包1. PHP中实…...

微服务技术栈-Nacos配置管理和Feign远程调用

文章目录 前言一、统一配置管理1.添加配置文件2.微服务拉取配置3.配置共享 三、Feign远程调用总结 前言 在上篇文章中介绍了微服务技术栈中Nacos这个组件的概念&#xff0c;Nacos除了可以做注册中心&#xff0c;同样可以做配置管理来使用。同时我们将学习一种新的远程调用方式…...

操作系统 OS

本文章是学习《操作系统》慕课版 和 王道《2024年 操作系统 考研复习指导》后所做的笔记&#xff0c;其中一些图片来源于学习资料。 目录 概念&#xff08;定义&#xff09; 目标 方便性 有效性 可扩充性 开放性 作用 OS 作为用户与计算机硬件系统之间的接口 — 人机交…...

基于ffmpeg给视频添加时间字幕

FFmpeg是一套可以用来记录、转换数字音频、视频&#xff0c;并能将其转化为流的开源计算机程序&#xff0c;我们可以基于ffmpeg对视频进行各种操作。本文主要介绍基于ffmpeg给视频添加字幕&#xff0c;字幕的内容为视频所播放的时间&#xff08;故需要安装ffmpeg&#xff0c;具…...

爬虫基础知识点快速入门

以下是一个包含注释的Python示例&#xff0c;演示了基本的网页爬取过程&#xff0c;以及一些常见的爬虫知识点&#xff1a; # 导入必要的库 import requests # 用于发送HTTP请求 from bs4 import BeautifulSoup # 用于解析HTML import csv # 用于数据存储# 1. 指定目标网站…...

解释器模式 行为型模式之五

1.定义 给定一个语言&#xff0c;定义它的文法的一种表示&#xff0c;并定义一个解释器。比如加减乘除在代码里是一种表示&#xff0c;我们需要翻译成可以阅读的数学公式&#xff0c;并且可以供用户输入数字得到输出结果。 2.组成结构 抽象表达式&#xff08;Abstract Expres…...

2023年中国汽车座舱行业发展现状及趋势分析:高级人机交互(HMI)系统将逐步提升[图]

2022年有22.3%的汽车用户认为座舱内车载娱乐功能成为影响使用体验的关键因素。当前智能电动汽车的用户画像与娱乐、游戏等应用的用户画像相似&#xff0c;均以年轻人作为目标用户。年轻化的用户将娱乐功能的使用习惯延伸至汽车座舱内&#xff0c;对于座舱功能的需求不再局限于导…...

常见的通用型项目管理软件推荐

常用项目管理软件有很多。按适合的行业来分&#xff0c;有针对各种垂直行业定制的&#xff0c;也有全行业通用的。从厂商来看&#xff0c;有国外研发的&#xff0c;也有国内厂商的&#xff0c;体现在软件的设计思路不同&#xff0c;上手的手感也很不一样。从体量来看&#xff0…...

51c自动驾驶~合集58

我自己的原文哦~ https://blog.51cto.com/whaosoft/13967107 #CCA-Attention 全局池化局部保留&#xff0c;CCA-Attention为LLM长文本建模带来突破性进展 琶洲实验室、华南理工大学联合推出关键上下文感知注意力机制&#xff08;CCA-Attention&#xff09;&#xff0c;…...

Swift 协议扩展精进之路:解决 CoreData 托管实体子类的类型不匹配问题(下)

概述 在 Swift 开发语言中&#xff0c;各位秃头小码农们可以充分利用语法本身所带来的便利去劈荆斩棘。我们还可以恣意利用泛型、协议关联类型和协议扩展来进一步简化和优化我们复杂的代码需求。 不过&#xff0c;在涉及到多个子类派生于基类进行多态模拟的场景下&#xff0c;…...

从深圳崛起的“机器之眼”:赴港乐动机器人的万亿赛道赶考路

进入2025年以来&#xff0c;尽管围绕人形机器人、具身智能等机器人赛道的质疑声不断&#xff0c;但全球市场热度依然高涨&#xff0c;入局者持续增加。 以国内市场为例&#xff0c;天眼查专业版数据显示&#xff0c;截至5月底&#xff0c;我国现存在业、存续状态的机器人相关企…...

在 Nginx Stream 层“改写”MQTT ngx_stream_mqtt_filter_module

1、为什么要修改 CONNECT 报文&#xff1f; 多租户隔离&#xff1a;自动为接入设备追加租户前缀&#xff0c;后端按 ClientID 拆分队列。零代码鉴权&#xff1a;将入站用户名替换为 OAuth Access-Token&#xff0c;后端 Broker 统一校验。灰度发布&#xff1a;根据 IP/地理位写…...

鸿蒙中用HarmonyOS SDK应用服务 HarmonyOS5开发一个医院查看报告小程序

一、开发环境准备 ​​工具安装​​&#xff1a; 下载安装DevEco Studio 4.0&#xff08;支持HarmonyOS 5&#xff09;配置HarmonyOS SDK 5.0确保Node.js版本≥14 ​​项目初始化​​&#xff1a; ohpm init harmony/hospital-report-app 二、核心功能模块实现 1. 报告列表…...

【Web 进阶篇】优雅的接口设计:统一响应、全局异常处理与参数校验

系列回顾&#xff1a; 在上一篇中&#xff0c;我们成功地为应用集成了数据库&#xff0c;并使用 Spring Data JPA 实现了基本的 CRUD API。我们的应用现在能“记忆”数据了&#xff01;但是&#xff0c;如果你仔细审视那些 API&#xff0c;会发现它们还很“粗糙”&#xff1a;有…...

ElasticSearch搜索引擎之倒排索引及其底层算法

文章目录 一、搜索引擎1、什么是搜索引擎?2、搜索引擎的分类3、常用的搜索引擎4、搜索引擎的特点二、倒排索引1、简介2、为什么倒排索引不用B+树1.创建时间长,文件大。2.其次,树深,IO次数可怕。3.索引可能会失效。4.精准度差。三. 倒排索引四、算法1、Term Index的算法2、 …...

JDK 17 新特性

#JDK 17 新特性 /**************** 文本块 *****************/ python/scala中早就支持&#xff0c;不稀奇 String json “”" { “name”: “Java”, “version”: 17 } “”"; /**************** Switch 语句 -> 表达式 *****************/ 挺好的&#xff…...

Java入门学习详细版(一)

大家好&#xff0c;Java 学习是一个系统学习的过程&#xff0c;核心原则就是“理论 实践 坚持”&#xff0c;并且需循序渐进&#xff0c;不可过于着急&#xff0c;本篇文章推出的这份详细入门学习资料将带大家从零基础开始&#xff0c;逐步掌握 Java 的核心概念和编程技能。 …...

零基础在实践中学习网络安全-皮卡丘靶场(第九期-Unsafe Fileupload模块)(yakit方式)

本期内容并不是很难&#xff0c;相信大家会学的很愉快&#xff0c;当然对于有后端基础的朋友来说&#xff0c;本期内容更加容易了解&#xff0c;当然没有基础的也别担心&#xff0c;本期内容会详细解释有关内容 本期用到的软件&#xff1a;yakit&#xff08;因为经过之前好多期…...