Oracle中的单列函数-聚合函数

目录

一、distinct -去重

二、group by-分组

三、having -分组之后的筛选

四、order by --排序

五、listagg(列,'分隔符')within group(order by 列)

---

Oracle中的聚合函数是用于对一组数据进行聚合计算的函数，包括SUM、AVG、MAX、MIN、COUNT等。它们的作用是对数据进行统计分析和汇总，用于得出数据的总体情况和特征，例如计算平均值、总和、最大值、最小值、数量等，以便进行决策和分析。聚合函数可以用于SELECT语句中，对查询结果进行聚合计算，返回一个聚合结果。

聚合函数又称为分组函数，有以下几种常用的聚合函数：

①max(列) 最大值

②min(列) 最小值

③avg(列) 平均值 ，只能是数值型

④sum(列) 求和 ，只能是数值型

⑤count(列)计数 ，不分组计算全表，count（）函数的用法可查看http://t.csdn.cn/bkstx

比如： 

select min(sal),max(sal),avg(sal),sum(sal),count(sal)
from emp;

聚合函数适用于需要对数据进行统计和计算的场景，如：

• 统计某个表中某列的行数；
• 计算某个表中某列的总和、平均值、最小值、最大值等；
• 对查询结果进行分组并计算每个分组的数据的聚合值。

下面结合聚合函数描述下分组-排序-去重-。

一、distinct -去重

DISTINCT用于查询不同的值。它可以用于SELECT语句中指定要查询的列，以返回不同的值。例如，以下查询将返回不同的城市名称：

SELECT DISTINCT city FROM customers;

如果要查询多列并返回不同的值，可以在DISTINCT关键字后面指定多个列名，例如：

SELECT DISTINCT city, country FROM customers;

注意：

使用DISTINCT会增加查询的开销，因为Oracle需要对结果集进行去重操作。在查询大量数据时，使用DISTINCT可能会导致性能问题。

二、group by-分组

GROUP BY用于将查询结果按照一个或多个列进行分组。它可以用于SELECT语句中指定要分组的列，以便对每个分组计算聚合函数。例如，以下查询将返回每个城市的顾客数量：

SELECT city, COUNT(*) as customer_count 
FROM customers GROUP BY city;

在上面的查询中，我们使用了COUNT函数来计算每个城市中的顾客数量，并使用GROUP BY语句将结果按城市进行分组。

如果要按多个列进行分组，可以在GROUP BY语句中指定多个列名，例如：

SELECT city, country, COUNT(*) as customer_count 
FROM customers 
GROUP BY city, country;

注意：

在GROUP BY语句中指定的列必须出现在SELECT语句中，或者是聚合函数的参数。否则，Oracle会报错。

 再举例运用一下：

--查询每个部门的人数以及平均工资
select deptno,count(ename) 人数,avg(sal)
from emp
group by deptno;

分组后select后只能写分组的列和聚合函数

查询每个部门每种职位入职最早、最晚的入职日期

select deptno,job,min(hiredate),max(hiredate)
from emp
group by deptno,job;

三、having -分组之后的筛选

HAVING用于对分组结果进行过滤。它可以用于SELECT语句中，在GROUP BY语句之后和ORDER BY语句之前进行过滤操作。例如，以下查询将返回城市中顾客数量大于10的城市和顾客数量：

SELECT city, COUNT(*) as customer_count 
FROM customers 
GROUP BY city 
HAVING COUNT(*) > 10;

在上面的查询中，使用了HAVING语句来对分组结果进行过滤，只返回顾客数量大于10的城市和顾客数量。

注意：

HAVING语句只能用于对分组结果进行过滤，而不能用于对单个行进行过滤。如果要对单个行进行过滤，应该使用WHERE语句。在使用HAVING语句时，必须先使用GROUP BY语句对查询结果进行分组。

where 分组前的筛选条件

having 分组后的筛选条件

再举例运用一下：

查询每种职位的人数,只显示人数大于3的

select job,count(1)
from emp
group by job
having count(1)>3;

查询部门和每个部门的最高工资,只显示最高工资大于2900的部门

select deptno,max(sal)
from emp
group by deptno
having max(sal)>2900;

查询最早的入职日期在1981-1-1至1981-6-1 之间的职位

select job,min(hiredate)
from emp
group by job
having min(hiredate) between date'1981-1-1' and date'1981-6-1';

四、order by --排序

写在最后,最后执行；asc 升序 ，不写默认升序；desc 降序。

默认null最大 ，可以通过nulls first,nulls last 调整空的位置；

order by 后面可以加数字， 表示第几列。

select ename,sal,comm
from emp
order by 2 desc,3 desc;

select ename
from emp
order by 1;

举例运用一下：

查询每个部门的平均工资,按照部门编号升序排序

select deptno,avg(sal)
from emp
group by deptno
order by deptno;

查询每个职位的最高工资,只显示最高工资不小于2500的,按照最高工资从高到低排序

select job,max(sal)
from emp
group by job
having max(sal)>=2500
order by 2 desc;

查询每个部门中名字包含A的员工人数只显示人数小于三的

select deptno,count(1)
from emp
where ename like '%A%'
group by deptno
having count(1)<3;
--或
select deptno,count(case when ename like '%A%' then 1 end) a
from emp
group by deptno
having count(case when ename like '%A%' then 1 end)<3

统计每年每个月入职的人数,人数大于1的月份,按照人数大小排序

select to_char(hiredate,'yyyy-mm'),count(1)
from emp
group by to_char(hiredate,'yyyy-mm')
having count(1)>1
order by 2;

五、listagg(列,'分隔符')within group(order by 列)

分组连接字符串

查询每个部门的员工姓名,用/隔开，按照名字首字母排序

select deptno,listagg(ename,'/')within group(order by ename) a
from emp
group by deptno;

查询每个经理下属的 员工编号-员工姓名 中间用/隔开

select mgr,listagg(empno||'-'||ename,'/')within group(order by ename) a
from emp
where mgr is not null
group by mgr;