SQL 语句继续学习之记录二

三， 聚合与排序

 对表进行聚合查询，即使用聚合函数对表中的列进行合计值或者平均值等合计操作。

通常，聚合函数会对null以外的对象进行合计。但是只有count 函数例外，使用count(*) 可以查出包含null在内的全部数据行数。

使用distinct 关键字删除重复值。

1，聚合函数之常见的5个聚合函数

count：计算表中的记录数（行数）

SUM：计算表中数值列的数据合计值

AVG：计算表中数值列的数据平均值

MAX：求出表中任一列中数据的最大值

MIN：求出表中任意列中数据的最小值

如上，用于合计的函数称为聚合函数或者集合函数。所谓聚合，就是将多行汇总为一行。实际上，所有的聚合函数都是这样，输入多行输出一行

1）

a, 如下，我们要获取如下原始数据表中全部数据的行数。

 如上，一共有8行数据，所以我们获取到的结果应该是8.

如下 count（*） 中的 * 表示全部列

b, 计算null以外数据的行数

如上，查询的是全部数据（所有列的）行数，但是如果想要查询某个列（比如该列可能存在null 数据） 的行数，该怎么做呢？

比如想得到shiire_tanka 列（进货单价）中非空行数的列，，可以通过将对象列设定为参数来实现

Note：

Count 函数的结果根据参数不同而不同。count（*）会得到该表格包含null数据的所有行数，而count（某个列名）得到的是该列中，除了null之外的数据的行数。

Count 函数也是唯一一个可以以星号 * 为参数的函数，

2) 计算合计值

例如，我们需要计算销售单价的合计值，即销售单价列，所有数值加起来的总和。

1000 + 4000 + 3000 + 6800 + 500 + 880 + 100 = 16280 

Note: 之前我们提到，含有null 的算术运算符中，如果数据含有null，则最终运算结果为null，但如上，最终结果并不为null，而是我们日常生活中所理解的正常的数值。其原因为，聚合函数，除了count 函数外，其他几个聚合函数在最终计算时都会将null 数据提前排除在外。因此不会看到null的结果，这也证明我们之前的描述的正确性。 所谓count函数例外，是说count在参数为星号 * 时，会将null结果计算在内。

3）计算平均值

如我们要获取销售单价的平均值

注意，如上，hanbai_tanka 结果值是16280，如果按照8行为除数，结果应该是2035，但现在的结果是2325.7143,  是因为该数据的计算是 16280 除以 7 得到的结果，也就是说，计算平均值时，avg 函数会将null 数据所在的行排除掉，计算非null数据的总值，同时将非null数据的行数作为除数。

针对上述提到的点，需要特别注意，如果我们需要将null 作为0 进行计算，具体的实现方式需要参考后面的学习。暂时性放弃讲解，可此处留个记录，后续会详细讲述。

4）计算最大值和最小值

想要计算出多条记录中的最大值或最小值，可以分别使用MAX 和 MIN 函数

例如计算销售单价的最大值和进货单价的最小值。

如下，我们获取到了销售单价的最大值和进货单价的最小值

 

 注意：MAX 和 MIN 函数和 SUM/AVG函数有一点区别，即SUM/AVG 函数只能对数值类型的列使用，而 MAX 和 MIN 函数原则上可以适用于任何数据类型的列。例如，我们可以对日期列进行大小值的获取

note: MAX 和 MIN 函数适用于任何数据类型的列，即只要能进行排序的数据，就肯定有最大值和最小值，也就能使用这两个函数，但是需要注意的是，’排序‘，需要注意是否是字符串等特殊类型，因为字符串排序是要按照数据字典来排序，比如之前 “1”，“22”，“3”，“225”等 字符串排序时，3要比22 大。 另外对与日期和字符串类型数据来说，能够使用MAX/MIN 这两个函数，但不能使用SUM/AVG函数，因为对日期和字符串来说，合计值和平均值均没有实际意义。

即MAX/MIN 函数几乎适用于所有数据类型的列，但SUM/AVG函数只适用于数值类型的列。

5）使用聚合函数删除重复值（关键字distinct）

在之前shohin表中看到，商品种类和日期的数据中，存在多行数据相同的情况

 如果我们想要计算出商品种类的个数，怎么做呢？ 即删除重复数据，然后再计算数据行数。

note: distinct 必须写在括号中，是因为必须要在计算行数之前删除shohin_bunrui 列中的重复数据。如果不加括号，distinct 需要放在第一列的钱买呢，则如下就会先计算出数据行数，然后再删除重复数据，结果会得到shohin_bunrui列的所有行数，即8

假设我们再增加一个查询列，比如日期这种有重复行的列，结果会如何呢？

 如下

org.jkiss.dbeaver.model.sql.DBSQLException: SQL Error [1140] [42000]: In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column 'shop.shohin.torokubi'; this is incompatible with sql_mode=only_full_group_by
	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:133)
	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:582)
	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$1(SQLQueryJob.java:491)
	at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:190)
	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:498)
	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:934)
	at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:3928)
	at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:123)
	at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:190)
	at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:121)
	at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:5140)
	at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:105)
	at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)
Caused by: java.sql.SQLSyntaxErrorException: In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column 'shop.shohin.torokubi'; this is incompatible with sql_mode=only_full_group_by
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:763)
	at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648)
	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:330)
	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:131)
	... 12 more

提示sql 都有错误。 

为何呢？ 后面再做分析，暂时放在这儿

为了测试distinct 参数，我稍微修改了一下db 中打孔器的价格，从null 改为了500

如上，distinct 可以做count 函数的参数，其实，distinct也可以做其他所有聚合函数的参数，比如如下sql，我们可以尝试获取销售单价在有distinct参数和无distinct参数时的值差别 

如上，都是计算销售单价的合计值，但这两个最终的结果却不同，为何呢？

因为在销售单价这一列中，有2个相同值 500 元。上述查询中，

SUM(hanbai_tanka) 是直接查询该列所有的数值加在一起的和，而SUM(distinct hanbai_tanka) 的查询逻辑是，先将该列中重复的数据去除，然后计算数值的总和。因此，上面的查询为

SUM(hanbai_tanka) = 1000 + 500 +4000 +3000 + 6800 + 500 + 880 + 100 =16780

SUM(distinct hanbai_tanka) = 1000 + 500 + 4000 + 3000 + 6800 + 880 +100 =16280

2, 对表进行分组

使用group by 子句可以像切蛋糕那样将表进行分割。通过使用聚合函数 和group by 子句，可以根据“商品种类” 和 “登记日期” 等将表分割后再进行聚合

聚合键中包含null时，在结果中会以“不确定” 行（空行）的形式表现出来

使用聚合函数和group by 子句时需要注意以下4点，

a 只能写在select 子句中

b, group by 子句中不能使用select子句中列的别名

c, group by 子句的聚合效果是无序的

d, where 子句中不能使用聚合函数

1）group by 子句

之前学习记录中，我们看到的聚合函数的使用方法中，无论是否包含null，无论是否删除了重复数据，都是针对表中的所有数据进行的聚合处理。而接下来的这里，我们先把表分成几组，然后再进行聚合处理。也就是按照“商品种类” 、“登记日期”等进行聚合。

1> 使用group by 子句进行聚合的sql 格式

select <列名1>, <列名2 >, <列名3>

from < 表名>

group by < >, < >, <>

例如，我们按照商品种类来统计一下数据行数（=商品数量）

如上，商品种类一共有3种，衣服，办公用品，厨房用具

在此，我们需要分析一个问题，即group by 有和没有的区别。 如果没有group by 子句，聚合函数count(*) 相当于把整个表作为一个group，将该组中的数据行数进行计算。但是如果使用了group by,  系统会先把整个表按照聚合键（group by 子句中指定的列称为聚合键或者分组列）来切分成多个组，之后count 对每个组中的数据行数进行计算。

实际上，group by子句也和select子句一样，可以通过逗号分隔指定多列

如下，我们做测试如图

2> 使用where 子句时 Group by 的执行结果

在使用了group by 子句的select语句中，也可以正常使用where 子句。子句的排列顺序如图

使用where 子句和group by 子句进行聚合处理

select <列名1>, <列名2>,<列名3>

from <表名>

where

group by <列名1>,<列名2>,<列名3>

但请注意: group by 和 where 子句并用时，select语句的执行顺序如下所示

From ——> where ——> group by——>select

像这样使用where子句进行聚合处理时，会先根据where子句制定的条件进行过滤，然后再进行聚合处理。 如下

3> 与聚合函数和group by子句有关的常见错误

a, 常见错误问题1——在select子句中书写了多余的列

在使用count 这样的聚合函数时，select子句中的元素有严格的限制。实际上，使用聚合函数时，select子句中只能存在以下三种情况

（1）常数

（2）聚合函数

（3）group by 子句中指定的列名（也就是聚合键）

如上面记录的一个错误，此处由于select语句中使用了count这样的聚合函数，所以在select语句中不能出现类似torokubi 这样的shohin 表中其他的数据列，这样sql 将无法执行。

 

存在group by 的语句，select中不能出现聚合键之外的列（即group by子句中未指定的列）

b, 常见错误2，在group by 子句中写了列的别名

在select 子句可以使用as 关键字来指定别名。但是，在group by 子句中是不能使用别名的。

SELECT shohin_bunrui as sb, COUNT(*)
from shohin s
group by sb;

如上，sb 是定义在select语句中的一个列的别名，被使用在group by 语句中，该使用方法并非sql的标准使用方法，虽然在一些工具中该语句可以被执行，但是由于sql语句的执行顺序问题，如上，select会先执行group by 语句，之后再执行select语句，即当group by子句执行时，sb 这个名称还未被定义，因此严格来说如上sql 不准确，所以不建议使用，防止部份DBMS不支持。

c, 常见错误3， group by 子句的执行结果会排序吗？

结果是不会，因此如果需要一定的顺序，该展示顺序需要在select子句中通过 order by 子句来指定

d, 常见错误4， 在where子句中使用聚合函数，是不正确的。

例子如下，假设我们按照商品种类统计数据行数

如果我们想要在如上基础的数据中，查找出恰好包含2行数据的组该怎么办呢？满足要求的是渔夫和办公用品，如果我们最先想到是通过where 子句来筛选，执行语句如下

更具体的error如下

org.jkiss.dbeaver.model.sql.DBSQLException: SQL Error [1111] [HY000]: Invalid use of group function
	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:133)
	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:582)
	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$1(SQLQueryJob.java:491)
	at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:190)
	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:498)
	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:934)
	at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:3928)
	at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:123)
	at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:190)
	at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:121)
	at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:5140)
	at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:105)
	at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)
Caused by: java.sql.SQLException: Invalid use of group function
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:763)
	at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648)
	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:330)
	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:131)
	... 12 more

 为何会出现这样情况呢？ 就是因为count函数被写在了where子句中。更加细致的解释是，这点和上方一个例子实质一样，即sql语句的执行顺序。

sql 的执行顺序是where 子句——>group by 子句——>select 子句

我们期望的count 是针对group by 切分表之后的group 内数据统计行数，但如上coun(*)直接放在where子句中，是对全表进行统计。整个逻辑是不合理的。

实际上，只有select子句，having 子句，以及order by子句中可以使用count等聚合函数

3, 为聚合结果指定条件

使用count函数等对表中数据进行聚合操作时，为其指定条件的不是where子句，而需要使用having子句。

聚合函数可以在select子句、having子句和order by子句中使用

having子句要写在group by子句之后

where子句用来指定数据行对条件，having子句用来指定分组的条件

1）Having 子句

在之前那个示例中，我们想要取出分组后，每组恰好包含2行数据的特定组的数据，但是使用group by 分组，然后通过where子句来限定条件的方法无法实现。那如何才能取出“聚合结果正好为2行的组”呢？

注意：where子句只能指定记录（行）的条件，而不能用来指定组的条件。例如“数据行数（count）为2行” 或者“平均值（AVG）为500”等。此时便可以用Having子句来实现了。

Having子句的语法如下

select <列名1>,<列名2>,<列名3>,...
from <表名>
group by <列名1>,<列名2>,<列名3>,...
Having <分组对应的条件>

Having 子句必须写在Group by 子句之后，其在DBMS内部执行的顺序也排在group by子句之后

》使用Having子句时，select语句的写法顺序如下

select——> from ——> where ——> group by ——> Having

 注意，有Having 子句在DBMS内部的执行顺序如下

from ——> where ——> group by——> Having ——> Select 

使用示例

示例1，

对比之前的结果

 示例2，

 如果我们还是通过商品种类对表进行分组，但是要对每组的销售单价进行统计

如果条件变成了“销售单价的平均值大于等于2500”

2）Having 子句的构成要素

Having 子句和包含group by子句时的select 子句一样，能够使用的要素也有一定的限制。限制内容也是完全相同的。Having 子句中也只能如下如下3种要素，

a 常数

b 聚合函数

c group by 子句中指定的列名（即聚合键）

在此举一个反例

即shohin_mei 列必须包含在group by子句当中，或者在聚合函数中使用

 3）相比于having子句，更适合写在where子句中的条件

注意，此处先写结论

where 子句 = 指定行所对应的条件

Having子句 = 指定组所对应的条件

假设一个条件既可以写在where 子句中，也可以写在having子句中，可以按照上述标准进行分类。

如下两个测试结果

如上，虽然结果都对，但是还是建议聚合键所对应的条件应该写在where 子句中， why?

由于where 子句和having子句的执行速度问题

在where 子句和having子句中都可以使用的条件，最好写在where子句中的另一个理由与性能即执行速度有关系。为了得到相同的结果，将条件写在where子句中要比写在having子句中的处理速度更快，返回结果所需时间更短。

为了理解其中的原因，就要从DBMS的内部运行机制来考虑。使用count函数等对表中的数据进行聚合操作时，DBMS内部就会进行排序处理。排序处理会大大增加机器的负担，此即所谓高负荷的处理。因此，只有尽可能减少排序的行数，才能增加处理速度。

通过where 子句指定条件时，由于排序之前就对数据进行了过滤，所以能够减少排序的数据量。但having 子句是在排序之后才对数据进行分组的，因此与在where子句中指定条件比起来，需要排序的数量就会多得多。虽然DBMS的内部处理不尽相同，但是对于排序来说，基本上都是一样的。

此外，where子句耕具速度又是的另一个理由是，可以对where子句指定条件所对应的列创建索引，这样也可以大幅提高处理速度。创建索引是一种非常普遍的提高DBMS性能的方法，效果也是粉明显，这对where子句来说也十分有利。

4，对查询结果进行排序

使用order by子句对查询结果进行排序；

在order by 子句中列名的后面使用关键字ASC可以进行升序排序，使用DESC关键字可以进行降序排序；

order by 子句中可以指定多个排序键；

排序键中包含null时，会在开头或末尾进行汇总

order by子句中可以使用select子句中定义的列的别名

order by 子句中可以使用select子句中未出现的列或者聚合键

order by子句中不能使用列的编号

1）在没有order by 的select语句中，返回结果是个随机的，如果出现某个特定的排序，也只是个偶然的事件。所以通常从表中抽取数据时，如果需要按照某些列进行一定的排序，则需要用到order by子句。

order by 子句的语法如下

select <列名1>,<列名2>,<列名3>,...
from <表名>
order by <排序基准列1>,<排序基准列2>,...

例如，我们按照销售单价由低到高（升序）进行排列

注意：不论何种情况，order by子句都需要写在select语句的末尾。这是因为对数据进行排序的操作必须在结果即将返回时执行。order by子句中书写的列名称为排序键。该子句与其它子句的顺序关系如下所示

》子句的书写顺序如下

1 select子句——>2 from 子句——>3 where子句——>4 group by子句——>5 having 子句——>6 order by 子句

2）指定升序或者降序

与上述示例想对，想要按照销售单价由高到低，也就是降序排列时，在列名后使用Desc

Note：在使用升序排列时，通常会把升序符号ASC 给省略，可能大部分使用场景为升序排列。但正式的用法应该是使用关键字的。其中ASC是 ascendent 缩写，DESC是descendent的缩写

3）指定多个排序键

如上例子，我们以销售单价进行升序或者降序排列时，有2个单价都为500的商品，如果我们需要对这两条记录（行）进行排序，比如以商品ID也进行降序排序（如上，看起来是升序），该怎么操作呢？

 即，我们可以对每个排序键单独指定升降规则。同时，第一个排序键优先排序，之后再对第一排序键相同的结果按照第二第三的规则进行排序

4）Null数据的排序

上方排序中提到的排序键所在列，所有的数据都是一个确定有效的数字（没有null数据），如果排序键是含有null数据的情况，结果会怎么样呢？

如上，null数据都放在了最后。

实际上，在不同的DBMS中，处理结果可能是不同的，null数据会集中放在开头，或者放在末尾。

5）在排序键中可以使用显示用别名

之前提到，group by子句中不能使用select子句中定义的别名，但是在order by子句中却是允许使用的。如下示例

为何可以在order by子句中可以使用呢？

这是因为SQL语句在DBMS内部的执行顺序被掩盖起来了，select语句按照子句为单位的执行顺序

From——>where——>group by——>Having——>select——>order by

这是一个粗略的统计，具体还是要看不同的DBMS内部执行。一定要记住的是select子句的执行顺序在group by子句之后，order by 子句之前。因此order by子句可以使用select子句中定义的别名。

6）order by 子句中可以使用的列

order by子句中可以使用存在于表中，但并不包含在select子句之中的列。

如上，shohin_id 未出现在select子句中，但可以放在order by 中

order by 还可以使用聚合函数，如下

7) 不要使用列编号

何为列编号？

如上图红色框线标记的部份，这个就是sql 返回结果时标记区分不同记录的列编号（行号），该编号不可以被用在sql语句中。

到此，最最简单的sql语句的查询有了，感觉sql语句中子句的执行顺序，以及聚合函数，聚合函数指定条件，聚合函数容易犯的错误总是迷糊，希望这次加深理解后，后面使用能顺利点。之后继续学习，更改数据，以及更复杂点的查询吧！