14.分组函数

1.分组函数

分组函数是操作一个分组的结果集，将行分组，按照组产生一个结果集，常用的分组函数有：avg，count，max，min，stddev，sum，variance

hr@ORCLPDB01 2023-02-19 15:58:03> select avg(salary),max(salary),min(salary),sum(salary)
  2  from employees
  3  where job_id like '%REP%';

AVG(SALARY) MAX(SALARY) MIN(SALARY) SUM(SALARY)
----------- ----------- ----------- -----------
 8272.72727	  11500        6000	 273000

Elapsed: 00:00:00.01

--对数字，字符，日期类型的可以使用min，max
hr@ORCLPDB01 2023-02-19 15:58:51> select min(hire_date),max(hire_date)
  2  from employees;

MIN(HIRE_DATE)	    MAX(HIRE_DATE)
------------------- -------------------
2001-01-13 00:00:00 2008-04-21 00:00:00

Elapsed: 00:00:00.00

--count函数
hr@ORCLPDB01 2023-02-19 16:01:29> select count(*)
     from employees
     where department_id = 50

  COUNT(*)
----------
	45

Elapsed: 00:00:00.01
hr@ORCLPDB01 2023-02-19 16:01:31> select count(commission_pct) from employees where department_id = 80;

COUNT(COMMISSION_PCT)
---------------------
		   34

distinct关键字

--返回非空唯一总行数
--显示唯一部门数量
hr@ORCLPDB01 2023-02-19 16:02:54> select count(distinct department_id) from employees ;

COUNT(DISTINCTDEPARTMENT_ID)
----------------------------
			  11

空值

--默认分组忽略空值
--nvl可以强制分组函数处理空值
hr@ORCLPDB01 2023-02-19 16:04:25> select avg(commission_pct) from employees;

AVG(COMMISSION_PCT)
-------------------
	 .222857143

Elapsed: 00:00:00.01
hr@ORCLPDB01 2023-02-19 16:05:48> select avg(nvl(commission_pct,0)) from employees;

AVG(NVL(COMMISSION_PCT,0))
--------------------------
		.072897196

Elapsed: 00:00:00.01

2.创建分组数据库

hr@ORCLPDB01 2023-02-19 16:06:06> select department_id,avg(salary)
  2  from employees
  3  group by department_id;

DEPARTMENT_ID AVG(SALARY)
------------- -----------
	   50  3475.55556
	   40	     6500
	  110	    10154
	   90  19333.3333
	   30	     4150
	   70	    10000
		     7000
	   10	     4400
	   20	     9500
	   60	     5760
	  100  8601.33333
	   80  8955.88235

12 rows selected.

Elapsed: 00:00:00.00

--分组列可以不出现在select列表中
hr@ORCLPDB01 2023-02-19 16:08:57> r
  1  select avg(salary)
  2  from employees
  3* group by department_id

AVG(SALARY)
-----------
 3475.55556
       6500
      10154
 19333.3333
       4150
      10000
       7000
       4400
       9500
       5760
 8601.33333
 8955.88235

12 rows selected.

Elapsed: 00:00:00.01

 多列分组例子

hr@ORCLPDB01 2023-02-19 16:08:59> select department_id,job_id,sum(salary)
  2  from employees
  3  where department_id > 40
  4  group by department_id,job_id
  5  order by department_id;

DEPARTMENT_ID JOB_ID	 SUM(SALARY)
------------- ---------- -----------
	   50 SH_CLERK	       64300
	   50 ST_CLERK	       55700
	   50 ST_MAN	       36400
	   60 IT_PROG	       28800
	   70 PR_REP	       10000
	   80 SA_MAN	       61000
	   80 SA_REP	      243500
	   90 AD_PRES	       24000
	   90 AD_VP	       34000
	  100 FI_ACCOUNT       39600
	  100 FI_MGR	       12008
	  110 AC_ACCOUNT	8300
	  110 AC_MGR	       12008

13 rows selected.

Elapsed: 00:00:00.01

注意：

      1.限制分组函数结果不能用where，必须用having

      2.where子句中不能使用分组函数

3.hangving语法

--行被分组
--分组函数已使用
--匹配having的结果被显示
hr@ORCLPDB01 2023-02-19 16:11:12> select department_id , max(salary) 
  2  from employees
  3  group by department_id 
  4  having max(salary) > 10000;

DEPARTMENT_ID MAX(SALARY)
------------- -----------
	  110	    12008
	   90	    24000
	   30	    11000
	   20	    13000
	  100	    12008
	   80	    14000

6 rows selected.

Elapsed: 00:00:00.00

嵌套函数

hr@ORCLPDB01 2023-02-19 16:16:39> select max(avg(salary)) from employees group by department_id;

MAX(AVG(SALARY))
----------------
      19333.3333

Elapsed: 00:00:00.00