目录
五、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;