【SQL边干边学系列】07高级问题-3

文章目录

  • 前言
  • 回顾
  • 高级问题
    • 41.逾期订单
    • 42.逾期订单-哪些员工?
    • 43.逾期订单与总订单相比
    • 44.逾期订单与总订单相比 - 丢失的员工
    • 45.逾期订单与总订单相比 - 修复null
    • 46.逾期订单与总订单之间的百分比
    • 47.逾期订单与总订单相比 - 修正decimal
  • 答案
    • 41.逾期订单
    • 42.逾期订单-哪些员工?
    • 43.逾期订单与总订单相比
    • 44.逾期订单与总订单相比 - 丢失的员工
    • 45.逾期订单与总订单相比 - 修复null
    • 46.逾期订单与总订单之间的百分比
    • 47.逾期订单与总订单相比 - 修正decimal
  • 未完待续


前言

在这里插入图片描述
该系列教程,将会从实际问题出发,边干边学,逐步深入讲解SQL的各方面知识。

你需要完成所有的问题吗?绝对不是。介绍性的问题相当简单,所以你可以直接跳过到“中级问题”部分。如果你不是初学者,但不确定应该从哪里开始,请在“入门问题”部分看看问题和预期结果,并确保你理解这些概念。如果已经理解了这些概念,请开始阅读“中级问题”部分。

你想从这本书中复制代码并在你的服务器上运行?我建议你手动输入,而不是复制粘贴。为什么要去麻烦地重新打字呢?科学表明,打字的行为会在你的脑中留下更深刻的印象。当你只是复制和粘贴时,代码只是直接从你电脑里的一个窗口转到另一个窗口,而不会给你留下多少印象。但是当你把它打出来时,你必须集中精力,这非常有助于保留信息。

一旦你完成了所有的问题,将拥有一些在数据分析和高级Select语句使用方面非常有用的技能。当然,这并不是SQL的全部内容。还有修改数据(更新、插入、删除)、DDL(数据定义语言,即如何创建和修改数据库对象)、编程(如存储过程)和许多其他主题。

该系列教程中,只涉及到了使用Select语句检索数据的问题,这几乎是所有其他数据库主题的基础开端。


回顾

上篇文章👉《【SQL边干边学系列】06高级问题-2》 开始讨论高级问题,这篇我们接着讨论。


高级问题

41.逾期订单

一些客户抱怨他们的订单迟到了。哪些订单迟到了?

-- 预期结果
OrderID     OrderDate  RequiredDate ShippedDate
----------- ---------- ------------ -----------
10264       2014-07-24 2014-08-21   2014-08-23
10271       2014-08-01 2014-08-29   2014-08-30
10280       2014-08-14 2014-09-11   2014-09-12
10302       2014-09-10 2014-10-08   2014-10-09
10309       2014-09-19 2014-10-17   2014-10-23
10380       2014-12-12 2015-01-09   2015-01-16
10423       2015-01-23 2015-02-06   2015-02-24
10427       2015-01-27 2015-02-24   2015-03-03
10433       2015-02-03 2015-03-03   2015-03-04
10451       2015-02-19 2015-03-05   2015-03-12
10483       2015-03-24 2015-04-21   2015-04-25
10515       2015-04-23 2015-05-07   2015-05-23
......
10970 2016-03-24 2016-04-07 2016-04-24
10978 2016-03-26 2016-04-23 2016-04-23
10998 2016-04-03 2016-04-17 2016-04-17
(39 row(s) affected)

提示:要确定哪些订单会延迟,可以组合使用RequiredDate和ShippedDate。如果ShippedDate实际上已经晚于RequiredDate,你可以确定它已经晚了。

42.逾期订单-哪些员工?

有些销售人员逾期的订单比其他人多。也许他们没有跟进订购流程,需要更多的培训。哪些销售人员的订单逾期最多?

-- 预期结果
EmployeeID  LastName             TotalLateOrders
----------- -------------------- ---------------
4           Peacock              10
3           Leverling            5
8           Callahan             5
9           Dodsworth            5
7           King                 4
2           Fuller               4
1           Davolio              3
6           Suyama               3
(8 row(s) affected)

提示:上面这个问题的答案是一个很好的起点。你需和“ Employee”表Join以获得last name,并添加“Count”来显示逾期订单总数。

43.逾期订单与总订单相比

仅仅查看每个销售人员逾期的订单数量并不是一个好主意。它需要与每个销售人员的订单总数进行比较。返回如下结果:

-- 预期结果
EmployeeID  LastName             AllOrders   LateOrders
----------- -------------------- ----------- -----------
1           Davolio              123         3
2           Fuller               96          4
3           Leverling            127         5
4           Peacock              156         10
6           Suyama               67          3
7           King                 72          4
8           Callahan             104         5
9           Dodsworth            43          5
(8 row(s) affected)

提示如下

你可以在一个查询中使用多个CTE。这将是解决这个问题的一种直接的方法。

下面是2个SQL语句,它们可以放入CTE中,并一起放入最终的SQL语句中。

-- Late orders
Select
 EmployeeID
 ,TotalOrders = Count(*)
From Orders 
Where
 RequiredDate <= ShippedDate
Group By
 EmployeeID
-- Total orders
Select
 EmployeeID
 ,TotalOrders = Count(*)
From Orders 
Group By
 EmployeeID

44.逾期订单与总订单相比 - 丢失的员工

在上面这个问题的答案中缺少了一个员工。修复SQL,以显示所有已接受订单的员工。

-- 预期结果
EmployeeID  LastName              AllOrders  LateOrders
----------- -------------------- ----------- -----------
1           Davolio              123         3
2           Fuller               96          4
3           Leverling            127         5 
4           Peacock              156         10
5           Buchanan             42          NULL
6           Suyama               67          3
7           King                 72          4
8           Callahan             104         5
9           Dodsworth            43          5
(9 row(s) affected)

提示:当你只运行 AllOrders CTE时,返回了多少行?如果你只运行LateOrders CTE,返回了多少行?应该添加一个左连接(也称为左外部连接),以确保我们显示每一行,即使没有逾期订单。

45.逾期订单与总订单相比 - 修复null

继续上面查询的答案,让我们修复第5行的结果。在逾期订单中应该有0而不是null。

-- 预期结果
EmployeeID  LastName              AllOrders  LateOrders
----------- -------------------- ----------- -----------
1           Davolio              123         3
2           Fuller               96          4
3           Leverling            127         5 
4           Peacock              156         10
5           Buchanan             42          0
6           Suyama               67          3
7           King                 72          4
8           Callahan             104         5
9           Dodsworth            43          5
(9 row(s) affected)

提示:找到一个函数来测试一个值是否为空,并在该值为null时返回一个不同的值。

46.逾期订单与总订单之间的百分比

现在我们想知道逾期订单占总订单的比例。

-- 预期结果
EmployeeID  LastName              AllOrders  LateOrders  PercentLateOrders
----------- -------------------- ----------- ----------- -------------------
1           Davolio              123         3           0.0243902439024
2           Fuller               96          4           0.0416666666666
3           Leverling            127         5           0.0393700787401
4           Peacock              156         10          0.0641025641025
5           Buchanan             42          0           0.0000000000000
6           Suyama               67          3           0.0447761194029
7           King                 72          4           0.0555555555555
8           Callahan             104         5           0.0480769230769
9           Dodsworth            43          5           0.1162790697674
(9 row(s) affected)

提示如下

通过将逾期订单除以总订单,应该能够得到百分比。然而,人们会遇到一个常见的问题,即一个整数除以一个整数返回一个整数。例如,如果运行这条SQL:select 3/2,你将得到1而不是1.5,因为它将返回最接近的整数。

47.逾期订单与总订单相比 - 修正decimal

为了使输出更容易读取,我们让百分比字段保留2位小数。

-- 预期结果
EmployeeID  LastName              AllOrders  LateOrders  PercentLateOrders
----------- -------------------- ----------- ----------- -------------------
1           Davolio              123         3           0.02
2           Fuller               96          4           0.04
3           Leverling            127         5           0.04
4           Peacock              156         10          0.06
5           Buchanan             42          0           0.00
6           Suyama               67          3           0.04
7           King                 72          4           0.06
8           Callahan             104         5           0.05
9           Dodsworth            43          5           0.12
(9 row(s) affected)

提示如下

一种简单的方法是显式地将PercentageLateOrders转换为特定的Decimal类型。使用Decimal类型,您可以指定小数点右侧的位数。

计算PercentLateOrders变得有点长和复杂,要使所有的逗号和括号都正确可能很棘手。

简化它的一种方法是用一个实际的值进行验证。例如:

Select convert(decimal(10,2), 0.0243902439024)

答案

41.逾期订单

答案

Select
 OrderID 
 ,OrderDate = convert(date, OrderDate)
 ,RequiredDate = convert(date, RequiredDate)
 ,ShippedDate = convert(date, ShippedDate)
From Orders 
Where
 RequiredDate <= ShippedDate

42.逾期订单-哪些员工?

答案

Select
 Employees.EmployeeID
 ,LastName
 ,TotalLateOrders = Count(*)
From Orders 
 Join Employees
 on Employees.EmployeeID = Orders.EmployeeID
Where
 RequiredDate <= ShippedDate
Group By
 Employees.EmployeeID
 ,Employees.LastName
Order by TotalLateOrders desc

讨论

请注意,“Employees”表中的LastName和EmployeeID都需要包含在Group by子句中,否则我们将得到错误信息。

从技术上讲,EmployeeID是一个主键字段,由于我们已经按其进行分组,只有一个LastName与EmployeeID相关联。但是,数据库引擎并不知道这一点,并且仍然需要在 Group by子句中包含LastName。

43.逾期订单与总订单相比

答案

;With LateOrders as (
 Select
 EmployeeID
 ,TotalOrders = Count(*)
 From Orders 
 Where
 RequiredDate <= ShippedDate
 Group By
 EmployeeID
)
, AllOrders as (
 Select
 EmployeeID
 ,TotalOrders = Count(*)
 From Orders 
 Group By
 EmployeeID
)
Select
 Employees.EmployeeID
 ,LastName
 ,AllOrders = AllOrders.TotalOrders
 ,LateOrders = LateOrders.TotalOrders
From Employees
 Join AllOrders 
 on AllOrders.EmployeeID = Employees.EmployeeID
 Join LateOrders
 on LateOrders.EmployeeID = Employees.EmployeeID

讨论

上面的查询几乎是正确的,但是如果你仔细注意,你会意识到它有一个小问题。我们将在下一个问题中了解到更多信息。

44.逾期订单与总订单相比 - 丢失的员工

答案

;With LateOrders as (
 Select
 EmployeeID
 ,TotalOrders = Count(*)
 From Orders 
 Where
 RequiredDate <= ShippedDate
 Group By
 EmployeeID
)
, AllOrders as (
 Select
 EmployeeID
 ,TotalOrders = Count(*)
 From Orders 
 Group By
 EmployeeID
)
Select
 Employees.EmployeeID
 ,LastName
 ,AllOrders = AllOrders.TotalOrders
 ,LateOrders = LateOrders.TotalOrders
From Employees
 Join AllOrders 
 on AllOrders.EmployeeID = Employees.EmployeeID
 Left Join LateOrders
 on LateOrders.EmployeeID = Employees.EmployeeID

讨论

如果我们展示所有的员工(包括那些没有订单的员工),我们需要使用左连接。

45.逾期订单与总订单相比 - 修复null

答案

;With LateOrders as (
 Select
 EmployeeID
 ,TotalOrders = Count(*)
 From Orders 
 Where
 RequiredDate <= ShippedDate
 Group By
 EmployeeID
)
, AllOrders as (
 Select
 EmployeeID
 ,TotalOrders = Count(*)
 From Orders 
 Group By
 EmployeeID
)
Select
 Employees.EmployeeID
 ,LastName
 ,AllOrders = AllOrders.TotalOrders
 ,LateOrders = IsNull(LateOrders.TotalOrders, 0)
From Employees
 Join AllOrders 
 on AllOrders.EmployeeID = Employees.EmployeeID
 Left Join LateOrders
 on LateOrders.EmployeeID = Employees.EmployeeID

讨论

在LateOrder上直接使用IsNull是解决这个问题的最佳方法。

另一种写方法是使用Case语句:

LateOrders =
 Case
 When LateOrders.TotalOrders is null Then 0
 Else LateOrders.TotalOrders
 End

但是,当你除了测试null之外不需要任何其他逻辑时,IsNull是最好的方法。

46.逾期订单与总订单之间的百分比

答案

;With LateOrders as (
 Select
 EmployeeID
 ,TotalOrders = Count(*)
 From Orders 
 Where
 RequiredDate <= ShippedDate
 Group By
 EmployeeID
)
, AllOrders as (
 Select
 EmployeeID
 ,TotalOrders = Count(*)
 From Orders 
 Group By
 EmployeeID
)
Select
 Employees.EmployeeID
 ,LastName
 ,AllOrders = AllOrders.TotalOrders
 ,LateOrders = IsNull(LateOrders.TotalOrders, 0)
 ,PercentLateOrders =
 (IsNull(LateOrders.TotalOrders, 0) * 1.00) / AllOrders.TotalOrders
From Employees
 Join AllOrders 
 on AllOrders.EmployeeID = Employees.EmployeeID
 Left Join LateOrders
 on LateOrders.EmployeeID = Employees.EmployeeID

讨论

如果你只是添加一个字段:

PercentLateOrders = LateOrders.TotalLateOrders/AllOrders.TotalOrders

所有字段都会得到0,尽管这显然是不正确的。但这就是当你把两个整数除在一起时所发生的情况。你需要将其中一个转换为decimal。转换为decimal数据类型的一种常见方法是乘以1.00。

请注意,在进行除法之前,你需要将整数转换decimal。如果你在除法之后做,就像这样:

(IsNull(LateOrders.TotalOrders, 0) / AllOrders.TotalOrders) * 1.00

你仍然会得到0。

47.逾期订单与总订单相比 - 修正decimal

答案

;With LateOrders as (
 Select
 EmployeeID
 ,TotalOrders = Count(*)
 From Orders 
 Where
 RequiredDate <= ShippedDate
 Group By
 EmployeeID
)
, AllOrders as (
 Select
 EmployeeID
 ,TotalOrders = Count(*)
 From Orders 
 Group By
 EmployeeID
)
Select
 Employees.EmployeeID
 ,LastName
 ,AllOrders = AllOrders.TotalOrders
 ,LateOrders = IsNull(LateOrders.TotalOrders, 0)
 ,PercentLateOrders =
 Convert(
 Decimal (10,2)
 ,(IsNull(LateOrders.TotalOrders, 0) * 1.00) / AllOrders.TotalOrders
 )
From Employees
 Join AllOrders 
 on AllOrders.EmployeeID = Employees.EmployeeID
 Left Join LateOrders
 on LateOrders.EmployeeID = Employees.EmployeeID

讨论

四舍五入、截断和转换数据类型可能会变得复杂,而且有很多方法可能获得意想不到的结果。一定要仔细检查你的结果,并知道你是想要舍入,还是想要截断。

你可能已经注意到,我在计算中添加了一些新的行,以使它更容易阅读。这并不是必要的,但这是一种很好的编程实践,它更容易阅读和进行故障排除。


未完待续

下一篇我们接着讲剩余的高级问题。


如果喜欢这篇文章,请不要忘记关注🧡、点赞👍和收藏📔哦!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:/a/705005.html

如若内容造成侵权/违法违规/事实不符,请联系我们进行投诉反馈qq邮箱809451989@qq.com,一经查实,立即删除!

相关文章

Python Flask框架基础(七)留言板

本章示例程序是一个非常简单的留言板程序SayHello&#xff0c;涉及的知识完全是前面六个章节的内容 。这一章会基于这个程序介绍一种组织代码的形式&#xff0c;并了解Web程序开发流程&#xff0c;对前面六章的知识进行简单的回顾复习。 在具体的开发中&#xff0c;代码编写主…

AI大模型探索之路-实战篇:智能化IT领域搜索引擎之GLM-4大模型技术的实践探索

系列篇章&#x1f4a5; No.文章1AI大模型探索之路-实战篇&#xff1a;智能化IT领域搜索引擎的构建与初步实践2AI大模型探索之路-实战篇&#xff1a;智能化IT领域搜索引擎之GLM-4大模型技术的实践探索3AI大模型探索之路-实战篇&#xff1a;智能化IT领域搜索引擎之知乎网站数据获…

【计算机毕业设计】257基于大学生社团活动管理的微信小程序

&#x1f64a;作者简介&#xff1a;拥有多年开发工作经验&#xff0c;分享技术代码帮助学生学习&#xff0c;独立完成自己的项目或者毕业设计。 代码可以私聊博主获取。&#x1f339;赠送计算机毕业设计600个选题excel文件&#xff0c;帮助大学选题。赠送开题报告模板&#xff…

二分+ST表+递推,Cf 1237D - Balanced Playlist

一、题目 1、题目描述 2、输入输出 2.1输入 2.2输出 3、原题链接 Problem - 1237D - Codeforces 二、解题报告 1、思路分析 case3提示我们一件事情&#xff1a;如果存在某个位置永远不停止&#xff0c;那么所有位置都满足永远不停止 很容易证明 随着下标右移&#xff0c…

YOLOv10原理与实战训练自己的数据集

课程链接&#xff1a;YOLOv10原理与实战训练自己的数据集_在线视频教程-CSDN程序员研修院 YOLOv10是最近提出的YOLO的改进版本。在后处理方面&#xff0c;提出了一致性双重分配策略用于无NMS训练&#xff0c;从而实现了高效的端到端检测。在模型架构方面&#xff0c;引入了全面…

Ubuntu安装opendaylight控制器

目录 实验任务 实验环境 安装过程&#xff1a; 将opendaylight添加到环境变量中 实验任务 在虚拟机1中安装opendaylight控制器并安装相应的组件在虚拟机2中使用mininet创建一个测试拓扑并将控制器的地址指向虚拟机1在虚拟机1中的opendaylight的web界面可以查看到创建的拓扑将…

实践分享:鸿蒙跨平台开发实例

先来理解什么是跨平台 提到跨平台&#xff0c;要先理解什么是“平台”&#xff0c;这里的平台&#xff0c;就是指应用程序的运行环境&#xff0c;例如操作系统&#xff0c;或者是Web浏览器&#xff0c;具体的像HarmonyOS、Android、iOS、或者浏览器&#xff0c;都可以叫做平台…

[vue2]深入理解路由

本节目标 单页应用程序路由概念VueRouter基本使用组件分类存放路由模块封装声明式导航其他路由配置路由模式编程式导航案例-面经基础版 单页应用程序 单页应用程序(SPA): 所有的功能都在一个HTML页面上实现 网易云音乐: 网易云音乐 多页应用程序(MPA): 不同功能通过切换不同…

透平油氧化安定性检测 发动机油运动粘度40℃检测

透平油氧化安定性检测 透平油&#xff0c;也称为涡轮机油或汽轮机油&#xff0c;是专门用于汽轮机的润滑油。它具有良好的抗氧化安定性和抗乳化性能&#xff0c;主要用于发电厂蒸气轮机、水电站水轮发电机以及其他需要深度精细润滑的场合。透平油的氧化安定性是衡量其在高温条件…

CentOs7 安装mysql5.7

1.卸载原系统中的mariadb…… 首先执行命令rpm -qa|grep mariadb查看是否有mariadb的安装包&#xff0c;没有可以不管 接下来&#xff0c;执行 rpm -e --nodeps mariadb-libs #删除掉下载mysql5.7安装包 1.前往官方网站复制yum源链接Mysql官网 然后鼠标右键粘贴 wget 执行…

极限网关助力好未来 Elasticsearch 容器化升级

极限网关在好未来的最佳实践案例&#xff0c;轻松扛住日增百 TB 数据的流量&#xff0c;助力 ES 从物理机到云原生架构的改造&#xff0c;实现了流控、请求分析、安全管理、无缝迁移等场景。一次完美的客户体验~ 背景 物理机架构时代 2022 年&#xff0c;好未来整个日志 Elas…

【云计算】Docker部署Nextcloud网盘并实现随地公网远程访问

配置文件 切换root权限&#xff0c;新建一个nextcloud的文件夹&#xff0c;进入该目录&#xff0c;创建docker-compose.yml [cpslocalhost ~]$ su root Password: 666666 [rootlocalhost cps]# ls Desktop Documents Downloads Music Pictures Public Templates Vide…

为CAP面板添加简单的Authentication登录验证功能 C#|.net

终于搞定了CAP Dashboard的登录验证功能! 因为网上找不到简单的CAP Dashboard的登录验证功能,所以这个功能摸索着开发了好久。 这个Authentication认证功能,不仅适用于CAP面板,也适用于懒得开发登录页面,但是又需要简单用户名密码登录的网页。 做过后端的比较熟悉,CAP面…

Science Advances|全溶液工艺制备超柔性有机光电器件(柔性电子/柔性传感/可穿戴电子/电子皮肤/有机光电器件)

2024年4月10日,日本东京大学Takao Someya和日本理化学研究所(RIKEN)Kenjiro Fukuda课题组,在《Science Advances》上发布了一篇题为“All-solution-processed ultraflexible wearable sensor enabled with universal trilayer structure for organic optoelectronic device…

TiKV 源码分析之 PointGet

作者&#xff1a;来自 vivo 互联网存储研发团队-Guo Xiang 本文介绍了TiDB中最基本的PointGet算子在存储层TiKV中的执行流程。 一、背景介绍 TiDB是一款具有HTAP能力(同时支持在线事务处理与在线分析处理 )的融合型分布式数据库产品&#xff0c;具备水平扩容或者缩容等重要特…

计算机网络知识点(三)

目录 一、简述TCP连接和关闭的状态转移 二、简述TCP慢启动 三、简述TCP如何保证有序 四、简述TCP常见的拥塞控制算法 五、简述TCP超时重传 一、简述TCP连接和关闭的状态转移 状态转移图 图中上半部分是TCP的三次握手过程的状态变迁&#xff0c;下半部分是TCP四次挥手过程的…

Three.js动效(第17辑):可视化大屏中炫酷的例子效果,如何实现

Hi&#xff0c;前几天分享了一些炫酷的例子动画背景图&#xff0c;很多老铁在评论区问我是如何实现的&#xff0c;10经验的前端开发和UI设计老司机→贝格前端工场&#xff0c;为您分享。 之前的文章&#xff1a;背景图的动效&#xff0c;非常的炫酷&#xff0c;非一般的感觉。…

el-table有横向滚动条时,最后一行数据被横向滚动条遮挡,且不出现纵向滚动条;只有当鼠标移到fixed列才能纵向滚动,移到非fixed列无法纵向滚动。

问题背景 项目使用的vue2&#xff0c;el-table有横向滚动条时&#xff0c;最后一行数据被横向滚动条遮挡&#xff0c;且不出现纵向滚动条&#xff1b;只有当鼠标移到fixed列才能纵向滚动&#xff0c;移到非fixed列无法纵向滚动。 见下图&#xff1a;最后一行被遮挡住了一部分…

R语言ggHoriPlot包绘制地平线图

数据和代码获取&#xff1a;请查看主页个人信息&#xff01;&#xff01;&#xff01; 关键词“地平线图” 1. 数据读取与处理 首先&#xff0c;从TSV文件中读取数据&#xff0c;并进行数据清洗和处理。 rm(listls()) pacman::p_load(tidyverse,ggalt,ggHoriPlot,hrbrthemes…

python接入汇率换算工具提高网站/小程序日活度

实时汇率换算工具可以帮助用户快速准确地计算不同货币之间最新的汇兑比例。无论是金融从业者或者是人们日常生活出行都会使用到&#xff0c;广泛用于国际结算、银行汇率查询应用、开展跨国贸易、投资等参考场景。 我们可以通过在网站或者小程序中接入这样一个小工具&#xff0…