Oracle-24-聚合函数&group by的使用

一、聚合函数

1.count——返回找到的记录数；

2.min——返回一个数字列或计算列的最小值；

3.max——返回一个数字列或计算列的最大值；

4.sum——返回一个数字列或计算列的综合；

5.avg——对一个数字列或计算列求平均值；

 

例1：练习count函数

解：查询当前登录用户下的student表全部信息：

用count查询记录数：

由此可以看出，count()括号既可以传*也可以传列名，如果传入列名就是计算这一列非空数据的个数，比如这时在student表中插入一个新数据：

这时候再用count(*)和count(sname)查询：

因为sname列最后一个数据为null，所以conut(sname)比count(*)少1。

 

例2：练习min和max函数

解：

 

例3：练习sum函数

解：

发现报错，因为sum只能用于数字类型数据，如下则不报错：

 

例4：练习avg函数

解：

同样avg只能用于数字类型的数据，如下则不报错：

【注意】avg只用于非空数据求平均，比如有11个数据，前10个有值，最后一个是null，那么avg是将前10个相加之后除以10，而不是除以11。

 

二、GROUP BY

1.应用场景：

当一个SQL语句中有某表的一列+一个聚合函数，那么就需要用group by，否则报错，如下：

 

2.Group by子句定义（或作用）：Group by主要可以用于将行分组为具有相同列值的多个部分。

如：SQL>select job from emp group by job;

说白了，group by就是将值相同的数据分在一组，如现在的student表：

那么现在用group by：

 

例5：练习group by函数。

解：

上图这条，就是先将student表按照ssex分组，男的一组，女的一组，不男不女一组，然后再调用聚合函数count计算每组个数。

【注意】分组group by一般与聚合函数连用，即先分组再聚合（如先分组再求和、先分组再求平均等等）

 

3.group by如果只是对行进行单独的分组使用，那么使用意义不大。在复杂的SQL查询中Group by子句大多数时候与聚合函数一起出现。

如：查询各个职位的平均工资是多少？

SQL>select job, avg(sal) from emp group by job;

上述SQL语句执行思路：

按照job列相同值为一组的原则，使用group by子句将表中数据分组，使用avg函数对每组数据分别求平均。

 

【group by总结】

如果查询中包含一个聚合函数（如下面的sum函数），而所选择的列（如下面的ssex）并不在聚合函数（如下面的sum函数）中，那么这些列（如下面的ssex）就必须在group by子句中；否则就会报错：ORA-00937：不是单租分组函数。

如：SQL>select ssex, sum(sage) from student group by ssex;简单说，就是ssex没被聚合函数sum修饰，那么必须放在group by后面，否则必报错。

 

例6：在当前登录用户下的student表中查询男生和女生的平均年龄。

解：

【总结】

SQL>select★,聚合函数(△)  from A group by★;

其中★填写的很简单，根据谁分组就写谁，比如根据性别分组，这★就是ssex组。执行的时候是先根据★分组，然后再在每个组里面计算聚合函数。

 

例7：在当前登录用户下的student表中，计算李姓学生的平均年龄。

解：

注意where的使用，更注意like的使用。

那么如果希望查询结果显示“李”怎么办？如下：

substr(sname,1,1)是从sname列每个数据的第1个位置开始取子串，只取1个长度的子串，然后去匹配where sname like ‘李%’条件，根据substr(sname,1,1)分组，用聚合函数avg计算平均年龄。