Oracle学习(四)----分组函数

最新推荐文章于 2024-09-07 09:29:09 发布

原创最新推荐文章于 2024-09-07 09:29:09 发布 · 443 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Oracle

数据库专栏收录该内容

12 篇文章

订阅专栏

本文介绍SQL中的分组函数及其应用场景，包括AVG、COUNT、MAX、MIN和SUM等函数的使用方法。探讨了如何通过GROUP BY进行数据分组，以及HAVING子句在分组数据过滤中的应用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1 什么是分组函数
  分组函数作用于一组数据，并对一组数据返回一个值。
  1.1 组函数类型
      AVG / COUNT / MAX / MIN / SUM
      
      select max(sal) 最大值, min(sal) 最小值, avg(sal) 平均值, sum(sal) 总, count(sal) 计数 from emp
	
        最大值     最小值     平均值         总       计数
    ---------- ---------- ---------- ---------- ----------
          8000        800 2468.33333      37025         15
  1.2 当组函数和null在一起，组函数会自动过滤空值

      1.2.1：

	SQL> select avg(comm) 方法一, sum(comm)/count(comm) 方法二, sum(comm)/count(*) 方法三 from emp;

   	 方法一     方法二     方法三
	---------- ---------- ----------
 	      550        550  146.666667

      1.2.2：组函数会自动过滤空值

	SQL> select count(comm), count(*) from emp;

	COUNT(COMM)   COUNT(*)
	----------- ----------
      	    4         15

      1.2.3：修正组函数的虑空(利用nvl修正count虑空) 

	select count(nvl(comm, 0)), count(*) from emp;

	COUNT(NVL(COMM,0))   COUNT(*)
	------------------ ----------
     	           15         15
	
  1.3 补充

	SQL> select count(distinct deptno) from emp;

	COUNT(DISTINCTDEPTNO)
	---------------------
                    	3

2 分组数据
  2.1 求各个部门的平均工资
      思想：需要把各个部门的数据划分......10部门 20部门 30部门.......分组......


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

	    DEPTNO   AVG(SAL)
	---------- ----------
        	30 1566.66667
	        20       2175
       	 	10     4187.5
  2.2  注意：
	(1)select 检索的列必须位于group by后面的集合中，但是包含在group by子句中的列不必包含在select列表中；
        (2)组函数设计的本意：必须要在分组数据之上进行结果集合的检索，解释了第一条

  2.3 group by后面有多列
     求各个部门的每一个工种的平均工资（分两组）

	SQL> select deptno, job, avg(sal), count(deptno) from emp
	  2  group by deptno, job
 	  3  order by 1;

 	   DEPTNO JOB         AVG(SAL) COUNT(DEPTNO)
	---------- --------- ---------- -------------
  	     	10 CLERK           1300             1
   	    	10 MANAGER         2450             1
       		10 PRESIDENT       5000             1
        	10                 8000             1
        	20 ANALYST         3000             2
        	20 CLERK            950             2
        	20 MANAGER         2975             1
        	30 CLERK            950             1
        	30 MANAGER         2850             1
        	30 SALESMAN        1400             4
3 分组过滤
  查询平均工资>2000的部门
  思路：先分组，再过滤
	SQL> ed
	已写入 file afiedt.buf

 	1  select deptno, avg(sal) from emp
  	2  group by deptno
  	3* having avg(sal) > 2000
	SQL> /

    	DEPTNO      AVG(SAL)
	---------- ----------
        	20       2175
        	10     4187.5

4 having子句和where子句的区别，where子句中不能使用分组函数，having子句中可以使用分组函数

  例：求10号部门的平均工资

  方法一：先分组，再过滤

	SQL> select deptno, avg(sal) from emp
  	2  group by deptno
  	3  having deptno = 10;

    DEPTNO   AVG(SAL)
---------- ----------
        10     4187.5

  方法二：先过滤，再分组（这种方法好!）

	SQL> select deptno,avg(sal) from emp
  	2  where deptno = 10
  	3  group by deptno;

    	DEPTNO   AVG(SAL)
	---------- ----------
        	10     4187.5
  错误示例：
	SQL> select deptno, avg(sal) from emp
  	2  where deptno = 10;
	select deptno, avg(sal) from emp
       		*
	第 1 行出现错误:
	ORA-00937: 不是单组分组函数
  解析：若果没有group by，则deptno会返回若干行数据，而avg(sal)只会返回一行数据，这种多对一的情况是不可能实现的！


  
5 oracle优化
  5.1：oracle解析逻辑表达式的方向是从右向左
      select * from emp where (deptno = 10) and (deptno = 20) and (deptno = 30);
                                         <------------<-------------<--------



语法次序：
	select ...... from ......
	where ......
	group by ......
	having ......
	order by ......