SQL | 分组数据

10-分组数据

两个新的select子句:group by子句和having子句。

10.1-数据分组

上面我们学到了,使用SQL中的聚集函数可以汇总数据,这样,我们就能够对行进行计数,计算和,计算平均数。

目前为止,所有的计算都是在表的所有数据或者匹配特定的where子句的数据上进行的。

select count(*) as num_prods
from products
where vend_id = 'DLL01';

如上述SQL语句,返回供应商为DLL01的所有产品数目。

但是,现在有一个功能,就是想要返回每个供应商的产品数目;或者返回只提供一种商品的供应商数目。

这个时候就需要用到这次要写的分组的内容了。

使用分组可以将数据分为多个逻辑组,对每个组进行聚集计算。

10.2-创建分组

分组是使用select语句和group by子句进行创建的。

select vend_id,count(*) as num_prods
from products
group by vend_id;

上述SQL语句执行后,会分别查出来供应商为BRS01的产品数目,供应商为DLL01的产品数目,供应商为FNG01的产品数目。

group by子句指示DBMS按照vend_id排序并分组数据。这么做就会对每个不同的vend_id进行分别查询。

因为使用了group by子句,就不必指定要计算和估值的每个组了,系统会自动完成。group by子句提示DBMS对数据按照vend_id进行分组,然后对每个组而不是整个结果集进行聚集。根据上述的SQL语句,DBMS按照我们的指示,分为三组,然后每组进行分别计算。

使用group by之前,需要知道下面内容:

  • gruop by子句可以包含任意数目的列,因而可以对分组进行嵌套,更细致地进行分组。

  • 如果group by 子句中嵌套了分组,数据将在最后指定的分组上进行汇总。在建立分组时,所有列都一起计算,所以不能从个别列取数据。

  • group by 子句中累出的每一列都必须是检索列或者有效的表达式,但是不能为聚集函数如果在select中使用表达式,则必须在gruop by子句中指定相同的表达式,不能使用别名

  • 大多数SQL实现不允许group by 列带有长度可变的数据类型(如文本字段,备注型字段)。

  • 除聚集计算语句外,select语句中的每一列都必须在group by子句中给出。

  • 如果分组列中包含具有NULL值的行,则NULL将作为一个分组返回。如果列中有多行NULL值,他们将分为一组。

  • group by子句必须出现在where子句之后,order by子句之前。

10.3-过滤分组

除了使用group by分组数据外,SQL还能过滤分组,可以规定包括哪些分组,排除哪些分组。例如,我们查找至少有两个订单的顾客;商品数量大于100的供应商等。必须基于完整的分组而不是个别的行进行过滤。

看到过滤,就想起了where子句,但是过滤分组这个功能可能不能使用where,因为where过滤的是某些不符合条件的行,而不是组。

SQL提供的另一个子句:having子句,是专门用来过滤分组的。having类似于where。

目前为止,所有where子句都可以使用having进行替换。只不过having用户过滤组,where用于过滤行。

having支持所有where操作符:where子句的条件,包括通配符条件和带多个操作符的子句,学过的这些有关where的所有技术和选项都使用having。句法是相同的,只是关键字不同而已。

select cust_id,count(*) as orders
from orders
group by cust_id
having count(*) >= 2;

上述SQL语句通过分组查询订单量大于等于2的顾客id和订单数量。可以看到,我们是通过having过滤组数据的。

having和where的差别:where在数据分组前进行过滤,having在数据分组后进行过滤。经过where过滤后的数据,就不包含在组中了。

select vend_id,count(*) as num_prods
from products
where prod_price >= 4
group by vend_id
having count(*) >= 2;

 

 

上述SQL语句用于查询产品列表中,某个供应商供应的产品数量大于等于2,并且产品价格大于等于4的供应商的数量。

第一行正常使用selec子句,使用聚集函数count(*)。第三行过滤产品价格大于等于4的行;第四行按照vend_id进行分组;然后第五行过滤计数大于等于2的组。

如果没有where子句,会怎么样呢?

select vend_id,count(*) as num_prods
from products
group by vend_id
having count(*) >= 2;

上述SQL语句除去了where子句,可以看到数据比上面多了一条。

关于使用where和having:如果没有group by子句,大多数DBMS会同等对待这两个子句。但是实际开发过程中应该知道,只用having时,后面要跟group by子句。

10.4-分组和排序

group by 和 order by

对于第一条区别,有时我们使用group by,大部分情况是按照分组顺序进行排序的,但并不是总是这样。如果想要指定输出的数据为某种指定的排序,那么还是要指定order by子句,即使它的效果等同于group by子句。

select order_num,count(*) as items
from orderitems
group by order_num
having count(*) >=3;

 

但是,如果我们按照订购物品的数目进行排序输出。

select order_num,count(*) as items
from orderitems
group by order_num
having count(*)  >= 3
order by items,order_num;

 

 

上述SQL语句按照order_num分组数据,查询符合大于等于3的数据,然后按照数量进行排序。

10.5-select子句顺序

练习

  1. OrderItems表包含每个订单的每个产品。编写SQL语句,返回每个订单号(order_num)各有多少行数(order_lines),并 按order_lines对结果进行排序。

    select order_num,count(*) as order_lines
    from orderitems
    group by order_num
    order by order_lines;

     

  2. 编写SQL语句,返回名为cheapest_item的字段,该字段包含每个供应商成本最低的产品(使用Products表中的prod_price), 然后从最低成本到最高成本对结果进行排序。

    select vend_id,min(prod_price) as cheapest_item
    from products
    group by vend_id
    order by cheapest_item;

     

  3. 确定最佳顾客非常重要,请编写SQL语句,返回至少含100项的所有订单的订单号(OrderItems表中的order_num)。

    select order_num,count(*) as orders
    from orderitems
    where quantity >= 100
    group by order_num;

     

  4. 确定最佳顾客的另一种方式是看他们花了多少钱。编写SQL语句,返回总价至少为1000的所有订单的订单号(OrderItems表中的order_num)。提示:需要计算总和(item_price乘以quantity)。按订单号对结果进行排序。

    select order_num
    from orderitems
    where (item_price * quantity) >= 1000
    group by order_num;

     

     

  5. 下面的SQL语句有问题吗?(尝试在不运行的情况下指出。)

    SELECT order_num, COUNT(*) AS items 
    FROM OrderItems
    GROUP BY items
    HAVING COUNT(*) >= 3
    ORDER BY items, order_num;

    group by 子句应当时候表中的字段名,而不是别名,正确:group by order_num;

 

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

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

相关文章

【JavaWeb】实训的长篇笔记(上)

JavaWeb的实训是学校的一门课程,老师先讲解一些基础知识,然后让我们自己开发一个比较简单的Web程序。可涉及的知识何其之多,不是实训课的 3 周时间可以讲得完的,只是快速带过。他说:重点是Web开发的流程。 我的实训草草…

神经网络分类算法原理详解

目录 神经网络分类算法原理详解 神经网络工作流程 反向传播算法 1) 反向传播原理 2) 应用示例 总结 正向传播 (forward-propagation):指对神经网络沿着输入层到输出层的顺序,依次计算并存储模型的中间变量。 反向传播 &a…

UE4拾取物品高亮显示

UE4系列文章目录 文章目录 UE4系列文章目录前言一、如何实现 前言 先看下效果,当角色靠近背包然后看向背包,背包就会高亮显示。 一、如何实现 1.为选中物品创建蓝图接口 在“内容” 窗口中,鼠标右键选择“蓝图”->蓝图接口&#xff0c…

ChatGPT收录

VSCode插件-ChatGPT 多磨助手 多磨助手 (domore.run) Steamship Steamship 免费合集 免费chatGPT - Ant Design Pro 免费AI聊天室 (xyys.one)

计算机竞赛 python 机器视觉 车牌识别 - opencv 深度学习 机器学习

1 前言 🔥 优质竞赛项目系列,今天要分享的是 🚩 基于python 机器视觉 的车牌识别系统 🥇学长这里给一个题目综合评分(每项满分5分) 难度系数:3分工作量:3分创新点:3分 🧿 更多资…

企业做直播时如何选择适合自己的直播平台?

企业做直播时如何选择适合自己的直播平台? 可以通过对比不同直播平台的技术能力、服务质量、安全性等方面的内容,选择最适合自己的直播平台。 企业做直播如何选择直播平台 我的文章推荐: [视频图文] 线上研讨会是什么,企业对内对…

Java实现DTLS之技术背景原理(一)

文章目录 前言一、DTLS是什么?二、RFC6347标准定义DTLS1.中文翻译 总结感谢 前言 需求:升级服务侧SDK,实现与灯控器之间DTLS加密通信,代替SM4国密。目前通信是采用UDP协议并实现SM4国密加密,为了提升产品竞争力需要实…

Centos7.9_解决每次重启机器配置的java环境变量都需要重新source /etc/profile才生效的问题---Linux工作笔记060

这种情况需要把环境变量,java的环境变量在/root/.bashrc文件中也放一份,注意这个文件是隐藏的,默认是,需要进行ls -a才能显示. #jdk export JAVA_HOME/lib/jvm export JRE_HOME${JAVA_HOME}/jre export CLASSPATH.:${JAVA_HOME}/lib:${JRE_HOME}/lib export PATH${JAVA_HOME}/b…

TiDB Bot:用 Generative AI 构建企业专属的用户助手机器人

本文介绍了 PingCAP 是如何用 Generative AI 构建一个使用企业专属知识库的用户助手机器人。除了使用业界常用的基于知识库的回答方法外,还尝试使用模型在 few shot 方法下判断毒性。 最终,该机器人在用户使用后,点踩的比例低于 5%&#xff0…

三星发布新智能手表:旋转边框回归,Galaxy Watch6 系列震撼来袭

今晚,三星发布了最新的Galaxy Watch6系列智能手表,其中包括Galaxy Watch6和Watch6 Classic两款。Watch6 Classic再次引入了三星手表所独有的旋转边框特色。这两款手表提供多种尺寸和支持LTE的版本,同时还搭载了内置的eSIM模块,为用…

深入了解Bear Necessities Hackathon黑客松的优胜者们

生态系统中的资深建设者皆知道Moonbeam是大型黑客松狂热爱好者,不论是线上虚拟的还是现场的。然而,很少有黑客松能达到Moonbeam和AWS举办的Bear Necessities黑客松这样的规模和吸引力。本次黑客松共有755人参与,共提交了62份参赛作品。其中22…

【实操】2023年npm组件库的创建发布流程

2022年的实践为基础,2023年我再建一个组件库【ZUI】。步骤回顾: 2022年的npm组件包的发布删除教程_npm i ant-design/pro-components 怎么删除_啥咕啦呛的博客-CSDN博客 1.在gitee上创建一个项目,相信你是会的 2.创建初始化项目,看吧&#…

文本挖掘 day5:文本挖掘与贝叶斯网络方法识别化学品安全风险因素

文本挖掘与贝叶斯网络方法识别化学品安全风险因素 1. Introduction现实意义理论意义提出方法,目标 2. 材料与方法2.1 数据集2.2 数据预处理2.3 关键字提取2.3.1 TF-IDF2.3.2 改进的BM25——BM25WBM25BM25W 2.3.3 关键词的产生(相关系数) 2.4 关联规则分析2.5 贝叶斯…

优思学院|在六西格玛项目中如何知道过程是否受控?

当我们说过程处于统计受控状态时,我们是指过程不存在特有原因。 以下面的图表来说明,过程处于“不受控”状态和“受控”状态时的情况。 当过程在统计意义上是受控的,它并不意味着过程产出的产品就不会超过规定的规范,符合质量要…

springboot整合kafka多数据源

整合kafka多数据源 项目背景依赖配置生产者消费者消息体 项目背景 在很多与第三方公司对接的时候,或者处在不同的网络环境下,比如在互联网和政务外网的分布部署服务的时候,我们需要对接多台kafka来达到我们的业务需求,那么当kafk…

Golang通过alibabaCanal订阅MySQLbinlog

最近在做redis和MySQL的缓存一致性,一个方式是订阅MySQL的BinLog文件,我们使用阿里巴巴的Canal的中间件来做。 Canal是服务端和客户端两部分构成,我们需要先启动Canal的服务端,然后在Go程序里面连接Canal服务端,即可监…

P8642 [蓝桥杯 2016 国 AC] 路径之谜

[蓝桥杯 2016 国 AC] 路径之谜 题目描述 小明冒充 X X X 星球的骑士,进入了一个奇怪的城堡。 城堡里边什么都没有,只有方形石头铺成的地面。 假设城堡地面是 n n n\times n nn 个方格。如图所示。 按习俗,骑士要从西北角走到东南角。 …

​比特丛林用量子纠缠对抗高智商犯罪

世界上没有绝对完美的犯罪,但是预谋和统筹良久的高智商犯罪都几乎接近于完美和无比烧脑。 警局的洽谈室,只有我和嫌疑人两个人。 各自坐在桌子两边,门已关。在这个封闭的空间里,我一手拿着筷子吃着盒饭,一边撇了一下…

面向云思考安全

Gartner最近的一项研究表明,到 2025 年,85% 的企业会采用云战略,虽然这一数字是面向全球的,但可以看到在中国的环境中,基于云所带来的优势,越来越多的企业也同样开始积极向云转型。 但同时,有报…

CSS自学框架之表单

首先我们看一下表单样式,下面共有5张截图 一、CSS代码 /*表单*/fieldset{border: none;margin-bottom: 2em;}fieldset > *{ margin-bottom: 1em }fieldset:last-child{ margin-bottom: 0 }fieldset legend{ margin: 0 0 1em }/* legend标签是CSS中用于定义…