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

野性酷女 2024-03-22 11:34 37阅读 0赞

**目录**

一、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][http_t.csdn.cn_bkstx]

比如：

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

![25110aadb5cd43f89743ebe0b329eb6c.png][]

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

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

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

## 一、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;

[http_t.csdn.cn_bkstx]: http://t.csdn.cn/bkstx
[25110aadb5cd43f89743ebe0b329eb6c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/a582f4f262894ecab728bc6d542d83ff.png